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

[BP to 7.1.1] - LEFT JOIN breaks with between operator on non-existing attribute

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Unknown

    Description

      Steps to reproduce:

      a.  Create bucket customer, scope s1, and collections c1 and c2.

      b.  Add this doc in c1 and c2:

      {"id": 1}

      c.  create these indexes:

       

      CREATE PRIMARY INDEX ON `customer`.`s1`.`c1`;
      CREATE INDEX `idx1` ON `customer`.`s1`.`c2`(`id`);
      

       

      d.  run this query:

      SELECT lhs.id
      FROM customer.s1.c1 lhs
      LEFT JOIN customer.s1.c2 rhs 
      ON lhs.id = rhs.id
          AND rhs.x between 1 and 10

      No documents are output.

      e.  run this query:

      SELECT lhs.id
      FROM customer.s1.c1 lhs
      LEFT JOIN customer.s1.c2 rhs 
      ON lhs.id = rhs.id
          AND rhs.x >= 1 and rhs.x <= 10

      Now I get 1 doc.  

      Attachments

        Issue Links

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

          Activity

            Build couchbase-server-7.1.1-3066 contains query commit 1a562c0 with commit message:
            MB-52179 Properly handle derived filters in post-join filter determination

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.1.1-3066 contains query commit 1a562c0 with commit message: MB-52179 Properly handle derived filters in post-join filter determination

            Verified on 7.1.1-3066

            cbq> SELECT lhs.id FROM customer.s1.c1 lhs LEFT JOIN customer.s1.c2 rhs ON lhs.id = rhs.id AND rhs.x between 1 and 10 ;
            {
                "requestID": "c407faa2-6214-4331-8fec-8d7e3cfc1b21",
                "signature": {
                    "id": "json"
                },
                "results": [
                {
                    "id": 1
                }
                ],
                "status": "success",
                "metrics": {
                    "elapsedTime": "6.895397ms",
                    "executionTime": "6.763857ms",
                    "resultCount": 1,
                    "resultSize": 23,
                    "serviceLoad": 6
                }
            }
             

            pierre.regazzoni Pierre Regazzoni added a comment - Verified on 7.1.1-3066 cbq> SELECT lhs.id FROM customer.s1.c1 lhs LEFT JOIN customer.s1.c2 rhs ON lhs.id = rhs.id AND rhs.x between 1 and 10 ; {     "requestID" : "c407faa2-6214-4331-8fec-8d7e3cfc1b21" ,     "signature" : {         "id" : "json"     },     "results" : [     {         "id" : 1     }     ],     "status" : "success" ,     "metrics" : {         "elapsedTime" : "6.895397ms" ,         "executionTime" : "6.763857ms" ,         "resultCount" : 1 ,         "resultSize" : 23 ,         "serviceLoad" : 6     } }

            Build couchbase-server-7.2.0-5000 contains query commit 1a562c0 with commit message:
            MB-52179 Properly handle derived filters in post-join filter determination

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.2.0-5000 contains query commit 1a562c0 with commit message: MB-52179 Properly handle derived filters in post-join filter determination

            People

              pierre.regazzoni Pierre Regazzoni
              bingjie.miao Bingjie Miao
              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