summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/join.result2
-rw-r--r--mysql-test/r/subselect2.result25
-rw-r--r--mysql-test/r/subselect_sj.result2
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result2
-rw-r--r--mysql-test/t/subselect2.test20
-rw-r--r--sql/sql_select.cc10
6 files changed, 55 insertions, 6 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index ae224fe0220..a1796d7fa3b 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1460,7 +1460,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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;
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result
index 4fd303dfd44..e06d799845e 100644
--- a/mysql-test/r/subselect2.result
+++ b/mysql-test/r/subselect2.result
@@ -295,4 +295,29 @@ node_uid date mirror_date result
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;
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 3fc91b452a5..2d229db9ec5 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -2485,7 +2485,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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);
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index f18de3c4d0b..a0ebbb3305d 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -2499,7 +2499,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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);
diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test
index 68894ad18cb..e286c92285d 100644
--- a/mysql-test/t/subselect2.test
+++ b/mysql-test/t/subselect2.test
@@ -315,6 +315,26 @@ set optimizer_switch=@tmp_mdev614;
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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index bdede4e831b..87ab88ade10 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1379,6 +1379,12 @@ JOIN::optimize()
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 @@ static void add_not_null_conds(JOIN *join)
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);
View Lorry Controller Status