[Commits] Rev 4146: MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with exists_to_in=on, MERGE view or a SELECT SQ in file:///home/bell/maria/bzr/work-maria-10.0-MDEV-5401/

sanja at montyprogram.com sanja at montyprogram.com
Thu Apr 10 12:14:19 EEST 2014


At file:///home/bell/maria/bzr/work-maria-10.0-MDEV-5401/

------------------------------------------------------------
revno: 4146
revision-id: sanja at montyprogram.com-20140410091418-y0bqpmvk4hxr42x6
parent: knielsen at knielsen-hq.org-20140410073857-7laqshw8k7mjkq8b
committer: sanja at montyprogram.com
branch nick: work-maria-10.0-MDEV-5401
timestamp: Thu 2014-04-10 12:14:18 +0300
message:
  MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with exists_to_in=on, MERGE view or a SELECT SQ
  
  The problem was that the view substitute its fields (on prepare) with reverting the change after execution. After prepare on optimization exists2in convertion substituted arguments of '=' with constsnt '1', but then one of the arguments of '=' was reverted to the view field reference.This lead to incorrect WHERE condition on the second execution.
  
  To fix the problem we replace whole '=' with '1' permannently.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2013-03-27 22:41:02 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2014-04-10 09:14:18 +0000
@@ -862,4 +862,27 @@ i	c1	c2	t1_field	t2_field
 drop table t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_switch='exists_to_in=on';
+#
+#MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with
+#exists_to_in=on, MERGE view or a SELECT SQ
+#
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (2),(3);
+SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a );
+a
+2
+PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )";
+EXECUTE stmt;
+a
+2
+EXECUTE stmt;
+a
+2
+deallocate prepare stmt;
+drop view v1;
+drop table t1,t2;
+# End of 10.0 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect_exists2in.test'
--- a/mysql-test/t/subselect_exists2in.test	2013-02-25 23:20:17 +0000
+++ b/mysql-test/t/subselect_exists2in.test	2014-04-10 09:14:18 +0000
@@ -735,5 +735,30 @@ drop table t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_switch='exists_to_in=on';
 
+--echo #
+--echo #MDEV-5401: Wrong result (missing row) on a 2nd execution of PS with
+--echo #exists_to_in=on, MERGE view or a SELECT SQ
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (2),(3);
+
+SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a );
+
+PREPARE stmt FROM "SELECT * FROM v1 WHERE EXISTS ( SELECT * FROM t2 t2x, t2 t2y WHERE t2y.b = a )";
+EXECUTE stmt;
+EXECUTE stmt;
+
+deallocate prepare stmt;
+
+drop view v1;
+drop table t1,t2;
+
+--echo # End of 10.0 tests
+
 #restore defaults
 set optimizer_switch=default;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2014-03-26 21:25:38 +0000
+++ b/sql/item_subselect.cc	2014-04-10 09:14:18 +0000
@@ -2626,7 +2626,7 @@ static bool check_equality_for_exist2in(
 
 typedef struct st_eq_field_outer
 {
-  Item_func **eq_ref;
+  Item **eq_ref;
   Item_ident *local_field;
   Item *outer_exp;
 } EQ_FIELD_OUTER;
@@ -2665,7 +2665,7 @@ static bool find_inner_outer_equalities(
                                       &element.outer_exp))
       {
         found= TRUE;
-        element.eq_ref= (Item_func **)li.ref();
+        element.eq_ref= li.ref();
         if (result.append(element))
           goto alloc_err;
       }
@@ -2677,7 +2677,7 @@ static bool find_inner_outer_equalities(
                                        &element.outer_exp))
   {
     found= TRUE;
-    element.eq_ref= (Item_func **)conds;
+    element.eq_ref= conds;
     if (result.append(element))
       goto alloc_err;
   }
@@ -2700,7 +2700,7 @@ bool Item_exists_subselect::exists2in_pr
   THD *thd= (THD *)opt_arg;
   SELECT_LEX *first_select=unit->first_select(), *save_select;
   JOIN *join= first_select->join;
-  Item_func *eq= NULL, **eq_ref= NULL;
+  Item **eq_ref= NULL;
   Item_ident *local_field= NULL;
   Item *outer_exp= NULL;
   Item *left_exp= NULL; Item_in_subselect *in_subs;
@@ -2774,7 +2774,6 @@ bool Item_exists_subselect::exists2in_pr
     {
       Item *item= it++;
       eq_ref= eqs.at(i).eq_ref;
-      eq= *eq_ref;
       local_field= eqs.at(i).local_field;
       outer_exp= eqs.at(i).outer_exp;
       /* Add the field to the SELECT_LIST */
@@ -2789,10 +2788,7 @@ bool Item_exists_subselect::exists2in_pr
 
       /* remove the parts from condition */
       if (!upper_not || !local_field->maybe_null)
-      {
-        eq->arguments()[0]= new Item_int(1);
-        eq->arguments()[1]= new Item_int(1);
-      }
+        *eq_ref= new Item_int(1);
       else
       {
         *eq_ref= new Item_func_isnotnull(



More information about the commits mailing list