Changeset: b5fb4f3fdc2d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b5fb4f3fdc2d
Added Files:
sql/test/miscellaneous/Tests/update_delete_aliases.sql
sql/test/miscellaneous/Tests/update_delete_aliases.stable.err
sql/test/miscellaneous/Tests/update_delete_aliases.stable.out
Modified Files:
clients/mapiclient/mhelp.c
sql/server/rel_updates.c
sql/server/sql_parser.y
sql/test/miscellaneous/Tests/All
Branch: merge-statements
Log Message:
Added optional aliases for update and delete statements
Exemplifying queries:
update t1 as a1 set col1=n where a1.col2=m;
delete from t1 as a1 where a1.col1=n;
diffs (truncated from 327 to 300 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -275,7 +275,7 @@ SQLhelp sqlhelp[] = {
NULL},
{"DELETE",
"",
- "[ WITH with_list ] DELETE FROM qname [ WHERE search_condition ]",
+ "[ WITH with_list ] DELETE FROM qname [ [AS] ident ] [ WHERE
search_condition ]",
"with_list,search_condition",
NULL},
{"DROP AGGREGATE",
@@ -478,7 +478,7 @@ SQLhelp sqlhelp[] = {
NULL},
{"UPDATE",
"",
- "[ WITH with_list ] UPDATE qname SET assignment_list [ WHERE
search_condition ]",
+ "[ WITH with_list ] UPDATE qname [ [AS] ident ] SET assignment_list [
WHERE search_condition ]",
"with_list,assignment_list,search_condition",
NULL},
{"WHILE",
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -886,7 +886,7 @@ update_check_column(mvc *sql, sql_table
}
static sql_rel *
-update_table(mvc *sql, dlist *qname, dlist *assignmentlist, symbol *opt_from,
symbol *opt_where)
+update_table(mvc *sql, dlist *qname, str alias, dlist *assignmentlist, symbol
*opt_from, symbol *opt_where)
{
char *sname = qname_schema(qname);
char *tname = qname_table(qname);
@@ -931,6 +931,10 @@ update_table(mvc *sql, dlist *qname, dli
pcols = mt->part.pexp->cols;
}
res = bt;
+ if(alias) {
+ for(node *nn = res->exps->h ; nn ; nn = nn->next)
+ exp_setname(sql->sa, (sql_exp*) nn->data,
alias, NULL); //the last parameter is optional, hence NULL
+ }
#if 0
dlist *selection = dlist_create(sql->sa);
dlist *from_list = dlist_create(sql->sa);
@@ -983,9 +987,16 @@ update_table(mvc *sql, dlist *qname, dli
for (n = fl->h; n && res; n = n->next) {
fnd = table_ref(sql, NULL, n->data.sym, 0);
- if (fnd)
+ if (fnd) {
+ if(alias) {
+ for(node *nn = fnd->exps->h ;
nn ; nn = nn->next) {
+ sql_exp* ee =
(sql_exp*) nn->data;
+ if(ee->rname &&
!strcmp(ee->rname, alias))
+ return
sql_error(sql, 02, SQLSTATE(42000) "UPDATE: multiple references into table
'%s'", alias);
+ }
+ }
res = rel_crossproduct(sql->sa, res,
fnd, op_join);
- else
+ } else
res = fnd;
}
if (!res)
@@ -1196,7 +1207,7 @@ rel_truncate(sql_allocator *sa, sql_rel
}
static sql_rel *
-delete_table(mvc *sql, dlist *qname, symbol *opt_where)
+delete_table(mvc *sql, dlist *qname, str alias, symbol *opt_where)
{
char *sname = qname_schema(qname);
char *tname = qname_table(qname);
@@ -1236,6 +1247,10 @@ delete_table(mvc *sql, dlist *qname, sym
sql->errstr[0] = 0;
sql->session->status = status;
r = rel_basetable(sql, t, t->base.name );
+ if(alias) {
+ for(node *nn = r->exps->h ; nn ; nn =
nn->next)
+ exp_setname(sql->sa, (sql_exp*)
nn->data, alias, NULL); //the last parameter is optional, hence NULL
+ }
r = rel_logical_exp(sql, r, opt_where,
sql_where);
}
if (!r) {
@@ -1937,7 +1952,8 @@ rel_updates(mvc *sql, symbol *s)
{
dlist *l = s->data.lval;
- ret = update_table(sql, l->h->data.lval, l->h->next->data.lval,
l->h->next->next->data.sym, l->h->next->next->next->data.sym);
+ ret = update_table(sql, l->h->data.lval, l->h->next->data.sval,
l->h->next->next->data.lval,
+
l->h->next->next->next->data.sym, l->h->next->next->next->next->data.sym);
sql->type = Q_UPDATE;
}
break;
@@ -1945,7 +1961,7 @@ rel_updates(mvc *sql, symbol *s)
{
dlist *l = s->data.lval;
- ret = delete_table(sql, l->h->data.lval, l->h->next->data.sym);
+ ret = delete_table(sql, l->h->data.lval, l->h->next->data.sval,
l->h->next->next->data.sym);
sql->type = Q_UPDATE;
}
break;
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
@@ -3028,11 +3028,12 @@ string_commalist_contents:
;
delete_stmt:
- sqlDELETE FROM qname opt_where_clause
+ sqlDELETE FROM qname opt_alias_name opt_where_clause
{ dlist *l = L();
append_list(l, $3);
- append_symbol(l, $4);
+ append_string(l, $4);
+ append_symbol(l, $5);
$$ = _symbol_create_list( SQL_DELETE, l ); }
;
@@ -3058,13 +3059,14 @@ truncate_stmt:
;
update_stmt:
- UPDATE qname SET assignment_commalist opt_from_clause opt_where_clause
+ UPDATE qname opt_alias_name SET assignment_commalist opt_from_clause
opt_where_clause
{ dlist *l = L();
append_list(l, $2);
- append_list(l, $4);
- append_symbol(l, $5);
+ append_string(l, $3);
+ append_list(l, $5);
append_symbol(l, $6);
+ append_symbol(l, $7);
$$ = _symbol_create_list( SQL_UPDATE, l ); }
;
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
@@ -1,3 +1,4 @@
declared_tables
#now
trace_test
+update_delete_aliases
diff --git a/sql/test/miscellaneous/Tests/update_delete_aliases.sql
b/sql/test/miscellaneous/Tests/update_delete_aliases.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/update_delete_aliases.sql
@@ -0,0 +1,17 @@
+create table updateme (a int, b int);
+create table other (a int, b int);
+insert into updateme values (1,1), (2,2), (3,3);
+
+update updateme as other set a=3 where b=2;
+update updateme as other set a=2 where other.b=3;
+update updateme as other set a=3 where updateme.b=2; --error
+update updateme as other set a=4 from other where other.a=1; --error
+select a, b from updateme;
+
+delete from updateme as other where other.a=3;
+delete from updateme where other.b=2; --error
+delete from updateme as other where updateme.b=2; --error
+select a, b from updateme;
+
+drop table updateme;
+drop table other;
diff --git a/sql/test/miscellaneous/Tests/update_delete_aliases.stable.err
b/sql/test/miscellaneous/Tests/update_delete_aliases.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/update_delete_aliases.stable.err
@@ -0,0 +1,51 @@
+stderr of test 'update_delete_aliases` in directory 'sql/test/miscellaneous`
itself:
+
+
+# 15:27:52 >
+# 15:27:52 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=37046" "--set"
"mapi_usock=/var/tmp/mtest-19826/.s.monetdb.37046" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "embedded_c=true"
+# 15:27:52 >
+
+# 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 = 37046
+# cmdline opt mapi_usock = /var/tmp/mtest-19826/.s.monetdb.37046
+# 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
+
+# 15:27:53 >
+# 15:27:53 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-19826" "--port=37046"
+# 15:27:53 >
+
+MAPI = (monetdb) /var/tmp/mtest-19826/.s.monetdb.37046
+QUERY = update updateme as other set a=3 where updateme.b=2; --error
+ERROR = !SELECT: no such column 'updateme.b'
+CODE = 42S22
+MAPI = (monetdb) /var/tmp/mtest-19826/.s.monetdb.37046
+QUERY = update updateme as other set a=4 from other where other.a=1; --error
+ERROR = !UPDATE: multiple references into table 'other'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-19826/.s.monetdb.37046
+QUERY = delete from updateme where other.b=2; --error
+ERROR = !SELECT: no such column 'other.b'
+CODE = 42S22
+MAPI = (monetdb) /var/tmp/mtest-19826/.s.monetdb.37046
+QUERY = delete from updateme as other where updateme.b=2; --error
+ERROR = !SELECT: no such column 'updateme.b'
+CODE = 42S22
+
+# 15:27:53 >
+# 15:27:53 > "Done."
+# 15:27:53 >
+
diff --git a/sql/test/miscellaneous/Tests/update_delete_aliases.stable.out
b/sql/test/miscellaneous/Tests/update_delete_aliases.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/miscellaneous/Tests/update_delete_aliases.stable.out
@@ -0,0 +1,98 @@
+stdout of test 'update_delete_aliases` in directory 'sql/test/miscellaneous`
itself:
+
+
+# 15:34:26 >
+# 15:34:26 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38079" "--set"
"mapi_usock=/var/tmp/mtest-23035/.s.monetdb.38079" "--set" "monet_prompt="
"--forcemito"
"--dbpath=/home/ferreira/repositories/MonetDB-default/BUILD/var/MonetDB/mTests_sql_test_miscellaneous"
"--set" "embedded_c=true"
+# 15:34:26 >
+
+# MonetDB 5 server v11.32.0# MonetDB5 server v11.32.0 (hg id: 0d6a22537b43+)
+# 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.492 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://wired-142.cwi.nl:38079/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-23035/.s.monetdb.38079
+# 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.
+
+# 15:34:26 >
+# 15:34:26 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-23035" "--port=38079"
+# 15:34:26 >
+
+#create table updateme (a int, b int);
+#create table other (a int, b int);
+#insert into updateme values (1,1), (2,2), (3,3);
+[ 3 ]
+#update updateme as other set a=3 where b=2;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
