Details
-
Bug
-
Resolution: Fixed
-
Major
-
7.0.2, 7.1.0
-
Couchbase 7.0.2. Also tested in Docker on my machine.
-
1
Description
In the following query, the LEFT JOIN acts like a regular JOIN.
SELECT p AS id,
|
earliest_in_future
|
FROM [{ "id": 1 }, { "id": 2 }, { "id": 3 }, { "id": 4 }, { "id": 5 }] p LEFT
|
JOIN [{ "parent": 1, "date": "2030-01-01" }, { "parent": 1, "date": "2000-01-01" }, { "parent": 2, "date": "2000-01-01" }, { "parent": 3, "date": "2030-01-01" }, { "parent": 3, "date": "2060-01-01" }] c ON c.parent = p.id
|
AND SUBSTR(c.date, 0, 10) >= SUBSTR(NOW_STR(), 0, 10)
|
GROUP BY p LETTING earliest_in_future = MIN(SUBSTR(c.date, 0, 10))
|
This query should be returning 5 results since there is a LEFT JOIN and p has 5 documents. This works in 6.6.2 but in 7.0.2, the LEFT JOIN acts like a normal join and ignores any documents that don't have a linked c document. If you hard code the current date (swap SUBSTR(NOW_STR(), 0, 10) with the current date "2021-11-08") then it provides the correct results.
Sitaram Vemulapalli mentioned there is an extra filter in the HashJoin which is causing the issue.