Changeset: 6e71a843ec7d for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6e71a843ec7d
Added Files:
sql/test/BugTracker-2013/Tests/between.Bug-3259.sql
sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.err
sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.out
Modified Files:
sql/server/rel_optimizer.c
sql/test/BugTracker-2013/Tests/All
Branch: Feb2013
Log Message:
fixed bug 3259
The rel_push_func_down optimizer didn't push down expressions from
joins
diffs (203 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -1286,11 +1286,11 @@ can_push_func(sql_exp *e, sql_rel *rel,
if (e->flag == cmp_or || e->flag == cmp_in || e->flag ==
cmp_notin || get_cmp(e) == cmp_filter)
return 0;
- return (l->type != e_column && can_push_func(l, rel, &mustl) &&
(*must = mustl)) ||
- (!f && r->type != e_column && can_push_func(r, rel,
&mustr) && (*must = mustr)) ||
+ return ((l->type == e_column || can_push_func(l, rel, &mustl))
&& (*must = mustl)) ||
+ (!f && (r->type == e_column || can_push_func(r, rel,
&mustr)) && (*must = mustr)) ||
(f &&
- r->type != e_column && can_push_func(r, rel, &mustr) &&
- f->type != e_column && can_push_func(f, rel, &mustf) &&
(*must = (mustr || mustf)));
+ (r->type == e_column || can_push_func(r, rel, &mustr))
&&
+ (f->type == e_column || can_push_func(f, rel, &mustf))
&& (*must = (mustr || mustf)));
}
case e_convert:
return can_push_func(e->l, rel, must);
@@ -1408,7 +1408,7 @@ rel_push_func_down(int *changes, mvc *sq
append(r->exps,
ne);
else
append(l->exps,
ne);
- ne =
exp_column(sql->sa, exp_relname(ne), exp_name(ne), exp_subtype(ne), ne->card,
has_nil(ne), is_intern(ne));
+ ne =
exp_column(sql->sa, NULL, exp_name(ne), exp_subtype(ne), ne->card, has_nil(ne),
is_intern(ne));
(*changes)++;
}
e->l = ne;
@@ -1422,7 +1422,7 @@ rel_push_func_down(int *changes, mvc *sq
append(r->exps,
ne);
else
append(l->exps,
ne);
- ne =
exp_column(sql->sa, exp_relname(ne), exp_name(ne), exp_subtype(ne), ne->card,
has_nil(ne), is_intern(ne));
+ ne =
exp_column(sql->sa, NULL, exp_name(ne), exp_subtype(ne), ne->card, has_nil(ne),
is_intern(ne));
(*changes)++;
}
e->r = ne;
@@ -1437,7 +1437,7 @@ rel_push_func_down(int *changes, mvc *sq
append(r->exps, ne);
else
append(l->exps, ne);
- ne =
exp_column(sql->sa, exp_relname(ne), exp_name(ne), exp_subtype(ne), ne->card,
has_nil(ne), is_intern(ne));
+ ne =
exp_column(sql->sa, NULL, exp_name(ne), exp_subtype(ne), ne->card, has_nil(ne),
is_intern(ne));
(*changes)++;
}
e->f = ne;
diff --git a/sql/test/BugTracker-2013/Tests/All
b/sql/test/BugTracker-2013/Tests/All
--- a/sql/test/BugTracker-2013/Tests/All
+++ b/sql/test/BugTracker-2013/Tests/All
@@ -6,3 +6,4 @@ perl-undef-0.Bug-3235
crash-from-optimizer.Bug-3241
select-view-view.Bug-3245
stddev-group.Bug-3257
+between.Bug-3259
diff --git a/sql/test/BugTracker-2013/Tests/between.Bug-3259.sql
b/sql/test/BugTracker-2013/Tests/between.Bug-3259.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2013/Tests/between.Bug-3259.sql
@@ -0,0 +1,19 @@
+START TRANSACTION;
+
+CREATE TABLE treeitems (
+ "tree" CHARACTER LARGE OBJECT,
+ "subject" INTEGER,
+ "pre" BIGINT,
+ "post" BIGINT,
+ "size" BIGINT,
+ "level" TINYINT,
+ "prob" DOUBLE DEFAULT 1.0,
+ CONSTRAINT "treeitems_tree_pre_unique" UNIQUE ("tree", "pre"),
+ CONSTRAINT "treeitems_tree_post_unique" UNIQUE ("tree", "post")
+);
+
+explain
+SELECT t1.subject as id1, t2.subject as id2
+FROM treeitems t1, treeitems t2
+WHERE t2.pre between t1.pre and t1.pre + t1.size;
+ROLLBACK;
diff --git a/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.err
b/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.err
@@ -0,0 +1,35 @@
+stderr of test 'between.Bug-3259` in directory 'sql/test/BugTracker-2013`
itself:
+
+
+# 20:18:30 >
+# 20:18:30 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=31042" "--set"
"mapi_usock=/var/tmp/mtest-5226/.s.monetdb.31042" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2013"
"--set" "mal_listing=0"
+# 20:18:30 >
+
+# builtin opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# 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 mapi_open = true
+# cmdline opt mapi_port = 31042
+# cmdline opt mapi_usock = /var/tmp/mtest-5226/.s.monetdb.31042
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbpath =
/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2013
+# cmdline opt mal_listing = 0
+
+# 20:18:31 >
+# 20:18:31 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-5226" "--port=31042"
+# 20:18:31 >
+
+
+# 20:18:31 >
+# 20:18:31 > "Done."
+# 20:18:31 >
+
diff --git a/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.out
b/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2013/Tests/between.Bug-3259.stable.out
@@ -0,0 +1,80 @@
+stdout of test 'between.Bug-3259` in directory 'sql/test/BugTracker-2013`
itself:
+
+
+# 20:18:30 >
+# 20:18:30 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=31042" "--set"
"mapi_usock=/var/tmp/mtest-5226/.s.monetdb.31042" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB/mTests_sql_test_BugTracker-2013"
"--set" "mal_listing=0"
+# 20:18:30 >
+
+# MonetDB 5 server v11.15.4
+# This is an unreleased version
+# Serving database 'mTests_sql_test_BugTracker-2013', 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-2013 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:31042/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-5226/.s.monetdb.31042
+# MonetDB/GIS module loaded
+# MonetDB/JAQL module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 20:18:31 >
+# 20:18:31 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-5226" "--port=31042"
+# 20:18:31 >
+
+#START TRANSACTION;
+#CREATE TABLE treeitems (
+# "tree" CHARACTER LARGE OBJECT,
+# "subject" INTEGER,
+# "pre" BIGINT,
+# "post" BIGINT,
+# "size" BIGINT,
+# "level" TINYINT,
+# "prob" DOUBLE DEFAULT 1.0,
+# CONSTRAINT "treeitems_tree_pre_unique" UNIQUE ("tree", "pre"),
+# CONSTRAINT "treeitems_tree_post_unique" UNIQUE ("tree", "post")
+#);
+#explain
+#SELECT t1.subject as id1, t2.subject as id2
+#FROM treeitems t1, treeitems t2
+#WHERE t2.pre between t1.pre and t1.pre + t1.size;
+% .explain # table_name
+% mal # name
+% clob # type
+% 58 # length
+function user.s0_1():void;
+ X_22 := nil:bat[:oid,:int];
+ X_24 := nil:bat[:oid,:int];
+barrier X_44 := language.dataflow();
+ X_1 := sql.mvc();
+ X_2:bat[:oid,:oid] := sql.tid(X_1,"sys","treeitems");
+ X_5 := sql.bind(X_1,"sys","treeitems","pre",0);
+ X_8 := algebra.leftfetchjoin(X_2,X_5);
+ X_9 := sql.bind(X_1,"sys","treeitems","size",0);
+ X_11 := algebra.leftfetchjoin(X_2,X_9);
+ X_12:bat[:oid,:lng] := batcalc.+(X_8,X_11);
+ (X_13,r1_40) := algebra.join(X_8,X_8,X_12,true,true);
+ X_16 := sql.bind(X_1,"sys","treeitems","subject",0);
+ X_21 := algebra.leftfetchjoin(X_2,X_16);
+ X_22 := algebra.leftfetchjoin(r1_40,X_21);
+ X_24 := algebra.leftfetchjoin(X_13,X_21);
+ language.pass(X_8);
+ language.pass(X_8);
+ language.pass(X_2);
+ language.pass(X_21);
+exit X_44;
+ X_26 := sql.resultSet(2,1,X_22);
+ sql.rsColumn(X_26,"sys.t1","id1","int",32,0,X_22);
+ sql.rsColumn(X_26,"sys.t2","id2","int",32,0,X_24);
+ X_36 := io.stdout();
+ sql.exportResult(X_36,X_26);
+end s0_1;
+#ROLLBACK;
+
+# 20:18:31 >
+# 20:18:31 > "Done."
+# 20:18:31 >
+
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
