Merge moonlight.home:/home/tomash/src/mysql_ab/mysql-5.0-bug20492

into  moonlight.home:/home/tomash/src/mysql_ab/mysql-5.1-bug20492


sql/sql_select.cc:
  Auto merged
mysql-test/r/sp.result:
  Manual merge.
mysql-test/t/sp.test:
  Manual merge.

3 files changed, 74 insertions, 3 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 9b96781b58a..6c39b708e46 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -5631,6 +5631,32 @@ Called B
 Called B
 drop procedure proc_21462_a|
 drop procedure proc_21462_b|
+DROP PROCEDURE IF EXISTS p1|
+DROP VIEW IF EXISTS v1, v2|
+DROP TABLE IF EXISTS t3, t4|
+CREATE TABLE t3 (t3_id INT)|
+INSERT INTO t3 VALUES (0)|
+INSERT INTO t3 VALUES (1)|
+CREATE TABLE t4 (t4_id INT)|
+INSERT INTO t4 VALUES (2)|
+CREATE VIEW v1 AS
+SELECT t3.t3_id, t4.t4_id
+FROM t3 JOIN t4 ON t3.t3_id = 0|
+CREATE VIEW v2 AS
+SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id
+FROM t3 LEFT JOIN v1 ON t3.t3_id = 0|
+CREATE PROCEDURE p1() SELECT * FROM v2|
+CALL p1()|
+t3_id_1	t3_id_2	t4_id
+0	0	2
+1	NULL	NULL
+CALL p1()|
+t3_id_1	t3_id_2	t4_id
+0	0	2
+1	NULL	NULL
+DROP PROCEDURE p1|
+DROP VIEW v1, v2|
+DROP TABLE t3, t4|
 End of 5.0 tests
 Begin of 5.1 tests
 drop function if exists pi;
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 6c7fde71e78..0ca1df8ff2b 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -6585,6 +6585,46 @@ call proc_21462_b(1)|
 drop procedure proc_21462_a|
 drop procedure proc_21462_b|
 
+
+#
+# BUG#20492: Subsequent calls to stored procedure yeild incorrect
+# result if join is used 
+#
+# Optimized ON expression in join wasn't properly saved for reuse.
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1|
+DROP VIEW IF EXISTS v1, v2|
+DROP TABLE IF EXISTS t3, t4|
+--enable_warnings
+
+CREATE TABLE t3 (t3_id INT)|
+
+INSERT INTO t3 VALUES (0)|
+INSERT INTO t3 VALUES (1)|
+
+CREATE TABLE t4 (t4_id INT)|
+
+INSERT INTO t4 VALUES (2)|
+
+CREATE VIEW v1 AS
+SELECT t3.t3_id, t4.t4_id
+FROM t3 JOIN t4 ON t3.t3_id = 0|
+
+CREATE VIEW v2 AS
+SELECT t3.t3_id AS t3_id_1, v1.t3_id AS t3_id_2, v1.t4_id
+FROM t3 LEFT JOIN v1 ON t3.t3_id = 0|
+
+CREATE PROCEDURE p1() SELECT * FROM v2|
+
+# Results should not differ.
+CALL p1()|
+CALL p1()|
+
+DROP PROCEDURE p1|
+DROP VIEW v1, v2|
+DROP TABLE t3, t4|
+
 --echo End of 5.0 tests
 
 --echo Begin of 5.1 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d062cbb3ef6..2da2261d3e1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8166,9 +8166,14 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
 	*/ 
         expr= simplify_joins(join, &nested_join->join_list,
                              expr, FALSE);
-        table->on_expr= expr;
-        if (!table->prep_on_expr)
+
+        if (!table->prep_on_expr || expr != table->on_expr)
+        {
+          DBUG_ASSERT(expr);
+
+          table->on_expr= 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;
@@ -8178,7 +8183,7 @@ simplify_joins(JOIN *join, List<TABLE_LIST> *join_list, COND *conds, bool top)
     }
     else
     {
-      if (!(table->prep_on_expr))
+      if (!table->prep_on_expr)
         table->prep_on_expr= table->on_expr;
       used_tables= table->table->map;
       if (conds)