Changeset: 8a3e0f793552 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/8a3e0f793552 Added Files: sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.test sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133.test Removed 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: Jul2021 Log Message:
Converted tests diffs (truncated from 618 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 @@ -11,5 +11,5 @@ unnest-union.Bug-7127 subquery-missing.Bug-7128 batcalc-between-undefined.Bug-7129 batappend-undefined.Bug-7130 -KNOWNFAIL?WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133 -KNOWNFAIL?WITH-alias-DELETE-2.deletes-too-many-tuples.Bug-7133 +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 deleted file mode 100644 --- a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.sql +++ /dev/null @@ -1,65 +0,0 @@ -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 deleted file mode 100644 --- a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.err +++ /dev/null @@ -1,37 +0,0 @@ -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 deleted file mode 100644 --- a/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.stable.out +++ /dev/null @@ -1,94 +0,0 @@ -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-1.deletes-wrong-tuples.Bug-7133.test b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.test new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2021/Tests/WITH-alias-DELETE-1.deletes-wrong-tuples.Bug-7133.test @@ -0,0 +1,93 @@ +statement ok +CREATE TABLE test (ID BIGINT, UUID STRING, sec BIGINT, data VARCHAR(1000)) + +statement ok rowcount 4 +INSERT INTO test VALUES + (1000000, 'uuid0000', 1621539934, 'a0' ) +, (1000001, 'uuid0001', 1621539934, 'a1' ) +, (1000002, 'uuid0002', 1621539934, 'a2' ) +, (1000003, 'uuid0003', 1621539934, 'a3' ) + +statement ok +CREATE TABLE extra (ID BIGINT, UUID STRING, sec BIGINT, extra VARCHAR(1000)) + +statement ok rowcount 20 +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' ) + +query II nosort +SELECT id, COUNT(*) as cnt FROM extra GROUP BY id ORDER BY id +---- +1000000 +4 +1000001 +4 +1000002 +4 +1000003 +4 +1000009 +4 + +statement error 42S22!SELECT: no such column 'ca.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 + + +query II nosort _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
