Changeset: 44fe79e1edf2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=44fe79e1edf2
Added Files:
sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2019/Tests/All
sql/test/subquery/Tests/subquery.stable.err
sql/test/subquery/Tests/subquery.stable.out
Branch: Nov2019
Log Message:
Merge with Apr2019 and test approval
diffs (truncated from 311 to 300 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -3912,6 +3912,13 @@ static sql_exp *
if (uaname)
GDKfree(uaname);
return e;
+ } else if (!query_has_outer(query) && is_sql_where(f)) {
+ char *uaname = GDKmalloc(strlen(aname) + 1);
+ sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: not
allowed in WHERE clause",
+ uaname ? toUpperCopy(uaname, aname) :
aname);
+ if (uaname)
+ GDKfree(uaname);
+ return e;
}
if (groupby->op != op_groupby) { /* implicit groupby */
@@ -3931,15 +3938,6 @@ static sql_exp *
if (!*rel)
return NULL;
- if (!query_has_outer(query) && is_sql_where(f)) {
- char *uaname = GDKmalloc(strlen(aname) + 1);
- sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: not
allowed in WHERE clause",
- uaname ? toUpperCopy(uaname, aname) :
aname);
- if (uaname)
- GDKfree(uaname);
- return e;
- }
-
if (!args->data.sym) { /* count(*) case */
sql_exp *e;
diff --git a/sql/test/BugTracker-2019/Tests/All
b/sql/test/BugTracker-2019/Tests/All
--- a/sql/test/BugTracker-2019/Tests/All
+++ b/sql/test/BugTracker-2019/Tests/All
@@ -31,3 +31,4 @@ sequences-defaults.Bug-6744
sequences-types.Bug-6745
alter_table_drop_column.Bug-6749
HAVE_PYMONETDB?remote-table-non-existent-column.Bug-6750
+cte-union.Bug-6755
diff --git a/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/cte-union.Bug-6755.sql
@@ -0,0 +1,39 @@
+start transaction;
+CREATE TABLE ontime (
+ "Year" SMALLINT,
+ "Month" TINYINT,
+ "DayofMonth" TINYINT,
+ "Carrier" CHAR(2),
+ "CRSDepTime" DECIMAL(8,2),
+ "ArrDelay" DECIMAL(8,2)
+);
+CREATE TABLE tmp (
+ "Hour" TINYINT,
+ "PredictedArrDelay" DECIMAL(8,2) DEFAULT 0.0
+);
+INSERT INTO tmp ("Hour")
+VALUES
+ (0), (1), (2), (3), (4), (5),
+ (6), (7), (8), (9), (10), (11),
+ (12), (13), (14), (15), (16), (17),
+ (18), (19), (20), (21), (22), (23);
+
+INSERT INTO ontime VALUES (2001, 9, 2, 'AA', 900.00, -6.00);
+ALTER TABLE "ontime" SET READ ONLY;
+ANALYZE sys.ontime;
+WITH t1 AS (
+ SELECT "Carrier", CAST (FLOOR("CRSDepTime"%2400/100) AS INT) AS "Hour",
+ CAST(AVG("ArrDelay") AS DECIMAL(8,2)) AS "PredictedArrDelay"
+ FROM ontime
+ WHERE "Year" = 2007 AND "Month" = 10 AND "DayofMonth" = 24
+ GROUP BY "Carrier", "Hour"
+),
+t2 AS (
+ SELECT t."Carrier", tmp.*
+ FROM tmp, (SELECT DISTINCT "Carrier" FROM t1) AS t
+)
+SELECT "Carrier", "Hour", SUM("PredictedArrDelay")
+FROM (SELECT * FROM t1 UNION SELECT * FROM t2) AS t
+GROUP BY "Carrier", "Hour"
+ORDER BY "Carrier", "Hour";
+rollback;
diff --git a/sql/test/subquery/Tests/subquery.stable.err
b/sql/test/subquery/Tests/subquery.stable.err
--- a/sql/test/subquery/Tests/subquery.stable.err
+++ b/sql/test/subquery/Tests/subquery.stable.err
@@ -46,6 +46,10 @@ MAPI = (monetdb) /var/tmp/mtest-28086/.
QUERY = SELECT * FROM integers WHERE i=(SELECT i, i + 2 FROM integers);
ERROR = !SELECT: subquery must return only one column
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-17217/.s.monetdb.30774
+QUERY = SELECT 1 FROM integers WHERE SUM(i) > 1; --aggregates not allowed in
where clause
+ERROR = !SUM: not allowed in WHERE clause
+CODE = 42000
# 17:27:37 >
# 17:27:37 > "Done."
diff --git a/sql/test/subquery/Tests/subquery.stable.out
b/sql/test/subquery/Tests/subquery.stable.out
--- a/sql/test/subquery/Tests/subquery.stable.out
+++ b/sql/test/subquery/Tests/subquery.stable.out
@@ -90,8 +90,8 @@ stdout of test 'subquery` in directory '
% 1 # length
[ 3 ]
#SELECT *, (SELECT MAX(i) FROM integers) FROM integers ORDER BY i;
-% sys.integers, .L6 # table_name
-% i, L6 # name
+% sys.integers, .L3 # table_name
+% i, L3 # name
% int, int # type
% 1, 1 # length
[ NULL, 3 ]
@@ -99,14 +99,14 @@ stdout of test 'subquery` in directory '
[ 2, 3 ]
[ 3, 3 ]
#SELECT (SELECT 42) AS k, MAX(i) FROM integers GROUP BY k; -- 42, 3
-% .L2, sys.L4 # table_name
-% k, L4 # name
+% .L1, sys.L2 # table_name
+% k, L2 # name
% tinyint, int # type
% 2, 1 # length
[ 42, 3 ]
#SELECT i, MAX((SELECT 42)) FROM integers GROUP BY i ORDER BY i;
-% sys.integers, .L3 # table_name
-% i, L3 # name
+% sys.integers, .L1 # table_name
+% i, L1 # name
% int, tinyint # type
% 1, 2 # length
[ NULL, 42 ]
@@ -114,8 +114,8 @@ stdout of test 'subquery` in directory '
[ 2, 42 ]
[ 3, 42 ]
#SELECT (SELECT * FROM integers WHERE i>10) FROM integers;
-% .L4 # table_name
-% L4 # name
+% .L2 # table_name
+% L2 # name
% int # type
% 1 # length
[ NULL ]
@@ -141,8 +141,8 @@ stdout of test 'subquery` in directory '
[ 3 ]
[ NULL ]
#SELECT (SELECT i FROM integers WHERE i=1); --1
-% sys.L3 # table_name
-% L3 # name
+% sys.L2 # table_name
+% L2 # name
% int # type
% 1 # length
[ 1 ]
@@ -186,32 +186,32 @@ stdout of test 'subquery` in directory '
% int # type
% 1 # length
#SELECT EXISTS(SELECT * FROM integers); -- true
-% .L4 # table_name
-% L4 # name
+% .L3 # table_name
+% L3 # name
% boolean # type
% 5 # length
[ true ]
#SELECT EXISTS(SELECT * FROM integers WHERE i>10); -- false
-% .L4 # table_name
-% L4 # name
+% .L3 # table_name
+% L3 # name
% boolean # type
% 5 # length
[ false ]
#SELECT EXISTS(SELECT * FROM integers), EXISTS(SELECT * FROM integers); --
true, true
-% .L4, .L10 # table_name
-% L4, L10 # name
+% .L3, .L6 # table_name
+% L3, L6 # name
% boolean, boolean # type
% 5, 5 # length
[ true, true ]
#SELECT EXISTS(SELECT * FROM integers) AND EXISTS(SELECT * FROM integers); --
true
-% .L10 # table_name
-% L10 # name
+% .L6 # table_name
+% L6 # name
% boolean # type
% 5 # length
[ true ]
#SELECT EXISTS(SELECT EXISTS(SELECT * FROM integers)); -- true
-% .L10 # table_name
-% L10 # name
+% .L7 # table_name
+% L7 # name
% boolean # type
% 5 # length
[ true ]
@@ -239,8 +239,8 @@ stdout of test 'subquery` in directory '
% int # type
% 1 # length
#SELECT 1 IN (SELECT NULL) FROM integers;
-% .L10 # table_name
-% L10 # name
+% .L7 # table_name
+% L7 # name
% boolean # type
% 5 # length
[ NULL ]
@@ -248,17 +248,17 @@ stdout of test 'subquery` in directory '
[ NULL ]
[ NULL ]
#SELECT NULL IN (SELECT * FROM integers) FROM integers;
-% .L13 # table_name
-% L13 # name
+% .L11 # table_name
+% L11 # name
% boolean # type
% 5 # length
[ NULL ]
[ NULL ]
[ NULL ]
[ NULL ]
-#SELECT cast(SUM(i) as bigint) FROM integers WHERE 1 IN (SELECT * FROM
integers); -- 6
-% sys.L7 # table_name
-% L7 # name
+#SELECT CAST(SUM(i) AS BIGINT) FROM integers WHERE 1 IN (SELECT * FROM
integers); -- 6
+% sys.L5 # table_name
+% L5 # name
% bigint # type
% 1 # length
[ 6 ]
@@ -269,23 +269,23 @@ stdout of test 'subquery` in directory '
% 2 # length
[ 42 ]
#SELECT (SELECT EXISTS(SELECT * FROM integers WHERE i>2)) FROM integers; --
single column 4xtrue
-% .L4 # table_name
-% L4 # name
+% .L2 # table_name
+% L2 # name
% boolean # type
% 5 # length
[ true ]
[ true ]
[ true ]
[ true ]
-#SELECT (SELECT MAX(i) FROM integers) AS k, cast(SUM(i) as bigint) FROM
integers GROUP BY k; -- 3,6
-% .L3, .L7 # table_name
-% k, L7 # name
+#SELECT (SELECT MAX(i) FROM integers) AS k, CAST(SUM(i) AS BIGINT) FROM
integers GROUP BY k; -- 3,6
+% .L3, .L5 # table_name
+% k, L5 # name
% int, bigint # type
% 1, 1 # length
[ 3, 6 ]
-#SELECT i % 2 AS k, cast(SUM(i) as bigint) FROM integers GROUP BY k HAVING
SUM(i) > (SELECT MAX(i) FROM integers); -- 1,4
-% sys.L2, sys.L5 # table_name
-% k, L5 # name
+#SELECT i % 2 AS k, CAST(SUM(i) AS BIGINT) FROM integers GROUP BY k HAVING
SUM(i) > (SELECT MAX(i) FROM integers); -- 1,4
+% sys.L1, sys.L3 # table_name
+% k, L3 # name
% int, bigint # type
% 1, 1 # length
[ 1, 4 ]
@@ -295,9 +295,9 @@ stdout of test 'subquery` in directory '
% int # type
% 1 # length
[ 1 ]
-#SELECT (SELECT cast(SUM(i) as bigint) FROM integers), (SELECT 42);
-% .L6, .L10 # table_name
-% L6, L10 # name
+#SELECT (SELECT CAST(SUM(i) AS BIGINT) FROM integers), (SELECT 42);
+% .L5, .L7 # table_name
+% L5, L7 # name
% bigint, tinyint # type
% 1, 2 # length
[ 6, 42 ]
@@ -306,26 +306,26 @@ stdout of test 'subquery` in directory '
#INSERT INTO strings VALUES ('hello'), ('world'), (NULL);
[ 3 ]
#SELECT NULL IN (SELECT * FROM strings); -- NULL
-% .L14 # table_name
-% L14 # name
+% .L13 # table_name
+% L13 # name
% boolean # type
% 5 # length
[ NULL ]
#SELECT 'hello' IN (SELECT * FROM strings); -- true
-% .L14 # table_name
-% L14 # name
+% .L13 # table_name
+% L13 # name
% boolean # type
% 5 # length
[ true ]
#SELECT 'bla' IN (SELECT * FROM strings); -- NULL
-% .L14 # table_name
-% L14 # name
+% .L13 # table_name
+% L13 # name
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
