From: Date: September 7 2006 4:51pm
Subject: bk commit into 5.0 tree (kroki:1.2248) BUG#20492
List-Archive: http://lists.mysql.com/commits/11548
X-Bug: 20492
Message-Id: <200609071451.k87Ep5ZE023766@moonlight.intranet>

Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash 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, 2006-09-07 18:51:00+04:00, kroki@stripped +3 -0
  BUG#20492: Subsequent calls to stored procedure yield incorrect result
             if join is used
  
  For procedures with selects that use complicated joins with ON expression
  re-execution could erroneously ignore this ON expression, giving
  incorrect result.
  
  The problem was that optimized ON expression wasn't saved for
  re-execution.  The solution is to properly save it.

  mysql-test/r/sp.result@stripped, 2006-09-07 18:50:57+04:00, kroki@stripped +26 -0
    Add result for bug#20492: Subsequent calls to stored procedure yield
    incorrect result if join is used.

  mysql-test/t/sp.test@stripped, 2006-09-07 18:50:57+04:00, kroki@stripped +41 -0
    Add test case for bug#20492: Subsequent calls to stored procedure yield
    incorrect result if join is used.

  sql/sql_select.cc@stripped, 2006-09-07 18:50:58+04:00, kroki@stripped +8 -3
    Save modified ON expression for re-execution.

# 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:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug20492

--- 1.439/sql/sql_select.cc	2006-09-07 18:51:07 +04:00
+++ 1.440/sql/sql_select.cc	2006-09-07 18:51:07 +04:00
@@ -7427,9 +7427,14 @@ simplify_joins(JOIN *join, List<TABLE_LI
 	*/ 
         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;
@@ -7439,7 +7444,7 @@ simplify_joins(JOIN *join, List<TABLE_LI
     }
     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)

--- 1.210/mysql-test/r/sp.result	2006-09-07 18:51:07 +04:00
+++ 1.211/mysql-test/r/sp.result	2006-09-07 18:51:07 +04:00
@@ -5394,4 +5394,30 @@ Procedure	sql_mode	Create Procedure
 bug21416		CREATE DEFINER=`root`@`localhost` PROCEDURE `bug21416`()
 show create procedure bug21416
 drop procedure bug21416|
+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|
 drop table t1,t2;

--- 1.198/mysql-test/t/sp.test	2006-09-07 18:51:07 +04:00
+++ 1.199/mysql-test/t/sp.test	2006-09-07 18:51:07 +04:00
@@ -6322,6 +6322,47 @@ create procedure bug21416() show create 
 call bug21416()|
 drop procedure bug21416|
 
+
+#
+# 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|
+
+
 #
 # BUG#NNNN: New bug synopsis
 #