[Commits] Rev 3483: Fix of MDEV-3879: Wrong result (extra row) and unexpected warning with exists_to_in=on and a NOT EXISTS subquery. in file:///home/bell/maria/bzr/work-maria-10.0-exists2in/

sanja at montyprogram.com sanja at montyprogram.com
Tue Nov 27 14:12:19 EET 2012


At file:///home/bell/maria/bzr/work-maria-10.0-exists2in/

------------------------------------------------------------
revno: 3483
revision-id: sanja at montyprogram.com-20121127121216-23x72tjff11skij3
parent: psergey at askmonty.org-20121123063639-1jqbkjuxevqe1vb6
committer: sanja at montyprogram.com
branch nick: work-maria-10.0-exists2in
timestamp: Tue 2012-11-27 14:12:16 +0200
message:
  Fix of MDEV-3879: Wrong result (extra row) and unexpected warning with exists_to_in=on and a NOT EXISTS subquery.
  
  Lost IS NOT NULL in constructing expression for many items.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2012-11-12 20:14:16 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2012-11-27 12:12:16 +0000
@@ -728,4 +728,18 @@ col1	col2	col3
 2	3	4
 4	5	6
 drop table t1,t2;
+#
+# MDEV-3879: Exists2In: Wrong result (extra row) and unexpected
+# warning with exists_to_in=on and a NOT EXISTS subquery
+#
+SET optimizer_switch = 'exists_to_in=on';
+CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3,'y'),(6,'w');
+CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3,'y'),(6,'d');
+SELECT * FROM t1
+WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 );
+a1	b1
+6	w
+drop table t1, t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect_exists2in.test'
--- a/mysql-test/t/subselect_exists2in.test	2012-11-12 20:14:16 +0000
+++ b/mysql-test/t/subselect_exists2in.test	2012-11-27 12:12:16 +0000
@@ -568,5 +568,23 @@ select * from t1 where exists (select co
 select * from t1 where exists (select col2 from t2 where t2.col1=t1.col1 and t2.col2=t1.col2);
 drop table t1,t2;
 
+--echo #
+--echo # MDEV-3879: Exists2In: Wrong result (extra row) and unexpected
+--echo # warning with exists_to_in=on and a NOT EXISTS subquery
+--echo #
+SET optimizer_switch = 'exists_to_in=on';
+
+CREATE TABLE t1 (a1 INT, b1 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3,'y'),(6,'w');
+
+CREATE TABLE t2 (a2 INT, b2 CHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (3,'y'),(6,'d');
+
+SELECT * FROM t1
+WHERE NOT EXISTS ( SELECT * FROM t2 WHERE b2 = b1 AND a2 = a1 );
+
+drop table t1, t2;
+
+
 #restore defaults
 set optimizer_switch=default;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2012-11-23 06:36:39 +0000
+++ b/sql/item_subselect.cc	2012-11-27 12:12:16 +0000
@@ -2666,8 +2666,7 @@ bool Item_exists_subselect::exists2in_pr
   Item_func *eq= NULL, **eq_ref= NULL;
   Item_field *local_field= NULL;
   Item *outer_exp= NULL;
-  Item *left_exp= NULL;
-  Item_in_subselect *in_subs;
+  Item *left_exp= NULL; Item_in_subselect *in_subs;
   Query_arena *arena= NULL, backup;
   int res= FALSE;
   List<Item> outer;
@@ -2885,11 +2884,12 @@ bool Item_exists_subselect::exists2in_pr
       for (int i= 0; i < eqs.elements(); i++)
       {
         and_list->
-          push_front(new Item_direct_ref(&unit->outer_select()->context,
-                                         optimizer->arguments()[0]->
-                                         addr(i),
-                                         (char *)"<no matter>",
-                                         (char *)exists_outer_expr_name));
+          push_front(
+            new Item_func_isnotnull(
+              new Item_direct_ref(&unit->outer_select()->context,
+                                  optimizer->arguments()[0]->addr(i),
+                                  (char *)"<no matter>",
+                                  (char *)exists_outer_expr_name)));
       }
       and_list->push_front(optimizer);
       exp= new Item_cond_and(*and_list);



More information about the commits mailing list