Changeset: 3942bf20e695 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3942bf20e695 Added Files: sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.reqtests sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.sql sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.err sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.out Modified Files: sql/test/BugTracker-2018/Tests/All sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.sql sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out Branch: Mar2018 Log Message:
Split the truncation of the system core tables in schema tmp from the script which truncates the system core tables in schema sys. The behavior in schema sys (returns an error for testuser) is different for schema tmp (does NOT return an error for testuser). The truncate_tmp_tables.Bug-6543.stable.* files contain the desired output. diffs (truncated from 350 to 300 lines): diff --git a/sql/test/BugTracker-2018/Tests/All b/sql/test/BugTracker-2018/Tests/All --- a/sql/test/BugTracker-2018/Tests/All +++ b/sql/test/BugTracker-2018/Tests/All @@ -23,6 +23,7 @@ truncate_add_user USER=testuser&PASSWD=testpassword&HAVE_NETCDF?truncate_sys_netcdf_tables.Bug-6543 USER=testuser&PASSWD=testpassword&HAVE_SAMTOOLS?truncate_bam_tables.Bug-6543 USER=testuser&PASSWD=testpassword?truncate_sys_tables.Bug-6543 +USER=testuser&PASSWD=testpassword?truncate_tmp_tables.Bug-6543 truncate_remove_user truncate_empty_table.Bug-6544 sqlitelogictest-in-query.Bug-6545 diff --git a/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.sql b/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.sql --- a/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.sql +++ b/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.sql @@ -1,27 +1,3 @@ -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; -TRUNCATE TABLE tmp.triggers; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; - -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; -TRUNCATE TABLE tmp.objects; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; - -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; -TRUNCATE TABLE tmp.keys; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; - -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; -TRUNCATE TABLE tmp.idxs; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; - -SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; -TRUNCATE TABLE tmp._tables; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; - -SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; -TRUNCATE TABLE tmp._columns; -SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; - SELECT (COUNT(*) > 0) AS has_rows FROM sys.user_role; TRUNCATE TABLE sys.user_role; SELECT (COUNT(*) > 0) AS has_rows FROM sys.user_role; diff --git a/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out b/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out --- a/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out +++ b/sql/test/BugTracker-2018/Tests/truncate_sys_tables.Bug-6543.stable.out @@ -24,90 +24,6 @@ Ready. # 17:47:14 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-9985" "--port=31828" # 17:47:14 > -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp.triggers; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp.objects; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp.keys; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp.idxs; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp._tables; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] -#TRUNCATE TABLE tmp._columns; -[ 0 ] -#SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; -% tmp.L4 # table_name -% has_rows # name -% boolean # type -% 5 # length -[ false ] #SELECT (COUNT(*) > 0) AS has_rows FROM sys.user_role; % sys.L4 # table_name % has_rows # name diff --git a/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.reqtests b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.reqtests new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.reqtests @@ -0,0 +1,1 @@ +truncate_add_user diff --git a/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.sql b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.sql new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.sql @@ -0,0 +1,29 @@ +-- add some content into tmp._tables, tmp._columns, tmp.keys, tmp.idxs and tmp.objects by creating a temp table with a Pkey +CREATE TEMP TABLE tmptable6543 (id int NOT NULL PRIMARY KEY, name VARCHAR(99) NOT NULL) ON COMMIT PRESERVE ROWS; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; +TRUNCATE TABLE tmp.triggers; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; +TRUNCATE TABLE tmp.objects; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; +TRUNCATE TABLE tmp.keys; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; +TRUNCATE TABLE tmp.idxs; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp.idxs; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; +TRUNCATE TABLE tmp._tables; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp._tables; + +SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; +TRUNCATE TABLE tmp._columns; +SELECT (COUNT(*) > 0) AS has_rows FROM tmp._columns; + +DROP TABLE tmptable6543; + diff --git a/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.err b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.err @@ -0,0 +1,59 @@ +stderr of test 'truncate_tmp_tables.Bug-6543` in directory 'sql/test/BugTracker-2018` itself: + + +# 19:00:04 > +# 19:00:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=32461" "--set" "mapi_usock=/var/tmp/mtest-11969/.s.monetdb.32461" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2018" +# 19:00:04 > + +# builtin opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/monetdb5/dbfarm/demo +# builtin opt gdk_debug = 0 +# builtin opt gdk_vmtrim = no +# builtin opt monet_prompt = > +# builtin opt monet_daemon = no +# builtin opt mapi_port = 50000 +# builtin opt mapi_open = false +# builtin opt mapi_autosense = false +# builtin opt sql_optimizer = default_pipe +# builtin opt sql_debug = 0 +# cmdline opt gdk_nr_threads = 0 +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 32461 +# cmdline opt mapi_usock = /var/tmp/mtest-11969/.s.monetdb.32461 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2018 +# cmdline opt gdk_debug = 553648138 + +# 19:03:45 > +# 19:03:45 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-14006" "--port=32702" "-utestuser" "-Ptestpassword" +# 19:03:45 > + +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp.triggers; +ERROR = !TRUNCATE: cannot truncate system table 'triggers' +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp.objects; +ERROR = !TRUNCATE: cannot truncate system table 'objects' +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp.keys; +ERROR = !TRUNCATE: cannot truncate system table 'keys' +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp.idxs; +ERROR = !TRUNCATE: cannot truncate system table 'idxs' +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp._tables; +ERROR = !TRUNCATE: cannot truncate system table '_tables' +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-11969/.s.monetdb.32461 +QUERY = TRUNCATE TABLE tmp._columns; +ERROR = !TRUNCATE: cannot truncate system table '_columns' +CODE = 42000 + + +# 19:00:04 > +# 19:00:04 > "Done." +# 19:00:04 > + diff --git a/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.out b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/truncate_tmp_tables.Bug-6543.stable.out @@ -0,0 +1,105 @@ +stdout of test 'truncate_tmp_tables.Bug-6543` in directory 'sql/test/BugTracker-2018` itself: + + +# 19:00:04 > +# 19:00:04 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "mapi_open=true" "--set" "mapi_port=32461" "--set" "mapi_usock=/var/tmp/mtest-11969/.s.monetdb.32461" "--set" "monet_prompt=" "--forcemito" "--dbpath=/export/scratch1/dinther/INSTALL/var/MonetDB/mTests_sql_test_BugTracker-2018" +# 19:00:04 > + +# MonetDB 5 server v11.29.0 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2018', using 4 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 128bit integers +# Found 31.307 GiB available main-memory. +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://catskill.da.cwi.nl:32461/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-11969/.s.monetdb.32461 +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 19:00:04 > +# 19:00:04 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e" "--host=/var/tmp/mtest-11969" "--port=32461" "-utestuser" "-Ptestpassword" +# 19:00:04 > + +#CREATE TEMP TABLE tmptable6543 (id int NOT NULL PRIMARY KEY, name VARCHAR(99) NOT NULL); +#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; +% tmp.L4 # table_name +% has_rows # name +% boolean # type +% 5 # length +[ false ] +#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.triggers; +% tmp.L4 # table_name +% has_rows # name +% boolean # type +% 5 # length +[ false ] +#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; +% tmp.L4 # table_name +% has_rows # name +% boolean # type +% 5 # length +[ true ] +#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.objects; +% tmp.L4 # table_name +% has_rows # name +% boolean # type +% 5 # length +[ true ] +#SELECT (COUNT(*) > 0) AS has_rows FROM tmp.keys; +% tmp.L4 # table_name +% has_rows # name +% boolean # type _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
