Changeset: 8d2dff6e9f6c for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/8d2dff6e9f6c Modified Files: sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test Branch: Dec2025 Log Message:
Add more tests as provided by issue reporter diffs (140 lines): diff --git a/sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test b/sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test --- a/sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test +++ b/sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test @@ -27,3 +27,136 @@ True NOT YTD False +statement ok +CREATE TABLE t (v DOUBLE PRECISION) + +statement ok rowcount 6 +INSERT INTO t VALUES (1.0), (2.0), (NULL), (3.0), (8.0), (NULL) + +-- (1) Original bug shape — wrong on every row except v=8 +query RT nosort +SELECT v, CASE WHEN NOT (v = 8 AND v IS NOT NULL) THEN 'true' ELSE 'false' END AS f FROM t +---- +1.000 +true +2.000 +true +NULL +true +3.000 +true +8.000 +false +NULL +true + +-- (2) Your suggested workaround (operand swap) — strictly better, but +-- still wrong on the NULL rows (returns 'false' instead of 'true') +query RT nosort +SELECT v, CASE WHEN NOT (v IS NOT NULL AND v = 8) THEN 'true' ELSE 'false' END AS f FROM t +---- +1.000 +true +2.000 +true +NULL +true +3.000 +true +8.000 +false +NULL +true + +-- (3) OR-decompose — correct on every row, no NOT-AND-IS-NOT-NULL pattern at all +query RT nosort +SELECT v, CASE WHEN (v IS NULL OR v <> 8) THEN 'true' ELSE 'false' END AS f FROM t +---- +1.000 +true +2.000 +true +NULL +true +3.000 +true +8.000 +false +NULL +true + +query RIII nosort +SELECT v, + CASE WHEN NOT (v = 8 AND v IS NOT NULL) THEN 1 ELSE 0 END AS broken, + CASE WHEN (v IS NULL OR v <> 8) THEN 1 ELSE 0 END AS or_decompose, + CASE WHEN NOT (COALESCE(v = 8, FALSE)) THEN 1 ELSE 0 END AS coalesce_form +FROM t +---- +1.000 +1 +1 +1 +2.000 +1 +1 +1 +NULL +1 +1 +1 +3.000 +1 +1 +1 +8.000 +0 +0 +0 +NULL +1 +1 +1 + +statement ok +DROP TABLE t + +statement ok +CREATE TABLE t2 (a DOUBLE PRECISION, b DOUBLE PRECISION) + +statement ok rowcount 5 +INSERT INTO t2 VALUES (1,1),(1,2),(NULL,2),(3,NULL),(NULL,NULL) + +query RRII nosort +SELECT a, b, + -- "a = b" (null-aware) via NOT of the <> OR-decompose — WRONG: gives 0 for (1,1) + CASE WHEN NOT ((a IS NULL OR b IS NULL OR a<>b) AND (a IS NOT NULL OR b IS NOT NULL)) + THEN 1 ELSE 0 END AS or_decompose_broken, + -- COALESCE form — correct + CASE WHEN NOT (COALESCE(a<>b, a IS NOT NULL OR b IS NOT NULL)) + THEN 1 ELSE 0 END AS coalesce_form +FROM t2 +---- +1.000 +1.000 +1 +1 +1.000 +2.000 +0 +0 +NULL +2.000 +0 +0 +3.000 +NULL +0 +0 +NULL +NULL +1 +1 + +statement ok +DROP TABLE t2 + _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
