Changeset: 2d4518c9bdce for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2d4518c9bdce
Modified Files:
sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/check.stable.out
sql/test/emptydb-upgrade-chain/Tests/check.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
sql/test/emptydb-upgrade/Tests/check.stable.out
sql/test/emptydb-upgrade/Tests/check.stable.out.32bit
sql/test/emptydb-upgrade/Tests/check.stable.out.int128
sql/test/emptydb/Tests/check.SQL.py
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Check that "external" SQL functions refer to existing MAL functions.
diffs (276 lines):
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/check.stable.out.int128
@@ -1192,6 +1192,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3453,6 +3455,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out
@@ -1178,6 +1178,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3258,6 +3260,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.32bit
b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.32bit
--- a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.32bit
@@ -1182,6 +1182,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3262,6 +3264,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/check.stable.out.int128
@@ -1192,6 +1192,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3453,6 +3455,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/check.stable.out.int128
@@ -1192,6 +1192,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3453,6 +3455,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade/Tests/check.stable.out
b/sql/test/emptydb-upgrade/Tests/check.stable.out
--- a/sql/test/emptydb-upgrade/Tests/check.stable.out
+++ b/sql/test/emptydb-upgrade/Tests/check.stable.out
@@ -1178,6 +1178,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3258,6 +3260,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade/Tests/check.stable.out.32bit
b/sql/test/emptydb-upgrade/Tests/check.stable.out.32bit
--- a/sql/test/emptydb-upgrade/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb-upgrade/Tests/check.stable.out.32bit
@@ -1182,6 +1182,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3262,6 +3264,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb-upgrade/Tests/check.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/check.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/check.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/check.stable.out.int128
@@ -1192,6 +1192,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3453,6 +3455,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb/Tests/check.SQL.py
b/sql/test/emptydb/Tests/check.SQL.py
--- a/sql/test/emptydb/Tests/check.SQL.py
+++ b/sql/test/emptydb/Tests/check.SQL.py
@@ -72,6 +72,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
'''
# generate a monster query to get all functions with all their
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -1178,6 +1178,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3258,6 +3260,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -1182,6 +1182,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3262,6 +3264,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -1192,6 +1192,8 @@ select s.name, t.name, replace(replace(p
select t.name, c.name, c.type, c.type_digits, c.type_scale, c."default",
c."null", c.number, c.storage from sys._tables t, sys._columns c where t.id =
c.table_id order by t.name, c.number;
-- functions
select s.name, f.name,
replace(replace(pcre_replace(pcre_replace(pcre_replace(f.func, '--.*\n', '',
''), '[ \t\n]+', ' ', 'm'), '^ ', '', ''), '( ', '('), ' )', ')') as query,
f.mod, f.language, f.type, f.side_effect, f.varres, f.vararg from sys.functions
f left outer join sys.schemas s on f.schema_id = s.id order by s.name, f.name,
query;
+-- external functions that don't reference existing MAL function (should be
empty)
+with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
-- args
with
arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
@@ -3453,6 +3455,11 @@ drop function pcre_replace(string, strin
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "year", "year", "mtime", 0, 1, false, false,
false ]
[ "sys", "zero_or_one", "zero_or_one", "sql", 0, 3, false,
false, false ]
+#with x(name,func) as (select name, splitpart(func, ' external name ', 2) from
sys.functions where func like '% external name %' union select name,
splitpart(func, ' EXTERNAL NAME ', 2) from sys.functions where func like '%
EXTERNAL NAME %'), y(name,func) as (select name, trim(splitpart(func, ';', 1))
from x), z(name, mod, func) as (select name, trim(splitpart(func, '.', 1),
'"'), trim(splitpart(func, '.', 2), '"') from y) select z.name, z.mod, z.func
from z where z.mod || '.' || z.func not in (select m.module || '.' ||
m."function" from sys.malfunctions() m);
+% .z, .z, .z # table_name
+% name, mod, func # name
+% varchar, varchar, varchar # type
+% 0, 0, 0 # length
#with
#arg1 (id, id1, name1, type1, type_digits1, type_scale1, inout1) as (select
f.id, a1.id, a1.name, a1.type, a1.type_digits, a1.type_scale, a1.inout from
sys.functions f left outer join args a1 on a1.func_id = f.id and a1.number = 1),
#arg2 (id, id1, name1, type1, type_digits1, type_scale1, inout1, id2, name2,
type2, type_digits2, type_scale2, inout2) as (select arg1.*, a2.id, a2.name,
a2.type, a2.type_digits, a2.type_scale, a2.inout from arg1 left outer join args
a2 on a2.func_id = arg1.id and a2.number = 2),
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
