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

[ANSI JOINS] Plan error: buildOrScanNoPushdown: missing OR subterm

    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-1511
    • Triage:
      Untriaged
    • Is this a Regression?:
      Unknown

      Description

      The setup is the traditional rqg ansi join setup, the 5 buckets with a secondary key on each field in a bucket and a secondary key that contains each field in the bucket. Same buckets as were backuped in MB-27201.

      This query:

      SELECT  t_3.*  FROM  multiple_table_db_2143_simple_table_1 t_3  LEFT JOIN  multiple_table_db_2143_simple_table_10 t_4 ON ( ((t_3.decimal_field1 >= t_4.decimal_field1) AND (t_3.decimal_field1 < t_4.decimal_field1) OR t_3.char_field1 IS NOT NULL) )      WHERE  ((t_4.bool_field1 AND t_3.primary_key_id IS NOT NULL)) OR ((NOT (t_3.bool_field1) AND t_3.decimal_field1 = 4968))

       

      returns this error:

      [
      {
      "code": 4321,
      "msg": "Plan error: buildOrScanNoPushdown: missing OR subterm",
      "query_from_user": "SELECT t_3.* FROM multiple_table_db_102_simple_table_1 t_3 LEFT JOIN multiple_table_db_102_simple_table_10 t_4 ON ( ((t_3.decimal_field1 >= t_4.decimal_field1) AND (t_3.decimal_field1 < t_4.decimal_field1) OR t_3.char_field1 IS NOT NULL) ) WHERE ((t_4.bool_field1 AND t_3.primary_key_id IS NOT NULL)) OR ((NOT (t_3.bool_field1) AND t_3.decimal_field1 = 4968))"
      }
      ]

      The explain is the same error. Logs will be 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 -

          For this query an error will be returned after the fix:

          No index available for ANSI join term t_4

          The currently returned error is an internal error and is incorrect, however, this query cannot be properly executed, since the join filter is an OR clause, and the second arm of the OR only references t_3, not t_4, and thus no index scan on t_4 can be generated using this OR clause.

          For the query to work properly, if you are joining t_3 with t_4, with an OR clause, you need to make sure every subterm of the OR must contain a predicate on t_4.

          Show
          bingjie.miao Bingjie Miao added a comment - For this query an error will be returned after the fix: No index available for ANSI join term t_4 The currently returned error is an internal error and is incorrect, however, this query cannot be properly executed, since the join filter is an OR clause, and the second arm of the OR only references t_3, not t_4, and thus no index scan on t_4 can be generated using this OR clause. For the query to work properly, if you are joining t_3 with t_4, with an OR clause, you need to make sure every subterm of the OR must contain a predicate on t_4.
          Hide
          bingjie.miao Bingjie Miao added a comment -

          Another point to be made: although we support ANSI JOIN with non-equality predicate, you need to realize that we'll be doing range index scans for each outer document of the join, and that can have in some cases bad performance for the query. It's ok to generate such queries in RQG, just don't make them a large fraction, otherwise you'll see bad performance for RQG. It's probably better to mix equality predicate with non-equality predicates.

          Show
          bingjie.miao Bingjie Miao added a comment - Another point to be made: although we support ANSI JOIN with non-equality predicate, you need to realize that we'll be doing range index scans for each outer document of the join, and that can have in some cases bad performance for the query. It's ok to generate such queries in RQG, just don't make them a large fraction, otherwise you'll see bad performance for RQG. It's probably better to mix equality predicate with non-equality predicates.
          Hide
          bingjie.miao Bingjie Miao added a comment -

          Fix just merged in. Please wait for the build number containing the fix.

          Show
          bingjie.miao Bingjie Miao added a comment - Fix just merged in. Please wait for the build number containing the fix.
          Hide
          build-team Couchbase Build Team added a comment -

          Build 5.1.0-1545 contains query commit b1f73676cb3073b1d94af51254074f6ba6d3f518 with commit message:
          MB-27230 incorrect error message returned for ANSI JOIN with OR clauses
          https://github.com/couchbase/query/commit/b1f73676cb3073b1d94af51254074f6ba6d3f518

          Show
          build-team Couchbase Build Team added a comment - Build 5.1.0-1545 contains query commit b1f73676cb3073b1d94af51254074f6ba6d3f518 with commit message: MB-27230 incorrect error message returned for ANSI JOIN with OR clauses https://github.com/couchbase/query/commit/b1f73676cb3073b1d94af51254074f6ba6d3f518
          Hide
          ajay.bhullar Ajay Bhullar added a comment -

          New behavior is working as described in 5.5.0-2520

          [
          {
          "code": 4330,
          "msg": "No index available for ANSI join term t_4",
          "query_from_user": "SELECT t_3.* FROM multiple_table_db_32243823_simple_table_1 t_3 LEFT JOIN multiple_table_db_32243823_simple_table_10 t_4 ON ( ((t_3.decimal_field1 >= t_4.decimal_field1) AND (t_3.decimal_field1 < t_4.decimal_field1) OR t_3.char_field1 IS NOT NULL) ) WHERE ((t_4.bool_field1 AND t_3.primary_key_id IS NOT NULL)) OR ((NOT (t_3.bool_field1) AND t_3.decimal_field1 = 4968))"
          }
          ]

          Show
          ajay.bhullar Ajay Bhullar added a comment - New behavior is working as described in 5.5.0-2520 [ { "code": 4330, "msg": "No index available for ANSI join term t_4", "query_from_user": "SELECT t_3.* FROM multiple_table_db_32243823_simple_table_1 t_3 LEFT JOIN multiple_table_db_32243823_simple_table_10 t_4 ON ( ((t_3.decimal_field1 >= t_4.decimal_field1) AND (t_3.decimal_field1 < t_4.decimal_field1) OR t_3.char_field1 IS NOT NULL) ) WHERE ((t_4.bool_field1 AND t_3.primary_key_id IS NOT NULL)) OR ((NOT (t_3.bool_field1) AND t_3.decimal_field1 = 4968))" } ]

            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.