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

    • 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.1.0-1480
    • Triage:
      Untriaged
    • Is this a Regression?:
      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

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

          Activity

          Hide
          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

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

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

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

            People

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

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