Merge rurik.mysql.com:/home/igor/mysql-5.1

into  rurik.mysql.com:/home/igor/dev/mysql-5.1-0


mysql-test/r/loaddata.result:
  Auto merged
mysql-test/r/ndb_read_multi_range.result:
  Auto merged
mysql-test/t/innodb.test:
  Auto merged
mysql-test/t/loaddata.test:
  Auto merged
mysql-test/t/ndb_read_multi_range.test:
  Auto merged
sql/field.cc:
  Auto merged
sql/ha_ndbcluster.cc:
  Auto merged
sql/sql_load.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
storage/ndb/tools/Makefile.am:
  Auto merged

4 files changed, 123 insertions, 5 deletions

diff --git a/mysql-test/r/join_nested.result b/mysql-test/r/join_nested.result
index dfcfa35d31d..0747418111b 100644
--- a/mysql-test/r/join_nested.result
+++ b/mysql-test/r/join_nested.result
@@ -1504,3 +1504,61 @@ id	type	cid	id	pid	pid	type
 1	A	NULL	NULL	NULL	NULL	NULL
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int);
+SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+LEFT OUTER JOIN
+(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+ON t3.id2 IS NOT NULL
+WHERE t1.id1=2;
+id	ngroupbynsa
+PREPARE stmt FROM
+"SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+  FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+       LEFT OUTER JOIN
+       (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+       ON t3.id2 IS NOT NULL
+    WHERE t1.id1=2";
+EXECUTE stmt;
+id	ngroupbynsa
+EXECUTE stmt;
+id	ngroupbynsa
+EXECUTE stmt;
+id	ngroupbynsa
+EXECUTE stmt;
+id	ngroupbynsa
+INSERT INTO t1 VALUES (1,1), (2,1), (3,2);
+INSERT INTO t2 VALUES (2,1), (3,2), (4,3);
+INSERT INTO t3 VALUES (1,1), (3,2), (2,NULL);
+INSERT INTO t4 VALUES (1,1), (2,1), (3,3);
+INSERT INTO t5 VALUES (1,1), (2,2), (3,3), (4,3);
+EXECUTE stmt;
+id	ngroupbynsa
+2	1
+2	1
+EXECUTE stmt;
+id	ngroupbynsa
+2	1
+2	1
+EXECUTE stmt;
+id	ngroupbynsa
+2	1
+2	1
+EXECUTE stmt;
+id	ngroupbynsa
+2	1
+2	1
+SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+LEFT OUTER JOIN
+(t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+ON t3.id2 IS NOT NULL
+WHERE t1.id1=2;
+id	ngroupbynsa
+2	1
+2	1
+DROP TABLE t1,t2,t3,t4,t5;
diff --git a/mysql-test/t/join_nested.test b/mysql-test/t/join_nested.test
index 8adcf05be93..69886d035bf 100644
--- a/mysql-test/t/join_nested.test
+++ b/mysql-test/t/join_nested.test
@@ -942,3 +942,55 @@ SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
 
 DROP VIEW v1;
 DROP TABLE t1,t2,t3;
+
+
+#
+# Test for bug #18279: crash when on conditions are moved out of a nested join
+#                      to the on conditions for the nest
+
+CREATE TABLE t1 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t2 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t3 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t4 (id1 int PRIMARY KEY, id2 int);
+CREATE TABLE t5 (id1 int PRIMARY KEY, id2 int);
+
+SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+  FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+       LEFT OUTER JOIN
+       (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+       ON t3.id2 IS NOT NULL
+    WHERE t1.id1=2;
+
+PREPARE stmt FROM
+"SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+  FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+       LEFT OUTER JOIN
+       (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+       ON t3.id2 IS NOT NULL
+    WHERE t1.id1=2";
+
+EXECUTE stmt; 
+EXECUTE stmt; 
+EXECUTE stmt; 
+EXECUTE stmt;
+
+INSERT INTO t1 VALUES (1,1), (2,1), (3,2);
+INSERT INTO t2 VALUES (2,1), (3,2), (4,3);
+INSERT INTO t3 VALUES (1,1), (3,2), (2,NULL);
+INSERT INTO t4 VALUES (1,1), (2,1), (3,3);
+INSERT INTO t5 VALUES (1,1), (2,2), (3,3), (4,3);
+
+EXECUTE stmt; 
+EXECUTE stmt; 
+EXECUTE stmt; 
+EXECUTE stmt;
+
+SELECT t1.id1 AS id, t5.id1 AS ngroupbynsa
+  FROM t1 INNER JOIN t2 ON t2.id2 = t1.id1
+       LEFT OUTER JOIN
+       (t3 INNER JOIN t4 ON t4.id1 = t3.id2 INNER JOIN t5 ON t4.id2 = t5.id1)
+       ON t3.id2 IS NOT NULL
+    WHERE t1.id1=2; 
+
+DROP TABLE t1,t2,t3,t4,t5;
+
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index a0cfc5a7a9e..ced15b3f728 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -2128,11 +2128,17 @@ void reinit_stmt_before_use(THD *thd, LEX *lex)
     /* Reset is_schema_table_processed value(needed for I_S tables */
     tables->is_schema_table_processed= FALSE;
 
-    if (tables->prep_on_expr)
+    TABLE_LIST *embedded; /* The table at the current level of nesting. */
+    TABLE_LIST *embedding= tables; /* The parent nested table reference. */
+    do
     {
-      tables->on_expr= tables->prep_on_expr->copy_andor_structure(thd);
-      tables->on_expr->cleanup();
+      embedded= embedding;
+      if (embedded->prep_on_expr)
+        embedded->on_expr= embedded->prep_on_expr->copy_andor_structure(thd);
+      embedding= embedded->embedding;
     }
+    while (embedding &&
+           embedding->nested_join->join_list.head() == embedded);
   }
   lex->current_select= &lex->select_lex;
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 02eb4d72d5c..524f9c7b09b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7517,7 +7517,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
       */
       if (table->on_expr)
       {
-        Item *expr= table->prep_on_expr ? table->prep_on_expr : table->on_expr;
+        Item *expr= table->on_expr;
         /* 
            If an on expression E is attached to the table, 
            check all null rejected predicates in this expression.
@@ -7528,7 +7528,9 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
 	*/ 
         expr= simplify_joins(join, &nested_join->join_list,
                              expr, FALSE);
-        table->prep_on_expr= table->on_expr= expr;
+        table->on_expr= expr;
+        if (!table->prep_on_expr)
+          table->prep_on_expr= expr->copy_andor_structure(join->thd);
       }
       nested_join->used_tables= (table_map) 0;
       nested_join->not_null_tables=(table_map) 0;