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

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.0
    • Fix Version/s: 5.5.0
    • Component/s: query
    • Labels:
    • Environment:
      5.5.0-1632
    • Triage:
      Untriaged
    • Is this a Regression?:
      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

          Hide
          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.

          Show
          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.
          Hide
          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

          Show
          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
          Hide
          ajay.bhullar Ajay Bhullar added a comment -

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

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

            People

            • Assignee:
              ajay.bhullar Ajay Bhullar
              Reporter:
              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

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.