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
    • Resolution: Fixed
    • Major
    • 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

        Activity

          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:

              PagerDuty