Changeset: ca5b5fd87b32 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ca5b5fd87b32
Modified Files:
sql/server/rel_updates.c
sql/test/merge-statements/Tests/mergestmt00.stable.err
sql/test/merge-statements/Tests/mergestmt01.sql
sql/test/merge-statements/Tests/mergestmt01.stable.err
sql/test/merge-statements/Tests/mergestmt01.stable.out
Branch: merge-statements
Log Message:
We won't be supporting sub-queries inside insert statements inside merge
statements for now, as a non-standard feature.
diffs (126 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
@@ -583,7 +583,7 @@ merge_generate_inserts(mvc *sql, sql_tab
dlist *inserts = rowlist->h->data.lval;
if (dlist_length(rowlist) != 1)
- return sql_error(sql, 02, SQLSTATE(21S01)
"MERGE: number of insert rows must be exactly one in a merge statement");
+ return sql_error(sql, 02, SQLSTATE(42000)
"MERGE: number of insert rows must be exactly one in a merge statement");
if (dlist_length(inserts) != list_length(collist))
return sql_error(sql, 02, SQLSTATE(21S01)
"MERGE: number of values doesn't match number of columns of table '%s'",
t->base.name);
@@ -600,16 +600,11 @@ merge_generate_inserts(mvc *sql, sql_tab
if (collist)
res = rel_project(sql->sa, r, exps);
} else {
- exp_kind ek = {type_value, card_relation, TRUE};
- res = rel_subquery(sql, r, val_or_q, ek, APPLY_JOIN);
- if (!res)
- return NULL;
- if (res->op != op_project || res->r || need_distinct(res))
- res = rel_project(sql->sa, res, rel_projections(sql,
res, NULL, 1, 0));
+ return sql_error(sql, 02, SQLSTATE(42000) "MERGE: sub-queries
not yet supported in INSERT clauses inside MERGE statements");
}
if (!res)
return NULL;
- if ((res->exps && list_length(res->exps) != list_length(collist)) ||
!res->exps)
+ if ((res->exps && list_length(res->exps) != list_length(collist)) ||
(!res->exps && collist))
return sql_error(sql, 02, SQLSTATE(21S01) "MERGE: query result
doesn't match number of columns in table '%s'", t->base.name);
res->l = r;
diff --git a/sql/test/merge-statements/Tests/mergestmt00.stable.err
b/sql/test/merge-statements/Tests/mergestmt00.stable.err
--- a/sql/test/merge-statements/Tests/mergestmt00.stable.err
+++ b/sql/test/merge-statements/Tests/mergestmt00.stable.err
@@ -35,7 +35,7 @@ CODE = 42S22
MAPI = (monetdb) /var/tmp/mtest-27206/.s.monetdb.34138
QUERY = merge into predata using (select aa, bb from merging) sub on
predata.bb = sub.bb when not matched then insert values (1, 1), (2,2); --error,
only one row allowed in insert
ERROR = !MERGE: number of insert rows must be exactly one in a merge statement
-CODE = 21S01
+CODE = 42000
# 12:58:53 >
# 12:58:53 > "Done."
diff --git a/sql/test/merge-statements/Tests/mergestmt01.sql
b/sql/test/merge-statements/Tests/mergestmt01.sql
--- a/sql/test/merge-statements/Tests/mergestmt01.sql
+++ b/sql/test/merge-statements/Tests/mergestmt01.sql
@@ -40,17 +40,20 @@ select aa, bb from predata;
delete from predata;
insert into predata values (2, 2);
-merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
- when not matched then insert select 41, -12;
+merge into predata using (select aa, bb from merging) thee on predata.bb =
thee.bb
+ when not matched then insert;
select aa, bb from predata;
rollback;
merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
- when matched then update set bb = bb - 1; --error, bb is ambiguous
+ when not matched then insert select 41, -12; --error, not supported
merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
- when not matched then insert select 1, 2, 3; --error, cardinality
mismatch between select stmt and table
+ when not matched then insert select aa, bb from predata; --error, not
supported
+
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when matched then update set bb = bb - 1; --error, bb is ambiguous
drop table merging;
drop table predata;
diff --git a/sql/test/merge-statements/Tests/mergestmt01.stable.err
b/sql/test/merge-statements/Tests/mergestmt01.stable.err
--- a/sql/test/merge-statements/Tests/mergestmt01.stable.err
+++ b/sql/test/merge-statements/Tests/mergestmt01.stable.err
@@ -28,16 +28,21 @@ stderr of test 'mergestmt01` in director
# 14:42:45 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-2774" "--port=35532"
# 14:42:45 >
-MAPI = (monetdb) /var/tmp/mtest-19990/.s.monetdb.30279
+MAPI = (monetdb) /var/tmp/mtest-25830/.s.monetdb.34955
+QUERY = merge into predata using (select aa, bb from merging) as sub on
predata.bb = sub.bb
+ when not matched then insert select 41, -12; --error, not
supported
+ERROR = !MERGE: sub-queries not yet supported in INSERT clauses inside MERGE
statements
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-31827/.s.monetdb.35750
+QUERY = merge into predata using (select aa, bb from merging) as sub on
predata.bb = sub.bb
+ when not matched then insert select aa, bb from predata;
--error, not supported
+ERROR = !MERGE: sub-queries not yet supported in INSERT clauses inside MERGE
statements
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-31827/.s.monetdb.35750
QUERY = merge into predata using (select aa, bb from merging) as sub on
predata.bb = sub.bb
when matched then update set bb = bb - 1; --error, bb is
ambiguous
ERROR = !SELECT: identifier 'bb' ambiguous
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-20607/.s.monetdb.32504
-QUERY = merge into predata using (select aa, bb from merging) as sub on
predata.bb = sub.bb
- when not matched then insert select 1, 2, 3; --error,
cardinality mismatch between select stmt and table
-ERROR = !MERGE: query result doesn't match number of columns in table 'predata'
-CODE = 21S01
# 14:42:45 >
# 14:42:45 > "Done."
diff --git a/sql/test/merge-statements/Tests/mergestmt01.stable.out
b/sql/test/merge-statements/Tests/mergestmt01.stable.out
--- a/sql/test/merge-statements/Tests/mergestmt01.stable.out
+++ b/sql/test/merge-statements/Tests/mergestmt01.stable.out
@@ -129,16 +129,16 @@ Ready.
[ 10 ]
#insert into predata values (2, 2);
[ 1 ]
-#merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
-# when not matched then insert select 41, -12;
+#merge into predata using (select aa, bb from merging) thee on predata.bb =
thee.bb
+# when not matched then insert;
[ 1 ]
#select aa, bb from predata;
% sys.predata, sys.predata # table_name
% aa, bb # name
% int, int # type
-% 2, 3 # length
+% 1, 1 # length
[ 2, 2 ]
-[ 41, -12 ]
+[ NULL, NULL ]
#rollback;
#drop table merging;
#drop table predata;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
