bk commit into 5.0 tree (igor:1.2503) BUG#28571

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-05-26 10:13:28-07:00, igor@stripped +3 -0
  Fixed bug #28571. Outer join queries with ON conditions over
  constant outer tables did not return null complemented
  rows when conditions were evaluated to FALSE.
  Wrong results were returned because the conditions over constant
  outer tables, when being pushed down, were erroneously enclosed 
  into the guard function used for WHERE conditions.

  mysql-test/r/join_outer.result@stripped, 2007-05-26 10:13:26-07:00, igor@stripped +15
-0
    Added a test case for bug #28571.

  mysql-test/t/join_outer.test@stripped, 2007-05-26 10:13:26-07:00, igor@stripped +16 -0
    Added a test case for bug #28571.

  sql/sql_select.cc@stripped, 2007-05-26 10:13:26-07:00, igor@stripped +29 -25
    Fixed bug #28571. Outer join queries with ON conditions over
    constant outer tables did not return null complemented
    rows when conditions were evaluated to FALSE.
    Wrong results were returned because the conditions over constant
    outer tables, when being pushed down, were erroneously enclosed 
    into the guard function used for WHERE conditions.
    The problem is fixed in the function make_join_select. Now the
    conditions over constant tables from ON expressions are pushed
    down after the conditions from WHERE has been pushed down.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug28571

--- 1.524/sql/sql_select.cc	2007-05-26 10:13:35 -07:00
+++ 1.525/sql/sql_select.cc	2007-05-26 10:13:35 -07:00
@@ -5666,28 +5666,6 @@
                               join->const_table_map,
                               (table_map) 0);
         DBUG_EXECUTE("where",print_where(const_cond,"constants"););
-        for (JOIN_TAB *tab= join->join_tab+join->const_tables;
-             tab < join->join_tab+join->tables ; tab++)
-        {
-          if (*tab->on_expr_ref)
-          {
-            JOIN_TAB *cond_tab= tab->first_inner;
-            COND *tmp= make_cond_for_table(*tab->on_expr_ref,
-                                           join->const_table_map,
-                                         (  table_map) 0);
-            if (!tmp)
-              continue;
-            tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
-            if (!tmp)
-              DBUG_RETURN(1);
-            tmp->quick_fix_field();
-            cond_tab->select_cond= !cond_tab->select_cond ? tmp :
-	                            new Item_cond_and(cond_tab->select_cond,tmp);
-            if (!cond_tab->select_cond)
-	      DBUG_RETURN(1);
-            cond_tab->select_cond->quick_fix_field();
-          }       
-        }
         if (const_cond && !const_cond->val_int())
         {
 	  DBUG_PRINT("info",("Found impossible WHERE condition"));
@@ -5918,13 +5896,39 @@
       }
       
       /* 
-        Push down all predicates from on expressions.
-        Each of these predicated are guarded by a variable
+        Push down conditions from all on expressions.
+        Each of these conditions are guarded by a variable
         that turns if off just before null complemented row for
-        outer joins is formed. Thus, the predicates from an
+        outer joins is formed. Thus, the condition from an
         'on expression' are guaranteed not to be checked for
         the null complemented row.
       */ 
+
+      /* First push down constant conditions from on expressions */
+      for (JOIN_TAB *tab= join->join_tab+join->const_tables;
+           tab < join->join_tab+join->tables ; tab++)
+      {
+        if (*tab->on_expr_ref)
+        {
+          JOIN_TAB *cond_tab= tab->first_inner;
+          COND *tmp= make_cond_for_table(*tab->on_expr_ref,
+                                         join->const_table_map,
+                                         (table_map) 0);
+          if (!tmp)
+            continue;
+          tmp= new Item_func_trig_cond(tmp, &cond_tab->not_null_compl);
+          if (!tmp)
+            DBUG_RETURN(1);
+          tmp->quick_fix_field();
+          cond_tab->select_cond= !cond_tab->select_cond ? tmp :
+	                            new Item_cond_and(cond_tab->select_cond,tmp);
+          if (!cond_tab->select_cond)
+	    DBUG_RETURN(1);
+          cond_tab->select_cond->quick_fix_field();
+        }       
+      }
+
+      /* Push down non-constant conditions from on expressions */
       JOIN_TAB *last_tab= tab;
       while (first_inner_tab && first_inner_tab->last_inner == last_tab)
       {  

--- 1.54/mysql-test/r/join_outer.result	2007-05-26 10:13:35 -07:00
+++ 1.55/mysql-test/r/join_outer.result	2007-05-26 10:13:35 -07:00
@@ -1239,3 +1239,18 @@
 Handler_read_rnd	0
 Handler_read_rnd_next	6
 DROP TABLE t1,t2;
+CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
+INSERT INTO t1 VALUES (1,0), (2,1);
+CREATE TABLE t2 (d int PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2), (3);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	3	Using where; Using index; Not exists
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+c	e	d
+1	0	NULL
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
+c	e	d
+1	0	NULL
+DROP TABLE t1,t2;

--- 1.40/mysql-test/t/join_outer.test	2007-05-26 10:13:35 -07:00
+++ 1.41/mysql-test/t/join_outer.test	2007-05-26 10:13:35 -07:00
@@ -845,3 +845,19 @@
 show status like 'Handler_read%';
 
 DROP TABLE t1,t2;
+
+#
+# Bug 28571: outer join with false on condition over constant tables 
+#
+
+CREATE TABLE t1 (c int  PRIMARY KEY, e int NOT NULL);
+INSERT INTO t1 VALUES (1,0), (2,1);
+CREATE TABLE t2 (d int PRIMARY KEY);
+INSERT INTO t2 VALUES (1), (2), (3);
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d IS NULL;
+SELECT * FROM t1 LEFT JOIN t2 ON e<>0 WHERE c=1 AND d<=>NULL;
+
+DROP TABLE t1,t2;
+