Changeset: dc8c9a199bcf for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/dc8c9a199bcf
Branch: Mar2025
Log Message:
Merge with Aug2024 branch.
diffs (118 lines):
diff --git a/sql/test/BugTracker-2025/Tests/7614_join_reordering.test
b/sql/test/BugTracker-2025/Tests/7614_join_reordering.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7614_join_reordering.test
@@ -0,0 +1,31 @@
+statement ok
+CREATE OR REPLACE FILTER FUNCTION maxlev_helper(a1 INTEGER, a2 INTEGER, a3
INTEGER, a4 INTEGER, a5 DOUBLE) EXTERNAL NAME txtsim.maxlevenshtein;
+
+statement ok
+CREATE TABLE t1 (a1 INTEGER, a2 INTEGER, p DOUBLE);
+
+statement ok
+CREATE TABLE t2 (a1 INTEGER, p DOUBLE);
+
+statement ok
+CREATE TABLE t3 (a1 INTEGER, a2 INTEGER, p DOUBLE);
+
+statement ok
+CREATE VIEW t4 AS SELECT t3.a1 AS a1, t3.a2 AS a2, t2.p AS a3, t2.p AS p FROM
t3, t2 WHERE t3.a2 = t2.a1;
+
+query T nosort
+PLAN SELECT COUNT(*) FROM (SELECT t1.a1 AS a1, t1.a2 AS a2, t4.a1 AS a3, t4.a2
AS a4, t4.a3 AS a5, t1.p AS p FROM t1, t4 WHERE [t1.a1,t1.a2] maxlev_helper
[t4.a1,t4.a2,t4.a3]) AS foo;
+----
+project (
+| group by (
+| | project (
+| | | join (
+| | | | join (
+| | | | | table("sys"."t3") [ "t3"."a1" NOT NULL UNIQUE, "t3"."a2" NOT NULL
UNIQUE ],
+| | | | | table("sys"."t2") [ "t2"."a1" NOT NULL UNIQUE, "t2"."p" NOT NULL
UNIQUE ]
+| | | | ) [ ("t3"."a2" NOT NULL UNIQUE) = ("t2"."a1" NOT NULL UNIQUE) ],
+| | | | table("sys"."t1") [ "t1"."a1" NOT NULL UNIQUE, "t1"."a2" NOT NULL
UNIQUE ]
+| | | ) [ ("t1"."a1" NOT NULL UNIQUE, "t1"."a2" NOT NULL UNIQUE) FILTER
"sys"."maxlev_helper"("t3"."a1" NOT NULL, "t3"."a2" NOT NULL UNIQUE, "t2"."p"
NOT NULL) ]
+| | ) [ "t1"."a1" NOT NULL as "foo"."a1" ]
+| ) [ ] [ "sys"."count"() NOT NULL UNIQUE as "%1"."%1" ]
+) [ "%1"."%1" NOT NULL UNIQUE ]
diff --git a/sql/test/BugTracker-2025/Tests/7615_join_reordering_2.test
b/sql/test/BugTracker-2025/Tests/7615_join_reordering_2.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7615_join_reordering_2.test
@@ -0,0 +1,42 @@
+statement ok
+CREATE OR REPLACE FILTER FUNCTION maxlevhelper(a1 INTEGER, a2 INTEGER, a3
INTEGER, a4 INTEGER, a5 DOUBLE) EXTERNAL NAME txtsim.maxlevenshtein
+
+statement ok
+CREATE TABLE x1 (a1 INTEGER, a2 INTEGER, p DOUBLE)
+
+statement ok
+CREATE TABLE x2 (a1 INTEGER, a2 INTEGER, p DOUBLE)
+
+statement ok
+CREATE TABLE x3 (a1 INTEGER, a2 INTEGER, p DOUBLE)
+
+statement ok
+CREATE TABLE x4 (a1 INTEGER, a2 INTEGER, a3 DOUBLE, p DOUBLE)
+
+statement ok
+CREATE VIEW x5 AS SELECT x4.a1 AS a1, x4.a2 as a2, x4.a3 as a3, x4.p as p from
x4, (select case when a1 = a2 then 1 else 0 end as a1, 1.0e0 as p from (select
foox0.a1 as a1, foox1.a1 as a2, foox0.p * foox1.p as p from (select count(a1)
as a1, max(p) as p from x3) as foox0, (select count(a1) as a1, max(p) as p from
x4) as foox1) as foox2) as foox3 where foox3.a1 = 1
+
+statement ok
+CREATE VIEW result AS SELECT x1.a1 AS a1, x1.a2 AS a2, x5.a1 AS a3, x5.a2 AS
a4, x5.a3 AS a5, x1.p * x5.p as p from x1, x5 where [x1.a1,x1.a2] maxlevhelper
[x5.a1,x5.a2,x5.a3]
+
+query T nosort
+plan select * from result;
+----
+project (
+| project (
+| | crossproduct (
+| | | join (
+| | | | table("sys"."x1") [ "x1"."a1" NOT NULL UNIQUE, "x1"."a2" NOT NULL
UNIQUE, "x1"."p" NOT NULL UNIQUE ],
+| | | | table("sys"."x4") [ "x4"."a1" NOT NULL UNIQUE, "x4"."a2" NOT NULL
UNIQUE, "x4"."a3" NOT NULL UNIQUE, "x4"."p" NOT NULL UNIQUE ]
+| | | ) [ ("x1"."a1" NOT NULL UNIQUE, "x1"."a2" NOT NULL UNIQUE) FILTER
"sys"."maxlevhelper"("x4"."a1" NOT NULL UNIQUE, "x4"."a2" NOT NULL UNIQUE,
"x4"."a3" NOT NULL UNIQUE) ],
+| | | select (
+| | | | project (
+| | | | | crossproduct (
+| | | | | | [ "sys"."cnt"(varchar "sys", varchar "x3") NOT NULL as "%1"."%1"
],
+| | | | | | [ "sys"."cnt"(varchar "sys", varchar "x4") NOT NULL as "%3"."%3" ]
+| | | | | ) [ ]
+| | | | ) [ "sys"."case"("sys"."="("%1"."%1" NOT NULL, "%3"."%3" NOT NULL) NOT
NULL, tinyint(1) "1", tinyint(1) "0") as "foox3"."a1" ]
+| | | ) [ ("foox3"."a1") = (tinyint(1) "1") ]
+| | ) [ ]
+| ) [ "x1"."a1" NOT NULL as "a1", "x1"."a2" NOT NULL as "a2", "x4"."a1" NOT
NULL as "a3", "x4"."a2" NOT NULL as "a4", "x4"."a3" NOT NULL as "a5",
"sys"."sql_mul"("x1"."p" NOT NULL, "x4"."p" NOT NULL) NOT NULL as "p" ]
+) [ "a1" NOT NULL as "result"."a1", "a2" NOT NULL as "result"."a2", "a3" NOT
NULL as "result"."a3", "a4" NOT NULL as "result"."a4", "a5" NOT NULL as
"result"."a5", "p" NOT NULL as "result"."p" ]
diff --git a/sql/test/BugTracker-2025/Tests/7616_join_reordering_3.test
b/sql/test/BugTracker-2025/Tests/7616_join_reordering_3.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7616_join_reordering_3.test
@@ -0,0 +1,22 @@
+statement ok
+CREATE TABLE x (a1 INTEGER, a2 INTEGER, a3 CHARACTER LARGE OBJECT, a4
CHARACTER LARGE OBJECT, p DOUBLE)
+
+statement ok
+CREATE TABLE y (a1 INTEGER, p DOUBLE)
+
+query T nosort
+PLAN SELECT a1, p FROM (SELECT y.a1 AS a1, foo_x3.a1 AS a2, y.p * foo_x3.p AS
p FROM y, (SELECT a1, p FROM (SELECT foo_x0.a1 AS a1, foo_x0.a2 AS a2,
foo_x1.a1 AS a3, foo_x0.p AS p FROM (SELECT a1, a3 AS a2, p FROM x) AS foo_x0,
(VALUES ('bar',1.0e0)) AS foo_x1(a1,p) WHERE [foo_x0.a2] contains
[foo_x1.a1,true]) AS foo_x2) AS foo_x3 WHERE y.a1 = foo_x3.a1) AS foo_x4
+----
+project (
+| project (
+| | join (
+| | | join (
+| | | | project (
+| | | | | table("sys"."x") [ "x"."a1" NOT NULL UNIQUE, "x"."a3" NOT NULL
UNIQUE, "x"."p" NOT NULL UNIQUE ]
+| | | | ) [ "x"."a1" NOT NULL UNIQUE as "foo_x0"."a1", "x"."a3" NOT NULL
UNIQUE as "foo_x0"."a2", "x"."p" NOT NULL UNIQUE as "foo_x0"."p" ],
+| | | | [ [ varchar(3) "bar" ] as "foo_x1"."a1" ]
+| | | ) [ ("foo_x0"."a2" NOT NULL UNIQUE) FILTER
"sys"."contains"(varchar["foo_x1"."a1" NOT NULL] NOT NULL, boolean(1) "true") ],
+| | | table("sys"."y") [ "y"."a1" NOT NULL UNIQUE, "y"."p" NOT NULL UNIQUE ]
+| | ) [ ("y"."a1" NOT NULL UNIQUE) = ("foo_x0"."a1" NOT NULL) ]
+| ) [ "y"."a1" NOT NULL as "foo_x4"."a1", "sys"."sql_mul"("y"."p" NOT NULL,
"foo_x0"."p" NOT NULL) NOT NULL as "foo_x4"."p" ]
+) [ "foo_x4"."a1" NOT NULL, "foo_x4"."p" NOT NULL ]
diff --git a/sql/test/BugTracker-2025/Tests/All
b/sql/test/BugTracker-2025/Tests/All
--- a/sql/test/BugTracker-2025/Tests/All
+++ b/sql/test/BugTracker-2025/Tests/All
@@ -1,1 +1,4 @@
7611_multiple_unnd_constraints
+7614_join_reordering
+7615_join_reordering_2
+7616_join_reordering_3
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
