bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3389) Bug#58422

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1-telco-7.0-spj-scan-scan/ based on revid:ole.john.aske@stripped

 3389 Ole John Aske	2010-12-07
      SPJ-scan-scan: Cherry picked fix for bug#58422: Incorrect result when OUTER JOIN'ing with an empty table
      
      Fixed incorrect checks in join_read_const_table() for when to 
      accept a non-existing, or empty const-row as a part of the const'ified 
      set of tables.
      
      Intention of this test is to only accept NULL-rows if this table is outer joined
      into the resultset. (In case of an inner-join we can conclude at this point that 
      resultset will be empty, end we want to return 'error' to signal this.)
      
      Initially 'maybe_null' is set to the same value as 'outer_join' in 
      setup_table_map(), mysql_priv.h ~line 2424. Later simplify_joins() will
      attemp to replace outer joins by inner join whenever possible. This
      will cause 'outer_join' to be updated. However, 'maybe_null' is *not* updated
      to reflect this rewrite as this field is used to currectly set the 'nullability'
      property for the columns in the resultset.
      
      We should therefore change join_read_const_table() to check the 'outer_join'
      property instead of 'maybe_null', as this correctly reflect the nullability of
      the *execution plan* (not *resultset*).

    modified:
      mysql-test/r/select.result
      mysql-test/t/select.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2010-06-24 08:00:48 +0000
+++ b/mysql-test/r/select.result	2010-12-07 11:56:10 +0000
@@ -4797,4 +4797,68 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 1
 1
 DROP TABLE t1;
+#
+# Bug #58422: Incorrect result when OUTER JOIN'ing 
+# with an empty table
+#
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON TRUE)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 CROSS JOIN t_empty)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+EXPLAIN
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT *
+FROM 
+t1
+LEFT OUTER JOIN
+(t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+ON t1.pk=t2.pk
+WHERE t2.pk <> 2;
+pk	i	pk	i	pk	i
+DROP TABLE t1,t2,t_empty;
 End of 5.1 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2010-06-24 17:13:08 +0000
+++ b/mysql-test/t/select.test	2010-12-07 11:56:10 +0000
@@ -4088,4 +4088,74 @@ SELECT 1 FROM t1 ORDER BY a COLLATE lati
 DROP TABLE t1;
 
 
+
+--echo #
+--echo # Bug #58422: Incorrect result when OUTER JOIN'ing 
+--echo # with an empty table
+--echo #
+
+CREATE TABLE t_empty(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+CREATE TABLE t1(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
+CREATE TABLE t2(pk INT PRIMARY KEY, i INT) ENGINE = MYISAM;
+INSERT INTO t2 VALUES (1,1), (2,2), (3,3);
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON TRUE)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 CROSS JOIN t_empty)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+EXPLAIN
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+SELECT *
+  FROM 
+    t1
+  LEFT OUTER JOIN
+    (t2 INNER JOIN t_empty ON t_empty.i=t2.i)
+  ON t1.pk=t2.pk
+  WHERE t2.pk <> 2;
+
+
+
+DROP TABLE t1,t2,t_empty;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-12-03 14:55:15 +0000
+++ b/sql/sql_select.cc	2010-12-07 11:56:10 +0000
@@ -11920,7 +11920,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }
@@ -11941,7 +11941,7 @@ join_read_const_table(JOIN_TAB *tab, POS
       /* Mark for EXPLAIN that the row was not found */
       pos->records_read=0.0;
       pos->ref_depend_map= 0;
-      if (!table->maybe_null || error > 0)
+      if (!table->pos_in_table_list->outer_join || error > 0)
 	DBUG_RETURN(error);
     }
   }


Attachment: [text/bzr-bundle] bzr/[e-mail]