Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.0.4, 6.5.0, Cheshire-Cat
-
Untriaged
-
No
Description
ANSI LEFT JOIN may return incorrect result:
insert into default (key, value) values("K01", { "c11": 1, "c12": 11, "type": "left" }); |
insert into default (key, value) values("K02", { "c11": 2, "c12": 12, "type": "left" }); |
|
|
insert into default (key, value) values("K03", { "c21": 1, "c22": 21, "type": "right" }); |
|
|
CREATE INDEX ix_right ON default(c21, c22) WHERE type = "right"; |
|
|
SELECT l.c12, r.c22
|
FROM default l LEFT JOIN default r |
ON l.c11 = r.c21 AND r.type = "right" |
WHERE l.type = "left" AND r.c22 IS MISSING; |
|
This query should return a single document:
"results": [ |
{
|
"c11": 2 |
}
|
],
|
|
However with the above index defined it returned two documents:
"results": [ |
{
|
"c11": 1 |
},
|
{
|
"c11": 2 |
}
|
],
|
The wrong result happens when:
- ANSI LEFT JOIN is used (l LEFT JOIN r)
- there is a IS MISSING predicate in the WHERE clause for r (r.c22 IS MISSING)
- the field with IS MISSING predicate (c22) is present in the index used for the right-hand side
Work around:
use a field that is not present in the index keys in the IS MISSING predicate, or use the entire document in the IS MISSING predicate (r IS MISSING instead of r.c22 IS MISSING). This makes right side not covered:
SELECT l.c12, r.c22
|
FROM default l LEFT JOIN default r |
ON l.c11 = r.c21 AND r.type = "right" |
WHERE l.type = "left" AND r IS MISSING; |
|
Attachments
Issue Links
- links to
| For Gerrit Dashboard: MB-38564 | ||||||
|---|---|---|---|---|---|---|
| # | Subject | Branch | Project | Status | CR | V |
| 124938,3 | MB-38564 wrong result possible with left join and IS MISSING predicate is present on inner table | master | query | Status: MERGED | +2 | +1 |
| 124951,2 | MB-38564 Test case modification | master | query | Status: MERGED | +2 | +1 |
| 124952,3 | MB-38564 wrong result possible with left join and IS MISSING predicate is present on inner table | mad-hatter | query | Status: MERGED | +2 | +1 |
| 154597,2 | MB-46607 Avoid using WHERE clause filters to generate index spans for inner of LEFT OUTER JOIN | master | query | Status: MERGED | +2 | +1 |
| 154626,2 | MB-46613 Avoid using WHERE clause filters to generate index spans for inner of LEFT OUTER JOIN | mad-hatter | query | Status: MERGED | +2 | +1 |
| 155415,2 | MB-46616 Code cleanup for issue with IS MISSING filter in the WHERE clause of LEFT OUTER JOIN | cheshire-cat | query | Status: MERGED | +2 | +1 |
| 155546,2 | MB-46616 Code cleanup for issue with IS MISSING filter in the WHERE clause of LEFT OUTER JOIN | master | query | Status: MERGED | +2 | +1 |