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

          Build 5.1.0-1501 contains query commit e4451ab61286a7035afa6b67474b983dc1d2dde6 with commit message:
          MB-27109 Unexpected error in ANSI JOIN if left-hand-side of join is a subquery
          https://github.com/couchbase/query/commit/e4451ab61286a7035afa6b67474b983dc1d2dde6

          build-team Couchbase Build Team added a comment - Build 5.1.0-1501 contains query commit e4451ab61286a7035afa6b67474b983dc1d2dde6 with commit message: MB-27109 Unexpected error in ANSI JOIN if left-hand-side of join is a subquery https://github.com/couchbase/query/commit/e4451ab61286a7035afa6b67474b983dc1d2dde6
          ajay.bhullar Ajay Bhullar added a comment -

          Verified fix in 5.1.0-1501, tracking ticket for test automation CBQE-4400

          ajay.bhullar Ajay Bhullar added a comment - Verified fix in 5.1.0-1501, tracking ticket for test automation CBQE-4400

          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