Changeset: 2e4f18297144 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2e4f18297144
Added Files:
sql/test/miscellaneous/Tests/groupby_error.test
sql/test/miscellaneous/Tests/groupby_prepare.SQL.py
sql/test/miscellaneous/Tests/groupby_prepare.sql
sql/test/miscellaneous/Tests/groupby_prepare.stable.err
sql/test/miscellaneous/Tests/groupby_prepare.stable.out
Removed Files:
sql/test/miscellaneous/Tests/groupby_error.SQL.py
Modified Files:
sql/test/miscellaneous/Tests/All
sql/test/miscellaneous/Tests/groupby_error.sql
sql/test/miscellaneous/Tests/groupby_error.stable.err
sql/test/miscellaneous/Tests/groupby_error.stable.out
Branch: default
Log Message:
Split prepared statements into a different test
diffs (truncated from 1318 to 300 lines):
diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All
--- a/sql/test/miscellaneous/Tests/All
+++ b/sql/test/miscellaneous/Tests/All
@@ -7,6 +7,7 @@ groupby_expressions
values
with_update_stmts
groupby_error
+groupby_prepare
alter_table_add_column
view-mul-digits
select_groupby
diff --git a/sql/test/miscellaneous/Tests/groupby_error.sql
b/sql/test/miscellaneous/Tests/groupby_error.sql
--- a/sql/test/miscellaneous/Tests/groupby_error.sql
+++ b/sql/test/miscellaneous/Tests/groupby_error.sql
@@ -97,41 +97,8 @@ SELECT DISTINCT * FROM tab2 WHERE NOT (
SELECT col0 FROM tab0 ORDER BY sys.tab0.col0; --error, TODO
-prepare select col0 from tab0 where (?) in (select col0 from tab0);
-prepare select col0 from tab0 where (?,?) in (select col0,col1 from tab0);
-prepare select col0 from tab0 where (col1,col1) in (select col0,? from tab0);
-prepare select col0 from tab0 where (col1,col1) in (select ?,? from tab0);
-prepare select col0 from tab0 where (col0) in (?);
-prepare select col0 from tab0 where (col0) in (?,?);
-
-prepare select ? < ANY (select max(col0) from tab0) from tab0 t1;
-prepare select col0 = ALL (select ? from tab0) from tab0 t1;
-
-prepare select 1 from tab0 where 1 between ? and ?;
-prepare select 1 from tab0 where ? between 1 and ?;
-prepare select 1 from tab0 where ? between ? and 1;
-
-prepare select EXISTS (SELECT ? FROM tab0) from tab0;
-prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
-
-prepare select col0 from tab0 where (?) in (?); --error
-prepare select ? = ALL (select ? from tab0) from tab0 t1; --error
-prepare select 1 from tab0 where ? between ? and ?; --error
-
-prepare select case when col0 = 0 then ? else 1 end from tab0;
-prepare select case when col0 = 0 then 1 else ? end from tab0;
-prepare select case when col0 = 0 then ? else ? end from tab0; --error
-
-prepare select case when col0 = 0 then ? when col0 = 1 then ? else 1 end from
tab0;
-prepare select case when col0 = 0 then ? when col0 = 1 then ? else ? end from
tab0; --error
-
-prepare select ? is null from tab0; --error
-prepare select max(?); --error
-prepare select max(?) over (); --error
-
drop table tab1;
CREATE TABLE tab1(col0 INTEGER, col1 STRING);
-prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
SELECT 1 FROM tab0 where CASE WHEN 64 IN ( col0 ) THEN true END;
--empty
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.err
b/sql/test/miscellaneous/Tests/groupby_error.stable.err
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.err
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.err
@@ -13,38 +13,6 @@ MAPI = (monetdb) /var/tmp/mtest-561836/
QUERY = SELECT col0 FROM tab0 ORDER BY sys.tab0.col0; --error, TODO
ERROR = !TODO: column names of level >= 3
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-561836/.s.monetdb.32526
-QUERY = prepare select col0 from tab0 where (?) in (?); --error
-ERROR = !Cannot have a parameter (?) on both sides of an expression
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-54771/.s.monetdb.35007
-QUERY = prepare select ? = ALL (select ? from tab0) from tab0 t1; --error
-ERROR = !Cannot have a parameter (?) on both sides of an expression
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-109859/.s.monetdb.35986
-QUERY = prepare select 1 from tab0 where ? between ? and ?; --error
-ERROR = !Cannot have a parameter (?) on both sides of an expression
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-128195/.s.monetdb.35720
-QUERY = prepare select case when col0 = 0 then ? else ? end from tab0; --error
-ERROR = !Result type missing
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-128195/.s.monetdb.35720
-QUERY = prepare select case when col0 = 0 then ? when col0 = 1 then ? else ?
end from tab0; --error
-ERROR = !Result type missing
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-143244/.s.monetdb.35226
-QUERY = prepare select ? is null from tab0; --error
-ERROR = !Could not determine type for argument number 1
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-160872/.s.monetdb.39398
-QUERY = prepare select max(?); --error
-ERROR = !MAX: parameters not allowed as arguments to aggregate functions
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-165100/.s.monetdb.38196
-QUERY = prepare select max(?) over (); --error
-ERROR = !MAX: parameters not allowed as arguments to window functions
-CODE = 42000
# 10:56:47 >
# 10:56:47 > "Done."
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.out
b/sql/test/miscellaneous/Tests/groupby_error.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.out
@@ -248,153 +248,8 @@ project (
[ 64, 77, 40 ]
[ 75, 67, 58 ]
[ 46, 51, 23 ]
-#prepare select col0 from tab0 where (?) in (select col0 from tab0);
-#prepare select col0 from tab0 where (?) in (select col0 from tab0);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 from tab0 where (?,?) in (select col0,col1 from tab0);
-#prepare select col0 from tab0 where (?,?) in (select col0,col1 from tab0);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 from tab0 where (col1,col1) in (select col0,? from tab0);
-#prepare select col0 from tab0 where (col1,col1) in (select col0,? from tab0);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 from tab0 where (col1,col1) in (select ?,? from tab0);
-#prepare select col0 from tab0 where (col1,col1) in (select ?,? from tab0);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 from tab0 where (col0) in (?);
-#prepare select col0 from tab0 where (col0) in (?);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 from tab0 where (col0) in (?,?);
-#prepare select col0 from tab0 where (col0) in (?,?);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 3, 3, 1, 0, 4, 4 # length
-[ "int", 32, 0, "", "tab0", "col0" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select ? < ANY (select max(col0) from tab0) from tab0 t1;
-#prepare select ? < ANY (select max(col0) from tab0) from tab0 t1;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 2, 1, 0, 2, 2 # length
-[ "boolean", 1, 0, "", "%4", "%4" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select col0 = ALL (select ? from tab0) from tab0 t1;
-#prepare select col0 = ALL (select ? from tab0) from tab0 t1;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 2, 1, 0, 2, 2 # length
-[ "boolean", 1, 0, "", "%4", "%4" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-#prepare select 1 from tab0 where 1 between ? and ?;
-#prepare select 1 from tab0 where 1 between ? and ?;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-#prepare select 1 from tab0 where ? between 1 and ?;
-#prepare select 1 from tab0 where ? between 1 and ?;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-#prepare select 1 from tab0 where ? between ? and 1;
-#prepare select 1 from tab0 where ? between ? and 1;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-#prepare select EXISTS (SELECT ? FROM tab0) from tab0;
-#prepare select EXISTS (SELECT ? FROM tab0) from tab0;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "boolean", 1, 0, "", "%2", "%2" ]
-[ "boolean", 1, 0, NULL, NULL, NULL ]
-#prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
-#prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "boolean", 1, 0, "", "%3", "%3" ]
-[ "boolean", 1, 0, NULL, NULL, NULL ]
-[ "boolean", 1, 0, NULL, NULL, NULL ]
-#prepare select case when col0 = 0 then ? else 1 end from tab0;
-#prepare select case when col0 = 0 then ? else 1 end from tab0;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-#prepare select case when col0 = 0 then 1 else ? end from tab0;
-#prepare select case when col0 = 0 then 1 else ? end from tab0;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-#prepare select case when col0 = 0 then ? when col0 = 1 then ? else 1 end from
tab0;
-#prepare select case when col0 = 0 then ? when col0 = 1 then ? else 1 end from
tab0;
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 1, 1, 0, 2, 2 # length
-[ "tinyint", 1, 0, "", "%1", "%1" ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
-[ "tinyint", 1, 0, NULL, NULL, NULL ]
#drop table tab1;
#CREATE TABLE tab1(col0 INTEGER, col1 STRING);
-#prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
-#prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
-% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
-% type, digits, scale, schema, table, column # name
-% varchar, int, int, str, str, str # type
-% 7, 2, 1, 0, 3, 3 # length
-[ "tinyint", 1, 0, "", "%10", "%10" ]
-[ "int", 32, 0, NULL, NULL, NULL ]
-[ "clob", 0, 0, NULL, NULL, NULL ]
#SELECT 1 FROM tab0 where CASE WHEN 64 IN ( col0 ) THEN true END;
% .%4 # table_name
% %4 # name
diff --git a/sql/test/miscellaneous/Tests/groupby_error.test
b/sql/test/miscellaneous/Tests/groupby_error.test
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/groupby_error.test
@@ -0,0 +1,443 @@
+statement ok
+CREATE SCHEMA "kagami_dump"
+
+statement ok
+CREATE TABLE "kagami_dump"."test_task" ("sys_id" CHAR(32) DEFAULT '', "number"
VARCHAR(40), "parent" VARCHAR(32))
+
+statement ok
+INSERT INTO "kagami_dump".test_task(sys_id, number, parent) VALUES ('aaa',
'T0001', null),('bbb','T0002','aaa')
+
+statement error
+SELECT parent."sys_id" FROM "kagami_dump"."test_task" parent INNER JOIN
"kagami_dump"."test_task" child ON child."parent" = parent."sys_id" GROUP BY
parent."sys_id" HAVING count(child."sys_id") >= 1 ORDER BY parent."number"
+
+statement ok
+DROP SCHEMA "kagami_dump" CASCADE
+
+statement ok
+CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER)
+
+statement ok
+INSERT INTO tab0 VALUES(97,1,99), (15,81,47), (87,21,10)
+
+statement ok
+CREATE TABLE tab1(col0 INTEGER, col1 INTEGER, col2 INTEGER)
+
+statement ok
+INSERT INTO tab1 VALUES (51,14,96), (85,5,59), (91,47,68)
+
+statement ok
+CREATE TABLE tab2(col0 INTEGER, col1 INTEGER, col2 INTEGER)
+
+statement ok
+INSERT INTO tab2 VALUES(64,77,40), (75,67,58), (46,51,23)
+
+query I rowsort
+SELECT CAST(+ col1 * - col1 AS BIGINT) AS col2 FROM tab0 GROUP BY col2, col0,
col1 HAVING + - col0 / - AVG ( ALL + col2 ) - - - AVG ( DISTINCT + col0 ) +
col0 IS NULL
+----
+
+query I rowsort
+SELECT DISTINCT + 40 / + + col0 AS col2 FROM tab0 GROUP BY col0, col0, col2
HAVING NOT ( NOT + - 80 BETWEEN NULL AND + - 73 ) OR NOT ( + col0 >= - COUNT (
* ) + - COUNT ( DISTINCT - col0 ) )
+----
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
