[Commits] Rev 3502: MDEV-4152 fix. in file:///home/bell/maria/bzr/work-maria-10.0-base-exists2in/

sanja at askmonty.org sanja at askmonty.org
Wed Feb 13 15:18:36 EET 2013


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

------------------------------------------------------------
revno: 3502
revision-id: sanja at askmonty.org-20130213131832-b1sk3puofj29jubr
parent: sanja at askmonty.org-20130207125211-tuj45glbgqhjhm73
committer: sanja at askmonty.org
branch nick: work-maria-10.0-base-exists2in
timestamp: Wed 2013-02-13 15:18:32 +0200
message:
  MDEV-4152 fix.
  
  Correct evaluating of used tables by transformed subquery.
  (move to references list all Items from old subquery which was not moved out of the query)
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2013-02-07 12:52:11 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2013-02-13 13:18:32 +0000
@@ -843,4 +843,23 @@ a
 drop table t1,t2;
 set optimizer_switch=default;
 set optimizer_switch='exists_to_in=on';
+#
+# MDEV-4152: Wrong result (missing rows) with exists_to_in=on, 
+# inner joins
+#
+SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on';
+CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d');
+CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('m'), ('b');
+CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+SELECT * FROM t1, t2 outer_t2 
+WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field );
+i	c1	c2	t1_field	t2_field
+1	test1	test2	f	b
+2	test3	test4	d	b
+drop table t1,t2,t3;
+set optimizer_switch=default;
+set optimizer_switch='exists_to_in=on';
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_exists_to_in.result'
--- a/mysql-test/r/subselect_exists_to_in.result	2013-01-25 10:18:50 +0000
+++ b/mysql-test/r/subselect_exists_to_in.result	2013-02-13 13:18:32 +0000
@@ -2971,7 +2971,7 @@ id	select_type	table	type	possible_keys
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
+Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
 explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
