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

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

    XMLWordPrintable

Details

    • Untriaged
    • 1
    • Yes

    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.0.4-7256 contains query commit 8b79520 with commit message:
            MB-51739 Properly handle derived filters in post-join filter determination

            build-team Couchbase Build Team added a comment - Build couchbase-server-7.0.4-7256 contains query commit 8b79520 with commit message: MB-51739 Properly handle derived filters in post-join filter determination

            verified in 7.0.4-7259

            now the query is returning results

            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

            [

            { "id": 1 }

            ]

            ajay.bhullar Ajay Bhullar added a comment - verified in 7.0.4-7259 now the query is returning results 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 [ { "id": 1 } ]

            People

              ajay.bhullar Ajay Bhullar
              bingjie.miao Bingjie Miao
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty