bk commit into 5.0 tree (igor:1.2351) BUG#24345

[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-01-03 12:16:03-08:00, igor@stripped +3 -0
  Fixed bug #24345.
  This bug appeared after the patch for bug 21390 that had added some code
  to handle outer joins with no matches after substitution of a const
  table in an efficient way. That code as it is cannot be applied to the case
  of nested outer join operations. Being applied to the queries with
  nested outer joins the code can cause crashes or wrong result sets.
  The fix blocks row substitution for const inner tables of an outer join
  if the inner operand is not a single table.  

  mysql-test/r/join_nested.result@stripped, 2007-01-03 12:16:01-08:00, igor@stripped +43
-0
    Added a test case for bug #24345.

  mysql-test/t/join_nested.test@stripped, 2007-01-03 12:16:01-08:00, igor@stripped +51
-0
    Added a test case for bug #24345.

  sql/sql_select.cc@stripped, 2007-01-03 12:16:01-08:00, igor@stripped +11 -1
    Fixed bug #24345.
    This bug appeared after the patch for bug 21390 that had added some code
    to handle outer joins with no matches after substitution of a const
    table in an efficient way. That code as it is cannot be applied to the case
    of nested outer join operations. Being applied to the queries with
    nested outer joins the code can cause crashes or wrong result sets.
    The fix blocks row substitution for const inner tables of an outer join
    if the inner operand is not a single table.  

# 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-bug24345

--- 1.478/sql/sql_select.cc	2007-01-03 12:16:09 -08:00
+++ 1.479/sql/sql_select.cc	2007-01-03 12:16:09 -08:00
@@ -2306,8 +2306,18 @@
         substitution of a const table the key value happens to be null
         then we can state that there are no matches for this equi-join.
       */  
-      if ((keyuse= s->keyuse) && *s->on_expr_ref)
+      if ((keyuse= s->keyuse) && *s->on_expr_ref &&
!s->embedding_map)
       {
+        /* 
+          When performing an outer join operation if there are no matching rows
+          for the single row of the outer table all the inner tables are to be
+          null complemented and thus considered as constant tables.
+          Here we apply this consideration to the case of outer join operations 
+          with a single inner table only because the case with nested tables
+          would require a more thorough analysis.
+          TODO. Apply single row substitution to null complemented inner tables
+          for nested outer join operations. 
+	*/              
         while (keyuse->table == table)
         {
           if (!(keyuse->val->used_tables() & ~join->const_table_map)
&&

--- 1.23/mysql-test/r/join_nested.result	2007-01-03 12:16:09 -08:00
+++ 1.24/mysql-test/r/join_nested.result	2007-01-03 12:16:09 -08:00
@@ -1562,3 +1562,46 @@
 2	1
 2	1
 DROP TABLE t1,t2,t3,t4,t5;
+CREATE TABLE t1 (
+id int NOT NULL PRIMARY KEY,
+ct int DEFAULT NULL,
+pc int DEFAULT NULL,
+INDEX idx_ct (ct),
+INDEX idx_pc (pc)
+);
+INSERT INTO t1 VALUES  
+(1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
+CREATE TABLE t2 (
+id int NOT NULL PRIMARY KEY,
+sr int NOT NULL,
+nm varchar(255) NOT NULL,
+INDEX idx_sr (sr)
+);
+INSERT INTO t2 VALUES
+(2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');
+CREATE TABLE t3 (
+id int NOT NULL PRIMARY KEY,
+ct int NOT NULL,
+ln int NOT NULL,
+INDEX idx_ct (ct),
+INDEX idx_ln (ln)
+);
+CREATE TABLE t4 (
+id int NOT NULL PRIMARY KEY,
+nm varchar(255) NOT NULL
+);
+INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
+SELECT t1.*
+FROM t1 LEFT JOIN
+(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+WHERE t1.id='5';
+id	ct	pc
+5	NULL	NULL
+SELECT t1.*, t4.nm
+FROM t1 LEFT JOIN
+(t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+LEFT JOIN t4 ON t2.sr=t4.id
+WHERE t1.id='5';
+id	ct	pc	nm
+5	NULL	NULL	NULL
+DROP TABLE t1,t2,t3,t4;

--- 1.19/mysql-test/t/join_nested.test	2007-01-03 12:16:09 -08:00
+++ 1.20/mysql-test/t/join_nested.test	2007-01-03 12:16:09 -08:00
@@ -994,3 +994,54 @@
 
 DROP TABLE t1,t2,t3,t4,t5;
 
+#
+# Test for bug #24345: crash with nested left outer join when outer table is substituted
+#                      for a row that happens to have a null value for the join
attribute.
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL PRIMARY KEY,
+  ct int DEFAULT NULL,
+  pc int DEFAULT NULL,
+  INDEX idx_ct (ct),
+  INDEX idx_pc (pc)
+);
+INSERT INTO t1 VALUES  
+ (1,NULL,NULL),(2,NULL,NULL),(3,NULL,NULL),(4,NULL,NULL),(5,NULL,NULL);
+
+CREATE TABLE t2 (
+  id int NOT NULL PRIMARY KEY,
+  sr int NOT NULL,
+  nm varchar(255) NOT NULL,
+  INDEX idx_sr (sr)
+);
+INSERT INTO t2 VALUES
+  (2441905,4308,'LesAbymes'),(2441906,4308,'Anse-Bertrand');
+
+CREATE TABLE t3 (
+  id int NOT NULL PRIMARY KEY,
+  ct int NOT NULL,
+  ln int NOT NULL,
+  INDEX idx_ct (ct),
+  INDEX idx_ln (ln)
+);
+
+CREATE TABLE t4 (
+  id int NOT NULL PRIMARY KEY,
+  nm varchar(255) NOT NULL
+);
+
+INSERT INTO t4 VALUES (4308,'Guadeloupe'),(4309,'Martinique');
+
+SELECT t1.*
+  FROM t1 LEFT JOIN
+       (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+    WHERE t1.id='5';
+
+SELECT t1.*, t4.nm
+  FROM t1 LEFT JOIN
+      (t2 LEFT JOIN t3 ON t3.ct=t2.id AND t3.ln='5') ON t1.ct=t2.id
+          LEFT JOIN t4 ON t2.sr=t4.id
+    WHERE t1.id='5';
+
+DROP TABLE t1,t2,t3,t4;