This changeset accientally contains changes to file sql/src/server/sql_parser.mx that should have gone into a seperate checking / changeset: " Refined my earlier fix "fixed (restored) order_by over union_all" (http://dev.monetdb.org/hg/MonetDB/rev/35ebc65eb74c): we now also (re-)allow ORDER BY over EXCEPT & UNION . Note. though, that (as also in Feb2010 and most probably earlier releases, MonetDB incorrectly ignores ORDER BY over UNION, EXCEPT & INTERSECT. See also Bug 2606 (http://bugs.monetdb.org/2606) " Stefan On Tue, Jul 13, 2010 at 10:16:39AM +0200, Stefan Manegold wrote:

Changeset: e399a0ffae31 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e399a0ffae31 Modified Files: sql/src/server/sql_parser.mx sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.sql sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Debian sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Windows sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.sql sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out.Debian sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out.Windows sql/src/test/BugTracker-2010/Tests/group-by_ordered_column.Bug-2564.sql sql/src/test/BugTracker-2010/Tests/group-by_ordered_column.Bug-2564.stable.out sql/src/test/BugTracker-2010/Tests/group-by_ordered_column.Bug-2564.stable.out.32bit sql/src/test/BugTracker/Tests/explain.SF-1739353-data.sql sql/src/test/BugTracker/Tests/explain.SF-1739353.stable.out sql/src/test/Tests/trace.sql sql/src/test/Tests/trace.stable.out Branch: Jun2010 Log Message:

disable parallelism (mitosis & dataflow) to avoid ambiguous EXPLAIN results

diffs (truncated from 630 to 300 lines):

diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/server/sql_parser.mx --- a/sql/src/server/sql_parser.mx Tue Jul 13 01:09:49 2010 +0200 +++ b/sql/src/server/sql_parser.mx Tue Jul 13 10:16:33 2010 +0200 @@ -2922,7 +2922,11 @@ { $$ = $1; if ($2 || $3 || $4) { - if ($1 != NULL && ($1->token == SQL_SELECT || $1->token == SQL_UNION)) { + if ($1 != NULL && + ($1->token == SQL_SELECT || + $1->token == SQL_UNION || + $1->token == SQL_EXCEPT || + $1->token == SQL_INTERSECT)) { SelectNode *s = (SelectNode*)$1;

s -> orderby = $2; diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.sql --- a/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.sql Tue Jul 13 01:09:49 2010 +0200 +++ b/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.sql Tue Jul 13 10:16:33 2010 +0200 @@ -1,3 +1,5 @@ +-- disable parallelism (mitosis & dataflow) to avoid ambiguous results +set optimizer='inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,history,multiplex,garbageCollector'; create table SF_2596114 (x double); explain select power(x,2) from SF_2596114; explain select x*x from SF_2596114; diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out --- a/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out Tue Jul 13 01:09:49 2010 +0200 +++ b/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out Tue Jul 13 10:16:33 2010 +0200 @@ -31,7 +31,6 @@ % 0 # length function user.s0_1{autoCommit=true}():void; _2 := sql.mvc(); -barrier _36 := language.dataflow(); _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); _10 := algebra.kdifference(_3,_8); @@ -42,7 +41,6 @@ _16 := bat.reverse(_15); _17 := algebra.kdifference(_14,_16); _18:bat[:oid,:dbl] := batmmath.pow(_17,2); -exit _36; _19 := sql.resultSet(1,1,_18); sql.rsColumn(_19,"sys.","power_x","double",53,0,_18); _25 := io.stdout(); @@ -54,7 +52,6 @@ % 0 # length function user.s1_1{autoCommit=true}():void; _2 := sql.mvc(); -barrier _35 := language.dataflow(); _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); _10 := algebra.kdifference(_3,_8); @@ -65,7 +62,6 @@ _16 := bat.reverse(_15); _17 := algebra.kdifference(_14,_16); _18:bat[:oid,:dbl] := batcalc.*(_17,_17); -exit _35; _19 := sql.resultSet(1,1,_18); sql.rsColumn(_19,"sys.","sql_mul_x","double",53,0,_18); _25 := io.stdout(); diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Debian --- a/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Debian Tue Jul 13 01:09:49 2010 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,74 +0,0 @@ -stdout of test 'POWER_vs_prod.SF-2596114` in directory 'src/test/BugTracker-2009` itself: - - -# 23:02:29 > -# 23:02:29 > mserver5 "--config=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/etc/monetdb5.conf" --debug=10 --set gdk_nr_threads=0 --set "monet_mod_path=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5/bin" --set "gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/var/MonetDB5/dbfarm" --set "sql_logdir=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/var/MonetDB5/sql_logs" --set mapi_open=true --set xrp c_open=true --set mapi_port=33742 --set xrpc_port=44239 --set monet_prompt= --trace "--dbname=mTests_src_test_BugTracker-2009" --set mal_listing=0 "--dbinit= include sql;" ; echo ; echo Over.. -# 23:02:29 > - -# MonetDB server v5.10.3, based on kernel v1.28.3 -# Serving database 'mTests_src_test_BugTracker-2009', using 4 threads -# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked -# Copyright (c) 1993-July 2008 CWI. -# Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved -# Visit http://monetdb.cwi.nl/ for further information -# Listening for connection requests on mapi:monetdb://rig.ins.cwi.nl:33742/ -# MonetDB/SQL module v2.28.1 loaded - -Ready. -#function user.main():void; -# clients.quit(); -#end main; - -Over.. - -# 15:46:08 > -# 15:46:08 > mclient -lsql -i -umonetdb -Pmonetdb --host=rig --port=35464 -# 15:46:08 > - -% .explain # table_name -% mal # name -% clob # type -% 0 # length -function user.s0_1{autoCommit=true}():void; - _2 := sql.mvc(); - _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); - _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); - _10 := algebra.kdifference(_3,_8); - _11 := algebra.kunion(_10,_8); - _12:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",1); - _14 := algebra.kunion(_11,_12); - _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","sf_2596114",1); - _16 := bat.reverse(_15); - _17 := algebra.kdifference(_14,_16); - _18:bat[:oid,:dbl] := batmmath.pow(_17,2); - _19 := sql.resultSet(1,1,_18); - sql.rsColumn(_19,"sys.","power_x","double",53,0,_18); - _25 := io.stdout(); - sql.exportResult(_25,_19); -end s0_1; -% .explain # table_name -% mal # name -% clob # type -% 0 # length -function user.s1_1{autoCommit=true}():void; - _2 := sql.mvc(); - _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); - _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); - _10 := algebra.kdifference(_3,_8); - _11 := algebra.kunion(_10,_8); - _12:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",1); - _14 := algebra.kunion(_11,_12); - _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","sf_2596114",1); - _16 := bat.reverse(_15); - _17 := algebra.kdifference(_14,_16); - _18:bat[:oid,:dbl] := batcalc.*(_17,_17); - _19 := sql.resultSet(1,1,_18); - sql.rsColumn(_19,"sys.","sql_mul_x","double",53,0,_18); - _25 := io.stdout(); - sql.exportResult(_25,_19); -end s1_1; - -# 23:02:30 > -# 23:02:30 > Done. -# 23:02:30 > - diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Windows --- a/sql/src/test/BugTracker-2009/Tests/POWER_vs_prod.SF-2596114.stable.out.Windows Tue Jul 13 01:09:49 2010 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,74 +0,0 @@ -stdout of test 'POWER_vs_prod.SF-2596114` in directory 'src/test/BugTracker-2009` itself: - - -# 23:02:29 > -# 23:02:29 > mserver5 "--config=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/etc/monetdb5.conf" --debug=10 --set gdk_nr_threads=0 --set "monet_mod_path=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/lib64/MonetDB5/bin" --set "gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/var/MonetDB5/dbfarm" --set "sql_logdir=/ufs/manegold/_/scratch0/Monet/Testing/Stable/prefix.--enable-strict_--disable-optimize_--enable-debug_--enable-assert/var/MonetDB5/sql_logs" --set mapi_open=true --set xrp c_open=true --set mapi_port=33742 --set xrpc_port=44239 --set monet_prompt= --trace "--dbname=mTests_src_test_BugTracker-2009" --set mal_listing=0 "--dbinit= include sql;" ; echo ; echo Over.. -# 23:02:29 > - -# MonetDB server v5.10.3, based on kernel v1.28.3 -# Serving database 'mTests_src_test_BugTracker-2009', using 4 threads -# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked -# Copyright (c) 1993-July 2008 CWI. -# Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved -# Visit http://monetdb.cwi.nl/ for further information -# Listening for connection requests on mapi:monetdb://rig.ins.cwi.nl:33742/ -# MonetDB/SQL module v2.28.1 loaded - -Ready. -#function user.main():void; -# clients.quit(); -#end main; - -Over.. - -# 15:46:08 > -# 15:46:08 > mclient -lsql -i -umonetdb -Pmonetdb --host=rig --port=35464 -# 15:46:08 > - -% .explain # table_name -% mal # name -% clob # type -% 0 # length -function user.s0_1{autoCommit=true}():void; - _2 := sql.mvc(); - _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); - _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); - _10 := algebra.kdifference(_3,_8); - _11 := algebra.kunion(_10,_8); - _12:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",1); - _14 := algebra.kunion(_11,_12); - _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","sf_2596114",1); - _16 := bat.reverse(_15); - _17 := algebra.kdifference(_14,_16); - _18:bat[:oid,:dbl] := batmmath.pow(_17,2); - _19 := sql.resultSet(1,1,_18); - sql.rsColumn(_19,"sys.","power_x","double",53,0,_18); - _25 := io.stdout(); - sql.exportResult(_25,_19); -end s0_1; -% .explain # table_name -% mal # name -% clob # type -% 0 # length -function user.s1_1{autoCommit=true}():void; - _2 := sql.mvc(); - _3:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",0); - _8:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",2); - _10 := algebra.kdifference(_3,_8); - _11 := algebra.kunion(_10,_8); - _12:bat[:oid,:dbl] := sql.bind(_2,"sys","sf_2596114","x",1); - _14 := algebra.kunion(_11,_12); - _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","sf_2596114",1); - _16 := bat.reverse(_15); - _17 := algebra.kdifference(_14,_16); - _18:bat[:oid,:dbl] := batcalc.*(_17,_17); - _19 := sql.resultSet(1,1,_18); - sql.rsColumn(_19,"sys.","sql_mul_x","double",53,0,_18); - _25 := io.stdout(); - sql.exportResult(_25,_19); -end s1_1; - -# 23:02:30 > -# 23:02:30 > Done. -# 23:02:30 > - diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.sql --- a/sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.sql Tue Jul 13 01:09:49 2010 +0200 +++ b/sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.sql Tue Jul 13 10:16:33 2010 +0200 @@ -1,3 +1,5 @@ +-- disable parallelism (mitosis & dataflow) to avoid ambiguous results +set optimizer='inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,history,multiplex,garbageCollector'; \f csv create table blabla(id integer); select '#~BeginVariableOutput~#'; diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out --- a/sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out Tue Jul 13 01:09:49 2010 +0200 +++ b/sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out Tue Jul 13 10:16:33 2010 +0200 @@ -28,8 +28,7 @@ #~BeginVariableOutput~# function user.s1_1{autoCommit=true}():void; _2 := sql.mvc(); -" sql.catalog(29\,\"sys\"\,139834807776504\,0);" -barrier _37 := language.dataflow(); +" sql.catalog(29\,\"sys\"\,140688029790296\,0);" " _7:bat[:oid\,:int] := sql.bind(_2\,\"sys\"\,\"blabla\"\,\"id\"\,0);" _10 := algebra.selectNotNil(_7); " _11:bat[:oid\,:oid] := sql.bind_dbat(_2\,\"sys\"\,\"blabla\"\,1);" @@ -43,13 +42,11 @@ _21 := calc.isnil(_20); " _23 := calc.ifthenelse(_21\,0:wrd\,_20);" " _24 := calc.!=(_19\,_23);" -exit _37; " sql.assert(_24\,\"UPDATE: UNIQUE constraint 'blabla.dada' violated\");" end s1_1; function user.s2_1{autoCommit=true}():void; _2 := sql.mvc(); -" sql.catalog(29\,\"sys\"\,139834808040824\,0);" -barrier _37 := language.dataflow(); +" sql.catalog(29\,\"sys\"\,140688030052568\,0);" " _7:bat[:oid\,:int] := sql.bind(_2\,\"sys\"\,\"blabla\"\,\"id\"\,0);" _10 := algebra.selectNotNil(_7); " _11:bat[:oid\,:oid] := sql.bind_dbat(_2\,\"sys\"\,\"blabla\"\,1);" @@ -63,15 +60,13 @@ _21 := calc.isnil(_20); " _23 := calc.ifthenelse(_21\,0:wrd\,_20);" " _24 := calc.!=(_19\,_23);" -exit _37; " sql.assert(_24\,\"UPDATE: UNIQUE constraint 'blabla.dada' violated\");" end s2_1; #~EndVariableOutput~# #~BeginVariableOutput~# function user.s1_1{autoCommit=true}():void; _2 := sql.mvc(); -" sql.catalog(29\,\"sys\"\,139834807056504\,0);" -barrier _37 := language.dataflow(); +" sql.catalog(29\,\"sys\"\,140688029790296\,0);" " _7:bat[:oid\,:int] := sql.bind(_2\,\"sys\"\,\"blabla\"\,\"id\"\,0);" _10 := algebra.selectNotNil(_7); " _11:bat[:oid\,:oid] := sql.bind_dbat(_2\,\"sys\"\,\"blabla\"\,1);" @@ -85,7 +80,6 @@ _21 := calc.isnil(_20); " _23 := calc.ifthenelse(_21\,0:wrd\,_20);" " _24 := calc.!=(_19\,_23);" -exit _37; " sql.assert(_24\,\"UPDATE: UNIQUE constraint 'blabla.dada' violated\");" end s1_1; #~EndVariableOutput~# diff -r 75116ec8f9a7 -r e399a0ffae31 sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out.Debian --- a/sql/src/test/BugTracker-2009/Tests/explain_gives_crash.SF-2741829.stable.out.Debian Tue Jul 13 01:09:49 2010 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,90 +0,0 @@ -stdout of test 'explain_gives_crash.SF-2741829` in directory 'src/test/BugTracker-2009` itself: - - -# 22:45:54 > -# 22:45:54 > mserver5 "--config=/ufs/niels/scratch/rc/Linux-x86_64/etc/monetdb5.conf" --debug=10 --set gdk_nr_threads=0 --set "monet_mod_path=/ufs/niels/scratch/rc/Linux-x86_64/lib/MonetDB5:/ufs/niels/scratch/rc/Linux-x86_64/lib/MonetDB5/lib:/ufs/niels/scratch/rc/Linux-x86_64/lib/MonetDB5/bin" --set "gdk_dbfarm=/ufs/niels/scratch/rc/Linux-x86_64/var/MonetDB5/dbfarm" --set mapi_open=true --set xrpc_open=true --set mapi_port=35519 --set xrpc_port=48894 --set monet_prompt= --trace "--dbname=mTests_src_test_BugTracker-2009" --set mal_listing=0 "--dbinit= include sql;" ; echo ; echo Over.. -# 22:45:54 > - -# MonetDB server v5.12.0, based on kernel v1.30.0 -# Serving database 'mTests_src_test_BugTracker-2009', using 4 threads -# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked -# Copyright (c) 1993-July 2008 CWI. -# Copyright (c) August 2008-2009 MonetDB B.V., all rights reserved -# Visit http://monetdb.cwi.nl/ for further information -# Listening for connection requests on mapi:monetdb://alf.ins.cwi.nl:35519/ -# MonetDB/SQL module v2.30.0 loaded - -Ready. -#function user.main():void; _______________________________________________ Checkin-list mailing list Checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |