[Commits] Rev 3858: Fixed bug mdev-4420. in file:///home/igor/maria/maria-5.5/

Igor Babaev igor at askmonty.org
Fri Aug 23 17:25:46 EEST 2013


At file:///home/igor/maria/maria-5.5/

------------------------------------------------------------
revno: 3858
revision-id: igor at askmonty.org-20130823142545-4itfs6sq2px2dtuk
parent: igor at askmonty.org-20130821193458-ju0edat1o4msyvc9
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.5
timestamp: Fri 2013-08-23 07:25:45 -0700
message:
  Fixed bug mdev-4420.
  The code of JOIN::optimize that performed substitutions for the best equal
  field in all ref items did not take into account that a multiple equality
  could contain the result of the single-value subquery if the subquery is
  inexpensive. This code was corrected.
  Also made necessary corresponding corrections in the code of make_join_select().
-------------- next part --------------
=== modified file 'mysql-test/r/join.result'
--- a/mysql-test/r/join.result	2013-07-16 17:09:54 +0000
+++ b/mysql-test/r/join.result	2013-08-23 14:25:45 +0000
@@ -1460,7 +1460,7 @@
 1	SIMPLE	D	system	PRIMARY	NULL	NULL	NULL	1	
 1	SIMPLE	DSAR	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	DT	range	t_id	t_id	2	NULL	2	Using where
-1	SIMPLE	DSA	ref	PRIMARY	PRIMARY	8	const,test.DT.t_id,test.D.birthday	1	Using index
+1	SIMPLE	DSA	ref	PRIMARY	PRIMARY	8	const,test.DT.t_id,func	1	Using index
 SELECT * FROM t5 DU, t1 D, t4 DT, t2 DSA, t3 DSAR
 WHERE DU.dog_id=D.dog_id AND D.dog_id=DT.dog_id AND D.birthday=DT.birthday AND
 DT.t_id=DSA.t_id AND DT.birthday=DSA.birthday AND DSA.dog_id=DSAR.dog_id;

=== modified file 'mysql-test/r/subselect2.result'
--- a/mysql-test/r/subselect2.result	2012-11-04 15:09:46 +0000
+++ b/mysql-test/r/subselect2.result	2013-08-23 14:25:45 +0000
@@ -295,4 +295,29 @@
 2084	2012-02-01 00:00:00	2013-01-01 00:00:00	0
 set optimizer_switch=@tmp_mdev614;
 DROP TABLE t1;
+#
+# MDEV-4420: non-expensive single-value subquery used as
+#            used as an access key to join a table
+#
+create table t1 (a varchar(3));
+insert into t1 values ('USA'), ('FRA');
+create table t2 select * from t1;
+insert into t2 values ('RUS');
+create table t3 select * from t2;
+create index idx on t3(a);
+explain extended
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+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	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	t3	ref	idx	idx	6	func	2	100.00	Using where; Using index
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where ((`test`.`t1`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`)) and (`test`.`t2`.`a` = (select min(`test`.`t1`.`a`) from `test`.`t1`)))
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+a	a	a
+FRA	FRA	FRA
+drop table t1,t2,t3;
 set optimizer_switch=@subselect2_test_tmp;

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2013-07-16 05:22:17 +0000
+++ b/mysql-test/r/subselect_sj.result	2013-08-23 14:25:45 +0000
@@ -2485,7 +2485,7 @@
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	test.t3.b	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2013-07-16 05:22:17 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2013-08-23 14:25:45 +0000
@@ -2499,7 +2499,7 @@
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	1	
 1	PRIMARY	t1	ref	a	a	5	const	1	Using index
-1	PRIMARY	t2	ref	a	a	5	test.t3.b	1	Using index
+1	PRIMARY	t2	ref	a	a	5	func	1	Using index
 2	MATERIALIZED	t4	ALL	NULL	NULL	NULL	NULL	0	
 SELECT * FROM t1, t2
 WHERE t1.a = t2.a AND t2.a IN (SELECT b FROM t3 STRAIGHT_JOIN t4);

=== modified file 'mysql-test/t/subselect2.test'
--- a/mysql-test/t/subselect2.test	2012-11-04 15:09:46 +0000
+++ b/mysql-test/t/subselect2.test	2013-08-23 14:25:45 +0000
@@ -315,6 +315,26 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-4420: non-expensive single-value subquery used as
+--echo #            used as an access key to join a table
+--echo #
+
+create table t1 (a varchar(3));
+insert into t1 values ('USA'), ('FRA');
+create table t2 select * from t1;
+insert into t2 values ('RUS');
+create table t3 select * from t2;
+create index idx on t3(a);
+
+explain extended
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+
+select * from t1, t2 left join t3 on ( t2.a = t3.a )
+where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 );
+
+drop table t1,t2,t3;
 
 set optimizer_switch=@subselect2_test_tmp;
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-08-21 18:27:02 +0000
+++ b/sql/sql_select.cc	2013-08-23 14:25:45 +0000
@@ -1379,6 +1379,12 @@
               new store_key_const_item(*tab->ref.key_copy[key_copy_index],
                                        item);
           }
+          else if (item->const_item())
+	  {
+            tab->ref.key_copy[key_copy_index]=
+              new store_key_item(*tab->ref.key_copy[key_copy_index],
+                                 item, TRUE);
+          }            
           else
           {
             store_key_field *field_copy= ((store_key_field *)key_copy);
@@ -8243,14 +8249,12 @@
           Item *item= tab->ref.items[keypart];
           Item *notnull;
           Item *real= item->real_item();
-          if (real->basic_const_item())
+	  if (real->const_item() && !real->is_expensive())
           {
             /*
               It could be constant instead of field after constant
               propagation.
             */
-            DBUG_ASSERT(real->is_expensive() || // prevent early expensive eval
-                        !real->is_null()); // NULLs are not propagated
             continue;
           }
           DBUG_ASSERT(real->type() == Item::FIELD_ITEM);



More information about the commits mailing list