Changeset: 9f1cd8ded319 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9f1cd8ded319
Modified Files:
sql/server/rel_optimizer.c
sql/test/BugTracker-2012/Tests/All
sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.sql
sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.err
sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.out
Branch: Dec2011
Log Message:
fixed bug 3041. Make sure we push a select realy under a left join
not in a left join.
diffs (244 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
@@ -934,6 +934,7 @@ order_joins(mvc *sql, list *rels, list *
}
if (list_length(exps)) { /* more expressions (add selects) */
node *n;
+ set_processed(top);
top = rel_select(sql->sa, top, NULL);
for(n=exps->h; n; n = n->next) {
sql_exp *e = n->data;
@@ -2568,7 +2569,9 @@ rel_push_select_down(int *changes, mvc *
return rel;
/* introduce selects under the join (if needed) */
- if (!is_select(jl->op))
+ set_processed(jl);
+ set_processed(jr);
+ if (!is_select(jl->op))
r->l = jl = rel_select(sql->sa, jl, NULL);
if (!is_select(jr->op))
r->r = jr = rel_select(sql->sa, jr, NULL);
@@ -2629,6 +2632,7 @@ rel_push_select_down(int *changes, mvc *
rel->exps = new_exp_list(sql->sa);
pl = r->l;
/* introduce selects under the project (if needed) */
+ set_processed(pl);
if (!is_select(pl->op))
r->l = pl = rel_select(sql->sa, pl, NULL);
@@ -3234,6 +3238,8 @@ rel_push_select_down_union(int *changes,
}
/* introduce selects under the set (if needed) */
+ set_processed(ul);
+ set_processed(ur);
ul = rel_select(sql->sa, ul, NULL);
ur = rel_select(sql->sa, ur, NULL);
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
@@ -11,3 +11,4 @@ outerjoin_predicate.Bug-2964
innerjoin_subquery.Bug-3034
rangejoin.Bug-3017
outerjoin_select.Bug-2946
+leftjoin-Bug.3041
diff --git a/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.sql
b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.sql
@@ -0,0 +1,48 @@
+CREATE TABLE emp (
+ empno int,
+ ename varchar(10),
+ job varchar(9),
+ mgr int,
+ hiredate date,
+ sal decimal(7,2),
+ comm decimal(7,2),
+ deptno int
+ );
+
+ INSERT INTO emp VALUES
+(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
+ INSERT INTO emp VALUES
+(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30);
+ INSERT INTO emp VALUES
+(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30);
+ INSERT INTO emp VALUES
+(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20);
+ INSERT INTO emp VALUES
+(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30);
+ INSERT INTO emp VALUES
+(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30);
+ INSERT INTO emp VALUES
+(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10);
+ INSERT INTO emp VALUES
+(7788,'SCOTT','ANALYST',7566,'1982-12-09',3000.00,NULL,20);
+ INSERT INTO emp VALUES
+(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10);
+ INSERT INTO emp VALUES
+(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30);
+ INSERT INTO emp VALUES
+(7876,'ADAMS','CLERK',7788,'1983-01-12',1100.00,NULL,20);
+ INSERT INTO emp VALUES
+(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30);
+ INSERT INTO emp VALUES
+(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20);
+ INSERT INTO emp VALUES
+(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
+
+ SELECT e.job, e.ename, ep.ename, epp.ename
+ FROM emp e
+ LEFT JOIN (SELECT * FROM emp) ep ON (e.mgr = ep.empno)
+ LEFT JOIN emp epp ON (ep.mgr = epp.empno)
+ WHERE e.job = 'SALESMAN'
+ ORDER BY 1,2,3,4;
+
+drop table emp;
diff --git a/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.err
b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.err
@@ -0,0 +1,37 @@
+stderr of test 'leftjoin-Bug.3041` in directory 'test/BugTracker-2012` itself:
+
+
+# 10:29:01 >
+# 10:29:01 > "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=39002" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_BugTracker-2012"
"--set" "mal_listing=0"
+# 10:29:01 >
+
+# 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 = 39002
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_test_BugTracker-2012
+# cmdline opt mal_listing = 0
+
+# 10:29:01 >
+# 10:29:01 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=39002"
+# 10:29:01 >
+
+
+# 10:29:02 >
+# 10:29:02 > "Done."
+# 10:29:02 >
+
diff --git a/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.out
b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2012/Tests/leftjoin-Bug.3041.stable.out
@@ -0,0 +1,97 @@
+stdout of test 'leftjoin-Bug.3041` in directory 'test/BugTracker-2012` itself:
+
+
+# 10:29:01 >
+# 10:29:01 > "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=39002" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_BugTracker-2012"
"--set" "mal_listing=0"
+# 10:29:01 >
+
+# MonetDB 5 server v11.7.6
+# 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:39002/
+# MonetDB/GIS module loaded
+# MonetDB/SQL module loaded
+
+Ready.
+
+# 10:29:01 >
+# 10:29:01 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=39002"
+# 10:29:01 >
+
+#CREATE TABLE emp (
+# empno int,
+# ename varchar(10),
+# job varchar(9),
+# mgr int,
+# hiredate date,
+# sal decimal(7,2),
+# comm decimal(7,2),
+# deptno int
+# );
+#INSERT INTO emp VALUES
+#(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7788,'SCOTT','ANALYST',7566,'1982-12-09',3000.00,NULL,20);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7876,'ADAMS','CLERK',7788,'1983-01-12',1100.00,NULL,20);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20);
+[ 1 ]
+#INSERT INTO emp VALUES
+#(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);
+[ 1 ]
+#SELECT e.job, e.ename, ep.ename, epp.ename
+# FROM emp e
+# LEFT JOIN (SELECT * FROM emp) ep ON (e.mgr = ep.empno)
+# LEFT JOIN emp epp ON (ep.mgr = epp.empno)
+# WHERE e.job = 'SALESMAN'
+# ORDER BY 1,2,3,4;
+% .e, .e, .ep, .epp # table_name
+% job, ename, ename, ename # name
+% varchar, varchar, varchar, varchar # type
+% 8, 6, 5, 4 # length
+[ "SALESMAN", "ALLEN", "BLAKE", "KING" ]
+[ "SALESMAN", "MARTIN", "BLAKE", "KING" ]
+[ "SALESMAN", "TURNER", "BLAKE", "KING" ]
+[ "SALESMAN", "WARD", "BLAKE", "KING" ]
+#drop table emp;
+
+# 10:29:02 >
+# 10:29:02 > "Done."
+# 10:29:02 >
+
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
