Changeset: da8527488eef for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=da8527488eef
Added Files:
sql/test/subquery/Tests/subquery4.sql
sql/test/subquery/Tests/subquery4.stable.err
sql/test/subquery/Tests/subquery4.stable.out
Modified Files:
sql/test/subquery/Tests/All
sql/test/subquery/Tests/subquery3.sql
sql/test/subquery/Tests/subquery3.stable.err
sql/test/subquery/Tests/subquery3.stable.out
Branch: linear-hashing
Log Message:
Backporting subquery tests into linear-hashing because, I would like it to be
paired with default for subquery fixes, and I found another crash this morning
diffs (297 lines):
diff --git a/sql/test/subquery/Tests/All b/sql/test/subquery/Tests/All
--- a/sql/test/subquery/Tests/All
+++ b/sql/test/subquery/Tests/All
@@ -3,6 +3,7 @@ any
all
subquery2
subquery3
+subquery4
scalar
any_all
exists
diff --git a/sql/test/subquery/Tests/subquery3.sql
b/sql/test/subquery/Tests/subquery3.sql
--- a/sql/test/subquery/Tests/subquery3.sql
+++ b/sql/test/subquery/Tests/subquery3.sql
@@ -441,8 +441,16 @@ FROM another_T;
-- 1
SELECT
- (SELECT outt FROM evilfunction((SELECT MAX(col1) FROM
tbl_ProductSales)))
-FROM another_T; --error, more than one row returned by a subquery used as an
expression
+ (SELECT outt FROM evilfunction((SELECT MAX(ColID) FROM
tbl_ProductSales)))
+FROM another_T;
+ -- 4
+ -- 4
+ -- 4
+ -- 4
+
+SELECT
+ (SELECT outt FROM evilfunction((SELECT MAX(t1.col1) FROM
tbl_ProductSales)))
+FROM another_T t1; --error, more than one row returned by a subquery used as
an expression
SELECT
(SELECT outt FROM evilfunction((SELECT MIN(t2.col1) FROM another_T
t2)))
@@ -452,9 +460,27 @@ FROM another_T;
-- 1
-- 1
-PREPARE SELECT
- (SELECT ? FROM evilfunction((SELECT 1)))
-FROM another_T;
+CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS TABLE (outt INT)
BEGIN RETURN TABLE(VALUES (input), (input)); END;
+
+SELECT * FROM evilfunction(1);
+ -- 1
+ -- 1
+
+SELECT
+ (SELECT outt FROM evilfunction((SELECT MIN(col1))))
+FROM another_T; --error, more than one row returned by a subquery used as an
expression
+
+SELECT
+ (SELECT outt FROM evilfunction((SELECT MAX(ColID) FROM
tbl_ProductSales)))
+FROM another_T; --error, more than one row returned by a subquery used as an
expression
+
+SELECT
+ (SELECT outt FROM evilfunction((SELECT MAX(t1.col1) FROM
tbl_ProductSales)))
+FROM another_T t1; --error, more than one row returned by a subquery used as
an expression
+
+SELECT
+ (SELECT outt FROM evilfunction((SELECT MIN(t2.col1) FROM another_T
t2)))
+FROM another_T; --error, more than one row returned by a subquery used as an
expression
/* We shouldn't allow the following internal functions/procedures to be called
from regular queries */
--SELECT "identity"(col1) FROM another_T;
diff --git a/sql/test/subquery/Tests/subquery3.stable.err
b/sql/test/subquery/Tests/subquery3.stable.err
--- a/sql/test/subquery/Tests/subquery3.stable.err
+++ b/sql/test/subquery/Tests/subquery3.stable.err
@@ -252,18 +252,36 @@ QUERY = SELECT
FROM another_T; --error, more than one row returned by a subquery used
as an expression
ERROR = !Cardinality violation, scalar value expected
CODE = 21000
-MAPI = (monetdb) /var/tmp/mtest-511176/.s.monetdb.31334
+MAPI = (monetdb) /var/tmp/mtest-516119/.s.monetdb.36929
QUERY = SELECT
- (SELECT outt FROM evilfunction((SELECT MAX(col1) FROM
tbl_ProductSales)))
- FROM another_T; --error, more than one row returned by a subquery
used as an expression
+ (SELECT outt FROM evilfunction((SELECT MAX(t1.col1) FROM
tbl_ProductSales)))
+ FROM another_T t1; --error, more than one row returned by a subquery
used as an expression
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-123694/.s.monetdb.33701
+QUERY = SELECT
+ (SELECT outt FROM evilfunction((SELECT MIN(col1))))
+ FROM another_T; --error, more than one row returned by a subquery used
as an expression
ERROR = !zero_or_one: cardinality violation, scalar expression expected
CODE = M0M29
-MAPI = (monetdb) /var/tmp/mtest-511176/.s.monetdb.31334
-QUERY = PREPARE SELECT
- (SELECT ? FROM evilfunction((SELECT 1)))
- FROM another_T;
-ERROR = !Could not determine type for argument number 1
-CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-123694/.s.monetdb.33701
+QUERY = SELECT
+ (SELECT outt FROM evilfunction((SELECT MAX(ColID) FROM
tbl_ProductSales)))
+ FROM another_T; --error, more than one row returned by a subquery used
as an expression
+ERROR = !Cardinality violation, scalar value expected
+CODE = 21000
+MAPI = (monetdb) /var/tmp/mtest-123694/.s.monetdb.33701
+QUERY = SELECT
+ (SELECT outt FROM evilfunction((SELECT MAX(t1.col1) FROM
tbl_ProductSales)))
+ FROM another_T t1; --error, more than one row returned by a subquery
used as an expression
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-123694/.s.monetdb.33701
+QUERY = SELECT
+ (SELECT outt FROM evilfunction((SELECT MIN(t2.col1) FROM
another_T t2)))
+ FROM another_T; --error, more than one row returned by a subquery used
as an expression
+ERROR = !Cardinality violation, scalar value expected
+CODE = 21000
# 17:40:41 >
# 17:40:41 > "Done."
diff --git a/sql/test/subquery/Tests/subquery3.stable.out
b/sql/test/subquery/Tests/subquery3.stable.out
--- a/sql/test/subquery/Tests/subquery3.stable.out
+++ b/sql/test/subquery/Tests/subquery3.stable.out
@@ -437,6 +437,17 @@ stdout of test 'subquery3` in directory
% 1 # length
[ 1 ]
#SELECT
+# (SELECT outt FROM evilfunction((SELECT MAX(col1) FROM
tbl_ProductSales)))
+#FROM another_T;
+% .%4 # table_name
+% %4 # name
+% int # type
+% 1 # length
+[ 4 ]
+[ 4 ]
+[ 4 ]
+[ 4 ]
+#SELECT
# (SELECT outt FROM evilfunction((SELECT MIN(t2.col1) FROM another_T
t2)))
#FROM another_T;
% .%4 # table_name
@@ -447,6 +458,14 @@ stdout of test 'subquery3` in directory
[ 1 ]
[ 1 ]
[ 1 ]
+#CREATE OR REPLACE FUNCTION evilfunction(input INT) RETURNS TABLE (outt INT)
BEGIN RETURN TABLE(VALUES (input), (input)); END;
+#SELECT * FROM evilfunction(1);
+% .%2 # table_name
+% outt # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
#DROP FUNCTION evilfunction(INT);
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
diff --git a/sql/test/subquery/Tests/subquery4.sql
b/sql/test/subquery/Tests/subquery4.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/subquery/Tests/subquery4.sql
@@ -0,0 +1,26 @@
+CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6
INT, col7 INT, col8 INT);
+INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+CREATE TABLE integers(i INTEGER);
+INSERT INTO integers VALUES (1), (2), (3), (NULL);
+CREATE FUNCTION evilfunction(input INT) RETURNS TABLE (outt INT) BEGIN RETURN
TABLE(SELECT input); END;
+CREATE FUNCTION evilfunction(input1 INT, input2 INT) RETURNS TABLE (outt INT)
BEGIN RETURN TABLE(SELECT input1 + input2); END;
+
+PREPARE SELECT
+ (SELECT ? FROM evilfunction((SELECT 1)))
+FROM another_T;
+
+PREPARE SELECT
+ (SELECT 1 FROM evilfunction((SELECT ?)))
+FROM another_T;
+
+PREPARE SELECT
+ (SELECT 1 FROM evilfunction((SELECT ?, ?)))
+FROM another_T;
+
+DROP FUNCTION evilfunction(INT);
+DROP FUNCTION evilfunction(INT, INT);
+DROP TABLE tbl_ProductSales;
+DROP TABLE another_T;
+DROP TABLE integers;
diff --git a/sql/test/subquery/Tests/subquery4.stable.err
b/sql/test/subquery/Tests/subquery4.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/subquery/Tests/subquery4.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'subquery4` in directory 'sql/test/subquery` itself:
+
+
+# 15:41:18 >
+# 15:41:18 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34582" "--set"
"mapi_usock=/var/tmp/mtest-262250/.s.monetdb.34582" "--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_subquery"
"--set" "embedded_c=true"
+# 15:41:18 >
+
+# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-default/BUILD/var/monetdb5/dbfarm/demo
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_ipv6 = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# builtin opt raw_strings = false
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 34582
+# cmdline opt mapi_usock = /var/tmp/mtest-262250/.s.monetdb.34582
+# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_subquery
+# cmdline opt embedded_c = true
+
+# 15:41:18 >
+# 15:41:18 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-262250" "--port=34582"
+# 15:41:18 >
+
+MAPI = (monetdb) /var/tmp/mtest-262250/.s.monetdb.34582
+QUERY = PREPARE SELECT
+ (SELECT ? FROM evilfunction((SELECT 1)))
+ FROM another_T;
+ERROR = !Could not determine type for argument number 1
+CODE = 42000
+
+# 15:41:18 >
+# 15:41:18 > "Done."
+# 15:41:18 >
+
diff --git a/sql/test/subquery/Tests/subquery4.stable.out
b/sql/test/subquery/Tests/subquery4.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/subquery/Tests/subquery4.stable.out
@@ -0,0 +1,70 @@
+stdout of test 'subquery4` in directory 'sql/test/subquery` itself:
+
+
+# 15:41:18 >
+# 15:41:18 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=34582" "--set"
"mapi_usock=/var/tmp/mtest-262250/.s.monetdb.34582" "--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_subquery"
"--set" "embedded_c=true"
+# 15:41:18 >
+
+# MonetDB 5 server v11.36.0 (hg id: 96d3e2d22c7d)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_subquery', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.498 GiB available main-memory of which we use 12.631 GiB
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2020 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on
mapi:monetdb://localhost.localdomain:34582/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-262250/.s.monetdb.34582
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+# 15:41:18 >
+# 15:41:18 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-262250" "--port=34582"
+# 15:41:18 >
+
+#CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+#INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+[ 4 ]
+#CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
col6 INT, col7 INT, col8 INT);
+#INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+[ 4 ]
+#CREATE TABLE integers(i INTEGER);
+#INSERT INTO integers VALUES (1), (2), (3), (NULL);
+[ 4 ]
+#CREATE FUNCTION evilfunction(input INT) RETURNS TABLE (outt INT) BEGIN RETURN
TABLE(SELECT input); END;
+#CREATE FUNCTION evilfunction(input1 INT, input2 INT) RETURNS TABLE (outt INT)
BEGIN RETURN TABLE(SELECT input1 + input2); END;
+#PREPARE SELECT
+# (SELECT 1 FROM evilfunction((SELECT ?)))
+#FROM another_T;
+#PREPARE SELECT
+# (SELECT 1 FROM evilfunction((SELECT ?)))
+#FROM another_T;
+% .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
+[ "tinyint", 8, 0, "", "%4", "%4" ]
+[ "int", 32, 0, NULL, NULL, NULL ]
+#PREPARE SELECT
+# (SELECT 1 FROM evilfunction((SELECT ?, ?)))
+#FROM another_T;
+#PREPARE SELECT
+# (SELECT 1 FROM evilfunction((SELECT ?, ?)))
+#FROM another_T;
+% .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
+[ "tinyint", 8, 0, "", "%5", "%5" ]
+[ "int", 32, 0, NULL, NULL, NULL ]
+[ "int", 32, 0, NULL, NULL, NULL ]
+#DROP FUNCTION evilfunction(INT);
+#DROP FUNCTION evilfunction(INT, INT);
+#DROP TABLE tbl_ProductSales;
+#DROP TABLE another_T;
+#DROP TABLE integers;
+
+# 15:41:18 >
+# 15:41:18 > "Done."
+# 15:41:18 >
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
