Changeset: f6757ebce84e for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f6757ebce84e
Modified Files:
sql/src/test/BugTracker-2010/Tests/All
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.sql
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.err
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.out
Branch: Jun2010
Log Message:
added extra exhasutive test for bug 2622
On a 64-bit big-endian machines, our SQL compiler appears to create
incorrect MAL code for queries that use all three of ORDER BY, LIMIT
and OFFSET. In this case, the generated MAL plan seems to be missing
the algebra.slice() call that implements the LIMIT & OFFSET.
If only one of ORDER BY, LIMIT and OFFSET, or any combination of two
of them is used, the plan appears to be correct also on 64-bit big-endian.
I have not yet managed to locate where in the SQL compiler code this
part of the MAL plan is generated ...
diffs (truncated from 628 to 300 lines):
diff -r 55a4e63d64cb -r f6757ebce84e sql/src/test/BugTracker-2010/Tests/All
--- a/sql/src/test/BugTracker-2010/Tests/All Sat Jul 17 12:43:39 2010 +0200
+++ b/sql/src/test/BugTracker-2010/Tests/All Sat Jul 17 21:15:56 2010 +0200
@@ -34,3 +34,4 @@
second_function_existing_signature.Bug-2611
update_statement_in_function_ignored.Bug-2614
error-truncated.Bug-2615
+LIMIT_OFFSET_big-endian.Bug-2622
diff -r 55a4e63d64cb -r f6757ebce84e
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.sql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.sql
Sat Jul 17 21:15:56 2010 +0200
@@ -0,0 +1,41 @@
+set optimizer = 'minimal_pipe';
+create table oblo (a int);
+insert into oblo values (4);
+insert into oblo values (3);
+insert into oblo values (2);
+insert into oblo values (1);
+
+ PLAN select * from oblo;
+ PLAN select * from oblo OFFSET 2;
+ PLAN select * from oblo LIMIT 2;
+ PLAN select * from oblo LIMIT 1 OFFSET 2;
+ PLAN select * from oblo LIMIT 2 OFFSET 1;
+ PLAN select * from oblo ORDER BY a;
+ PLAN select * from oblo ORDER BY a OFFSET 2;
+ PLAN select * from oblo ORDER BY a LIMIT 2;
+ PLAN select * from oblo ORDER BY a LIMIT 2 OFFSET 1;
+ PLAN select * from oblo ORDER BY a LIMIT 1 OFFSET 2;
+
+EXPLAIN select * from oblo;
+EXPLAIN select * from oblo OFFSET 2;
+EXPLAIN select * from oblo LIMIT 2;
+EXPLAIN select * from oblo LIMIT 1 OFFSET 2;
+EXPLAIN select * from oblo LIMIT 2 OFFSET 1;
+EXPLAIN select * from oblo ORDER BY a;
+EXPLAIN select * from oblo ORDER BY a OFFSET 2;
+EXPLAIN select * from oblo ORDER BY a LIMIT 2;
+EXPLAIN select * from oblo ORDER BY a LIMIT 2 OFFSET 1;
+EXPLAIN select * from oblo ORDER BY a LIMIT 1 OFFSET 2;
+
+ select * from oblo;
+ select * from oblo OFFSET 2;
+ select * from oblo LIMIT 2;
+ select * from oblo LIMIT 1 OFFSET 2;
+ select * from oblo LIMIT 2 OFFSET 1;
+ select * from oblo ORDER BY a;
+ select * from oblo ORDER BY a OFFSET 2;
+ select * from oblo ORDER BY a LIMIT 2;
+ select * from oblo ORDER BY a LIMIT 2 OFFSET 1;
+ select * from oblo ORDER BY a LIMIT 1 OFFSET 2;
+
+drop table oblo;
diff -r 55a4e63d64cb -r f6757ebce84e
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.err
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.err
Sat Jul 17 21:15:56 2010 +0200
@@ -0,0 +1,78 @@
+stderr of test 'LIMIT_OFFSET_big-endian.Bug-2622` in directory
'src/test/BugTracker-2010` itself:
+
+
+# 21:05:24 >
+# 21:05:24 > mserver5
"--config=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf"
--debug=10 --set gdk_nr_threads=0 --set
"monet_mod_path=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin"
--set
"gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm"
--set mapi_open=true --set xrpc_open=true --set mapi_port=38140 --set
xrpc_port=48203 --set monet_prompt= --set mal_listing=2 --trace
"--dbname=mTests_src_test_BugTracker-2010" --set mal_listing=0 ; echo ; echo
Over..
+# 21:05:24 >
+
+# builtin opt gdk_arch = 64bitx86_64-unknown-linux-gnu
+# builtin opt gdk_version = 1.38.3
+# builtin opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# builtin opt exec_prefix = ${prefix}
+# builtin opt gdk_dbname = demo
+# builtin opt gdk_dbfarm = ${prefix}/var/MonetDB/dbfarm
+# builtin opt gdk_debug = 0
+# builtin opt gdk_alloc_map = no
+# builtin opt gdk_vmtrim = yes
+# builtin opt monet_admin = adm
+# builtin opt monet_prompt = >
+# builtin opt monet_welcome = yes
+# builtin opt monet_mod_path = ${exec_prefix}/lib64/MonetDB
+# builtin opt monet_daemon = no
+# builtin opt host = localhost
+# builtin opt mapi_port = 50000
+# builtin opt mapi_clients = 2
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_debug = 0
+# builtin opt standoff_ns =
+# builtin opt standoff_start = start
+# builtin opt standoff_end = end
+# config opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# config opt config = ${prefix}/etc/monetdb5.conf
+# config opt prefix =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert
+# config opt exec_prefix = ${prefix}
+# config opt gdk_dbfarm = ${prefix}/var/MonetDB5/dbfarm
+# config opt monet_mod_path =
${exec_prefix}/lib64/MonetDB5:${exec_prefix}/lib64/MonetDB5/lib:${exec_prefix}/lib64/MonetDB5/bin
+# config opt mero_pidfile = ${prefix}/var/run/MonetDB/merovingian.pid
+# config opt mero_controlport = 50001
+# config opt sql_optimizer = default_pipe
+# config opt minimal_pipe = inline,remap,deadcode,multiplex,garbageCollector
+# config opt default_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt nov2009_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt replication_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector
+# config opt accumulator_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,accumulators,dataflow,history,multiplex,garbageCollector
+# config opt recycler_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector
+# config opt cracker_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,selcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt sidcrack_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,sidcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt datacell_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,datacell,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt octopus_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,constants,commonTerms,joinPath,octopus,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt mapreduce_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mapreduce,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt datacyclotron_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,datacyclotron,mergetable,deadcode,constants,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector
+# config opt derive_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,derivePath,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt dictionary_pipe =
inline,remap,dictionary,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector
+# config opt compression_pipe =
inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,compression,dataflow,history,multiplex,garbageCollector
+# cmdline opt config =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt monet_mod_path =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin
+# cmdline opt gdk_dbfarm =
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm
+# cmdline opt mapi_open = true
+# cmdline opt xrpc_open = true
+# cmdline opt mapi_port = 38140
+# cmdline opt xrpc_port = 48203
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_src_test_BugTracker-2010
+# cmdline opt mal_listing = 0
+#warning: please don't forget to set your vault key!
+#(see
/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf)
+
+# 21:05:24 >
+# 21:05:24 > mclient -lsql -ftest -i -e --host=rig --port=38140
+# 21:05:24 >
+
+
+# 21:05:24 >
+# 21:05:24 > Done.
+# 21:05:24 >
+
diff -r 55a4e63d64cb -r f6757ebce84e
sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.out
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/sql/src/test/BugTracker-2010/Tests/LIMIT_OFFSET_big-endian.Bug-2622.stable.out
Sat Jul 17 21:15:56 2010 +0200
@@ -0,0 +1,489 @@
+stdout of test 'LIMIT_OFFSET_big-endian.Bug-2622` in directory
'src/test/BugTracker-2010` itself:
+
+
+# 21:05:24 >
+# 21:05:24 > mserver5
"--config=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/etc/monetdb5.conf"
--debug=10 --set gdk_nr_threads=0 --set
"monet_mod_path=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/lib:/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/lib64/MonetDB5/bin"
--set
"gdk_dbfarm=/ufs/manegold/_/scratch0/Monet/HG/Jun2010/prefix.--enable-strict_--disable-debug_--enable-optimize_--enable-assert/var/MonetDB5/dbfarm"
--set mapi_open=true --set xrpc_open=true --set mapi_port=38140 --set
xrpc_port=48203 --set monet_prompt= --set mal_listing=2 --trace
"--dbname=mTests_src_test_BugTracker-2010" --set mal_listing=0 ; echo ; echo
Over..
+# 21:05:24 >
+
+# MonetDB server v5.20.3, based on kernel v1.38.3
+# Serving database 'mTests_src_test_BugTracker-2010', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 7.751 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2010 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:38140/
+# MonetDB/SQL module v2.38.3 loaded
+# MonetDB/GIS module v0.18.3 loaded
+
+Ready.
+
+Over..
+
+# 21:05:24 >
+# 21:05:24 > mclient -lsql -ftest -i -e --host=rig --port=38140
+# 21:05:24 >
+
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+
+#PLAN select * from oblo;
+% .plan # table_name
+% rel # name
+% clob # type
+% 48 # length
+project (
+| table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+) [ oblo.a ]
+#PLAN select * from oblo OFFSET 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ]
+) [ 2 ]
+#PLAN select * from oblo LIMIT 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ]
+) [ 2 ]
+#PLAN select * from oblo LIMIT 1 OFFSET 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ]
+) [ 1, 2 ]
+#PLAN select * from oblo LIMIT 2 OFFSET 1;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ]
+) [ 2, 1 ]
+#PLAN select * from oblo ORDER BY a;
+% .plan # table_name
+% rel # name
+% clob # type
+% 48 # length
+project (
+| table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+) [ oblo.a ASC ] [ oblo.a ]
+#PLAN select * from oblo ORDER BY a OFFSET 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ASC ] [ oblo.a ]
+) [ 2 ]
+#PLAN select * from oblo ORDER BY a LIMIT 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ASC ] [ oblo.a ]
+) [ 2 ]
+#PLAN select * from oblo ORDER BY a LIMIT 2 OFFSET 1;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ASC ] [ oblo.a ]
+) [ 2, 1 ]
+#PLAN select * from oblo ORDER BY a LIMIT 1 OFFSET 2;
+% .plan # table_name
+% rel # name
+% clob # type
+% 50 # length
+top N (
+| project (
+| | table(sys.oblo) [ oblo.a, oblo.%TID% NOT NULL ]
+| ) [ oblo.a ASC ] [ oblo.a ]
+) [ 1, 2 ]
+
+#EXPLAIN select * from oblo;
+% .explain # table_name
+% mal # name
+% clob # type
+% 0 # length
+function user.s1_1{autoCommit=true}():void;
+ _2 := sql.mvc();
+ _3:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",0);
+ _8:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",2);
+ _10 := algebra.kdifference(_3,_8);
+ _11 := algebra.kunion(_10,_8);
+ _12:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",1);
+ _14 := algebra.kunion(_11,_12);
+ _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","oblo",1);
+ _16 := bat.reverse(_15);
+ _17 := algebra.kdifference(_14,_16);
+ _18 := sql.resultSet(1,1,_17);
+ sql.rsColumn(_18,"sys.oblo","a","int",32,0,_17);
+ _23 := io.stdout();
+ sql.exportResult(_23,_18);
+end s1_1;
+#EXPLAIN select * from oblo OFFSET 2;
+% .explain # table_name
+% mal # name
+% clob # type
+% 0 # length
+function user.s2_1{autoCommit=true}():void;
+ _2 := sql.mvc();
+ _3:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",0);
+ _8:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",2);
+ _10 := algebra.kdifference(_3,_8);
+ _11 := algebra.kunion(_10,_8);
+ _12:bat[:oid,:int] := sql.bind(_2,"sys","oblo","a",1);
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
