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

[ANSI JOIN] a query that has the correct index built says there is no available index on term

    XMLWordPrintable

Details

    • Bug
    • Resolution: Fixed
    • Major
    • 5.5.0
    • 5.5.0
    • query
    • 5.1.0-1480
    • Untriaged
    • Unknown

    Description

      Related to MB-26649, with the exact environment described in that bug, running the query in that bug now yields this error:

      [
      {
      "code": 4330,
      "msg": "No index available for ANSI join term t2",
      "query_from_user": "select * from (select int_field1 from multiple_table_db_7916_simple_table_1 WHERE (select bool_field1 from multiple_table_db_7916_simple_table_1 t10 WHERE bool_field1 = false)) t1 JOIN multiple_table_db_7916_simple_table_10 t2 ON t1.int_field1 = t2.int_field1"
      }
      ]

      However, there is an index on the field t2.int_field:

      CREATE INDEX int_field1 on multiple_table_db_7916_simple_table_10(int_field1) 

      The query:

      select * from (select int_field1 from multiple_table_db_7916_simple_table_1 WHERE (select bool_field1 from multiple_table_db_7916_simple_table_1 t10 WHERE bool_field1 = false)) t1 JOIN multiple_table_db_7916_simple_table_10 t2 ON t1.int_field1 = t2.int_field1

       

      To reproduce the environment (since we had problems with backup) please run this rqg command(please pull an updated testrunner to ensure you can properly run this command) and then create an index on the int_field1 of the simple_table_10: 

      ./testrunner -i VMs.ini -p gsi_type=plasma -t rqg.test_rqg.RQGTests.test_rqg_concurrent,test_file_path=b/resources/rqg/multiple_table_db/query_test_using_templates/queries_joins_50000.txt.zip,database=multiple_table_db,reset_database=True,concurreny_count=10,index_quota_percent=30,record_failure=False,password=password,use_mysql=True,replicas=0,total_queries=5,failure_record_path=/tmp,skip_cleanup=false,subquery=False,ansi_joins=True,create_secondary_meta_indexes=True,create_secondary_indexes=True

      Attachments

        For Gerrit Dashboard: MB-27109
        # Subject Branch Project Status CR V

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty