Selection of a subquery with a LEFT JOIN returns the wrong result set

[nuno-faria]

Describe the bug
When performing some selections on a subquery with a left join, it causes the join to fail. It requires a combination of specific filters, indexes, and NOT NULL properties for it to occur.

To Reproduce
Create two tables, naturally joined by two columns x1, x2, with the first table indexed by x1, x2 and x2, and with NOT NULL properties:

CREATE TABLE T1 (x1 int NOT NULL, x2 int NOT NULL, y int NOT NULL);
CREATE INDEX T1_x1_x2 ON T1 (x1, x2);
CREATE INDEX T1_x2 ON T1 (x2);

CREATE TABLE T2 (x1 int NOT NULL, x2 int NOT NULL, z int NOT NULL);
CREATE INDEX T2_x1_x2 ON T2 (x1, x2);

Populate with some data:

INSERT INTO T1 VALUES (1, 0, 1), (1, 2, 1);
INSERT INTO T2 VALUES (1, 0, 3), (1, 2, 100);

Perform the subquery first to see that it is working correctly:

SELECT T1.*, T2.x1 as t2_x1, z
FROM T1 
LEFT JOIN T2 ON T1.x1 = T2.x1 AND T1.x2 = T2.x2
WHERE 10 <= T2.z OR T2.z IS NULL; -- (x1, x2, z) = (1, 0, 3) is dropped here, as 10 <= 3 is False

Returns:
+------+------+------+-------+------+
| x1   | x2   | y    | t2_x1 | z    |
+======+======+======+=======+======+
|    1 |    2 |    1 |     1 |  100 |
+------+------+------+-------+------+

Perform the filter on the subquery to check that the join fails (t2_x1 and z become null, and the other row is also returned):

SELECT *
FROM (
    SELECT T1.*, T2.x1 as t2_x1, z
    FROM T1 
    LEFT JOIN T2 ON T1.x1 = T2.x1 AND T1.x2 = T2.x2
    WHERE 10 <= T2.z OR T2.z IS NULL
) T
WHERE T.x1 = 1; -- this filter should return the same row

Returns:
+------+------+------+-------+------+
| x1   | x2   | y    | t2_x1 | z    |
+======+======+======+=======+======+
|    1 |    0 |    1 |  null | null |
|    1 |    2 |    1 |  null | null |
+------+------+------+-------+------+

Expected behavior
Return the same result as the nested query.

Software versions

• monetdb -v: MonetDB Database Server Toolkit v11.41.11 (Jul2021-SP1).
• OS: Ubuntu 20.04.3 LTS;
• Monetdb installed from release packages with apt (packages monetdb5-sql and monetdb-client);

Additional context
There are already some things I discovered that should help narrow the issue:

• Both indexes on T1 must be created, as without one of them the query works. The index on T2 is not relevant;
• The column x2 of T1 must be tagged with NOT NULL. The NOT NULL in the other columns is not relevant;
• There must be a filter after the subquery in one of the columns of T1 (x1, x2, or y). Filters done to t2_x1 or z make the join succeed (e.g., WHERE T.z = 100). Likewise, the nested query alone with no filter also returns the correct result;
• Manually pushing the filter WHERE T.x1 = 1 to the subquery works correctly;
• Removing the T2.z IS NULL from the subquery also makes the join succeed.

[PedroTadim]

I think this is related to the 'unique' value not being correctly propagated. I fixed this while developing a new feature in the Jan2022 branch. I think it's not worth to backport it because of many changes, and also it will be released soon. If you are going to use Jan2022 be aware it's not stable yet, and we are still testing it.

[nuno-faria]

Thanks, I can confirm that the Jan2022 does not display the error.