bzr commit into mysql-next-mr-bugfixing branch (tor.didriksen:3076) Bug#49198

[Tor Didriksen]

#At file:///export/home/didrik/mysqldev-next-mr/next-mr-opt-backporting-br2/ based on revid:tor.didriksen@stripped

 3076 Tor Didriksen	2010-04-15
      Bug #49198 Wrong result for second call of procedure with view in subselect 
      
      Backport of tor.didriksen@stripped
      
      The problem was that fix_after_pullout() after semijoin conversion 
      wasn't propagated from the view to the underlying table. 
      On subesequent executions of the prepared statement, 
      we would mark the underlying table as 'dependent' and the predicate 
      anlysis would lead to a different (and illegal) execution plan.
     @ mysql-test/r/subselect_sj.result
        Add test cases.
     @ mysql-test/t/subselect_sj.test
        Add test cases.
     @ sql/item.cc
        Always propagate the fix_after_pullout() to the referenced object in Item_direct_view_ref::fix_after_pullout()
     @ sql/item.h
        Add Item_direct_view_ref::fix_after_pullout()

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/t/subselect_sj.test
      sql/item.cc
      sql/item.h
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2010-04-15 07:38:15 +0000
+++ b/mysql-test/r/subselect_sj.result	2010-04-15 11:11:40 +0000
@@ -1057,3 +1057,50 @@ deallocate prepare stmt;
 drop table t1, t2;
 drop view v1;
 # End of Bug#49097
+# 
+# Bug#49198 Wrong result for second call of procedure
+#           with view in subselect.
+# 
+CREATE TABLE t1 (t1field integer, primary key (t1field));
+CREATE TABLE t2 (t2field integer, primary key (t2field));
+CREATE TABLE t3 (t3field integer, primary key (t3field));
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(1),(2);
+INSERT INTO t3 VALUES(1),(2);
+PREPARE stmt FROM
+"
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2);
+";
+EXECUTE stmt;
+t1field
+1
+2
+EXECUTE stmt;
+t1field
+1
+2
+PREPARE stmt FROM
+"
+EXPLAIN
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2)
+  AND t1field IN (SELECT * FROM v3)
+";
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	PRIMARY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+EXECUTE stmt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.t1field	1	Using index
+DROP TABLE t1, t2, t3;
+DROP VIEW v2, v3;
+# End of Bug#49198

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2010-04-15 07:38:15 +0000
+++ b/mysql-test/t/subselect_sj.test	2010-04-15 11:11:40 +0000
@@ -752,3 +752,46 @@ drop table t1, t2;
 drop view v1;
 
 --echo # End of Bug#49097
+
+--echo # 
+--echo # Bug#49198 Wrong result for second call of procedure
+--echo #           with view in subselect.
+--echo # 
+
+CREATE TABLE t1 (t1field integer, primary key (t1field));
+CREATE TABLE t2 (t2field integer, primary key (t2field));
+CREATE TABLE t3 (t3field integer, primary key (t3field));
+
+CREATE VIEW v2 AS SELECT * FROM t2;
+CREATE VIEW v3 AS SELECT * FROM t3;
+
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t2 VALUES(1),(2);
+INSERT INTO t3 VALUES(1),(2);
+
+PREPARE stmt FROM
+"
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2);
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+PREPARE stmt FROM
+"
+EXPLAIN
+SELECT t1field
+FROM t1
+WHERE t1field IN (SELECT * FROM v2)
+  AND t1field IN (SELECT * FROM v3)
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DROP TABLE t1, t2, t3;
+DROP VIEW v2, v3;
+
+--echo # End of Bug#49198

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2010-03-25 11:08:24 +0000
+++ b/sql/item.cc	2010-04-15 11:11:40 +0000
@@ -6714,11 +6714,19 @@ void Item_outer_ref::fix_after_pullout(s
 
 void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
 {
-  if (depended_from == new_parent)
-  {
-    (*ref)->fix_after_pullout(new_parent, ref);
-    depended_from= NULL;
-  }
+  /*
+    Item_ref could be used for referencing items from HAVING/ORDER BY clauses
+    and for outer references in SELECT list or in HAVING clause of inner query.
+    Neither of these cases are allowed by semi-join optimization.
+  */
+  DBUG_ASSERT(false);
+}
+
+void Item_direct_view_ref::fix_after_pullout(st_select_lex *new_parent,
+                                             Item **refptr)
+{
+  (*ref)->fix_after_pullout(new_parent, ref);
+  depended_from= NULL;
 }
 
 /**

=== modified file 'sql/item.h'
--- a/sql/item.h	2010-03-25 11:14:23 +0000
+++ b/sql/item.h	2010-04-15 11:11:40 +0000
@@ -2590,6 +2590,7 @@ public:
     :Item_direct_ref(thd, item) {}
 
   bool fix_fields(THD *, Item **);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
   bool eq(const Item *item, bool binary_cmp) const;
   Item *get_tmp_table_item(THD *thd)
   {


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