[Commits] Rev 3627: Fixed bug mdev-4172. in file:///home/igor/maria/maria-5.3-mdev4172/

Igor Babaev igor at askmonty.org
Fri Feb 22 03:13:12 EET 2013


At file:///home/igor/maria/maria-5.3-mdev4172/

------------------------------------------------------------
revno: 3627
revision-id: igor at askmonty.org-20130222011312-0n7i0ki83efkz17e
parent: igor at askmonty.org-20130221032202-ed2p8w1a984bxa2p
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-mdev4172
timestamp: Thu 2013-02-21 17:13:12 -0800
message:
  Fixed bug mdev-4172.
  This bug in the legacy code could manifest itself in queries with
  semi-join materialized subqueries.
  When a subquery is materialized all conditions that are imposed
  only on the columns belonging to the tables from the subquery 
  are taken into account.The code responsible for subquery optimizations
  that employes subquery materialization  makes sure to remove these
  conditions from the WHERE conditions of the query obtained after
  it has transformed the original query into a query with a semi-join.
  If the condition to be removed is an equality condition it could
  be added to ON expressions and/or conditions from disjunctive branches
  (parts of OR conditions) in an attempt to generate better access keys
  to the tables of the query. Such equalities are supposed to be removed
  later from all the formulas where they have been added to.
  However, erroneously, this was not done in some cases when an ON
  expression and/or a disjunctive part of the OR condition could
  be converted into one multiple equality. As a result some equality
  predicates over columns belonging to the tables of the materialized
  subquery remained in the ON condition and/or the a disjunctive 
  part of the OR condition, and the excuter later, when trying to
  evaluate them, returned wrong answers as the values of the fields
  from these equalities were not valid.  
  This happened because any standalone multiple equality (a multiple
  equality that are not ANDed with any other predicates) lacked
  the information about equality predicates inherited from upper
  levels (in particular, inherited from the WHERE condition).
  The fix adds a reference to such information to any standalone
  multiple equality. 
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2013-02-21 03:22:02 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2013-02-22 01:13:12 +0000
@@ -1281,3 +1281,55 @@
 g	y	y	y
 DROP TABLE t1,t2,t3,t4;
 set optimizer_switch=@save_optimizer_switch;
