bzr push into mysql-6.0-bka-preview branch (igor:2662 to 2663) Bug#40192, Bug#40317

[Igor Babaev]

 2663 Igor Babaev	2008-10-24
      Fixed bug #40317.
      The condition pushed from constant ON expressions of an outer join
      mistakingly was not set in the select->cond fields of the last inner 
      table if there were no other conditions pushed to this table.
      This led to wrong results returned for queries with outer join on 
      constant conditions equal to FALSE.
      Also the comment that a pushdown condition had been used for the last
      inner table was missing in the output of the EXPLAIN command for
      such a query.
      Wrong results could appear only when join buffer was used for an outer
      join because only in this case the value of select->cond was
      used when condition pushdown operation was performed. If no join
      buffer was employed to join the last inner table the value of
      the select_cond field was used for this purposes. This field always
      was set correctly. 
        
modified:
  mysql-test/r/join_cache.result
  mysql-test/r/join_outer.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

 2662 Igor Babaev	2008-10-23
      Fixed bug #40192.
      When Blocked Nested Loops (BNL) algorithm is used for 
      for a regular join each record of the joined table first
      is checked by the condition pushed to this table. Only
      after this the matches for this record in the join buffer
      are looked for. 
      This does not work for an outer join operation as the 
      condition pushed to an inner table may include predicates
      from the where clause which should be applied only after
      a match has been found.
modified:
  mysql-test/r/join_cache.result
  mysql-test/t/join_cache.test
  sql/sql_select.cc

=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result	2008-10-23 18:33:52 +0000
+++ b/mysql-test/r/join_cache.result	2008-10-25 00:35:49 +0000
@@ -3316,3 +3316,30 @@ a	b	a	c
 2	20	NULL	NULL
 set join_cache_level=default;
 drop table t1, t2;
+#
+# Bug #40317: outer join with with constant on expression equal to FALSE
+#
+create table t1 (a int);
+insert into t1 values (30), (40), (20);
+create table t2 (b int);
+insert into t2 values (200), (100);
+set join_cache_level=6;
+select * from t1 left join t2 on (1=0);
+a	b
+30	NULL
+40	NULL
+20	NULL
+explain select * from t1 left join t2 on (1=0) where a=40;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
+select * from t1 left join t2 on (1=0) where a=40;
+a	b
+40	NULL
+set join_cache_level=1;
+explain select * from t1 left join t2 on (1=0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+set join_cache_level=default;
+drop table t1, t2;

=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2008-07-21 16:38:27 +0000
+++ b/mysql-test/r/join_outer.result	2008-10-25 00:35:49 +0000
@@ -859,14 +859,14 @@ a1	a2
 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON a1=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
 a1	a2	a3
 1	NULL	NULL
 EXPLAIN SELECT * FROM t1 LEFT JOIN (t2,t3) ON a1=0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
 SELECT * FROM t0, t1 LEFT JOIN (t2,t3) ON a1=0 WHERE a0=a1;
 a0	a1	a2	a3
@@ -875,7 +875,7 @@ EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
 INSERT INTO t0 VALUES (0);
 INSERT INTO t1 VALUES (0);
@@ -886,7 +886,7 @@ EXPLAIN SELECT * FROM t0, t1 LEFT JOIN (
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t0	const	PRIMARY	PRIMARY	4	const	1	Using index
 1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	2	
 drop table t1,t2;
 create table t1 (a int, b int);

=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test	2008-10-23 18:33:52 +0000
+++ b/mysql-test/t/join_cache.test	2008-10-25 00:35:49 +0000
@@ -856,3 +856,24 @@ select * from t1 left join t2 on t1.a=t2
 
 set join_cache_level=default;
 drop table t1, t2;
+
+--echo #
+--echo # Bug #40317: outer join with with constant on expression equal to FALSE
+--echo #
+
+create table t1 (a int);
+insert into t1 values (30), (40), (20);
+create table t2 (b int);
+insert into t2 values (200), (100);
+
+set join_cache_level=6;
+
+select * from t1 left join t2 on (1=0);
+explain select * from t1 left join t2 on (1=0) where a=40;
+select * from t1 left join t2 on (1=0) where a=40;
+
+set join_cache_level=1;
+explain select * from t1 left join t2 on (1=0);
+
+set join_cache_level=default;
+drop table t1, t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-23 18:33:52 +0000
+++ b/sql/sql_select.cc	2008-10-25 00:35:49 +0000
@@ -7894,6 +7894,8 @@ make_join_select(JOIN *join,SQL_SELECT *
 	    DBUG_RETURN(1);
           cond_tab->select_cond->update_used_tables();
           cond_tab->select_cond->quick_fix_field();
+          if (cond_tab->select)
+            cond_tab->select->cond= cond_tab->select_cond; 
         }       
       }