Description:
A row constructor (tuple) comparison used as a WHERE filter silently drops
rows that satisfy it, whenever one of the tuple elements is a column that
receives its NULL value from outer-join row complementation (rather than a
NULL stored in the table).
`SELECT ... WHERE (1, oj_col) <= (2, 3)` returns 0 rows where the same
predicate evaluated in the SELECT list over the same row stream returns
TRUE for every row. The tuple comparison itself is evaluated correctly in
every other context tested.
No error or warning is raised — the query silently returns an empty result.
### Minimal repro (100% deterministic)
```sql
CREATE TABLE t4 (a INT); -- stays EMPTY
CREATE TABLE t6 (b INT, c27 VARCHAR(10));
INSERT INTO t6 VALUES (1,'x'),(2,'y'),(3,'z');
-- 3 null-extended rows: t4.a is NULL in every row of the join output.
-- Form A: the predicate evaluated in the SELECT list of a derived table
-- (aggregate reads the TRUE values). Returns 3 -- correct.
SELECT COALESCE(SUM(p = 1), 0) AS true_rows FROM
(SELECT ((1, t4.a) <= (2, 3)) AS p
FROM t6 LEFT JOIN t4 ON 1 = 1) AS t;
-- Form B: the SAME predicate as a WHERE filter over the SAME row stream.
-- Returns 0 -- wrong, expected 3.
SELECT COUNT(*) FROM t6 LEFT JOIN t4 ON 1 = 1
WHERE (1, t4.a) <= (2, 3);
```
Expected result for Form B: 3. `(1, NULL) <= (2, 3)` short-circuits on the
first element (`1 < 2`), so the comparison is TRUE regardless of the NULL
second element. The server agrees when asked directly:
```sql
SELECT (1, NULL) <= (2, 3); -- 1
```
### What makes it fail — the stored-NULL control
The defect is specific to NULLs introduced by outer-join complementation.
The same tuple comparison over a NULL **stored in the table** works
correctly:
```sql
CREATE TABLE t5 (a INT);
INSERT INTO t5 VALUES (NULL);
SELECT COUNT(*) FROM t5 WHERE (1, a) <= (2, 3); -- 1 -- correct
```
So both of these hold:
- tuple comparison with a NULL element: correct semantics everywhere;
- WHERE filtering over a null-extended column: correct for every other
predicate shape tested (`IS NULL`, scalar comparisons, constant tuples).
Only the combination — row constructor comparison in WHERE whose tuple
contains an outer-join-complemented column — misbehaves.
### Differential matrix (all on 8.0.46)
| Query shape | Result | Correct? |
|---|---|---|
| `WHERE (1, NULL) <= (2, 3)` over a plain 3-row table | 3 | yes |
| `WHERE (1, c27) <= (2, 3)` over a plain table | 3 | yes |
| `WHERE (1, 5) <= (2, 3)` over the join row stream (constant tuple) | 3 | yes |
| `WHERE t4.a IS NULL` over the join row stream | 3 | yes |
| `WHERE (1, a) <= (2, 3)` over a table with a stored NULL | 1 | yes |
| SELECT-list form of the predicate over the join row stream (aggregate) | 3 (TRUE) | yes |
| **`WHERE (1, t4.a) <= (2, 3)` over `t6 LEFT JOIN t4`** | **0** | **no** |
| `WHERE (2, 3) >= (1, t4.a)` (join column on the RHS) | 0 | no |
| Same with `t4 RIGHT JOIN t6` | 0 | no |
| Same with `ON (TRUE) IS NOT FALSE` instead of `ON 1 = 1` | 0 | no |
The join column fails whether it sits on the left or the right of the
comparison, under LEFT or RIGHT joins, and under any always-true join
condition.
### Analysis hints
Form A (correct) forces the join into a materialized derived table before
the predicate is evaluated; Form B (wrong) applies the predicate as a WHERE
condition directly over the join's null-extended rows. EXPLAIN shows the
failing form as a plain two-table hash join with `Using where`. The
suspicion is that the row-constructor comparison is evaluated over the
complemented (null-extended) row buffer in a path that mishandles the
NULL-complemented column — e.g. reading the column's "not found" state as
an ordinary NULL value, or a condition-pushdown/const-tuple optimization
dropping the condition.
### Versions
- 8.0.46 (current) — reproduces.
- 8.0.13 — reproduces.
Found by an automated SQL equivalence testing harness: the WHERE form and
its SELECT-list-projection form are semantically identical queries, and the
harness flagged their result sets diverging (0 vs 9 rows on the original,
fuzz-generated query).
How to repeat:
Run the minimal repro above. Both SELECTs must return 3; the second returns
0 on affected versions.
Description: A row constructor (tuple) comparison used as a WHERE filter silently drops rows that satisfy it, whenever one of the tuple elements is a column that receives its NULL value from outer-join row complementation (rather than a NULL stored in the table). `SELECT ... WHERE (1, oj_col) <= (2, 3)` returns 0 rows where the same predicate evaluated in the SELECT list over the same row stream returns TRUE for every row. The tuple comparison itself is evaluated correctly in every other context tested. No error or warning is raised — the query silently returns an empty result. ### Minimal repro (100% deterministic) ```sql CREATE TABLE t4 (a INT); -- stays EMPTY CREATE TABLE t6 (b INT, c27 VARCHAR(10)); INSERT INTO t6 VALUES (1,'x'),(2,'y'),(3,'z'); -- 3 null-extended rows: t4.a is NULL in every row of the join output. -- Form A: the predicate evaluated in the SELECT list of a derived table -- (aggregate reads the TRUE values). Returns 3 -- correct. SELECT COALESCE(SUM(p = 1), 0) AS true_rows FROM (SELECT ((1, t4.a) <= (2, 3)) AS p FROM t6 LEFT JOIN t4 ON 1 = 1) AS t; -- Form B: the SAME predicate as a WHERE filter over the SAME row stream. -- Returns 0 -- wrong, expected 3. SELECT COUNT(*) FROM t6 LEFT JOIN t4 ON 1 = 1 WHERE (1, t4.a) <= (2, 3); ``` Expected result for Form B: 3. `(1, NULL) <= (2, 3)` short-circuits on the first element (`1 < 2`), so the comparison is TRUE regardless of the NULL second element. The server agrees when asked directly: ```sql SELECT (1, NULL) <= (2, 3); -- 1 ``` ### What makes it fail — the stored-NULL control The defect is specific to NULLs introduced by outer-join complementation. The same tuple comparison over a NULL **stored in the table** works correctly: ```sql CREATE TABLE t5 (a INT); INSERT INTO t5 VALUES (NULL); SELECT COUNT(*) FROM t5 WHERE (1, a) <= (2, 3); -- 1 -- correct ``` So both of these hold: - tuple comparison with a NULL element: correct semantics everywhere; - WHERE filtering over a null-extended column: correct for every other predicate shape tested (`IS NULL`, scalar comparisons, constant tuples). Only the combination — row constructor comparison in WHERE whose tuple contains an outer-join-complemented column — misbehaves. ### Differential matrix (all on 8.0.46) | Query shape | Result | Correct? | |---|---|---| | `WHERE (1, NULL) <= (2, 3)` over a plain 3-row table | 3 | yes | | `WHERE (1, c27) <= (2, 3)` over a plain table | 3 | yes | | `WHERE (1, 5) <= (2, 3)` over the join row stream (constant tuple) | 3 | yes | | `WHERE t4.a IS NULL` over the join row stream | 3 | yes | | `WHERE (1, a) <= (2, 3)` over a table with a stored NULL | 1 | yes | | SELECT-list form of the predicate over the join row stream (aggregate) | 3 (TRUE) | yes | | **`WHERE (1, t4.a) <= (2, 3)` over `t6 LEFT JOIN t4`** | **0** | **no** | | `WHERE (2, 3) >= (1, t4.a)` (join column on the RHS) | 0 | no | | Same with `t4 RIGHT JOIN t6` | 0 | no | | Same with `ON (TRUE) IS NOT FALSE` instead of `ON 1 = 1` | 0 | no | The join column fails whether it sits on the left or the right of the comparison, under LEFT or RIGHT joins, and under any always-true join condition. ### Analysis hints Form A (correct) forces the join into a materialized derived table before the predicate is evaluated; Form B (wrong) applies the predicate as a WHERE condition directly over the join's null-extended rows. EXPLAIN shows the failing form as a plain two-table hash join with `Using where`. The suspicion is that the row-constructor comparison is evaluated over the complemented (null-extended) row buffer in a path that mishandles the NULL-complemented column — e.g. reading the column's "not found" state as an ordinary NULL value, or a condition-pushdown/const-tuple optimization dropping the condition. ### Versions - 8.0.46 (current) — reproduces. - 8.0.13 — reproduces. Found by an automated SQL equivalence testing harness: the WHERE form and its SELECT-list-projection form are semantically identical queries, and the harness flagged their result sets diverging (0 vs 9 rows on the original, fuzz-generated query). How to repeat: Run the minimal repro above. Both SELECTs must return 3; the second returns 0 on affected versions.