+#
+# mdev-4172: LEFT JOIN with materialized multi-table IN subquery in WHERE
+#            and OR in ON condition
+#
+set @save_optimizer_switch=@@optimizer_switch;
+CREATE TABLE t1 (a1 int, c1 varchar(1));
+INSERT t1 VALUES (7,'v'), (3,'y');
+CREATE TABLE t2 (c2 varchar(1));
+INSERT INTO t2 VALUES ('y'), ('y');
+CREATE TABLE t3 (c3 varchar(1));
+INSERT INTO t3 VALUES
+('j'), ('v'), ('c'), ('m'), ('d'),
+('d'), ('y'), ('t'), ('d'), ('s');
+CREATE TABLE t4 (a4 int, c4 varchar(1));
+INSERT INTO t4 SELECT * FROM t1;
+set optimizer_switch='materialization=off';
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Start temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; End temporary; Using join buffer (flat, BNL join)
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) or (`test`.`t1`.`c1` > 'z'))) where ((`test`.`t4`.`c4` = `test`.`t1`.`c1`) and (`test`.`t3`.`c3` = `test`.`t1`.`c1`))
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+a1	c1	c2
+3	y	y
+3	y	y
+7	v	NULL
+set optimizer_switch='materialization=on';
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` semi join (`test`.`t3` join `test`.`t4`) left join `test`.`t2` on(((`test`.`t2`.`c2` = `test`.`t1`.`c1`) or (`test`.`t1`.`c1` > 'z'))) where (`test`.`t3`.`c3` = `test`.`t4`.`c4`)
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+a1	c1	c2
+3	y	y
+3	y	y
+7	v	NULL
+DROP TABLE t1,t2,t3,t4;
+set optimizer_switch=@save_optimizer_switch;

=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result	2013-02-21 02:01:36 +0000
+++ b/mysql-test/r/view.result	2013-02-22 01:13:12 +0000
@@ -1437,7 +1437,7 @@
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 Warnings:
-Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on(((`test`.`t1`.`a` = `test`.`t3`.`a`) and (`test`.`t2`.`a` = `test`.`t3`.`a`)))) on((`test`.`t1`.`a` = `test`.`t3`.`a`)) where 1
+Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b` from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t2`.`a` = `test`.`t3`.`a`))) on((`test`.`t1`.`a` = `test`.`t3`.`a`)) where 1
 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
 execute stmt1;
 a	a	b

=== modified file 'mysql-test/t/subselect_sj2_mat.test'
--- a/mysql-test/t/subselect_sj2_mat.test	2013-02-21 03:22:02 +0000
+++ b/mysql-test/t/subselect_sj2_mat.test	2013-02-22 01:13:12 +0000
@@ -177,3 +177,45 @@
 DROP TABLE t1,t2,t3,t4;
 
 set optimizer_switch=@save_optimizer_switch;
+
+--echo #
+--echo # mdev-4172: LEFT JOIN with materialized multi-table IN subquery in WHERE
+--echo #            and OR in ON condition
+--echo #
+
+set @save_optimizer_switch=@@optimizer_switch;
+
+CREATE TABLE t1 (a1 int, c1 varchar(1));
+INSERT t1 VALUES (7,'v'), (3,'y');
+
+CREATE TABLE t2 (c2 varchar(1));
+INSERT INTO t2 VALUES ('y'), ('y');
+
+CREATE TABLE t3 (c3 varchar(1));
+INSERT INTO t3 VALUES
+  ('j'), ('v'), ('c'), ('m'), ('d'),
+  ('d'), ('y'), ('t'), ('d'), ('s');
+
+CREATE TABLE t4 (a4 int, c4 varchar(1));
+INSERT INTO t4 SELECT * FROM t1;
+
+set optimizer_switch='materialization=off';
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+  WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+  WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+
+set optimizer_switch='materialization=on';
+
+EXPLAIN EXTENDED
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+  WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+SELECT * FROM t1 LEFT JOIN t2 ON (c2 = c1 OR c1 > 'z')
+  WHERE c1 IN ( SELECT c4 FROM t3,t4 WHERE c3 = c4);
+
+DROP TABLE t1,t2,t3,t4;
+
+set optimizer_switch=@save_optimizer_switch;
+

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2013-01-10 14:40:21 +0000
+++ b/sql/item_cmpfunc.cc	2013-02-22 01:13:12 +0000
@@ -5458,6 +5458,7 @@
   equal_items.push_back(f1);
   equal_items.push_back(f2);
   compare_as_dates= with_const_item && f2->cmp_type() == TIME_RESULT;
+  upper_levels= NULL;  
 }
 
 
@@ -5486,6 +5487,7 @@
   with_const= item_equal->with_const;
   compare_as_dates= item_equal->compare_as_dates;
   cond_false= item_equal->cond_false;
+  upper_levels= item_equal->upper_levels;
 }
 
 

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2013-02-21 02:01:36 +0000
+++ b/sql/item_cmpfunc.h	2013-02-22 01:13:12 +0000
@@ -1739,7 +1739,11 @@
     used in the original equality.
   */
   Item_field *context_field;
+
 public:
+
+  COND_EQUAL *upper_levels;       /* multiple equalities of upper and levels */
+
   inline Item_equal()
     : Item_bool_func(), with_const(FALSE), eval_item(0), cond_false(0),
       context_field(NULL)

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-02-21 03:22:02 +0000
+++ b/sql/sql_select.cc	2013-02-22 01:13:12 +0000
@@ -11438,6 +11438,7 @@
           item_equal->update_used_tables();
           set_if_bigger(thd->lex->current_select->max_equal_elems,
                         item_equal->n_field_items());  
+          item_equal->upper_levels= inherited;
           return item_equal;
 	}
 
@@ -12121,6 +12122,7 @@
   {
     item_equal= (Item_equal *) cond;
     item_equal->sort(&compare_fields_by_table_order, table_join_idx);
+    cond_equal= item_equal->upper_levels;
     if (cond_equal && cond_equal->current_level.head() == item_equal)
       cond_equal= cond_equal->upper_levels;
     cond= eliminate_item_equal(0, cond_equal, item_equal);



More information about the commits mailing list