[Commits] Rev 2858: Fixed LP bug #675516. in file:///home/igor/maria/maria-5.3-mwl128-bug675516/

Igor Babaev igor at askmonty.org
Tue Nov 16 07:02:07 EET 2010


At file:///home/igor/maria/maria-5.3-mwl128-bug675516/

------------------------------------------------------------
revno: 2858
revision-id: igor at askmonty.org-20101116050207-343i6ed2k3ixy03f
parent: igor at askmonty.org-20101115073825-308h4tlasze5mw8w
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mwl128-bug675516
timestamp: Mon 2010-11-15 21:02:07 -0800
message:
  Fixed LP bug #675516.
  When pushing the condition for a table in the function
  JOIN_TAB::make_scan_filter the optimizer must not push
  conditions from WHERE close into the inner tables of
  outer joins.
-------------- next part --------------
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2010-11-15 07:38:25 +0000
+++ b/mysql-test/r/join_cache.result	2010-11-16 05:02:07 +0000
@@ -5921,4 +5921,52 @@
 SET SESSION optimizer_switch = 'outer_join_with_cache=off';
 SET SESSION join_cache_level = DEFAULT;
 DROP TABLE t1,t2,t3,t4,t5;
+#
+# Bug #675516: nested outer join with 3 tables in the nest
+#             using BNL + BNLH 
+#
+CREATE TABLE t1 (a1 int, b1 int, c1 int) ;
+INSERT INTO t1 VALUES (7,8,0), (6,4,0);
+CREATE TABLE t2 (a2 int) ;
+INSERT INTO t2 VALUES (5);
+CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ;
+INSERT INTO t3 VALUES (2,5,0);
+CREATE TABLE t4 (a4 int, b4 int, c4 int) ;
+INSERT INTO t4 VALUES (7,8,0);
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT * FROM
+t1 LEFT JOIN 
+((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+WHERE t3.a3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where; Using join buffer (incremental, BNLH join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)
+SELECT * FROM
+t1 LEFT JOIN 
+((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+WHERE t3.a3 IS NULL;
+a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT * FROM
+t1 LEFT JOIN 
+((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+WHERE t3.a3 IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t2.a2	1	Using where
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	1	Using where
+SELECT * FROM
+t1 LEFT JOIN 
+((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+WHERE t3.a3 IS NULL;
+a1	b1	c1	a2	a3	b3	c3	a4	b4	c4
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+DROP TABLE t1,t2,t3,t4;
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2010-11-15 07:38:25 +0000
+++ b/mysql-test/t/join_cache.test	2010-11-16 05:02:07 +0000
@@ -2579,5 +2579,51 @@
 
 DROP TABLE t1,t2,t3,t4,t5;
 
+--echo #
+--echo # Bug #675516: nested outer join with 3 tables in the nest
+--echo #             using BNL + BNLH 
+--echo #
+
+CREATE TABLE t1 (a1 int, b1 int, c1 int) ;
+INSERT INTO t1 VALUES (7,8,0), (6,4,0);
+
+CREATE TABLE t2 (a2 int) ;
+INSERT INTO t2 VALUES (5);
+
+CREATE TABLE t3 (a3 int, b3 int, c3 int, PRIMARY KEY (b3)) ;
+INSERT INTO t3 VALUES (2,5,0);
+
+CREATE TABLE t4 (a4 int, b4 int, c4 int) ;
+INSERT INTO t4 VALUES (7,8,0);
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=on';
+
+SET SESSION join_cache_level = 4;
+EXPLAIN
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+
+SET SESSION join_cache_level = 0;
+EXPLAIN
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+SELECT * FROM
+  t1 LEFT JOIN 
+  ((t2 JOIN t3 ON t2.a2 = t3.b3) JOIN t4 ON t4.b4 <> 0) ON t1.c1 = t3.c3
+   WHERE t3.a3 IS NULL;
+
+SET SESSION optimizer_switch = 'outer_join_with_cache=off';
+SET SESSION join_cache_level = DEFAULT;
+
+DROP TABLE t1,t2,t3,t4;
+
 # this must be the last command in the file
 set @@optimizer_switch=@save_optimizer_switch;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-11-15 07:38:25 +0000
+++ b/sql/sql_select.cc	2010-11-16 05:02:07 +0000
@@ -6010,9 +6010,9 @@
 int JOIN_TAB::make_scan_filter()
 {
   COND *tmp;
-  DBUG_ENTER("make_join_select");
+  DBUG_ENTER("make_scan_filter");
 
-  Item *cond= is_last_inner_table() ?
+  Item *cond= is_inner_table_of_outer_join() ?
                 *get_first_inner_table()->on_expr_ref : join->conds;
 
   if (cond &&



More information about the commits mailing list