Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-27483

[ANSI JOIN] query says no index available for term, however it is there

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 5.5.0
    • 5.5.0
    • query
    • 5.5.0-1632
    • Untriaged
    • Unknown

    Description

      This query:

      SELECT t_2.*  FROM  multiple_table_db_492_simple_table_10 t_4  INNER JOIN  multiple_table_db_492_simple_table_4 t_2 ON ( NOT ((t_4.char_field1 = t_2.char_field1 AND t_4.int_field1 = t_2.int_field1)) )        WHERE  (NOT ((t_2.bool_field1 = true AND t_2.decimal_field1 < 4878))) OR ((((t_2.bool_field1 != true AND t_4.int_field1  IN [  0 , 12 , 21 , 32 , 39  ])) OR ((t_2.decimal_field1 != 4878) OR (t_4.decimal_field1 >= 4821))) AND (NOT (((NOT (t_4.bool_field1) OR t_2.char_field1 LIKE "N")) AND ((t_2.decimal_field1 BETWEEN 5 and 9985 AND t_2.varchar_field1 IS NULL AND t_2.bool_field1 = false))))) LIMIT 100

      Returns this error:

       

      [
      {
      "code": 4330,
      "msg": "No index available for ANSI join term t_2",
      "query_from_user": "SELECT t_2.* FROM multiple_table_db_492_simple_table_10 t_4 INNER JOIN multiple_table_db_492_simple_table_4 t_2 ON ( NOT ((t_4.char_field1 = t_2.char_field1 AND t_4.int_field1 = t_2.int_field1)) ) WHERE (NOT ((t_2.bool_field1 = true AND t_2.decimal_field1 < 4878))) OR ((((t_2.bool_field1 != true AND t_4.int_field1 IN [ 0 , 12 , 21 , 32 , 39 ])) OR ((t_2.decimal_field1 != 4878) OR (t_4.decimal_field1 >= 4821))) AND (NOT (((NOT (t_4.bool_field1) OR t_2.char_field1 LIKE \"N\")) AND ((t_2.decimal_field1 BETWEEN 5 and 9985 AND t_2.varchar_field1 IS NULL AND t_2.bool_field1 = false))))) LIMIT 100"
      }
      ]

       

      However I have an index on t_2.char_field1 and t_2.int_field1, is the NOT somehow interfering here? Other indexes are t_4 each field individually as well as each field in one big index, t_2 each field individually as well as each field in one big index. You can use the backup I gave in the last bug filed, it should be the same environment more or less. Logs attached 

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          bingjie.miao Bingjie Miao added a comment -

          Build 5.5.0-1665 or later contains the fix. For some reason the build comment that normally appears did not this time.

          bingjie.miao Bingjie Miao added a comment - Build 5.5.0-1665 or later contains the fix. For some reason the build comment that normally appears did not this time.

          Build couchbase-server-5.5.0-1665 contains query commit 7f4876a27c22cd24976dcf52ecd337a80d4ee54d with commit message:
          MB-27483 Use on clause only for index selection if where clause interferes
          https://github.com/couchbase/query/commit/7f4876a27c22cd24976dcf52ecd337a80d4ee54d

          build-team Couchbase Build Team added a comment - Build couchbase-server-5.5.0-1665 contains query commit 7f4876a27c22cd24976dcf52ecd337a80d4ee54d with commit message: MB-27483 Use on clause only for index selection if where clause interferes https://github.com/couchbase/query/commit/7f4876a27c22cd24976dcf52ecd337a80d4ee54d
          ajay.bhullar Ajay Bhullar added a comment -

          Verified in 5.5.0-2520, now this query returns 100 results

          ajay.bhullar Ajay Bhullar added a comment - Verified in 5.5.0-2520, now this query returns 100 results

          People

            ajay.bhullar Ajay Bhullar
            ajay.bhullar Ajay Bhullar
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty