Changeset: 5f09438acd6f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5f09438acd6f
Modified Files:
sql/server/rel_updates.c
sql/test/subquery/Tests/subquery4.sql
sql/test/subquery/Tests/subquery4.stable.err
Branch: default
Log Message:
Disallow multiple assignments on the same column on the same update. Also test
for table update privileges on every case.
diffs (115 lines):
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
@@ -910,16 +910,10 @@ rel_update(mvc *sql, sql_rel *t, sql_rel
static sql_exp *
update_check_column(mvc *sql, sql_table *t, sql_column *c, sql_exp *v, sql_rel
*r, char *cname, const char *action)
{
- if (!c) {
- rel_destroy(r);
- return sql_error(sql, 02, SQLSTATE(42S22) "%s: no such column
'%s.%s'", action, t->base.name, cname);
- }
if (!table_privs(sql, t, PRIV_UPDATE) && !sql_privilege(sql,
sql->user_id, c->base.id, PRIV_UPDATE))
return sql_error(sql, 02, SQLSTATE(42000) "%s: insufficient
privileges for user '%s' to update table '%s' on column '%s'", action,
stack_get_string(sql, "current_user"), t->base.name, cname);
- if (!v || (v = rel_check_type(sql, &c->type, r, v, type_equal)) ==
NULL) {
- rel_destroy(r);
+ if (!v || (v = rel_check_type(sql, &c->type, r, v, type_equal)) == NULL)
return NULL;
- }
return v;
}
@@ -964,6 +958,9 @@ update_generate_assignments(sql_query *q
if (single && a->token == SQL_DEFAULT) {
char *colname = assignment->h->next->data.sval;
sql_column *col = mvc_bind_column(sql, t,
colname);
+
+ if (!col)
+ return sql_error(sql, 02,
SQLSTATE(42S22) "%s: no such column '%s.%s'", action, t->base.name, colname);
if (col->def) {
char *typestr =
subtype2string2(&col->type);
if (!typestr)
@@ -1019,6 +1016,10 @@ update_generate_assignments(sql_query *q
sql_column *c = mvc_bind_column(sql, t, cname);
sql_exp *v = n->data;
+ if (!c)
+ return sql_error(sql, 02,
SQLSTATE(42S22) "%s: no such column '%s.%s'", action, t->base.name, cname);
+ if (updates[c->colnr])
+ return sql_error(sql, 02,
SQLSTATE(42000) "%s: Multiple assignments to same column '%s'", action,
c->base.name);
if (mt && pcols) {
for (node *nn = pcols->h; nn; nn =
n->next) {
int next = *(int*) nn->data;
@@ -1035,13 +1036,11 @@ update_generate_assignments(sql_query *q
exp_label(sql->sa, v, ++sql->label);
if (!exp_is_atom(v) || outer)
v = exp_ref(sql->sa, v);
- if (!v) { /* check for NULL */
+ if (!v) /* check for NULL */
v = exp_atom(sql->sa,
atom_general(sql->sa, &c->type, NULL));
- } else if ((v = update_check_column(sql, t, c,
v, r, cname, action)) == NULL) {
+ if (!(v = update_check_column(sql, t, c, v, r,
cname, action)))
return NULL;
- }
list_append(exps, exp_column(sql->sa,
t->base.name, cname, &c->type, CARD_MULTI, 0, 0));
- assert(!updates[c->colnr]);
exp_setname(sql->sa, v, c->t->base.name,
c->base.name);
updates[c->colnr] = v;
}
@@ -1049,6 +1048,10 @@ update_generate_assignments(sql_query *q
char *cname = assignment->h->next->data.sval;
sql_column *c = mvc_bind_column(sql, t, cname);
+ if (!c)
+ return sql_error(sql, 02, SQLSTATE(42S22) "%s:
no such column '%s.%s'", action, t->base.name, cname);
+ if (updates[c->colnr])
+ return sql_error(sql, 02, SQLSTATE(42000) "%s:
Multiple assignments to same column '%s'", action, c->base.name);
if (mt && pcols) {
for (node *nn = pcols->h; nn; nn = nn->next) {
int next = *(int*) nn->data;
@@ -1061,11 +1064,10 @@ update_generate_assignments(sql_query *q
}
}
}
- if (!v) {
+ if (!v)
v = exp_atom(sql->sa, atom_general(sql->sa,
&c->type, NULL));
- } else if ((v = update_check_column(sql, t, c, v, r,
cname, action)) == NULL) {
+ if (!(v = update_check_column(sql, t, c, v, r, cname,
action)))
return NULL;
- }
list_append(exps, exp_column(sql->sa, t->base.name,
cname, &c->type, CARD_MULTI, 0, 0));
exp_setname(sql->sa, v, c->t->base.name, c->base.name);
updates[c->colnr] = v;
diff --git a/sql/test/subquery/Tests/subquery4.sql
b/sql/test/subquery/Tests/subquery4.sql
--- a/sql/test/subquery/Tests/subquery4.sql
+++ b/sql/test/subquery/Tests/subquery4.sql
@@ -150,6 +150,8 @@ INSERT INTO another_T VALUES ((SELECT 1
--UPDATE another_T SET (col7, col8) = (SELECT 1,2 UNION ALL SELECT 1,2);
--error, more than one row returned by a subquery used as an expression
UPDATE another_T SET (col7, col8) = (SELECT 1 UNION ALL SELECT 2); --error,
number of columns does not match number of values
UPDATE another_T SET (col7, col8) = (SELECT 1,2,3); --error, number of columns
does not match number of values
+UPDATE another_T SET col5 = 1, col5 = 6; --error, multiple assignments to same
column "col5"
+UPDATE another_T SET (col5, col6) = ((select 1,2)), col5 = 6; --error,
multiple assignments to same column "col5"
DECLARE x int;
SET x = MAX(1) over (); --error, not allowed
diff --git a/sql/test/subquery/Tests/subquery4.stable.err
b/sql/test/subquery/Tests/subquery4.stable.err
--- a/sql/test/subquery/Tests/subquery4.stable.err
+++ b/sql/test/subquery/Tests/subquery4.stable.err
@@ -164,7 +164,15 @@ MAPI = (monetdb) /var/tmp/mtest-450395/
QUERY = UPDATE another_T SET (col7, col8) = (SELECT 1,2,3); --error, number of
columns does not match number of values
ERROR = !UPDATE: The number of specified columns between the SET clause and
the right side don't match (2 != 3)
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-450395/.s.monetdb.35919
+MAPI = (monetdb) /var/tmp/mtest-548551/.s.monetdb.35009
+QUERY = UPDATE another_T SET col5 = 1, col5 = 6; --error, multiple assignments
to same column "col5"
+ERROR = !UPDATE: Multiple assignments to same column 'col5'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-548551/.s.monetdb.35009
+QUERY = UPDATE another_T SET (col5, col6) = ((select 1,2)), col5 = 6; --error,
multiple assignments to same column "col5"
+ERROR = !UPDATE: Multiple assignments to same column 'col5'
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-548551/.s.monetdb.35009
QUERY = SET x = MAX(1) over (); --error, not allowed
ERROR = !MAX: window function 'max' not allowed in SET clause (use subquery)
CODE = 42000
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
