Changeset: f92cb100b755 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f92cb100b755
Modified Files:
sql/test/BugTracker-2026/Tests/All
Branch: default
Log Message:
merge with Dec2025
diffs (217 lines):
diff --git
a/sql/test/BugTracker-2026/Tests/7937-where-not-in-values-null-bug.test
b/sql/test/BugTracker-2026/Tests/7937-where-not-in-values-null-bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7937-where-not-in-values-null-bug.test
@@ -0,0 +1,36 @@
+statement ok
+CREATE TABLE t (a int primary key, b int)
+
+statement ok
+INSERT INTO t VALUES (0, 0)
+
+-- next query returns a row but should not return a row
+query II nosort
+SELECT * FROM t WHERE a NOT IN (VALUES (null))
+----
+
+query II nosort
+SELECT * FROM t WHERE b NOT IN (VALUES (null))
+----
+
+statement ok
+DROP TABLE t
+
+-- it appears the bug is due to a not null constraint for column a (which is
added implicitly for primary key columns)
+statement ok
+CREATE TABLE t (a int not null, b int)
+
+statement ok
+INSERT INTO t VALUES (0, 0)
+
+-- next query returns a row but should not return a row
+query II nosort
+SELECT * FROM t WHERE a NOT IN (VALUES (null))
+----
+
+query II nosort
+SELECT * FROM t WHERE b NOT IN (VALUES (null))
+----
+
+statement ok
+DROP TABLE t
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
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2026/Tests/7938-not-x-is-const-and-x-is-not-null-bug.test
@@ -0,0 +1,162 @@
+query TT nosort
+SELECT x, CASE WHEN NOT(x = 'YTD' AND x IS NOT NULL) THEN 'False' ELSE 'True'
END AS f
+ FROM (VALUES ('YTD'), ('NOT YTD')) AS v(x)
+----
+YTD
+True
+NOT YTD
+False
+
+-- strangly when swapping condition order (a AND b) into (b AND a), it works
correct
+query TT nosort
+SELECT x, CASE WHEN NOT(x IS NOT NULL AND x = 'YTD') THEN 'False' ELSE 'True'
END AS f
+ FROM (VALUES ('YTD'), ('NOT YTD')) AS v(x)
+----
+YTD
+True
+NOT YTD
+False
+
+-- without the NOT is also works correct
+query TT nosort
+SELECT x, CASE WHEN (x = 'YTD' AND x IS NOT NULL) THEN 'True' ELSE 'False' END
AS f
+ FROM (VALUES ('YTD'), ('NOT YTD')) AS v(x)
+----
+YTD
+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
+
diff --git a/sql/test/BugTracker-2026/Tests/All
b/sql/test/BugTracker-2026/Tests/All
--- a/sql/test/BugTracker-2026/Tests/All
+++ b/sql/test/BugTracker-2026/Tests/All
@@ -129,3 +129,5 @@ KNOWNFAIL?7931-rel2bin_select-assertion-
7932-canditer_init-assertion-failure
7933-query-without-result-bug
7934-ALGouterselect-assertion-failure
+KNOWNFAIL?7937-where-not-in-values-null-bug
+KNOWNFAIL?7938-not-x-is-const-and-x-is-not-null-bug
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
