Changeset: 8de8fc28bee1 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8de8fc28bee1 Added Files: sql/test/miscellaneous/Tests/with_update_stmts.sql sql/test/miscellaneous/Tests/with_update_stmts.stable.err sql/test/miscellaneous/Tests/with_update_stmts.stable.out Modified Files: sql/ChangeLog sql/server/rel_select.c sql/server/sql_parser.y sql/test/miscellaneous/Tests/All Branch: default Log Message:
Allow WITH clauses on insert, update, delete and merge statements. It was mentioned on mhelp, but not allowed in the parser. diffs (284 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -8,6 +8,8 @@ lookup is performed). As an example the query: SELECT col1*2 FROM t1 GROUP BY col1*2; is correct, while SELECT sin(col1+5) FROM t1 GROUP BY col1*2; is wrong. +- Allow usage of WITH clauses in insert, update, delete and merge + statements. * Mon Dec 17 2018 Pedro Ferreira <[email protected]> - Implemented merge statements from SQL:2003 standard. Using a source 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 @@ -302,7 +302,7 @@ sql_rel * rel_with_query(mvc *sql, symbol *q ) { dnode *d = q->data.lval->h; - symbol *select = d->next->data.sym; + symbol *next = d->next->data.sym; sql_rel *rel; if(!stack_push_frame(sql, "WITH")) @@ -342,7 +342,7 @@ rel_with_query(mvc *sql, symbol *q ) noninternexp_setname(sql->sa, ne->data, name, NULL ); } } - rel = rel_semantic(sql, select); + rel = rel_semantic(sql, next); stack_pop_frame(sql); return rel; } 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 @@ -3429,8 +3429,13 @@ with_list_element: ; with_query_expression: - select_no_parens_orderby - ; + select_no_parens_orderby + | select_statement_single_row + | delete_stmt + | insert_stmt + | update_stmt + | merge_stmt + ; sql: 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 @@ -4,3 +4,4 @@ trace_test simple_selects update_delete_aliases groupby_expressions +with_update_stmts diff --git a/sql/test/miscellaneous/Tests/with_update_stmts.sql b/sql/test/miscellaneous/Tests/with_update_stmts.sql new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/with_update_stmts.sql @@ -0,0 +1,34 @@ +create table "testme" ("aa" int, "bb" clob); + +with "v1" as (select 1 as "c1"), + "v2" as (select 'a' as "c2") + insert into "testme" select "c1", "c2" from "v1", "v2"; +select "aa", "bb" from "testme"; + +with "v1" as (select 1 as "c1"), + "v2" as (select 'a' as "c2") + insert into "testme" select "c1" from "v1"; --error +with "v1" as (select 1 as "c1" union select 2 as "c1"), + "v2" as (select 'a' as "c2") + insert into "testme" select "c1", "c2" from "v1", "v2"; +select "aa", "bb" from "testme"; + +with "v1" as (select 1 as "c1") + delete from "testme" where "testme"."aa" = "v1"."c1"; +with "v1" as (select 0 as "c1") + delete from "testme" where "testme"."aa" = "v1"."c3"; --error +with "v1" as (select 0 as "c1") + delete from "testme" where "testme"."aa" = "v1"."c1"; +select "aa", "bb" from "testme"; + +with "v1" as (select 2 as "c1") + update "testme" set "aa" = 3 where "testme"."aa" = "v1"."c1"; +select "aa", "bb" from "testme"; + +with "v1" as (select 10 as "c1"), + "v2" as (select 'zzz' as "c2") +merge into "testme" using (select "c1" "joimne", "c2" from "v1","v2") "other" on "testme"."aa" = "other"."joimne" + when not matched then insert values ("joimne" * "other"."joimne", "c2"); +select "aa", "bb" from "testme"; + +drop table "testme"; diff --git a/sql/test/miscellaneous/Tests/with_update_stmts.stable.err b/sql/test/miscellaneous/Tests/with_update_stmts.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/with_update_stmts.stable.err @@ -0,0 +1,46 @@ +stderr of test 'with_update_stmts` in directory 'sql/test/miscellaneous` itself: + + +# 16:17:26 > +# 16:17:26 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=39137" "--set" "mapi_usock=/var/tmp/mtest-4854/.s.monetdb.39137" "--set" "monet_prompt=" "--forcemito" "--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous" "--set" "embedded_c=true" +# 16:17:26 > + +# builtin opt gdk_dbpath = /home/ferreira/repositories/MonetDB-default/BUILD/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = 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 = 39137 +# cmdline opt mapi_usock = /var/tmp/mtest-4854/.s.monetdb.39137 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous +# cmdline opt embedded_c = true +# cmdline opt gdk_debug = 553648138 + +# 16:17:26 > +# 16:17:26 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-4854" "--port=39137" +# 16:17:26 > + +MAPI = (monetdb) /var/tmp/mtest-4854/.s.monetdb.39137 +QUERY = with "v1" as (select 1 as "c1"), + "v2" as (select 'a' as "c2") + insert into "testme" select "c1" from "v1"; --error +ERROR = !INSERT INTO: query result doesn't match number of columns in table 'testme' +CODE = 21S01 +MAPI = (monetdb) /var/tmp/mtest-5252/.s.monetdb.30689 +QUERY = with "v1" as (select 0 as "c1") + delete from "testme" where "testme"."aa" = "v1"."c3"; --error +ERROR = !SELECT: no such column 'v1.c3' +CODE = 42S22 + +# 16:17:26 > +# 16:17:26 > "Done." +# 16:17:26 > + diff --git a/sql/test/miscellaneous/Tests/with_update_stmts.stable.out b/sql/test/miscellaneous/Tests/with_update_stmts.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/miscellaneous/Tests/with_update_stmts.stable.out @@ -0,0 +1,129 @@ +stdout of test 'with_update_stmts` in directory 'sql/test/miscellaneous` itself: + + +# 16:10:27 > +# 16:10:27 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=35526" "--set" "mapi_usock=/var/tmp/mtest-3668/.s.monetdb.35526" "--set" "monet_prompt=" "--forcemito" "--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous" "--set" "embedded_c=true" +# 16:10:27 > + +# MonetDB 5 server v11.32.0 (hg id: 0dce2a4262ba+) +# 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.528 GiB available main-memory. +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://localhost.localdomain:35526/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-3668/.s.monetdb.35526 +# MonetDB/GIS module loaded +# SQL catalog created, loading sql scripts once +# loading sql script: 09_like.sql +# loading sql script: 10_math.sql +# loading sql script: 11_times.sql +# loading sql script: 12_url.sql +# loading sql script: 13_date.sql +# loading sql script: 14_inet.sql +# loading sql script: 15_querylog.sql +# loading sql script: 16_tracelog.sql +# loading sql script: 17_temporal.sql +# loading sql script: 18_index.sql +# loading sql script: 20_vacuum.sql +# loading sql script: 21_dependency_views.sql +# loading sql script: 22_clients.sql +# loading sql script: 23_skyserver.sql +# loading sql script: 25_debug.sql +# loading sql script: 26_sysmon.sql +# loading sql script: 27_rejects.sql +# loading sql script: 39_analytics.sql +# loading sql script: 39_analytics_hge.sql +# loading sql script: 40_geom.sql +# loading sql script: 40_json.sql +# loading sql script: 40_json_hge.sql +# loading sql script: 41_md5sum.sql +# loading sql script: 45_uuid.sql +# loading sql script: 46_profiler.sql +# loading sql script: 51_sys_schema_extension.sql +# loading sql script: 60_wlcr.sql +# loading sql script: 72_fits.sql +# loading sql script: 74_netcdf.sql +# loading sql script: 75_lidar.sql +# loading sql script: 75_shp.sql +# loading sql script: 75_storagemodel.sql +# loading sql script: 80_statistics.sql +# loading sql script: 80_udf.sql +# loading sql script: 80_udf_hge.sql +# loading sql script: 85_bam.sql +# loading sql script: 90_generator.sql +# loading sql script: 90_generator_hge.sql +# loading sql script: 99_system.sql +# MonetDB/SQL module loaded + +Ready. + +# 16:10:28 > +# 16:10:28 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-3668" "--port=35526" +# 16:10:28 > + +#start transaction; +#create table "testme" ("aa" int, "bb" clob); +#with v1 as (select 1 as "c1"), +# v2 as (select 'a' as "c2") +# insert into "testme" select "c1", "c2" from v1, v2; +[ 1 ] +#select "aa", "bb" from "testme"; +% sys.testme, sys.testme # table_name +% aa, bb # name +% int, clob # type +% 1, 1 # length +[ 1, "a" ] +#with v1 as (select 1 as "c1" union select 2 as "c1"), +# v2 as (select 'a' as "c2") +# insert into "testme" select "c1", "c2" from v1, v2; +[ 2 ] +#select "aa", "bb" from "testme"; +% sys.testme, sys.testme # table_name +% aa, bb # name +% int, clob # type +% 1, 1 # length +[ 1, "a" ] +[ 1, "a" ] +[ 2, "a" ] +#with "v1" as (select 1 as "c1") +# delete from "testme" where "testme"."aa" = "v1"."c1"; +[ 2 ] +#with "v1" as (select 0 as "c1") +# delete from "testme" where "testme"."aa" = "v1"."c1"; +[ 0 ] +#select "aa", "bb" from "testme"; +% sys.testme, sys.testme # table_name +% aa, bb # name +% int, clob # type +% 1, 1 # length +[ 2, "a" ] +#with "v1" as (select 2 as "c1") +# update "testme" set "aa" = 3 where "testme"."aa" = "v1"."c1"; +[ 1 ] +#select "aa", "bb" from "testme"; +% sys.testme, sys.testme # table_name +% aa, bb # name +% int, clob # type +% 1, 1 # length +[ 3, "a" ] +#with "v1" as (select 10 as "c1"), +# "v2" as (select 'zzz' as "c2") +#merge into "testme" using (select "c1" "joimne", "c2" from "v1","v2") "other" on "testme"."aa" = "other"."joimne" +# when not matched then insert values ("joimne" * "other"."joimne", "c2"); +[ 1 ] +#select "aa", "bb" from "testme"; +% sys.testme, sys.testme # table_name +% aa, bb # name +% int, clob # type +% 3, 3 # length +[ 3, "a" ] +[ 100, "zzz" ] +#drop table "testme"; + +# 16:10:28 > +# 16:10:28 > "Done." +# 16:10:28 > + _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
