Changeset: 016611c3bf2e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/016611c3bf2e
Modified Files:
sql/test/BugTracker-2026/Tests/All
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Merge with Dec2025 branch.
diffs (truncated from 370 to 300 lines):
diff --git a/.hgtags b/.hgtags
--- a/.hgtags
+++ b/.hgtags
@@ -856,3 +856,4 @@ b4c2936c46f28ad6b3e59ed5a1a844726d521994
69f4fe2ad29b5d50d9ef06ad76639faaa60fcfa8 Dec2025_3
69f4fe2ad29b5d50d9ef06ad76639faaa60fcfa8 Dec2025_SP1_release
691e862e16ceb7f720536063ed6d3c2e3d11e33a Dec2025_5
+691e862e16ceb7f720536063ed6d3c2e3d11e33a Dec2025_SP2_release
diff --git a/sql/test/BugTracker-2026/Tests/7892-where-subqueries-sum-bug.test
b/sql/test/BugTracker-2026/Tests/7892-where-subqueries-sum-bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7892-where-subqueries-sum-bug.test
@@ -0,0 +1,70 @@
+statement ok
+CREATE TABLE comments7892 (
+ id INT,
+ post_id INT,
+ user_id INT,
+ content VARCHAR(1000),
+ is_spam INT,
+ created_at TIMESTAMP
+)
+
+statement ok
+INSERT INTO comments7892 VALUES
+(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'),
+(2, 1, 3, 'Spam here', 1, '2022-01-21 11:00:00'),
+(3, 2, 1, 'Thanks', 0, '2022-01-22 12:00:00'),
+(4, 4, 5, NULL, 0, '2022-01-23 13:00:00')
+
+query I nosort
+select count(*)
+from
+ comments7892 as ref_0
+----
+4
+
+query I nosort
+select (
+select count(*)
+from
+ comments7892 as ref_0
+where (false AND case when (ref_0.id < ref_0.user_id)
+ or (ref_0.created_at is not NULL) then
coalesce(nullif(true,nullif(false, false)),true) else true end)
+) + (
+select count(*)
+from
+ comments7892 as ref_0
+where (not (false AND case when (ref_0.id < ref_0.user_id)
+ or (ref_0.created_at is not NULL) then
coalesce(nullif(true,nullif(false, false)),true) else true end))
+) + (
+select count(*)
+from
+ comments7892 as ref_0
+where ((false AND case when (ref_0.id < ref_0.user_id)
+ or (ref_0.created_at is not NULL) then
coalesce(nullif(true,nullif(false, false)),true) else true end) is null)
+)
+----
+4
+
+query I nosort
+select (
+select count(*)
+from
+ comments7892
+where (false AND coalesce(true,false))
+) + (
+select count(*)
+from
+ comments7892
+where (not (false AND coalesce(true, false)))
+) + (
+select count(*)
+from
+ comments7892
+where ((false AND coalesce(true,false)) is null)
+)
+----
+4
+
+statement ok
+DROP TABLE comments7892
+
diff --git a/sql/test/BugTracker-2026/Tests/7893-where-subqueries-sum-bug.test
b/sql/test/BugTracker-2026/Tests/7893-where-subqueries-sum-bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7893-where-subqueries-sum-bug.test
@@ -0,0 +1,141 @@
+statement ok
+CREATE SCHEMA test7893
+
+statement ok
+SET SCHEMA test7893
+
+statement ok
+CREATE TABLE posts (
+ id INT,
+ user_id INT,
+ title VARCHAR(255),
+ content VARCHAR(1000),
+ views INT,
+ likes INT,
+ created_at TIMESTAMP,
+ rating DOUBLE
+)
+
+statement ok
+CREATE TABLE comments (
+ id INT,
+ post_id INT,
+ user_id INT,
+ content VARCHAR(1000),
+ is_spam INT,
+ created_at TIMESTAMP
+)
+
+statement ok
+INSERT INTO posts VALUES
+(1, 1, 'Hello World', 'First post', 100, 10, '2022-01-10 10:00:00', 4.5),
+(2, 1, 'Another Post', NULL, 150, 20, '2022-01-11 11:00:00', 3.0),
+(3, 2, 'Bob Post', 'Content', NULL, 5, '2022-01-12 12:00:00', NULL),
+(4, 3, NULL, 'Empty', 50, 2, '2022-01-13 13:00:00', 5.0),
+(5, 4, 'Last Post', 'Last', 300, 30,'2022-01-14 14:00:00', 4.9)
+
+statement ok
+INSERT INTO comments VALUES
+(1, 1, 2, 'Nice post', 0, '2022-01-20 10:00:00'),
+(2, 1, 3, 'Spam here', 1, '2022-01-21 11:00:00'),
+(3, 2, 1, 'Thanks', 0, '2022-01-22 12:00:00'),
+(4, 4, 5, NULL, 0, '2022-01-23 13:00:00')
+
+query I nosort
+SELECT COUNT(*)
+ FROM (
+ SELECT ref_1.views AS c0
+ FROM comments AS ref_0
+ RIGHT JOIN posts AS ref_1
+ ON (ref_1.likes IS NOT NULL)
+ WHERE (EXISTS (SELECT 1 FROM posts AS ref_2
+ WHERE (SELECT AVG(id) FROM posts) < 19.71)
+ OR TRUE OR (ref_0.id < ref_0.id))
+ ) AS subq_0,
+ LATERAL (
+ SELECT ref_3.user_id AS c0, subq_0.c0 AS c1
+ FROM comments AS ref_3
+ WHERE subq_0.c0 != (SELECT MAX(id) FROM posts)
+ ) AS subq_1
+----
+64
+
+query I nosort
+SELECT (
+ SELECT COUNT(*)
+ FROM (
+ SELECT ref_1.views AS c0
+ FROM comments AS ref_0
+ RIGHT JOIN posts AS ref_1
+ ON (ref_1.likes IS NOT NULL)
+ WHERE (EXISTS (SELECT 1 FROM posts AS ref_2
+ WHERE (SELECT AVG(id) FROM posts) < 19.71)
+ OR TRUE OR (ref_0.id < ref_0.id))
+ ) AS subq_0,
+ LATERAL (
+ SELECT ref_3.user_id AS c0, subq_0.c0 AS c1
+ FROM comments AS ref_3
+ WHERE subq_0.c0 != (SELECT MAX(id) FROM posts)
+ ) AS subq_1
+ WHERE (false AND CASE WHEN (subq_0.c0 != subq_1.c0) OR (72.88 < 97.58)
+ THEN true
+ ELSE startswith(ltrim(lpad('b', subq_1.c0)),'atk87g')
+ END)
+) + (
+ SELECT COUNT(*)
+ FROM (
+ SELECT ref_1.views AS c0
+ FROM comments AS ref_0
+ RIGHT JOIN posts AS ref_1
+ ON (ref_1.likes IS NOT NULL)
+ WHERE (EXISTS (SELECT 1 FROM posts AS ref_2
+ WHERE (SELECT AVG(id) FROM posts) < 19.71)
+ OR TRUE OR (ref_0.id < ref_0.id))
+ ) AS subq_0,
+ LATERAL (
+ SELECT ref_3.user_id AS c0, subq_0.c0 AS c1
+ FROM comments AS ref_3
+ WHERE subq_0.c0 != (SELECT MAX(id) FROM posts)
+ ) AS subq_1
+ WHERE NOT (
+ false AND CASE WHEN (subq_0.c0 != subq_1.c0) OR (72.88 < 97.58)
+ THEN true
+ ELSE startswith(ltrim(lpad('b', subq_1.c0)),'atk87g')
+ END
+ )
+) + (
+ SELECT COUNT(*)
+ FROM (
+ SELECT ref_1.views AS c0
+ FROM comments AS ref_0
+ RIGHT JOIN posts AS ref_1
+ ON (ref_1.likes IS NOT NULL)
+ WHERE (EXISTS (SELECT 1 FROM posts AS ref_2
+ WHERE (SELECT AVG(id) FROM posts) < 19.71)
+ OR TRUE OR (ref_0.id < ref_0.id))
+ ) AS subq_0,
+ LATERAL (
+ SELECT ref_3.user_id AS c0, subq_0.c0 AS c1
+ FROM comments AS ref_3
+ WHERE subq_0.c0 != (SELECT MAX(id) FROM posts)
+ ) AS subq_1
+ WHERE (false AND CASE WHEN (subq_0.c0 != subq_1.c0) OR (72.88 < 97.58)
+ THEN true
+ ELSE startswith(ltrim(lpad('b', subq_1.c0)),'atk87g')
+ END IS NULL)
+)
+----
+64
+
+statement ok
+DROP TABLE comments
+
+statement ok
+DROP TABLE posts
+
+statement ok
+SET SCHEMA sys
+
+statement ok
+DROP SCHEMA test7893
+
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
@@ -78,3 +78,5 @@ KNOWNFAIL?7774-insert-into-renamed-table
7889-delete-with-union-intersect
7890-insert-exists-issue
KNOWNFAIL?7891-update-issue
+KNOWNFAIL?7892-where-subqueries-sum-bug
+KNOWNFAIL?7893-where-subqueries-sum-bug
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -1,6 +1,3 @@
-Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
Running database upgrade commands:
drop function sys.dump_database;
drop view sys.dump_tables;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -1,6 +1,3 @@
-Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
Running database upgrade commands:
drop function sys.dump_database;
drop view sys.dump_tables;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -84,9 +84,6 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
-Running database upgrade commands:
drop function sys.dump_database;
drop view sys.dump_tables;
drop view sys.describe_tables;
diff --git a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -1,6 +1,3 @@
-Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
Running database upgrade commands:
drop function sys.dump_database;
drop view sys.dump_tables;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
@@ -1,6 +1,3 @@
-Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
Running database upgrade commands:
drop function sys.dump_database;
drop view sys.dump_tables;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
@@ -84,9 +84,6 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
Running database upgrade commands:
-delete from sys.dependencies where (id, depend_id) in (select c.id, f.id from
sys.functions f, sys._tables t, sys._columns c, sys.dependencies d where
c.table_id = t.id and f.id = d.depend_id and c.id = d.id and f.schema_id = 2000
and t.schema_id = 2000 and (f.name, t.name, c.name) in (values
('get_merge_table_partition_expressions', 'table_partitions', 'id')));
-
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
