Changeset: 019c651dec1c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=019c651dec1c
Modified Files:
testing/Mconvert.py.in
Branch: mtest
Log Message:
merge
diffs (truncated from 1007 to 300 lines):
diff --git a/monetdb5/scheduler/Tests/sched00.maltest
b/monetdb5/scheduler/Tests/sched00.maltest
--- a/monetdb5/scheduler/Tests/sched00.maltest
+++ b/monetdb5/scheduler/Tests/sched00.maltest
@@ -9,10 +9,11 @@ function f();
run_isolate.isolation();
io.print("done\n");
mdb.List();
-end f;
+end f
-query T rowsort
- f();
+query T rowsort
+f()
----
end user.f;@#[7] (0)
+
diff --git a/sql/test/merge-statements/Tests/mergestmt00.test
b/sql/test/merge-statements/Tests/mergestmt00.test
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-statements/Tests/mergestmt00.test
@@ -0,0 +1,53 @@
+statement ok
+create table merging (aa int, bb clob)
+
+statement ok
+insert into merging values (-100, 1)
+
+statement ok
+create table predata (aa int, bb int)
+
+statement ok
+insert into predata values (15, 3), (3, 1), (2, 1), (5, 3), (NULL, 2), (3, 2),
(4, 1), (6, 3), (8, 2), (NULL, 4)
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb when matched then delete
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb when matched then update set bb = 1
+
+statement ok
+update merging set bb = 2 where bb = 1
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb when matched then update set bb = 1
+
+query II rowsort
+select aa, bb from predata order by bb
+----
+14 values hashing to 877ac354eafe216cbc03e6a9db3cb23f
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb when not matched then insert values (5, 5)
+
+query II rowsort
+select aa, bb from predata order by bb
+----
+16 values hashing to 2a62489ee71c16e2563f54a79de06589
+
+statement ok
+merge into predata as othern using (select aa, bb from merging) sub on
othern.bb = sub.bb when not matched then insert values (5, 5)
+
+statement error
+merge into predata as othern using (select aa, bb from merging) sub on
predata.bb = sub.bb when not matched then insert values (5, 5)
+
+statement error
+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)
+
+statement ok
+drop table merging
+
+statement ok
+drop table predata
+
+
diff --git a/sql/test/merge-statements/Tests/mergestmt01.test
b/sql/test/merge-statements/Tests/mergestmt01.test
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-statements/Tests/mergestmt01.test
@@ -0,0 +1,167 @@
+statement ok
+create table merging (aa int, bb clob)
+
+statement ok
+create table predata (aa int, bb int)
+
+statement ok
+start transaction
+
+statement ok
+insert into merging values (-100, 1)
+
+statement ok
+insert into predata values (15, 3), (3, 1), (2, 1), (5, 3), (NULL, 2), (3, 2)
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb
+ when matched then delete when not matched then insert values (6, 6)
+
+query II rowsort
+select aa, bb from predata
+----
+15
+3
+3
+2
+5
+3
+NULL
+2
+
+statement ok
+delete from predata
+
+statement ok
+insert into predata values (15, 3), (3, 1), (2, 1), (5, 3), (4, 1), (6, 3)
+
+statement ok
+merge into predata using (select aa, bb from merging) sub on predata.bb =
sub.bb
+ when not matched then insert values (null, null) when matched then
update set bb = 3
+
+query II rowsort
+select aa, bb from predata
+----
+12 values hashing to 21aa56dd77ec4302cc2641c4cfea9233
+
+statement ok
+delete from predata
+
+statement ok
+insert into predata values (15, 3), (3, 1), (2, 1), (5, 3), (8, 2), (NULL, 4)
+
+statement ok
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when matched then update set bb = predata.bb + 1
+
+statement ok
+merge into predata othertt using (select aa, bb from merging) as sub on
othertt.bb = sub.bb
+ when matched then update set bb = othertt.bb + sub.bb
+
+query II rowsort
+select aa, bb from predata
+----
+12 values hashing to a7c1713160be18de5a7cdd8459a12cba
+
+statement ok
+delete from predata
+
+statement ok
+insert into predata values (15, 1), (3, 1), (6, 3), (8, 2)
+
+statement ok
+insert into merging values (-500, -300)
+
+statement ok
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when not matched then insert values (sub.aa, 2)
+
+query II rowsort
+select aa, bb from predata
+----
+10 values hashing to 0a2e202603af3ce2eade6b5c5ac22611
+
+statement ok
+insert into merging values (1900, 2)
+
+statement ok
+merge into predata othertt using (select aa, bb from merging) as sub on
othertt.bb = sub.bb
+ when not matched then insert values (sub.aa + 5, sub.bb * 2)
+
+query II rowsort
+select aa, bb from predata
+----
+12 values hashing to 63514145a98e5e67e763da075f69f364
+
+statement ok
+delete from predata
+
+statement ok
+insert into predata values (2, 2)
+
+statement ok
+merge into predata using (select aa, bb from merging) thee on predata.bb =
thee.bb
+ when not matched then insert
+
+query II rowsort
+select aa, bb from predata
+----
+2
+2
+NULL
+NULL
+NULL
+NULL
+
+statement ok
+rollback
+
+statement ok
+insert into predata values (1, 1)
+
+statement ok
+insert into merging values (1, 1), (2, 1)
+
+statement error
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when matched then update set aa = sub.aa
+
+query II rowsort
+select aa, bb from predata
+----
+1
+1
+
+statement error
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when matched then delete
+
+query II rowsort
+select aa, bb from predata
+----
+1
+1
+
+statement error
+merge into predata othertt using (select aa, bb from merging) as sub on
othertt.bb = sub.bb
+ when not matched then insert values (othertt.aa, othertt.bb)
+
+statement error
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when not matched then insert select 41, -12
+
+statement error
+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
+
+statement error
+merge into predata using (select aa, bb from merging) as sub on predata.bb =
sub.bb
+ when matched then update set bb = bb - 1
+
+statement ok
+drop table merging
+
+statement ok
+drop table predata
+
+
diff --git a/sql/test/merge-statements/Tests/mergestmt02.test
b/sql/test/merge-statements/Tests/mergestmt02.test
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-statements/Tests/mergestmt02.test
@@ -0,0 +1,43 @@
+statement ok
+create table merging (aa int, bb int)
+
+statement ok
+create table predata (aa int, bb int not null)
+
+statement ok
+merge into predata using (select * from merging) other on predata.bb = other.bb
+ when not matched then insert
+
+statement ok
+insert into merging
+
+statement error
+merge into predata using (select * from merging) other on predata.bb = other.bb
+ when not matched then insert
+
+statement ok
+truncate merging
+
+statement ok
+alter table predata alter bb set null
+
+statement ok
+alter table predata add constraint uniquebb unique (bb)
+
+statement ok
+insert into merging values (1,1), (0,1)
+
+statement ok
+insert into predata values (1,1)
+
+statement error
+merge into predata using (select * from merging) other on predata.aa = other.aa
+ when not matched then insert values (other.aa, other.bb)
+
+statement ok
+drop table merging
+
+statement ok
+drop table predata
+
+
diff --git a/sql/test/merge-statements/Tests/mergestmt03.test
b/sql/test/merge-statements/Tests/mergestmt03.test
new file mode 100644
--- /dev/null
+++ b/sql/test/merge-statements/Tests/mergestmt03.test
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
