Changeset: fa18c6cd7a97 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=fa18c6cd7a97
Added Files:
sql/test/miscellaneous/Tests/values.sql
sql/test/miscellaneous/Tests/values.stable.err
sql/test/miscellaneous/Tests/values.stable.out
Modified Files:
sql/server/rel_select.c
sql/server/rel_semantic.c
sql/server/sql_parser.y
sql/test/BugTracker-2017/Tests/sqlitelogictest-cast-decimal.Bug-6445.stable.out
sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
sql/test/miscellaneous/Tests/All
Branch: default
Log Message:
Implemented values (...) as a SQL query. This was a SQL standard feature
missing from MonetDB.
Instead of writing
SELECT a,b UNION SELECT c,d UNION SELECT e,f
we can just write
VALUES (a,b), (c,d), (e,f)
diffs (truncated from 440 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
@@ -717,17 +717,17 @@ rel_values(sql_query *query, symbol *tab
dnode *o;
node *m;
list *exps = sa_list(sql->sa);
-
exp_kind ek = {type_value, card_value, TRUE};
- if (!rowlist->h)
- r = rel_project(sql->sa, NULL, NULL);
-
- /* last element in the list is the table_name */
- for (o = rowlist->h; o->next; o = o->next) {
+
+ for (o = rowlist->h; o; o = o->next) {
dlist *values = o->data.lval;
- if (r && list_length(r->exps) != dlist_length(values)) {
- return sql_error(sql, 02, SQLSTATE(42000) "VALUES:
number of values doesn't match");
+ /* When performing sub-queries, the relation name appears under
a SQL_NAME symbol at the end of the list */
+ if (o->type == type_symbol && o->data.sym->token == SQL_NAME)
+ break;
+
+ if (!list_empty(exps) && list_length(exps) !=
dlist_length(values)) {
+ return sql_error(sql, 02, SQLSTATE(42000) "VALUES:
number of columns doesn't match between rows");
} else {
dnode *n;
@@ -2606,6 +2606,8 @@ rel_logical_value_exp(sql_query *query,
return rel_lastexp(sql, *rel);
return NULL;
}
+ case SQL_DEFAULT:
+ return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not
allowed outside insert and update statements");
default: {
sql_exp *re, *le = rel_value_exp(query, rel, sc, f, ek);
sql_subtype bt;
@@ -3272,6 +3274,8 @@ rel_logical_exp(sql_query *query, sql_re
case SQL_EXCEPT:
case SQL_INTERSECT:
return rel_setquery(query, rel, sc);
+ case SQL_DEFAULT:
+ return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not
allowed outside insert and update statements");
default: {
sql_exp *re, *le = rel_value_exp(query, &rel, sc, f, ek);
@@ -5814,6 +5818,8 @@ rel_value_exp2(sql_query *query, sql_rel
case SQL_COALESCE:
case SQL_NULLIF:
return rel_case_exp(query, rel, se, f);
+ case SQL_DEFAULT:
+ return sql_error(sql, 02, SQLSTATE(42000) "DEFAULT keyword not
allowed outside insert and update statements");
case SQL_XMLELEMENT:
case SQL_XMLFOREST:
case SQL_XMLCOMMENT:
@@ -6422,8 +6428,6 @@ rel_setquery(sql_query *query, sql_rel *
return res;
}
-
-
static sql_rel *
rel_joinquery_(sql_query *query, sql_rel *rel, symbol *tab1, int natural, jt
jointype, symbol *tab2, symbol *js)
{
@@ -6682,6 +6686,10 @@ rel_selects(sql_query *query, symbol *s)
ret = rel_with_query(query, s);
sql->type = Q_TABLE;
break;
+ case SQL_VALUES:
+ ret = rel_values(query, s);
+ sql->type = Q_TABLE;
+ break;
case SQL_SELECT: {
exp_kind ek = {type_value, card_relation, TRUE};
SelectNode *sn = (SelectNode *) s;
diff --git a/sql/server/rel_semantic.c b/sql/server/rel_semantic.c
--- a/sql/server/rel_semantic.c
+++ b/sql/server/rel_semantic.c
@@ -234,6 +234,7 @@ rel_semantic(sql_query *query, symbol *s
case SQL_UNION:
case SQL_EXCEPT:
case SQL_INTERSECT:
+ case SQL_VALUES:
return rel_selects(query, s);
default:
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -3223,8 +3223,6 @@ values_or_query_spec:
{ $$ = _symbol_create_list( SQL_VALUES, L()); }
| DEFAULT VALUES
{ $$ = _symbol_create_list( SQL_VALUES, L()); }
- | VALUES row_commalist
- { $$ = _symbol_create_list( SQL_VALUES, $2); }
| query_expression
;
@@ -3437,11 +3435,9 @@ with_query_expression:
| merge_stmt
;
-
sql:
- select_statement_single_row
-|
- select_no_parens_orderby
+ select_statement_single_row
+ | select_no_parens_orderby
;
simple_select:
@@ -3536,6 +3532,7 @@ select_no_parens:
append_list(l, $4);
append_symbol(l, $5);
$$ = _symbol_create_list( SQL_INTERSECT, l); }
+ | VALUES row_commalist { $$ = _symbol_create_list( SQL_VALUES, $2); }
| '(' select_no_parens ')' { $$ = $2; }
| simple_select
;
@@ -4018,21 +4015,14 @@ filter_exp:
$$ = _symbol_create_list(SQL_FILTER, l ); }
;
-
subquery_with_orderby:
'(' select_no_parens_orderby ')' { $$ = $2; }
- | '(' VALUES row_commalist ')'
- { $$ = _symbol_create_list( SQL_VALUES, $3); }
- | '(' with_query ')'
- { $$ = $2; }
+ | '(' with_query ')' { $$ = $2; }
;
subquery:
'(' select_no_parens ')' { $$ = $2; }
- | '(' VALUES row_commalist ')'
- { $$ = _symbol_create_list( SQL_VALUES, $3); }
- | '(' with_query ')'
- { $$ = $2; }
+ | '(' with_query ')' { $$ = $2; }
;
/* simple_scalar expressions */
diff --git
a/sql/test/BugTracker-2017/Tests/sqlitelogictest-cast-decimal.Bug-6445.stable.out
b/sql/test/BugTracker-2017/Tests/sqlitelogictest-cast-decimal.Bug-6445.stable.out
---
a/sql/test/BugTracker-2017/Tests/sqlitelogictest-cast-decimal.Bug-6445.stable.out
+++
b/sql/test/BugTracker-2017/Tests/sqlitelogictest-cast-decimal.Bug-6445.stable.out
@@ -30,7 +30,7 @@ stdout of test 'sqlitelogictest-cast-dec
% real # type
% 15 # length
#SELECT col0 + - - CAST( NULL AS DECIMAL ) - + col0 + + col1 FROM tab1 cor0
WHERE ( NULL ) IS NOT NULL;
-% .L3 # table_name
+% sys.L3 # table_name
% L3 # name
% decimal # type
% 20 # length
diff --git
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
---
a/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
+++
b/sql/test/BugTracker-2018/Tests/sqlitelogictest-cast-null-add.Bug-6630.stable.out
@@ -73,7 +73,7 @@ stdout of test 'sqlitelogictest-cast-nul
[ NULL ]
#SELECT ALL CAST ( col1 / - - ( + CAST ( NULL AS INTEGER ) ) AS BIGINT ) col0,
# CAST ( 39 + + + CAST ( NULL AS INTEGER ) + + - 10 + col1 / - col0
AS BIGINT ) AS col1 FROM tab2;
-% .L4, .L10 # table_name
+% sys.L4, .L10 # table_name
% col0, col1 # name
% bigint, bigint # type
% 1, 1 # length
diff --git a/sql/test/miscellaneous/Tests/All b/sql/test/miscellaneous/Tests/All
--- a/sql/test/miscellaneous/Tests/All
+++ b/sql/test/miscellaneous/Tests/All
@@ -5,4 +5,5 @@ trace_test
simple_selects
update_delete_aliases
groupby_expressions
+values
with_update_stmts
diff --git a/sql/test/miscellaneous/Tests/values.sql
b/sql/test/miscellaneous/Tests/values.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/values.sql
@@ -0,0 +1,32 @@
+values (1);
+values (NULL);
+values (1,2,3);
+values (1,2,3), (4,NULL,6), (7,8,NULL);
+values (); --error
+values (default); --error
+values (1,2), (1), (3,3); --error
+values (1), ('ok');
+values (1) union values (3);
+values (1,1) union values (1,1);
+values (1,2,3) union all values (1,2,3);
+values (3), (2) intersect values (3);
+values (1,2,3), (4,5,6) except select 1,2,4;
+values (1,2,3), (4,5,6) except select 1,2,3;
+select 'a', 'c' union select 'b', 'c' except values ('a', 'c'), ('b', 'c');
+select 'a', 'c' union select 'b', 'c' except values ('a', 'c'), ('b', 'd');
+with t1(a,b) as (values (1,2), (3,5)) select t1.b from t1 where a > 1;
+with t1(a,b) as (values (1,1), (2,2)),
+ t2(a,b) as (values (2,4), (3,3))
+ select * from t1 inner join t2 on t1.a = t2.a;
+with t1(a,b) as (values (1,1), (2,2)),
+ t2(a,b) as (values (2,4), (3,3,5))
+ select * from t1 inner join t2 on t1.a = t2.a; --error
+with t1(a,b) as (select 1) select * from t1; --error
+with t1 as (select 1) values (2);
+with t1 as (select 1) values (3,4,5,6,7,'ok'), (6,8,1,2,'still','ok');
+
+create function foo() returns table (aa int, bb int) begin return table(values
(1,2), (3)); end; --error
+create function foo() returns table (aa int, bb int) begin return table(values
(1,2)); end;
+select aa, bb + 1 from foo();
+select cc from foo() as bar(cc, dd);
+drop function foo;
diff --git a/sql/test/miscellaneous/Tests/values.stable.err
b/sql/test/miscellaneous/Tests/values.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/values.stable.err
@@ -0,0 +1,62 @@
+stderr of test 'values` in directory 'sql/test/miscellaneous` itself:
+
+
+# 16:16:10 >
+# 16:16:10 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=33718" "--set"
"mapi_usock=/var/tmp/mtest-17929/.s.monetdb.33718" "--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"
+# 16:16:10 >
+
+# 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 = 33718
+# cmdline opt mapi_usock = /var/tmp/mtest-17929/.s.monetdb.33718
+# 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!syntax error, unexpected ')'
in: "values ()"
+#client2:!ERROR:ParseException:SQLparser:42000!VALUES: number of columns
doesn't match between rows
+
+# 16:16:11 >
+# 16:16:11 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-17929" "--port=33718"
+# 16:16:11 >
+
+MAPI = (monetdb) /var/tmp/mtest-17929/.s.monetdb.33718
+QUERY = values (); --error
+ERROR = !syntax error, unexpected ')' in: "values ()"
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-2671/.s.monetdb.33813
+QUERY = values (default); --error
+ERROR = !DEFAULT keyword not allowed outside insert and update statements
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-2671/.s.monetdb.33813
+QUERY = values (1,2), (1), (3,3); --error
+ERROR = !VALUES: number of columns doesn't match between rows
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20225/.s.monetdb.30800
+QUERY = with t1(a,b) as (values (1,1), (2,2)),
+ t2(a,b) as (values (2,4), (3,3,5))
+ select * from t1 inner join t2 on t1.a = t2.a; --error
+ERROR = !VALUES: number of columns doesn't match between rows
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-20225/.s.monetdb.30800
+QUERY = with t1(a,b) as (select 1) select * from t1; --error
+ERROR = !WITH CLAUSE: number of columns does not match
+CODE = 21S02
+MAPI = (monetdb) /var/tmp/mtest-20225/.s.monetdb.30800
+QUERY = create function foo() returns table (aa int, bb int) begin return
table(values (1,2), (3)); end; --error
+ERROR = !VALUES: number of columns doesn't match between rows
+CODE = 42000
+
+# 16:16:11 >
+# 16:16:11 > "Done."
+# 16:16:11 >
+
diff --git a/sql/test/miscellaneous/Tests/values.stable.out
b/sql/test/miscellaneous/Tests/values.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/values.stable.out
@@ -0,0 +1,153 @@
+stdout of test 'values` in directory 'sql/test/miscellaneous` itself:
+
+
+# 16:16:10 >
+# 16:16:10 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=33718" "--set"
"mapi_usock=/var/tmp/mtest-17929/.s.monetdb.33718" "--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"
+# 16:16:10 >
+
+# MonetDB 5 server v11.34.0 (hg id: 46ba7db7f1bd)
+# This is an unreleased version
+# Serving database 'mTests_sql_test_miscellaneous', using 8 threads
+# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers
+# Found 15.527 GiB available main-memory.
+# Copyright (c) 1993 - July 2008 CWI.
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
