Changeset: 7e4c8b3f36c6 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7e4c8b3f36c6
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2012/Tests/All
sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.sql
sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.err
sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.out
Branch: Dec2011
Log Message:
in case of correlated groupby we need to add the outer projection's
to the groupby and aggr expressions.
Fixes bug 3011.
diffs (278 lines):
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4740,6 +4740,8 @@ rel_select_exp(mvc *sql, sql_rel *rel, s
if (!gbe)
return NULL;
+ if (outer && pre_prj)
+ list_merge(gbe, pre_prj, (fdup)NULL);
rel = rel_groupby(sql->sa, rel, gbe);
aggr = 1;
}
diff --git a/sql/test/BugTracker-2012/Tests/All
b/sql/test/BugTracker-2012/Tests/All
--- a/sql/test/BugTracker-2012/Tests/All
+++ b/sql/test/BugTracker-2012/Tests/All
@@ -14,3 +14,4 @@ outerjoin_select.Bug-2946
leftjoin-Bug.3041
with_in_derived_table.Bug-3043
boolean_coersion.Bug-3012
+correlated_groupby_in_selection.Bug-3011
diff --git
a/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.sql
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.sql
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.sql
@@ -0,0 +1,83 @@
+
+CREATE TABLE "sys"."test" (
+ "version" DECIMAL(2) NOT NULL,
+ "implicit" BOOLEAN NOT NULL,
+ "dataownercode" VARCHAR(10) NOT NULL,
+ "lineplanningnumber" VARCHAR(10) NOT NULL,
+ "journeypatterncode" VARCHAR(10) NOT NULL,
+ "timinglinkorder" DECIMAL(3) NOT NULL,
+ "userstopcodebegin" VARCHAR(10) NOT NULL,
+ "userstopcodeend" VARCHAR(10) NOT NULL,
+ "confinrelcode" VARCHAR(10) NOT NULL,
+ "destcode" VARCHAR(10) NOT NULL,
+ "istimingstop" BOOLEAN NOT NULL,
+ "displaypublicline" VARCHAR(4),
+ "productformulatype" VARCHAR(4)
+);
+
+
+select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+userstopcodebegin, istimingstop,
+ (select count(*) from test as counter where
+ counter.dataownercode = test.dataownercode and
+ counter.lineplanningnumber = test.lineplanningnumber and
+ counter.journeypatterncode = test.journeypatterncode and
+ counter.timinglinkorder < test.timinglinkorder and
+ counter.userstopcodebegin = test.userstopcodebegin
+ group by dataownercode, lineplanningnumber, journeypatterncode) as
+passagesequencenumber
+from test order by dataownercode, lineplanningnumber, journeypatterncode,
+timinglinkorder limit 20;
+
+select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+userstopcodebegin, istimingstop,
+ (select count(*) from test as counter where
+ counter.dataownercode = test.dataownercode and
+ counter.lineplanningnumber = test.lineplanningnumber and
+ counter.journeypatterncode = test.journeypatterncode and
+ counter.timinglinkorder < test.timinglinkorder and
+ counter.userstopcodebegin = test.userstopcodebegin) as
+passagesequencenumber
+from test order by dataownercode, lineplanningnumber, journeypatterncode,
+timinglinkorder limit 20;
+
+
+COPY 10 RECORDS INTO "test" FROM STDIN USING DELIMITERS ',', '\n';
+1,true,CXX,A001,0,0,40000010,40004015,santro,A00100998,true,,34
+1,true,CXX,A001,0,1,40004015,40004021,santro,A00100998,false,,34
+1,true,CXX,A001,0,2,40004021,40002570,santro,A00100998,false,,34
+1,true,CXX,A001,0,3,40002570,40002550,santro,A00100998,false,,34
+1,true,CXX,A001,0,4,40002550,40002590,santro,A00100998,false,,34
+1,true,CXX,A001,0,5,40002590,40002610,santro,A00100998,false,,34
+1,true,CXX,A001,0,6,40002610,40002630,santro,A00100998,false,,34
+1,true,CXX,A001,0,7,40002630,40002690,santro,A00100998,false,,34
+1,true,CXX,A001,0,8,40002690,40002770,santro,A00100998,false,,34
+1,true,CXX,A001,0,9,40002770,40009591,santro,A00100998,false,,34
+
+
+select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+userstopcodebegin, istimingstop,
+ (select count(*) from test as counter where
+ counter.dataownercode = test.dataownercode and
+ counter.lineplanningnumber = test.lineplanningnumber and
+ counter.journeypatterncode = test.journeypatterncode and
+ counter.timinglinkorder < test.timinglinkorder and
+ counter.userstopcodebegin = test.userstopcodebegin
+ group by dataownercode, lineplanningnumber, journeypatterncode) as
+passagesequencenumber
+from test order by dataownercode, lineplanningnumber, journeypatterncode,
+timinglinkorder limit 20;
+
+select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+userstopcodebegin, istimingstop,
+ (select count(*) from test as counter where
+ counter.dataownercode = test.dataownercode and
+ counter.lineplanningnumber = test.lineplanningnumber and
+ counter.journeypatterncode = test.journeypatterncode and
+ counter.timinglinkorder < test.timinglinkorder and
+ counter.userstopcodebegin = test.userstopcodebegin) as
+passagesequencenumber
+from test order by dataownercode, lineplanningnumber, journeypatterncode,
+timinglinkorder limit 20;
+
+DROP table test;
diff --git
a/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.err
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.err
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'correlated_groupby_in_selection.Bug-3011` in directory
'test/BugTracker-2012` itself:
+
+
+# 10:56:32 >
+# 10:56:32 > "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=38171" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_BugTracker-2012"
"--set" "mal_listing=0"
+# 10:56:32 >
+
+# 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 = 38171
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_test_BugTracker-2012
+# cmdline opt mal_listing = 0
+
+# 10:56:33 >
+# 10:56:33 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=38171"
+# 10:56:33 >
+
+
+# 10:56:33 >
+# 10:56:33 > "Done."
+# 10:56:33 >
+
diff --git
a/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.out
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.out
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2012/Tests/correlated_groupby_in_selection.Bug-3011.stable.out
@@ -0,0 +1,123 @@
+stdout of test 'correlated_groupby_in_selection.Bug-3011` in directory
'test/BugTracker-2012` itself:
+
+
+# 10:56:32 >
+# 10:56:32 > "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=38171" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_BugTracker-2012"
"--set" "mal_listing=0"
+# 10:56:32 >
+
+# MonetDB 5 server v11.7.8
+# This is an unreleased version
+# Serving database 'mTests_test_BugTracker-2012', using 4 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically
linked
+# Found 3.779 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:38171/
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 10:56:33 >
+# 10:56:33 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=38171"
+# 10:56:33 >
+
+#CREATE TABLE "sys"."test" (
+# "version" DECIMAL(2) NOT NULL,
+# "implicit" BOOLEAN NOT NULL,
+# "dataownercode" VARCHAR(10) NOT NULL,
+# "lineplanningnumber" VARCHAR(10) NOT NULL,
+# "journeypatterncode" VARCHAR(10) NOT NULL,
+# "timinglinkorder" DECIMAL(3) NOT NULL,
+# "userstopcodebegin" VARCHAR(10) NOT NULL,
+# "userstopcodeend" VARCHAR(10) NOT NULL,
+# "confinrelcode" VARCHAR(10) NOT NULL,
+# "destcode" VARCHAR(10) NOT NULL,
+#select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+#userstopcodebegin, istimingstop,
+# (select count(*) from test as counter where
+# counter.dataownercode = test.dataownercode and
+# counter.lineplanningnumber = test.lineplanningnumber and
+# counter.journeypatterncode = test.journeypatterncode and
+# counter.timinglinkorder < test.timinglinkorder and
+# counter.userstopcodebegin = test.userstopcodebegin
+# group by dataownercode, lineplanningnumber, journeypatterncode) as
+% .test, .test, .test, .test, .test, .test, .test # table_name
+% dataownercode, lineplanningnumber, journeypatterncode,
timinglinkorder, userstopcodebegin, istimingstop,
passagesequencenumber # name
+% varchar, varchar, varchar, decimal, varchar,
boolean, wrd # type
+% 0, 0, 0, 4, 0, 5, 1 # length
+#select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+#userstopcodebegin, istimingstop,
+# (select count(*) from test as counter where
+# counter.dataownercode = test.dataownercode and
+# counter.lineplanningnumber = test.lineplanningnumber and
+# counter.journeypatterncode = test.journeypatterncode and
+# counter.timinglinkorder < test.timinglinkorder and
+# counter.userstopcodebegin = test.userstopcodebegin) as
+#passagesequencenumber
+% .test, .test, .test, .test, .test, .test, .counter # table_name
+% dataownercode, lineplanningnumber, journeypatterncode,
timinglinkorder, userstopcodebegin, istimingstop,
passagesequencenumber # name
+% varchar, varchar, varchar, decimal, varchar,
boolean, wrd # type
+% 0, 0, 0, 4, 0, 5, 1 # length
+#COPY 10 RECORDS INTO "test" FROM STDIN USING DELIMITERS ',', '\n';
+#1,true,CXX,A001,0,0,40000010,40004015,santro,A00100998,true,,34
+#1,true,CXX,A001,0,1,40004015,40004021,santro,A00100998,false,,34
+#1,true,CXX,A001,0,2,40004021,40002570,santro,A00100998,false,,34
+#1,true,CXX,A001,0,3,40002570,40002550,santro,A00100998,false,,34
+#1,true,CXX,A001,0,4,40002550,40002590,santro,A00100998,false,,34
+#1,true,CXX,A001,0,5,40002590,40002610,santro,A00100998,false,,34
+#1,true,CXX,A001,0,6,40002610,40002630,santro,A00100998,false,,34
+[ 10 ]
+#select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+#userstopcodebegin, istimingstop,
+# (select count(*) from test as counter where
+# counter.dataownercode = test.dataownercode and
+# counter.lineplanningnumber = test.lineplanningnumber and
+# counter.journeypatterncode = test.journeypatterncode and
+# counter.timinglinkorder < test.timinglinkorder and
+# counter.userstopcodebegin = test.userstopcodebegin
+# group by dataownercode, lineplanningnumber, journeypatterncode) as
+% .test, .test, .test, .test, .test, .test, .test # table_name
+% dataownercode, lineplanningnumber, journeypatterncode,
timinglinkorder, userstopcodebegin, istimingstop,
passagesequencenumber # name
+% varchar, varchar, varchar, decimal, varchar,
boolean, wrd # type
+% 3, 4, 1, 4, 8, 5, 1 # length
+[ "CXX", "A001", "0", 0, "40000010", true, 1 ]
+[ "CXX", "A001", "0", 1, "40004015", false, 1 ]
+[ "CXX", "A001", "0", 2, "40004021", false, 1 ]
+[ "CXX", "A001", "0", 3, "40002570", false, 1 ]
+[ "CXX", "A001", "0", 4, "40002550", false, 1 ]
+[ "CXX", "A001", "0", 5, "40002590", false, 1 ]
+[ "CXX", "A001", "0", 6, "40002610", false, 1 ]
+[ "CXX", "A001", "0", 7, "40002630", false, 1 ]
+[ "CXX", "A001", "0", 8, "40002690", false, 1 ]
+[ "CXX", "A001", "0", 9, "40002770", false, 1 ]
+#select dataownercode, lineplanningnumber, journeypatterncode, timinglinkorder,
+#userstopcodebegin, istimingstop,
+# (select count(*) from test as counter where
+# counter.dataownercode = test.dataownercode and
+# counter.lineplanningnumber = test.lineplanningnumber and
+# counter.journeypatterncode = test.journeypatterncode and
+# counter.timinglinkorder < test.timinglinkorder and
+# counter.userstopcodebegin = test.userstopcodebegin) as
+#passagesequencenumber
+% .test, .test, .test, .test, .test, .test, .counter # table_name
+% dataownercode, lineplanningnumber, journeypatterncode,
timinglinkorder, userstopcodebegin, istimingstop,
passagesequencenumber # name
+% varchar, varchar, varchar, decimal, varchar,
boolean, wrd # type
+% 3, 4, 1, 4, 8, 5, 1 # length
+[ "CXX", "A001", "0", 0, "40000010", true, 1 ]
+[ "CXX", "A001", "0", 1, "40004015", false, 1 ]
+[ "CXX", "A001", "0", 2, "40004021", false, 1 ]
+[ "CXX", "A001", "0", 3, "40002570", false, 1 ]
+[ "CXX", "A001", "0", 4, "40002550", false, 1 ]
+[ "CXX", "A001", "0", 5, "40002590", false, 1 ]
+[ "CXX", "A001", "0", 6, "40002610", false, 1 ]
+[ "CXX", "A001", "0", 7, "40002630", false, 1 ]
+[ "CXX", "A001", "0", 8, "40002690", false, 1 ]
+[ "CXX", "A001", "0", 9, "40002770", false, 1 ]
+#DROP table test;
+
+# 10:56:33 >
+# 10:56:33 > "Done."
+# 10:56:33 >
+
_______________________________________________
Checkin-list mailing list
Checkin-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/checkin-list
