Changeset: 323b43e8da5f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/323b43e8da5f
Modified Files:
sql/test/BugTracker-2026/Tests/All
Branch: default
Log Message:
merge with Dec2025
diffs (truncated from 341 to 300 lines):
diff --git a/sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
b/sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
--- a/sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
+++ b/sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
@@ -130,6 +130,35 @@ JOIN mre_b AS b
LEFT JOIN mre_c AS c ON TRUE
----
+-- check plan when using correct date literals, so use WHERE b.d BETWEEN date
'2023-01-01' and date '2023-12-31'
+query T nosort
+EXPLAIN
+SELECT c.x
+FROM mre_a AS a
+JOIN mre_b AS b ON TRUE
+LEFT JOIN mre_c AS c ON TRUE
+WHERE b.d BETWEEN date '2023-01-01' and date '2023-12-31';
+----
+project (
+| left outer join (
+| | crossproduct (
+| | | table("sys"."mre_a") [ "mre_a"."%TID%" as "a"."%TID%" ],
+| | | select (
+| | | | table("sys"."mre_b") [ "mre_b"."d" as "b"."d" ]
+| | | ) [ (date "2023-01-01") <= ("b"."d") <= (date "2023-12-31") ]
+| | ) [ ],
+| | table("sys"."mre_c") [ "mre_c"."x" as "c"."x" ]
+| ) [ ]
+) [ "c"."x" ]
+
+query I nosort
+SELECT c.x
+FROM mre_a AS a
+JOIN mre_b AS b ON TRUE
+LEFT JOIN mre_c AS c ON TRUE
+WHERE b.d BETWEEN date '2023-01-01' and date '2023-12-31';
+----
+
statement ok
DROP TABLE IF EXISTS mre_c
diff --git a/sql/test/BugTracker-2026/Tests/7994-exists-groupby-rollup-bug.test
b/sql/test/BugTracker-2026/Tests/7994-exists-groupby-rollup-bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7994-exists-groupby-rollup-bug.test
@@ -0,0 +1,89 @@
+statement ok
+CREATE TABLE n4a(x INT)
+
+statement ok
+INSERT INTO n4a VALUES (10),(20),(99)
+
+statement ok
+CREATE TABLE n4b(y INT, z INT)
+
+statement ok
+INSERT INTO n4b VALUES (10,1),(10,2),(10,3),(20,7)
+
+-- (A) BUG: EXISTS with ROLLUP is FALSE for every outer row
+-- observed: (10, false), (20, false), (99, false) WRONG for x=10 and x=20
and x=99
+query IT rowsort
+SELECT a.x,
+ EXISTS (SELECT COUNT(*) FROM n4b WHERE n4b.y = a.x GROUP BY
ROLLUP(n4b.z)) AS e
+FROM n4a a ORDER BY a.x
+----
+10
+True
+20
+True
+99
+True
+
+-- (B) Proof the x=10 subquery genuinely produces 4 rows:
+-- observed: 1, 1, 1, 3 (four rows) -> EXISTS must be TRUE
+query I rowsort
+SELECT COUNT(*) FROM n4b WHERE n4b.y = 10 GROUP BY ROLLUP(n4b.z)
+----
+1
+1
+1
+3
+
+-- observed: 1, 1 (two rows) -> EXISTS must be TRUE
+query I rowsort
+SELECT COUNT(*) FROM n4b WHERE n4b.y = 20 GROUP BY ROLLUP(n4b.z)
+----
+1
+1
+
+-- observed: 0 (one row) -> EXISTS must be TRUE
+query I rowsort
+SELECT COUNT(*) FROM n4b WHERE n4b.y = 99 GROUP BY ROLLUP(n4b.z)
+----
+0
+
+-- (C) Control: plain GROUP BY (no ROLLUP) is correct
+-- observed: (10, true), (20, true), (99, false) correct
+query IT rowsort
+SELECT a.x,
+ EXISTS (SELECT COUNT(*) FROM n4b WHERE n4b.y = a.x GROUP BY n4b.z) AS e
+FROM n4a a ORDER BY a.x
+----
+10
+True
+20
+True
+99
+False
+
+-- (D) Reportability: WHERE EXISTS silently drops all rows
+-- observed: (empty) WRONG — expected {10, 20, 99}
+query I rowsort
+SELECT a.x FROM n4a a
+WHERE EXISTS (SELECT COUNT(*) FROM n4b WHERE n4b.y = a.x GROUP BY
ROLLUP(n4b.z))
+ORDER BY a.x
+----
+10
+20
+99
+
+-- same query by without the ROLLUP. This filters out row 99 correctly
+query I rowsort
+SELECT a.x FROM n4a a
+WHERE EXISTS (SELECT COUNT(*) FROM n4b WHERE n4b.y = a.x GROUP BY (n4b.z))
+ORDER BY a.x
+----
+10
+20
+
+statement ok
+DROP TABLE IF EXISTS n4a
+
+statement ok
+DROP TABLE IF EXISTS n4b
+
diff --git
a/sql/test/BugTracker-2026/Tests/7995-left-join-lateral-subquery-bug.test
b/sql/test/BugTracker-2026/Tests/7995-left-join-lateral-subquery-bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7995-left-join-lateral-subquery-bug.test
@@ -0,0 +1,54 @@
+statement ok
+CREATE TABLE n5a(id INT, c0 INT)
+
+statement ok
+INSERT INTO n5a VALUES (1,10),(2,NULL),(3,40)
+
+statement ok
+CREATE TABLE n5b(c0 INT)
+
+statement ok
+INSERT INTO n5b VALUES (10),(20),(NULL)
+
+-- (A) Correct row listing: (1,1),(2,0),(3,0) — three rows
+-- observed: (1,1), (2,0), (3,0) correct
+query II rowsort
+SELECT r0.id, l.n
+FROM n5a r0
+LEFT JOIN LATERAL (SELECT COUNT(*) AS n FROM n5b r1 WHERE r1.c0 = r0.c0) l ON
TRUE
+ORDER BY r0.id
+----
+1
+1
+2
+0
+3
+0
+
+-- (B) COUNT(*) over the SAME join: expected 3, get 4
+-- observed: 4 WRONG (11.55.5 returns 3)
+query I rowsort
+SELECT COUNT(*)
+FROM n5a r0
+LEFT JOIN LATERAL (SELECT COUNT(*) AS n FROM n5b r1 WHERE r1.c0 = r0.c0) l ON
TRUE
+----
+3
+
+-- (C) Projecting id alone: the NULL-correlated outer row (id=2) is duplicated
+-- observed: 1, 2, 2, 3 WRONG (11.55.5 returns 1, 2, 3)
+query I rowsort
+SELECT r0.id
+FROM n5a r0
+LEFT JOIN LATERAL (SELECT COUNT(*) AS n FROM n5b r1 WHERE r1.c0 = r0.c0) l ON
TRUE
+ORDER BY r0.id
+----
+1
+2
+3
+
+statement ok
+DROP TABLE IF EXISTS n5a
+
+statement ok
+DROP TABLE IF EXISTS n5b
+
diff --git
a/sql/test/BugTracker-2026/Tests/7996-delete-not-exists-correlated-query-bug.test
b/sql/test/BugTracker-2026/Tests/7996-delete-not-exists-correlated-query-bug.test
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2026/Tests/7996-delete-not-exists-correlated-query-bug.test
@@ -0,0 +1,76 @@
+-- Mode A: deletes rows it must keep (data loss)
+statement ok
+CREATE TABLE ta(id INT, k INT)
+
+statement ok rowcount 2
+INSERT INTO ta VALUES (1,10),(2,99)
+
+statement ok
+CREATE TABLE ua(k INT)
+
+statement ok rowcount 1
+INSERT INTO ua VALUES (10)
+
+-- id=2 (k=99) has no match in u
+statement ok rowcount 0
+DELETE FROM ta WHERE NOT EXISTS(SELECT COUNT(*) FROM ua WHERE ua.k = ta.k)
+
+-- MonetDB: {1} WRONG (deleted id=2) PostgreSQL: {1,2}
+query I rowsort
+SELECT id FROM ta ORDER BY id
+----
+1
+2
+
+statement ok
+DROP TABLE IF EXISTS ta
+
+statement ok
+DROP TABLE IF EXISTS ua
+
+
+-- Mode B: ROLLUP variant deletes all rows
+statement ok
+CREATE TABLE tb(id INT, k INT)
+
+statement ok rowcount 3
+INSERT INTO tb VALUES (1,10),(2,20),(3,40)
+
+statement ok
+CREATE TABLE ub(k INT)
+
+statement ok rowcount 2
+INSERT INTO ub VALUES (10),(20)
+
+statement ok rowcount 0
+DELETE FROM tb WHERE NOT EXISTS(SELECT COUNT(*) FROM ub WHERE ub.k = tb.k
GROUP BY ROLLUP(ub.k))
+
+-- MonetDB: (empty) WRONG (deleted all 3) PostgreSQL: {1,2,3}
+query I rowsort
+SELECT id FROM tb ORDER BY id
+----
+1
+2
+3
+
+-- Mode C (mirror): keeps rows it must delete (stale data)
+statement ok
+DELETE FROM tb
+
+statement ok rowcount 3
+INSERT INTO tb VALUES (1,10),(2,20),(3,40)
+
+-- MonetDB keeps the no-match row (3, 40) that should have been deleted.
+statement ok rowcount 3
+DELETE FROM tb WHERE EXISTS(SELECT COUNT(*) FROM ub WHERE ub.k = tb.k)
+
+query I rowsort
+SELECT id FROM tb ORDER BY id
+----
+
+statement ok
+DROP TABLE IF EXISTS tb
+
+statement ok
+DROP TABLE IF EXISTS ub
+
diff --git
a/sql/test/BugTracker-2026/Tests/7997-recursive-cte-number-overflow-issue.test
b/sql/test/BugTracker-2026/Tests/7997-recursive-cte-number-overflow-issue.test
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2026/Tests/7997-recursive-cte-number-overflow-issue.test
@@ -0,0 +1,20 @@
+-- MonetDB: 22003!overflow in calculation 127+1.
+-- PostgreSQL & DuckDB & SQLite all return: 200
+query I nosort
+WITH RECURSIVE r(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM r WHERE n < 200)
SELECT MAX(n) FROM r
+----
+200
+
+-- Casting the SEED to INTEGER fixes it (confirms the root cause is seed-type
inference):
+-- MonetDB: 200 (correct)
+query I nosort
+WITH RECURSIVE r(n) AS (SELECT CAST(1 AS INTEGER) UNION ALL SELECT n+1 FROM r
WHERE n < 200) SELECT MAX(n) FROM r
+----
+200
+
+-- extra check to test what happens when we start from 128, so type tinyint is
not possible.
+query I nosort
+WITH RECURSIVE r(n) AS (SELECT 128 UNION ALL SELECT n+1 FROM r WHERE n < 200)
SELECT MAX(n) FROM r
+----
+200
+
diff --git
a/sql/test/BugTracker-2026/Tests/7998-scalar-subquery-error-msg-improvement.test
b/sql/test/BugTracker-2026/Tests/7998-scalar-subquery-error-msg-improvement.test
new file mode 100644
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
