Changeset: 6d5185c50db5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/6d5185c50db5
Branch: default
Log Message:
Merge with Mar2025 branch.
diffs (265 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
diff --git a/testing/Mtest.py.in b/testing/Mtest.py.in
--- a/testing/Mtest.py.in
+++ b/testing/Mtest.py.in
@@ -1312,13 +1312,19 @@ def PerformDir(env, testdir, testlist, t
FdOut = FdErr = F_FAIL
break
try:
- dbh = pymonetdb.connect(username='monetdb',
- password='monetdb',
- hostname=HOST,
- port=int(pSrvr.port),
- database=TSTDB,
- autocommit=True,
- connect_timeout=1.0)
+ with pymonetdb.connect(username='monetdb',
+ password='monetdb',
+ hostname=HOST,
+ port=int(pSrvr.port),
+ database=TSTDB,
+ autocommit=True,
+ connect_timeout=1.0) as dbh:
+ dbh.settimeout(10)
+ with dbh.cursor() as crs:
+ try:
+ crs.execute("call
logging.setcomplevel('SQL_EXECUTION', 'INFO')")
+ except TimeoutError:
+ print('\nTimeout setting log level.\n')
except KeyboardInterrupt:
raise
except ConnectionRefusedError:
@@ -1327,15 +1333,6 @@ def PerformDir(env, testdir, testlist, t
break
except:
pass
- else:
- dbh.settimeout(10)
- crs = dbh.cursor()
- try:
- crs.execute("call
logging.setcomplevel('SQL_EXECUTION', 'INFO')")
- except TimeoutError:
- print('\nTimeout setting log level.\n')
- crs.close()
- dbh.close()
os.environ['MAPIPORT'] = env['MAPIPORT'] = pSrvr.port
SetExecEnv(env['exe'], pSrvr.port, verbosity > 1)
if global_timeout and start_time + global_timeout <
time.time():
@@ -1672,34 +1669,31 @@ def GetBitsAndModsAndThreads(env) :
connurl = open(os.path.join(dbpath, '.conn')).read()
res = mapiportre.search(connurl)
try:
- dbh = pymonetdb.connect(username='monetdb',
- password='monetdb',
- hostname=HOST,
- port=int(res.group('port')),
- database=TSTPREF,
- autocommit=True,
- connect_timeout=1.0)
+ with pymonetdb.connect(username='monetdb',
+ password='monetdb',
+ hostname=HOST,
+ port=int(res.group('port')),
+ database=TSTPREF,
+ autocommit=True,
+ connect_timeout=1.0) as dbh:
+ if timeout:
+ dbh.settimeout(30)
+ with dbh.cursor() as crs:
+ try:
+ crs.execute('select distinct module from
sys.malfunctions() order by module')
+ except TimeoutError:
+ pass
+ else:
+ mods = crs.fetchall()
+ mods = [x[0] for x in mods]
+ try:
+ mods.remove('user')
+ except ValueError:
+ pass
except KeyboardInterrupt:
raise
except:
pass
- else:
- if timeout:
- dbh.settimeout(30)
- crs = dbh.cursor()
- try:
- crs.execute('select distinct module from
sys.malfunctions() order by module')
- except TimeoutError:
- pass
- else:
- mods = crs.fetchall()
- mods = [x[0] for x in mods]
- try:
- mods.remove('user')
- except ValueError:
- pass
- crs.close()
- dbh.close()
proc.terminate()
qOut = proc.stdout.read(timeout=5)
qErr = proc.stderr.read(timeout=5)
@@ -2687,27 +2681,25 @@ class ServerClass:
if self.timer is not None:
self.stacktrace()
try:
- dbh = pymonetdb.connect(username='monetdb',
- password='monetdb',
- hostname=HOST,
- port=int(self.port),
- database=self.dbname,
- connect_timeout=1.0)
- dbh.settimeout(20)
- crs = dbh.cursor()
- crs.execute('select sessionid from sys.sessions()
where sessionid <> sys.current_sessionid()')
- ids = crs.fetchall()
- dbh.settimeout(10)
- for x in ids:
- if procdebug:
- print(f'stopping session {x[0]}')
- crs.execute(f'call sys.stopsession({x[0]})')
- if procdebug and not ids:
- print('no sessions to stop')
+ with pymonetdb.connect(username='monetdb',
+ password='monetdb',
+ hostname=HOST,
+ port=int(self.port),
+ database=self.dbname,
+ connect_timeout=1.0) as dbh:
+ dbh.settimeout(20)
+ with dbh.cursor() as crs:
+ crs.execute('select sessionid from
sys.sessions() where sessionid <> sys.current_sessionid()')
+ ids = crs.fetchall()
+ dbh.settimeout(10)
+ for x in ids:
+ if procdebug:
+ print(f'stopping session {x[0]}')
+ crs.execute(f'call
sys.stopsession({x[0]})')
+ if procdebug and not ids:
+ print('no sessions to stop')
except TimeoutError:
self.proc.kill()
- crs.close()
- dbh.close()
self.timer = None
finally:
self.lock.release()
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