@@ -2984,7 +2984,7 @@ id	select_type	table	type	possible_keys
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	100.00	
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	9	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
+Note	1003	select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<expr_cache><`test`.`t1`.`one`,`test`.`t1`.`two`>(<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one`,`test`.`t2`.`two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`)))) and trigcond(trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`))))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`)))))) AS `test` from `test`.`t1`
 DROP TABLE t1,t2;
 set optimizer_switch=@tmp11867_optimizer_switch;
 CREATE TABLE t1 (a char(5), b char(5));

=== modified file 'mysql-test/t/subselect_exists2in.test'
--- a/mysql-test/t/subselect_exists2in.test	2013-02-07 12:52:11 +0000
+++ b/mysql-test/t/subselect_exists2in.test	2013-02-13 13:18:32 +0000
@@ -713,6 +713,27 @@ drop table t1,t2;
 set optimizer_switch=default;
 set optimizer_switch='exists_to_in=on';
 
+--echo #
+--echo # MDEV-4152: Wrong result (missing rows) with exists_to_in=on, 
+--echo # inner joins
+--echo #
+SET optimizer_switch='materialization=on,semijoin=on,exists_to_in=on';
+
+CREATE TABLE t1 (i INT, c1 CHAR(5), c2 CHAR(5), t1_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,'test1','test2','f'), (2,'test3','test4','d');
+
+CREATE TABLE t2 (t2_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES ('m'), ('b');
+
+CREATE TABLE t3 (t3_field VARCHAR(1)) ENGINE=MyISAM;
+INSERT INTO t3 VALUES ('b'),('c');
+
+SELECT * FROM t1, t2 outer_t2 
+WHERE EXISTS ( SELECT 1 FROM t2, t3 WHERE t3_field = outer_t2.t2_field AND t2_field <= t1_field );
+
+drop table t1,t2,t3;
+set optimizer_switch=default;
+set optimizer_switch='exists_to_in=on';
 
 #restore defaults
 set optimizer_switch=default;

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2013-01-25 10:18:50 +0000
+++ b/sql/item.cc	2013-02-13 13:18:32 +0000
@@ -6486,6 +6486,13 @@ Item* Item::cache_const_expr_transformer
   return this;
 }
 
+/**
+  Find Item by reference in the expression
+*/
+bool Item::find_item_processor(uchar *arg)
+{
+  return (this == ((Item *) arg));
+}
 
 bool Item_field::send(Protocol *protocol, String *buffer)
 {

=== modified file 'sql/item.h'
--- a/sql/item.h	2013-01-25 10:18:50 +0000
+++ b/sql/item.h	2013-02-13 13:18:32 +0000
@@ -1159,6 +1159,7 @@ public:
   virtual bool collect_item_field_processor(uchar * arg) { return 0; }
   virtual bool add_field_to_set_processor(uchar * arg) { return 0; }
   virtual bool find_item_in_field_list_processor(uchar *arg) { return 0; }
+  virtual bool find_item_processor(uchar *arg);
   virtual bool change_context_processor(uchar *context) { return 0; }
   virtual bool reset_query_id_processor(uchar *query_id_arg) { return 0; }
   virtual bool is_expensive_processor(uchar *arg) { return 0; }
@@ -3228,8 +3229,7 @@ public:
   bool subst_argument_checker(uchar **arg);
   Item *equal_fields_propagator(uchar *arg);
   Item *replace_equal_field(uchar *arg);
-  void update_used_tables();
-  table_map used_tables() const;	
+  table_map used_tables() const;
   table_map not_null_tables() const;
   void update_used_tables();
   bool walk(Item_processor processor, bool walk_subquery, uchar *arg)

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2013-01-25 10:18:50 +0000
+++ b/sql/item_cmpfunc.cc	2013-02-13 13:18:32 +0000
@@ -982,18 +982,27 @@ int Arg_comparator::compare_e_datetime()
 int Arg_comparator::compare_string()
 {
   String *res1,*res2;
+  DBUG_ENTER("Arg_comparator::compare_string");
   if ((res1= (*a)->val_str(&value1)))
   {
+    DBUG_PRINT("info", ("a: '%s'", (res1->ptr() ? res1->ptr() : "<0>")));
     if ((res2= (*b)->val_str(&value2)))
     {
+      DBUG_PRINT("info", ("b: '%s'", (res1->ptr() ? res2->ptr() : "<0>")));
       if (set_null)
+      {
         owner->null_value= 0;
-      return sortcmp(res1,res2,cmp_collation.collation);
+        DBUG_PRINT("info", ("NOT NULL"));
+      }
+      DBUG_RETURN(sortcmp(res1,res2,cmp_collation.collation));
     }
   }
   if (set_null)
+  {
+    DBUG_PRINT("info", ("NULL"));
     owner->null_value= 1;
-  return -1;
+  }
+  DBUG_RETURN(-1);
 }
 
 

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-02-07 12:52:11 +0000
+++ b/sql/item_subselect.cc	2013-02-13 13:18:32 +0000
@@ -474,7 +474,7 @@ public:
 void Item_subselect::recalc_used_tables(st_select_lex *new_parent, 
                                         bool after_pullout)
 {
-  List_iterator<Ref_to_outside> it(upper_refs);
+  List_iterator_fast<Ref_to_outside> it(upper_refs);
   Ref_to_outside *upper;
   DBUG_ENTER("Item_subselect::recalc_used_tables");
   
@@ -2852,6 +2852,23 @@ bool Item_exists_subselect::exists2in_pr
       */
       goto out;
     }
+    {
+      /* Move dependence list */
+      List_iterator_fast<Ref_to_outside> it(upper_refs);
+      Ref_to_outside *upper;
+      while ((upper= it++))
+      {
+        uint i;
+        for (i= 0; i < (uint)eqs.elements(); i++)
+          if (eqs.at(i).outer_exp->
+              walk(&Item::find_item_processor, TRUE, (uchar*)upper->item))
+            break;
+        if (i == (uint)eqs.elements() &&
+            (in_subs->upper_refs.push_back(upper, thd->stmt_arena->mem_root)))
+          goto out;
+      }
+    }
+    in_subs->update_used_tables();
     /*
       The engine of the subquery is fixed so above fix_fields() is not
       complete and should be fixed

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-02-07 12:52:11 +0000
+++ b/sql/sql_select.cc	2013-02-13 13:18:32 +0000
@@ -16401,7 +16401,9 @@ evaluate_join_record(JOIN *join, JOIN_TA
   DBUG_ENTER("evaluate_join_record");
   DBUG_PRINT("enter",
              ("evaluate_join_record join: %p join_tab: %p"
-              " cond: %p error: %d", join, join_tab, select_cond, error));
+              " cond: %p error: %d  alias %s",
+              join, join_tab, select_cond, error,
+              join_tab->table->alias.ptr()));
   if (error > 0 || (join->thd->is_error()))     // Fatal error
     DBUG_RETURN(NESTED_LOOP_ERROR);
   if (error < 0)
@@ -16514,6 +16516,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
     if (join_tab->check_weed_out_table && found)
     {
       int res= join_tab->check_weed_out_table->sj_weedout_check_row(join->thd);
+      DBUG_PRINT("info", ("weedout_check: %d", res));
       if (res == -1)
         DBUG_RETURN(NESTED_LOOP_ERROR);
       else if (res == 1)
@@ -16534,8 +16537,8 @@ evaluate_join_record(JOIN *join, JOIN_TA
       (See above join->return_tab= tab).
     */
     join->examined_rows++;
-    DBUG_PRINT("counts", ("join->examined_rows++: %lu",
-                          (ulong) join->examined_rows));
+    DBUG_PRINT("counts", ("join->examined_rows++: %lu  found: %d",
+                          (ulong) join->examined_rows, (int) found));
 
     if (found)
     {



More information about the commits mailing list