Loading...

XML

Word

Printable

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.

is a backport of

MB-51736 LEFT JOIN breaks with between operator on non-existing attribute

relates to

MB-48601 LEFT JOIN on collections returns wrong results

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