Changeset: 13b94f34048b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=13b94f34048b
Modified Files:
sql/common/sql_types.c
sql/test/subquery/Tests/subquery2.sql
sql/test/subquery/Tests/subquery3.sql
Branch: default
Log Message:
Merge with Nov2019 branch.
diffs (132 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -1269,7 +1269,6 @@ sql_create_funcSE(sql_allocator *sa, con
return sql_create_func_(sa, name, mod, imp, l, sres, TRUE, F_FUNC,
fix_scale);
}
-
static sql_func *
sql_create_func3(sql_allocator *sa, const char *name, const char *mod, const
char *imp, sql_type *tpe1, sql_type *tpe2, sql_type *tpe3, sql_type *res, int
fix_scale)
{
@@ -2195,4 +2194,3 @@ types_init(sql_allocator *sa, int debug)
MT_lock_unset(&funcs->ht_lock);
sqltypeinit( sa );
}
-
diff --git a/sql/test/subquery/Tests/subquery2.sql
b/sql/test/subquery/Tests/subquery2.sql
--- a/sql/test/subquery/Tests/subquery2.sql
+++ b/sql/test/subquery/Tests/subquery2.sql
@@ -24,6 +24,9 @@ SELECT name, major FROM students s WHERE
drop table students;
drop table exams;
+SELECT 1 IN (1, (SELECT 2)), 1 NOT IN (1, (SELECT 2));
+ -- True, False
+
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);
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
@@ -33,5 +33,99 @@ GROUP BY col1, col2, col5;
-- 1 0
-- 1 0
+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
+
+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)
+FROM another_T t1; --error, more than one row returned by a subquery used as
an expression
+
+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
+
+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
+
+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)
+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
+
+SELECT
+ (SELECT SUM(AVG(ColID)) OVER (PARTITION BY SUM(ColID) ORDER BY MAX(ColID)
ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales GROUP BY t1.col1)
+FROM another_T t1;
+ -- 2,5000
+ -- 2,5000
+ -- 2,5000
+ -- 2,5000
+
+SELECT
+ (SELECT SUM(AVG(ColID + col1)) OVER (PARTITION BY SUM(ColID + col3) ORDER
BY MAX(ColID) * col4 ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales GROUP BY
t1.col1)
+FROM another_T t1;
+ -- 3,5000
+ -- 13,5000
+ -- 113,5000
+ -- 1113,5000
+
+SELECT
+ (SELECT MAX(t1.col2) * SUM(AVG(ColID)) OVER (PARTITION BY SUM(ColID) ORDER
BY MAX(ColID) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+FROM another_T t1;
+ -- 5555
+
+SELECT
+ (SELECT SUM(AVG(ColID)) OVER (PARTITION BY MAX(t1.col2) * SUM(ColID) ORDER
BY MAX(ColID) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+FROM another_T t1;
+ -- 2,5000
+
+SELECT
+ (SELECT SUM(AVG(ColID)) OVER (PARTITION BY SUM(ColID) ORDER BY
MAX(t1.col2) * MAX(ColID) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+FROM another_T t1;
+ -- 2,5000
+
+SELECT
+ (SELECT MAX(ColID) * SUM(AVG(ColID)) OVER (PARTITION BY SUM(ColID) ORDER
BY MAX(ColID) ROWS UNBOUNDED PRECEDING) FROM tbl_ProductSales)
+FROM another_T t1; --MonetDB outputs this one right, but we should leave it
here, as it doesn't trigger an error
+ -- 10
+ -- 10
+ -- 10
+ -- 10
+
+SELECT
+ (SELECT SUM(SUM(col2)) OVER (PARTITION BY SUM(col2) ORDER BY MAX(col1)
ROWS UNBOUNDED PRECEDING) FROM another_T)
+FROM another_T t1
+GROUP BY col1; --MonetDB outputs this one right, but we should leave it here,
as it doesn't trigger an error
+ -- 2468
+ -- 2468
+ -- 2468
+ -- 2468
+
+/* 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;
+SELECT "rotate_xor_hash"(1, 1, 1) FROM another_T;
+CALL sys_update_schemas();
+CALL sys_update_tables();
+
DROP TABLE tbl_ProductSales;
DROP TABLE another_T;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
