bzr commit into mysql-6.0-bka-preview branch (igor:2663) Bug#40317

[Igor Babaev]

#At file:///home/igor/dev-bzr/mysql-6.0-bug40317/

 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

per-file messages:
  mysql-test/r/join_cache.result
    Adjusted the output of the EXPLAIN commands in some test cases
    after the fix for bug #40317.
  mysql-test/r/join_outer.result
    Added a test case for bug#40317.
  mysql-test/t/join_cache.test
    Added a test case for bug#40317.
  sql/sql_select.cc
    Fixed bug #40317.
    Made sure that at the execution of a query the value of the 
    select->cond field was equal to the value of the select_cond
    for any JOIN_TAB structure used in the execution plan.
=== 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; 
         }       
       }