Changeset: d4ab8c265b66 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d4ab8c265b66 Added Files: sql/test/bugs/Tests/correlated_update_bug.sql sql/test/bugs/Tests/correlated_update_bug.stable.err sql/test/bugs/Tests/correlated_update_bug.stable.out Modified Files: sql/test/bugs/Tests/All Branch: Apr2012 Log Message:
added test for bug in optimizer (semi infinite recursive optimization) diffs (272 lines): diff --git a/sql/test/bugs/Tests/All b/sql/test/bugs/Tests/All --- a/sql/test/bugs/Tests/All +++ b/sql/test/bugs/Tests/All @@ -101,3 +101,4 @@ crash_order_by in_or_bug create_insert_select_aggr-bug-00001 procedure_resolution_bug +correlated_update_bug diff --git a/sql/test/bugs/Tests/correlated_update_bug.sql b/sql/test/bugs/Tests/correlated_update_bug.sql new file mode 100644 --- /dev/null +++ b/sql/test/bugs/Tests/correlated_update_bug.sql @@ -0,0 +1,86 @@ + +CREATE TABLE t1 + (id INT NOT NULL + ,c1 DOUBLE NOT NULL + ,c2 DOUBLE NOT NULL + ,c3 DOUBLE NOT NULL + ,c4 DOUBLE NOT NULL + ,c5 DOUBLE NOT NULL + ,c6 DOUBLE NOT NULL + ,c7 DOUBLE NOT NULL + ) +; + +CREATE TABLE t2 + (id INT NOT NULL + ,c1 DOUBLE NOT NULL + ,c2 DOUBLE NOT NULL + ,c3 DOUBLE NOT NULL + ,c4 DOUBLE NOT NULL + ,c5 DOUBLE NOT NULL + ,c6 DOUBLE NOT NULL + ,c7 DOUBLE NOT NULL + ) +; + +INSERT INTO t1 +VALUES + (1,1.0,2.0,3.0,4.0,5.0,6.0,7.0), + (2,2.0,3.0,4.0,5.0,6.0,7.0,8.0), + (3,3.0,4.0,5.0,6.0,7.0,8.0,9.0), + (4,4.0,5.0,6.0,7.0,8.0,9.0,1.0), + (5,5.0,6.0,7.0,8.0,9.0,1.0,2.0) +; + +INSERT INTO t2 +VALUES + (1,11.0,12.0,13.0,14.0,15.0,16.0,17.0), + (2,12.0,13.0,14.0,15.0,16.0,17.0,18.0), + (3,13.0,14.0,15.0,16.0,17.0,18.0,19.0), + (4,14.0,15.0,16.0,17.0,18.0,19.0,11.0), + (5,15.0,16.0,17.0,18.0,19.0,11.0,12.0) +; + +SELECT * FROM t1; +SELECT * FROM t2; + + +UPDATE t1 + SET c1 = (SELECT c1 + FROM t2 + WHERE t2.id = t1.id + ) + ,c2 = (SELECT c2 + FROM t2 + WHERE t2.id = t1.id + ) + ,c3 = (SELECT c3 + FROM t2 + WHERE t2.id = t1.id + ) + ,c4 = (SELECT c4 + FROM t2 + WHERE t2.id = t1.id + ) + ,c5 = (SELECT c5 + FROM t2 + WHERE t2.id = t1.id + ) + ,c6 = (SELECT c6 + FROM t2 + WHERE t2.id = t1.id + ) + ,c7 = (SELECT c7 + FROM t2 + WHERE t2.id = t1.id + ) + WHERE EXISTS (SELECT id + FROM t2 + WHERE t2.id = t1.id + ) +; + +SELECT * FROM t1; + +DROP TABLE t1; +DROP TABLE t2; diff --git a/sql/test/bugs/Tests/correlated_update_bug.stable.err b/sql/test/bugs/Tests/correlated_update_bug.stable.err new file mode 100644 --- /dev/null +++ b/sql/test/bugs/Tests/correlated_update_bug.stable.err @@ -0,0 +1,37 @@ +stderr of test 'correlated_update_bug` in directory 'test/bugs` itself: + + +# 16:41:52 > +# 16:41:52 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "gdk_dbfarm=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB" "--set" "mapi_open=true" "--set" "mapi_port=31957" "--set" "monet_prompt=" "--trace" "--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_bugs" "--set" "mal_listing=0" +# 16:41:52 > + +# builtin opt gdk_dbname = demo +# builtin opt gdk_dbfarm = /home/niels/scratch/rc-clean/Linux-x86_64/var/monetdb5/dbfarm +# builtin opt gdk_debug = 0 +# builtin opt gdk_alloc_map = no +# builtin opt gdk_vmtrim = yes +# 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 gdk_dbfarm = /home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB +# cmdline opt mapi_open = true +# cmdline opt mapi_port = 31957 +# cmdline opt monet_prompt = +# cmdline opt mal_listing = 2 +# cmdline opt gdk_dbname = mTests_test_bugs +# cmdline opt mal_listing = 0 + +# 16:41:52 > +# 16:41:52 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels" "--port=31957" +# 16:41:52 > + + +# 16:41:52 > +# 16:41:52 > "Done." +# 16:41:52 > + diff --git a/sql/test/bugs/Tests/correlated_update_bug.stable.out b/sql/test/bugs/Tests/correlated_update_bug.stable.out new file mode 100644 --- /dev/null +++ b/sql/test/bugs/Tests/correlated_update_bug.stable.out @@ -0,0 +1,126 @@ +stdout of test 'correlated_update_bug` in directory 'test/bugs` itself: + + +# 16:41:52 > +# 16:41:52 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set" "gdk_dbfarm=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB" "--set" "mapi_open=true" "--set" "mapi_port=31957" "--set" "monet_prompt=" "--trace" "--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_bugs" "--set" "mal_listing=0" +# 16:41:52 > + +# MonetDB 5 server v11.9.2 +# This is an unreleased version +# Serving database 'mTests_test_bugs', using 4 threads +# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked +# Found 3.778 GiB available main-memory. +# Copyright (c) 1993-July 2008 CWI. +# Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved +# Visit http://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://niels.nesco.mine.nu:31957/ +# MonetDB/GIS module loaded +# MonetDB/SQL module loaded + +Ready. + +# 16:41:52 > +# 16:41:52 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels" "--port=31957" +# 16:41:52 > + +#CREATE TABLE t1 +# (id INT NOT NULL +# ,c1 DOUBLE NOT NULL +# ,c2 DOUBLE NOT NULL +# ,c3 DOUBLE NOT NULL +# ,c4 DOUBLE NOT NULL +# ,c5 DOUBLE NOT NULL +# ,c6 DOUBLE NOT NULL +# ,c7 DOUBLE NOT NULL +# ) +#; +#CREATE TABLE t2 +# (id INT NOT NULL +# ,c1 DOUBLE NOT NULL +# ,c2 DOUBLE NOT NULL +# ,c3 DOUBLE NOT NULL +# ,c4 DOUBLE NOT NULL +# ,c5 DOUBLE NOT NULL +# ,c6 DOUBLE NOT NULL +# ,c7 DOUBLE NOT NULL +# ) +#; +#INSERT INTO t1 +#VALUES +# (1,1.0,2.0,3.0,4.0,5.0,6.0,7.0), +# (2,2.0,3.0,4.0,5.0,6.0,7.0,8.0), +# (3,3.0,4.0,5.0,6.0,7.0,8.0,9.0), +# (4,4.0,5.0,6.0,7.0,8.0,9.0,1.0), +# (5,5.0,6.0,7.0,8.0,9.0,1.0,2.0) +#; +[ 5 ] +#INSERT INTO t2 +#VALUES +# (1,11.0,12.0,13.0,14.0,15.0,16.0,17.0), +# (2,12.0,13.0,14.0,15.0,16.0,17.0,18.0), +# (3,13.0,14.0,15.0,16.0,17.0,18.0,19.0), +# (4,14.0,15.0,16.0,17.0,18.0,19.0,11.0), +# (5,15.0,16.0,17.0,18.0,19.0,11.0,12.0) +#; +[ 5 ] +#SELECT * FROM t1; +% sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1 # table_name +% id, c1, c2, c3, c4, c5, c6, c7 # name +% int, double, double, double, double, double, double, double # type +% 1, 24, 24, 24, 24, 24, 24, 24 # length +[ 1, 1, 2, 3, 4, 5, 6, 7 ] +[ 2, 2, 3, 4, 5, 6, 7, 8 ] +[ 3, 3, 4, 5, 6, 7, 8, 9 ] +[ 4, 4, 5, 6, 7, 8, 9, 1 ] +[ 5, 5, 6, 7, 8, 9, 1, 2 ] +#SELECT * FROM t2; +% sys.t2, sys.t2, sys.t2, sys.t2, sys.t2, sys.t2, sys.t2, sys.t2 # table_name +% id, c1, c2, c3, c4, c5, c6, c7 # name +% int, double, double, double, double, double, double, double # type +% 1, 24, 24, 24, 24, 24, 24, 24 # length +[ 1, 11, 12, 13, 14, 15, 16, 17 ] +[ 2, 12, 13, 14, 15, 16, 17, 18 ] +[ 3, 13, 14, 15, 16, 17, 18, 19 ] +[ 4, 14, 15, 16, 17, 18, 19, 11 ] +[ 5, 15, 16, 17, 18, 19, 11, 12 ] +#UPDATE t1 +# SET c1 = (SELECT c1 +# FROM t2 +# WHERE t2.id = t1.id +# ) +# ,c2 = (SELECT c2 +# FROM t2 +# WHERE t2.id = t1.id +# ) +# ,c3 = (SELECT c3 +# FROM t2 +# WHERE t2.id = t1.id +# ) +# ,c4 = (SELECT c4 +# FROM t2 +# WHERE t2.id = t1.id +# ) +# ,c5 = (SELECT c5 +# FROM t2 +# WHERE t2.id = t1.id +# ) +# ,c6 = (SELECT c6 +# FROM t2 +[ 5 ] +#SELECT * FROM t1; +% sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1, sys.t1 # table_name +% id, c1, c2, c3, c4, c5, c6, c7 # name +% int, double, double, double, double, double, double, double # type +% 1, 24, 24, 24, 24, 24, 24, 24 # length +[ 1, 11, 12, 13, 14, 15, 16, 17 ] +[ 2, 12, 13, 14, 15, 16, 17, 18 ] +[ 3, 13, 14, 15, 16, 17, 18, 19 ] +[ 4, 14, 15, 16, 17, 18, 19, 11 ] +[ 5, 15, 16, 17, 18, 19, 11, 12 ] +#DROP TABLE t1; +#DROP TABLE t2; + +# 16:41:52 > +# 16:41:52 > "Done." +# 16:41:52 > + _______________________________________________ Checkin-list mailing list [email protected] http://mail.monetdb.org/mailman/listinfo/checkin-list
