Changeset: 12c7c6eae5a6 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/12c7c6eae5a6 Added Files: sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.sql sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.err sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.out sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.sql sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.stable.err sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.stable.out Modified Files: sql/test/BugTracker-2021/Tests/All Branch: Oct2020 Log Message:
Added tests for Issue #7133 diffs (truncated from 424 to 300 lines): diff --git a/sql/test/BugTracker-2021/Tests/All b/sql/test/BugTracker-2021/Tests/All --- a/sql/test/BugTracker-2021/Tests/All +++ b/sql/test/BugTracker-2021/Tests/All @@ -10,3 +10,5 @@ unnest-union.Bug-7127 subquery-missing.Bug-7128 batcalc-between-undefined.Bug-7129 batappend-undefined.Bug-7130 +WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133 +WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133 diff --git a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.sql b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.sql @@ -0,0 +1,65 @@ +CREATE TABLE test (ID BIGINT, UUID STRING, sec BIGINT, data VARCHAR(1000)); +INSERT INTO test VALUES + (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000001, 'uuid0001', 1621539934, 'a1' ) +, (1000002, 'uuid0002', 1621539934, 'a2' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ); + +CREATE TABLE extra (ID BIGINT, UUID STRING, sec BIGINT, extra VARCHAR(1000)); +INSERT INTO extra VALUES + (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) + +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) + +, (1000001, 'uuid0001', 1621539934, 'a1' ) +, (1000001, 'uuid0001', 1621539934, 'a1' ) +, (1000001, 'uuid0001', 1621539934, 'a1' ) +, (1000001, 'uuid0001', 1621539934, 'a1' ) + +, (1000002, 'uuid0002', 1621539934, 'a2' ) +, (1000002, 'uuid0002', 1621539934, 'a2' ) +, (1000002, 'uuid0002', 1621539934, 'a2' ) +, (1000002, 'uuid0002', 1621539934, 'a2' ) + +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ); + +-- validate 4x of each id. +SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id; + +WITH ca AS +( + SELECT e.ID as id + , e.UUID as uuid + , e.sec as sec + FROM extra AS e +LEFT OUTER JOIN test AS t + ON t.ID = e.ID + AND t.UUID = e.UUID + AND t.sec = e.sec + WHERE t.ID IS NULL + AND t.UUID IS NULL + AND t.sec IS NULL +) +DELETE +FROM extra AS i +WHERE i.id = ca.id + AND i.sec = ca.sec + AND i.uuid = ca.uuid +; + +-- wrong tuples were deleted: +SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id; + +-- clean up +DROP TABLE IF EXISTS test; +DROP TABLE IF EXISTS extra; + diff --git a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.err b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.err @@ -0,0 +1,37 @@ +stderr of test 'WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133` in directory 'sql/test/BugTracker-2021` itself: + + +# 14:48:57 > +# 14:48:57 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_listenaddr=all" "--set" "mapi_port=38559" "--set" "mapi_usock=/var/tmp/mtest-711137/.s.monetdb.38559" "--forcemito" "--dbpath=/export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/MonetDB/mTests_sql_test_BugTracker-2021" "--set" "embedded_r=yes" "--set" "embedded_c=true" +# 14:48:57 > + +# builtin opt gdk_dbpath = /export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/monetdb5/dbfarm/demo +# builtin opt mapi_port = 50000 +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# builtin opt raw_strings = false +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_listenaddr = all +# cmdline opt mapi_port = 38559 +# cmdline opt mapi_usock = /var/tmp/mtest-711137/.s.monetdb.38559 +# cmdline opt gdk_dbpath = /export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/MonetDB/mTests_sql_test_BugTracker-2021 +# cmdline opt embedded_r = yes +# cmdline opt embedded_c = true + + + + + + +# 14:48:58 > +# 14:48:58 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-711137" "--port=38559" +# 14:48:58 > + +MAPI = (monetdb) /var/tmp/mtest-711137/.s.monetdb.38559 +ACTION= mapi_query_done + + +# 14:49:09 > +# 14:49:09 > "Done." +# 14:49:09 > + diff --git a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.out b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.out @@ -0,0 +1,94 @@ +stdout of test 'WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133` in directory 'sql/test/BugTracker-2021` itself: + + +# 14:48:57 > +# 14:48:57 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_listenaddr=all" "--set" "mapi_port=38559" "--set" "mapi_usock=/var/tmp/mtest-711137/.s.monetdb.38559" "--forcemito" "--dbpath=/export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/MonetDB/mTests_sql_test_BugTracker-2021" "--set" "embedded_r=yes" "--set" "embedded_c=true" +# 14:48:57 > + +# MonetDB 5 server v11.39.18 (hg id: d9135c8dafb2) +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2021', using 12 threads +# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers +# Found 62.658 GiB available main-memory of which we use 51.067 GiB +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2021 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://rocks202.scilens.private:38559/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-711137/.s.monetdb.38559 +# MonetDB/GIS module loaded +# MonetDB/R module loaded +# MonetDB/SQL module loaded + +# 14:48:58 > +# 14:48:58 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-711137" "--port=38559" +# 14:48:58 > + +#CREATE TABLE test (ID BIGINT, UUID STRING, sec BIGINT, data VARCHAR(1000)); +#INSERT INTO test VALUES +# (1000000, 'uuid0000', 1621539934, 'a0' ) +#, (1000001, 'uuid0001', 1621539934, 'a1' ) +#, (1000002, 'uuid0002', 1621539934, 'a2' ) +#, (1000003, 'uuid0003', 1621539934, 'a3' ); +[ 4 ] +#CREATE TABLE extra (ID BIGINT, UUID STRING, sec BIGINT, extra VARCHAR(1000)); +#INSERT INTO extra VALUES +# (1000009, 'uuid0009', 1621539934, 'a9' ) +#, (1000009, 'uuid0009', 1621539934, 'a9' ) +#, (1000009, 'uuid0009', 1621539934, 'a9' ) +#, (1000009, 'uuid0009', 1621539934, 'a9' ) +# +#, (1000000, 'uuid0000', 1621539934, 'a0' ) +#, (1000000, 'uuid0000', 1621539934, 'a0' ) +#, (1000000, 'uuid0000', 1621539934, 'a0' ) +#, (1000000, 'uuid0000', 1621539934, 'a0' ) +# +#, (1000001, 'uuid0001', 1621539934, 'a1' ) +#, (1000001, 'uuid0001', 1621539934, 'a1' ) +#, (1000001, 'uuid0001', 1621539934, 'a1' ) +#, (1000001, 'uuid0001', 1621539934, 'a1' ) +[ 20 ] +#SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id; +% sys.extra, sys. # table_name +% id, cnt # name +% bigint, bigint # type +% 7, 1 # length +[ 1000000, 4 ] +[ 1000001, 4 ] +[ 1000002, 4 ] +[ 1000003, 4 ] +[ 1000009, 4 ] +#WITH ca AS +#( +# SELECT e.ID as id +# , e.UUID as uuid +# , e.sec as sec +# FROM extra AS e +#LEFT OUTER JOIN test AS t +# ON t.ID = e.ID +# AND t.UUID = e.UUID +# AND t.sec = e.sec +# WHERE t.ID IS NULL +# AND t.UUID IS NULL +# AND t.sec IS NULL +#) +#DELETE +#FROM extra AS i +#WHERE i.id = ca.id +# AND i.sec = ca.sec +# AND i.uuid = ca.uuid +#; +[ 4 ] +#SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id; +% sys.extra, sys. # table_name +% id, cnt # name +% bigint, bigint # type +% 7, 1 # length +[ 1000000, 4 ] +[ 1000001, 4 ] +[ 1000002, 4 ] +[ 1000003, 4 ] + +# 14:49:09 > +# 14:49:09 > "Done." +# 14:49:09 > + diff --git a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.sql b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.sql @@ -0,0 +1,55 @@ +CREATE TABLE test (ID BIGINT, UUID STRING, sec BIGINT, data VARCHAR(1000)); +INSERT INTO test VALUES + (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ); + +CREATE TABLE extra (ID BIGINT, UUID STRING, sec BIGINT, extra VARCHAR(1000)); +INSERT INTO extra VALUES + (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) +, (1000009, 'uuid0009', 1621539934, 'a9' ) + +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000000, 'uuid0000', 1621539934, 'a0' ) + +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ); + +-- validate 4x of each id. +SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id; + +WITH ca AS +( + SELECT e.ID as id + , e.UUID as uuid + , e.sec as sec + FROM extra AS e +LEFT OUTER JOIN test AS t + ON t.ID = e.ID + AND t.UUID = e.UUID + AND t.sec = e.sec + WHERE t.ID IS NULL + AND t.UUID IS NULL + AND t.sec IS NULL +) +DELETE +FROM extra AS i +WHERE i.id = ca.id + AND i.sec = ca.sec + AND i.uuid = ca.uuid +; -- 16 affected rows! + +-- Count is -4! +SELECT COUNT(*) FROM extra; +-- Count is 12! +select table, column, type, count from sys.storage() where table = 'extra'; + +-- clean up +DROP TABLE IF EXISTS test; +DROP TABLE IF EXISTS extra; + diff --git a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.stable.err b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.stable.err @@ -0,0 +1,38 @@ +stderr of test 'WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133` in directory 'sql/test/BugTracker-2021` itself: + + +# 14:49:53 > +# 14:49:53 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_listenaddr=all" "--set" "mapi_port=36922" "--set" "mapi_usock=/var/tmp/mtest-711376/.s.monetdb.36922" "--forcemito" "--dbpath=/export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/MonetDB/mTests_sql_test_BugTracker-2021" "--set" "embedded_r=yes" "--set" "embedded_c=true" +# 14:49:53 > + +# builtin opt gdk_dbpath = /export/scratch1/home/zhang/monetdb/mdb-nst/Oct2020/var/monetdb5/dbfarm/demo +# builtin opt mapi_port = 50000 +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# builtin opt raw_strings = false +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_listenaddr = all +# cmdline opt mapi_port = 36922 _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
