Changeset: 77b925f9c4c5 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=77b925f9c4c5
Modified Files:
sql/server/rel_unnest.c
sql/test/subquery/Tests/subquery2.stable.out
sql/test/subquery/Tests/subquery3.sql
sql/test/subquery/Tests/subquery3.stable.err
sql/test/subquery/Tests/subquery3.stable.out
Branch: sq2
Log Message:
more fixes, ie make sure we append the outer variables to the proper argument of
the window function
diffs (truncated from 378 to 300 lines):
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -490,8 +490,10 @@ exp_rewrite(mvc *sql, sql_rel *rel, sql_
}
/* find partition expression in rankfunc */
/* diff function */
- if (exp_is_atom(pe) || is_wb)
+ if (exp_is_atom(pe) || (is_wb && (pe->type != e_func ||
strcmp(sf->func->base.name, "diff") != 0)))
pe = NULL;
+ else
+ is_wb = 0;
for(d=ad->h; d; d=d->next) {
sql_subfunc *df;
sql_exp *e = d->data;
diff --git a/sql/test/subquery/Tests/subquery2.stable.out
b/sql/test/subquery/Tests/subquery2.stable.out
--- a/sql/test/subquery/Tests/subquery2.stable.out
+++ b/sql/test/subquery/Tests/subquery2.stable.out
@@ -262,6 +262,19 @@ stdout of test 'subquery2` in directory
[ 555 ]
[ 5555 ]
#SELECT
+# SUM(col3) * col1
+#FROM another_T
+#GROUP BY col1
+#HAVING NOT col1 <> ANY (SELECT 0 FROM tbl_ProductSales GROUP BY ColID HAVING
NOT MAX(col1) <> col1 * AVG(col1 + ColID) * ColID);
+% sys.L34 # table_name
+% L34 # name
+% hugeint # type
+% 1 # length
+[ 3 ]
+[ 363 ]
+[ 36963 ]
+[ 3702963 ]
+#SELECT
# SUM(CAST(t1.col1 IN (SELECT t1.col1 FROM another_T) AS INTEGER))
#FROM another_T t1
#GROUP BY t1.col2;
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
@@ -3,7 +3,7 @@ INSERT INTO tbl_ProductSales VALUES (1,'
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);
-/* Wrong output */
+/* Wrong output (one true missing) */
SELECT
NOT MAX(t1.col6) IN (SELECT SUM(t1.col6) FROM tbl_ProductSales tp HAVING
MAX(t1.col1) > MIN(tp.colID))
FROM another_T t1
@@ -62,23 +62,19 @@ SELECT
FROM another_T t1;
-- True
-/* BROKEN
SELECT
(SELECT AVG(col1) OVER (PARTITION BY col5 ORDER BY col1 ROWS UNBOUNDED
PRECEDING) FROM tbl_ProductSales)
FROM another_T t1; --error, more than one row returned by a subquery used as
an expression
-*/
SELECT
(SELECT SUM(col2) OVER (PARTITION BY SUM(col2) ORDER BY MAX(col1 + ColID)
ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
FROM another_T t1
GROUP BY col1; --error, subquery uses ungrouped column "t1.col2" from outer
query
-/* BROKEN
SELECT
(SELECT SUM(SUM(col2)) OVER (PARTITION BY SUM(col2) ORDER BY MAX(col2)
ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
FROM another_T t1
GROUP BY col1; --error, more than one row returned by a subquery used as an
expression
-*/
SELECT
(SELECT DENSE_RANK() OVER (PARTITION BY col5 ORDER BY col1) FROM
tbl_ProductSales)
@@ -94,12 +90,10 @@ SELECT
FROM another_T t1
GROUP BY col6; --error, subquery uses ungrouped column "t1.col8" from outer
query
-/* BROKEN
SELECT
(SELECT t2.col1 * SUM(SUM(t1.col2)) OVER (PARTITION BY SUM(t1.col2) ORDER
BY MAX(t1.col1) ROWS UNBOUNDED PRECEDING) FROM another_T t2)
FROM another_T t1
GROUP BY col1; --error, more than one row returned by a subquery used as an
expression
-*/
SELECT
(SELECT t2.col1 * SUM(SUM(col2)) OVER (PARTITION BY SUM(col2) ORDER BY
MAX(col1) ROWS UNBOUNDED PRECEDING) FROM another_T t2)
@@ -154,7 +148,7 @@ GROUP BY col1; --MonetDB outputs this on
-- 2468
-- 2468
-/* We shouldn't allow the following internal functions/procedures to be called
from regular queries */
+/* TODO We shouldn't allow the following internal functions/procedures to be
called from regular queries */
--SELECT "identity"(col1) FROM another_T;
--SELECT "rowid"(col1) FROM another_T;
--SELECT "in"(true, true) 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
@@ -0,0 +1,92 @@
+stderr of test 'subquery3` in directory 'sql/test/subquery` itself:
+
+
+# 19:05:12 >
+# 19:05:12 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=1" "--set"
"mapi_open=true" "--set" "mapi_port=35625" "--set"
"mapi_usock=/var/tmp/mtest-2184/.s.monetdb.35625" "--forcemito"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_subquery"
"--set" "embedded_c=true"
+# 19:05:12 >
+
+# builtin opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/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
+# cmdline opt gdk_nr_threads = 1
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 35625
+# cmdline opt mapi_usock = /var/tmp/mtest-2184/.s.monetdb.35625
+# cmdline opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_subquery
+# cmdline opt embedded_c = true
+#client2:!ERROR:SQLException:assert:M0M29!21000!zero_or_one: cardinality
violation, scalar expression expected
+#client2:!ERROR:ParseException:SQLparser:42000!SELECT: cannot use non GROUP BY
column 'col2' in query results without an aggregate function
+#client2:!ERROR:SQLException:assert:M0M29!21000!zero_or_one: cardinality
violation, scalar expression expected
+#client2:!ERROR:SQLException:assert:M0M29!21000!zero_or_one: cardinality
violation, scalar expression expected
+#client2:!ERROR:SQLException:assert:M0M29!21000!zero_or_one: cardinality
violation, scalar expression expected
+#client2:!ERROR:ParseException:SQLparser:42000!SELECT: cannot use non GROUP BY
column 'col8' in query results without an aggregate function
+#client2:!ERROR:SQLException:assert:M0M29!21000!zero_or_one: cardinality
violation, scalar expression expected
+#client2:!ERROR:ParseException:SQLparser:42000!SELECT: cannot use non GROUP BY
column 't2.col1' in query results without an aggregate function
+
+# 19:05:12 >
+# 19:05:12 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-2184" "--port=35625"
+# 19:05:12 >
+
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT AVG(col1) OVER (PARTITION BY col5 ORDER BY col1 ROWS
UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+ FROM another_T t1; --error, more than one row returned by a subquery
used as an expression
+ERROR = !21000!zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT SUM(col2) OVER (PARTITION BY SUM(col2) ORDER BY MAX(col1 +
ColID) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+ FROM another_T t1
+ GROUP BY col1; --error, subquery uses ungrouped column "t1.col2" from
outer query
+ERROR = !SELECT: cannot use non GROUP BY column 'col2' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT SUM(SUM(col2)) OVER (PARTITION BY SUM(col2) ORDER BY
MAX(col2) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+ FROM another_T t1
+ GROUP BY col1; --error, more than one row returned by a subquery used
as an expression
+ERROR = !21000!zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT DENSE_RANK() OVER (PARTITION BY col5 ORDER BY col1) FROM
tbl_ProductSales)
+ FROM another_T t1; --error, more than one row returned by a subquery
used as an expression
+ERROR = !21000!zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT DENSE_RANK() OVER (PARTITION BY MIN(col5) ORDER BY
MAX(col8)) FROM tbl_ProductSales)
+ FROM another_T t1
+ GROUP BY col6; --error, more than one row returned by a subquery used
as an expression
+ERROR = !21000!zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT DENSE_RANK() OVER (PARTITION BY MIN(col5) ORDER BY col8 *
ColID) FROM tbl_ProductSales)
+ FROM another_T t1
+ GROUP BY col6; --error, subquery uses ungrouped column "t1.col8" from
outer query
+ERROR = !SELECT: cannot use non GROUP BY column 'col8' in query results
without an aggregate function
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT t2.col1 * SUM(SUM(t1.col2)) OVER (PARTITION BY
SUM(t1.col2) ORDER BY MAX(t1.col1) ROWS UNBOUNDED PRECEDING) FROM another_T t2)
+ FROM another_T t1
+ GROUP BY col1; --error, more than one row returned by a subquery used
as an expression
+ERROR = !21000!zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2184/.s.monetdb.35625
+QUERY = SELECT
+ (SELECT t2.col1 * SUM(SUM(col2)) OVER (PARTITION BY SUM(col2)
ORDER BY MAX(col1) ROWS UNBOUNDED PRECEDING) FROM another_T t2)
+ FROM another_T t1
+ GROUP BY col1; --error, column "t2.col1" must appear in the GROUP BY
clause or be used in an aggregate function
+ERROR = !SELECT: cannot use non GROUP BY column 't2.col1' in query results
without an aggregate function
+CODE = 42000
+
+# 19:05:12 >
+# 19:05:12 > "Done."
+# 19:05:12 >
+
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
@@ -0,0 +1,182 @@
+stdout of test 'subquery3` in directory 'sql/test/subquery` itself:
+
+
+# 19:05:12 >
+# 19:05:12 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=1" "--set"
"mapi_open=true" "--set" "mapi_port=35625" "--set"
"mapi_usock=/var/tmp/mtest-2184/.s.monetdb.35625" "--forcemito"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_subquery"
"--set" "embedded_c=true"
+# 19:05:12 >
+
+# MonetDB 5 server v11.35.0 (hg id: a1afcd80db71+)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_subquery', using 1 thread
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.384 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
+# Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
+# Visit https://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://xps13:35625/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-2184/.s.monetdb.35625
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+# 19:05:12 >
+# 19:05:12 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-2184" "--port=35625"
+# 19:05:12 >
+
+#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 ]
+#SELECT
+# NOT MAX(t1.col6) IN (SELECT SUM(t1.col6) FROM tbl_ProductSales tp HAVING
MAX(t1.col1) > MIN(tp.colID))
+#FROM another_T t1
+#GROUP BY t1.col6, t1.col7;
+% .L34 # table_name
+% L34 # name
+% boolean # type
+% 5 # length
+[ false ]
+[ true ]
+[ false ]
+[ false ]
+#SELECT
+# (SELECT MAX(ColID + col2) FROM tbl_ProductSales) * DENSE_RANK() OVER
(PARTITION BY AVG(DISTINCT col5)),
+# AVG(col1) * MIN(col8) OVER (PARTITION BY col5 ORDER BY col1 ROWS
UNBOUNDED PRECEDING) evil,
+# MAX(col3) / 10 + SUM(col1) * 10
+#FROM another_T
+#GROUP BY col1, col2, col5, col8;
+% .L35, .L36, .L37 # table_name
+% L35, evil, L37 # name
+% hugeint, double, hugeint # type
+% 4, 24, 5 # length
+[ 6, 8, 10 ]
+[ 26, 968, 113 ]
+[ 226, 98568, 1143 ]
+[ 2226, 9874568, 11443 ]
+#SELECT
+# CAST (NOT col1 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER)
| CAST (col2 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER),
+# CAST (EXISTS (SELECT MAX(col5) * MAX(col4) FROM another_T GROUP BY col5,
col4) AS INTEGER) & CAST (SUM(col4) IN (SELECT DISTINCT col2 FROM another_T
GROUP BY col2) AS INTEGER)
+#FROM another_T
+#GROUP BY col1, col2, col5;
+% .L51, .L52 # table_name
+% L51, L52 # name
+% int, int # type
+% 1, 1 # length
+[ 1, 0 ]
+[ 1, 0 ]
+[ 1, 0 ]
+[ 1, 0 ]
+#SELECT
+# EXISTS (SELECT col1 WHERE TRUE),
+# EXISTS (SELECT col1 WHERE FALSE),
+# EXISTS (SELECT col1 WHERE NULL),
+# NOT EXISTS (SELECT col1 WHERE TRUE),
+# NOT EXISTS (SELECT col1 WHERE FALSE),
+# NOT EXISTS (SELECT col1 WHERE NULL)
+#FROM another_T t1;
+% .L265, .L266, .L267, .L270, .L271, .L272 # table_name
+% L265, L266, L267, L270, L271, L272 # name
+% boolean, boolean, boolean, boolean, boolean,
boolean # type
+% 5, 5, 5, 5, 5, 5 # length
+[ true, false, false, false, true, true ]
+[ true, false, false, false, true, true ]
+[ true, false, false, false, true, true ]
+[ true, false, false, false, true, true ]
+#SELECT
+# EXISTS (SELECT AVG(col1) WHERE TRUE),
+# EXISTS (SELECT AVG(col1) WHERE FALSE),
+# EXISTS (SELECT AVG(col1) WHERE NULL),
+# NOT EXISTS (SELECT AVG(col1) WHERE TRUE),
+# NOT EXISTS (SELECT AVG(col1) WHERE FALSE),
+# NOT EXISTS (SELECT AVG(col1) WHERE NULL)
+#FROM another_T t1;
+% .L265, .L266, .L267, .L270, .L271, .L272 # table_name
+% L265, L266, L267, L270, L271, L272 # name
+% boolean, boolean, boolean, boolean, boolean,
boolean # type
+% 5, 5, 5, 5, 5, 5 # length
+[ true, false, false, false, true, true ]
+#SELECT
+# EXISTS (SELECT RANK() OVER (PARTITION BY SUM(DISTINCT col5)))
+#FROM another_T t1;
+% .L25 # table_name
+% L25 # name
+% boolean # type
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
