Changeset: d839abf3dc3e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d839abf3dc3e
Modified Files:
sql/server/rel_select.c
sql/test/miscellaneous/Tests/column_aliases.sql
sql/test/miscellaneous/Tests/column_aliases.stable.err
sql/test/subquery/Tests/subquery3.sql
sql/test/subquery/Tests/subquery3.stable.err
sql/test/subquery/Tests/subquery3.stable.out
Branch: Jun2020
Log Message:
Small bugfix, ensure every column from a subquery is renamed or not (this was
already enforced by the CTEs)
diffs (224 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
@@ -175,8 +175,8 @@ rel_table_optname(mvc *sql, sql_rel *sq,
sq = rel_project(sql->sa, sq, rel_projections(sql, sq,
NULL, 1, 0));
osq = sq;
}
- if (columnrefs && dlist_length(columnrefs) >
list_length(sq->exps))
- return sql_error(sql, 02, SQLSTATE(42000) "SELECT: The
number of aliases is longer than the number of columns (%d>%d)",
dlist_length(columnrefs), sq->nrcols);
+ if (columnrefs && dlist_length(columnrefs) !=
list_length(sq->exps))
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: The
number of aliases don't match the number of columns (%d != %d)",
dlist_length(columnrefs), sq->nrcols);
if (columnrefs && sq->exps) {
dnode *d = columnrefs->h;
diff --git a/sql/test/miscellaneous/Tests/column_aliases.sql
b/sql/test/miscellaneous/Tests/column_aliases.sql
--- a/sql/test/miscellaneous/Tests/column_aliases.sql
+++ b/sql/test/miscellaneous/Tests/column_aliases.sql
@@ -1,12 +1,16 @@
create table t1 (aa int, bb int, cc int);
insert into t1 values (1,1,1);
select t2.dd, t2.ee, t2.ff from t1 as t2(dd,ee,ff);
-select t2.cc from t1 as t2(dd);
-select t2.ee from t1 as t2(dd,ee);
+select t2.cc from t1 as t2(dd,ee,cc);
+select t2.ee from t1 as t2(dd,ee,ff);
+select t2.cc from t1 as t2(dd); --error
+select t2.ee from t1 as t2(dd,ee); --error
select t2.aa from t1 as t2(dd,dd,cc); --error
select t2.dd from t1 as t2(dd,ee,ff,gg); --error
select t3.output from generate_series(1, 2) as t3(output);
+select * from (values(1,2)) as a(a); --error
+
create table t2 as (select count(*) from t1); --error, labels not allowed in
column names
create table t2 as (select count(*) as "mylabel" from t1); --allowed
select count(*) from t2;
diff --git a/sql/test/miscellaneous/Tests/column_aliases.stable.err
b/sql/test/miscellaneous/Tests/column_aliases.stable.err
--- a/sql/test/miscellaneous/Tests/column_aliases.stable.err
+++ b/sql/test/miscellaneous/Tests/column_aliases.stable.err
@@ -5,37 +5,25 @@ stderr of test 'column_aliases` in direc
# 11:21:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38503" "--set"
"mapi_usock=/var/tmp/mtest-2631/.s.monetdb.38503" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "monet_daemon=yes" "--set" "embedded_c=true"
# 11:21:19 >
-# builtin opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-default/BUILD/var/monetdb5/dbfarm/demo
-# builtin opt monet_prompt = >
-# builtin opt monet_daemon = no
-# 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 = 0
-# cmdline opt mapi_open = true
-# cmdline opt mapi_port = 38503
-# cmdline opt mapi_usock = /var/tmp/mtest-2631/.s.monetdb.38503
-# cmdline opt monet_prompt =
-# cmdline opt gdk_dbpath =
/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous
-# cmdline opt monet_daemon = yes
-# cmdline opt embedded_c = true
-#client2:!ERROR:ParseException:SQLparser:42000!SELECT: Duplicate column name
't2.dd'
-#client2:!ERROR:ParseException:SQLparser:42000!SELECT: The number of aliases
is longer the number of columns (4>3)
-
-# 11:21:19 >
-# 11:21:19 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-2631" "--port=38503"
-# 11:21:19 >
-
-MAPI = (monetdb) /var/tmp/mtest-2631/.s.monetdb.38503
+MAPI = (monetdb) /var/tmp/mtest-261061/.s.monetdb.34489
+QUERY = select t2.cc from t1 as t2(dd); --error
+ERROR = !SELECT: The number of aliases don't match the number of columns (1 !=
3)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-261061/.s.monetdb.34489
+QUERY = select t2.ee from t1 as t2(dd,ee); --error
+ERROR = !SELECT: The number of aliases don't match the number of columns (2 !=
3)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-261061/.s.monetdb.34489
QUERY = select t2.aa from t1 as t2(dd,dd,cc); --error
ERROR = !SELECT: Duplicate column name 't2.dd'
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-2631/.s.monetdb.38503
QUERY = select t2.dd from t1 as t2(dd,ee,ff,gg); --error
-ERROR = !SELECT: The number of aliases is longer than the number of columns
(4>3)
+ERROR = !SELECT: The number of aliases don't match the number of columns (4 !=
3)
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-261061/.s.monetdb.34489
+QUERY = select * from (values(1,2)) as a(a); --error
+ERROR = !SELECT: The number of aliases don't match the number of columns (1 !=
2)
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-92354/.s.monetdb.36336
QUERY = create table t2 as (select count(*) from t1); --error, labels not
allowed in column names
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
@@ -514,7 +514,7 @@ SELECT
FROM another_T; --error, more than one row returned by a subquery used as an
expression
SELECT
- (SELECT i1.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i))
+ (SELECT i1.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i,j))
FROM integers i1; --error, subquery uses ungrouped column "i1.i" from outer
query
SELECT
@@ -523,9 +523,10 @@ FROM integers i1;
-- 1
SELECT
- (SELECT i2.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i))
+ (SELECT i2.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i,j)),
+ (SELECT i2.j FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i,j))
FROM integers i1;
- -- 1
+ -- 1, 3
SELECT
(SELECT i2.i FROM (VALUES (i1.i)) as i2(i))
@@ -548,7 +549,7 @@ SELECT
FROM integers i1; --error, more than one row returned by a subquery used as an
expression
SELECT
- (SELECT i2.i FROM (VALUES (i1.i, i1.i), (i1.i, i1.i)) as i2(i))
+ (SELECT i2.i FROM (VALUES (i1.i, i1.i), (i1.i, i1.i)) as i2(i,j))
FROM integers i1; --error, more than one row returned by a subquery used as an
expression
SELECT i FROM integers ORDER BY (SELECT true);
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
@@ -317,7 +317,7 @@ ERROR = !Cardinality violation, scalar v
CODE = 21000
MAPI = (monetdb) /var/tmp/mtest-114297/.s.monetdb.33062
QUERY = SELECT
- (SELECT i1.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i))
+ (SELECT i1.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i,j))
FROM integers i1; --error, subquery uses ungrouped column "i1.i" from
outer query
ERROR = !SELECT: cannot use non GROUP BY column 'i1.i' in query results
without an aggregate function
CODE = 42000
@@ -329,7 +329,7 @@ ERROR = !Cardinality violation, scalar v
CODE = 21000
MAPI = (monetdb) /var/tmp/mtest-600940/.s.monetdb.32137
QUERY = SELECT
- (SELECT i2.i FROM (VALUES (i1.i, i1.i), (i1.i, i1.i)) as i2(i))
+ (SELECT i2.i FROM (VALUES (i1.i, i1.i), (i1.i, i1.i)) as
i2(i,j))
FROM integers i1; --error, more than one row returned by a subquery
used as an expression
ERROR = !Cardinality violation, scalar value expected
CODE = 21000
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
@@ -193,8 +193,8 @@ stdout of test 'subquery3` in directory
# NOT SUM(t1.col2) * MIN(t1.col6 + t1.col6 - t1.col6 * t1.col6) NOT IN
(SELECT MAX(t2.col6) FROM another_T t2 GROUP BY t1.col6 HAVING t1.col7 +
MIN(t2.col8) < MAX(t2.col7 - t1.col6))
#FROM another_T t1
#GROUP BY t1.col7, t1.col6;
-% .%23 # table_name
-% %23 # name
+% .%24 # table_name
+% %24 # name
% boolean # type
% 5 # length
[ false ]
@@ -207,7 +207,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col1, t1.col2;
% ., . # table_name
-% %213, %214 # name
+% %271, %272 # name
% tinyint, tinyint # type
% 1, 1 # length
[ 2, 2 ]
@@ -231,7 +231,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col1;
% . # table_name
-% %31 # name
+% %62 # name
% tinyint # type
% 1 # length
[ 1 ]
@@ -286,8 +286,8 @@ stdout of test 'subquery3` in directory
# NOT SUM(t1.col6) NOT IN (SELECT MAX(t2.col6) FROM another_T t2 GROUP BY
t1.col6 HAVING t1.col7 < MAX(t2.col7 - t1.col6))
#FROM another_T t1
#GROUP BY t1.col7, t1.col6;
-% .t1, .t1, .%17 # table_name
-% col6, col7, %17 # name
+% .t1, .t1, .%20 # table_name
+% col6, col7, %20 # name
% int, int, boolean # type
% 4, 4, 5 # length
[ 6, 7, false ]
@@ -299,7 +299,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col1, t1.col2;
% . # table_name
-% %36 # name
+% %64 # name
% tinyint # type
% 1 # length
[ 2 ]
@@ -310,7 +310,7 @@ stdout of test 'subquery3` in directory
# CASE WHEN NULL NOT IN (SELECT 1 FROM tbl_ProductSales tp FULL OUTER JOIN
another_T t2 ON tp.ColID = t1.col1) THEN 1 ELSE 2 END
#FROM another_T t1;
% . # table_name
-% %40 # name
+% %105 # name
% tinyint # type
% 1 # length
[ 2 ]
@@ -488,11 +488,11 @@ stdout of test 'subquery3` in directory
#SELECT
# (SELECT i2.i FROM (VALUES (MIN(i1.i), MAX(i1.i))) as i2(i))
#FROM integers i1;
-% .%10 # table_name
-% %10 # name
-% int # type
-% 1 # length
-[ 1 ]
+% .%10, .%20 # table_name
+% %10, %20 # name
+% int, int # type
+% 1, 1 # length
+[ 1, 3 ]
#SELECT
# (SELECT i2.i FROM (VALUES (i1.i)) as i2(i))
#FROM integers i1;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
