Changeset: ec186bf6f4ee for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/ec186bf6f4ee
Added Files:
sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
Modified Files:
sql/test/BugTracker-2026/Tests/All
Branch: Dec2025
Log Message:
Add test for #7970
diffs (122 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
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2026/Tests/7970-optimize-where-predicate.test
@@ -0,0 +1,109 @@
+statement ok
+CREATE TABLE mre_a (
+ x INTEGER
+)
+
+statement ok
+CREATE TABLE mre_b (
+ d DATE
+)
+
+statement ok
+CREATE TABLE mre_c (
+ x INTEGER
+)
+
+statement ok
+INSERT INTO mre_a
+SELECT value
+FROM generate_series(1, 101)
+
+statement ok
+INSERT INTO mre_b
+SELECT DATE '2025-01-01'
+FROM generate_series(1, 101)
+
+statement ok
+INSERT INTO mre_c
+SELECT value
+FROM generate_series(1, 101)
+
+-- Slow query: the predicate is applied above the LEFT JOIN.
+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 '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+----
+project (
+| select (
+| | left outer join (
+| | | crossproduct (
+| | | | table("sys"."mre_a") [ "mre_a"."%TID%" as "a"."%TID%" ],
+| | | | table("sys"."mre_b") [ "mre_b"."d" as "b"."d" ]
+| | | ) [ ],
+| | | table("sys"."mre_c") [ "mre_c"."x" as "c"."x" ]
+| | ) [ ]
+| ) [ (date[varchar(19) "2023-01-01 00:00:00"]) <= ("b"."d") <=
(date[varchar(19) "2023-12-31 23:59:59"]) ]
+) [ "c"."x" ]
+
+-- next query takes 0.401 secs
+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 '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+----
+
+-- Fast equivalent query: the WHERE predicate is redundantly repeated in ON.
+query T nosort
+EXPLAIN
+SELECT c.x
+FROM mre_a AS a
+JOIN mre_b AS b
+ ON b.d BETWEEN '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+LEFT JOIN mre_c AS c ON TRUE
+WHERE b.d BETWEEN '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+----
+project (
+| select (
+| | left outer join (
+| | | select (
+| | | | crossproduct (
+| | | | | table("sys"."mre_a") [ "mre_a"."%TID%" as "a"."%TID%" ],
+| | | | | table("sys"."mre_b") [ "mre_b"."d" as "b"."d" ]
+| | | | ) [ ]
+| | | ) [ (date[varchar(19) "2023-01-01 00:00:00"]) <= ("b"."d") <=
(date[varchar(19) "2023-12-31 23:59:59"]) ],
+| | | table("sys"."mre_c") [ "mre_c"."x" as "c"."x" ]
+| | ) [ ]
+| ) [ (date[varchar(19) "2023-01-01 00:00:00"]) <= ("b"."d") <=
(date[varchar(19) "2023-12-31 23:59:59"]) ]
+) [ "c"."x" ]
+
+-- next query takes 0.014 secs, so 28 times faster than above query
+query I nosort
+SELECT c.x
+FROM mre_a AS a
+JOIN mre_b AS b
+ ON b.d BETWEEN '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+LEFT JOIN mre_c AS c ON TRUE
+WHERE b.d BETWEEN '2023-01-01 00:00:00'
+ AND '2023-12-31 23:59:59'
+----
+
+statement ok
+DROP TABLE IF EXISTS mre_c
+
+statement ok
+DROP TABLE IF EXISTS mre_b
+
+statement ok
+DROP TABLE IF EXISTS mre_a
+
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
@@ -153,3 +153,4 @@ KNOWNFAIL?7965-select-1-limit-0-bug
7967-optimize-in-select-distinct-queries
7968-optimize-distinct-from-q1-except-q2
7969-optimize-distinct-from-q1-intersect-q2
+7970-optimize-where-predicate
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
