diff options
author | Sergei Golubchik <sergii@pisem.net> | 2013-09-21 10:14:42 +0200 |
---|---|---|
committer | Sergei Golubchik <sergii@pisem.net> | 2013-09-21 10:14:42 +0200 |
commit | 9af177042ed0e297b8f26f2c2f8ae00b3a814a90 (patch) | |
tree | 0d8d2fab7ebeb02f3c25c00ac1543754b625bde8 /mysql-test/r/subselect2.result | |
parent | a6add4ff43a905cee1bfd00b2ec2d276018866ce (diff) | |
parent | 2fe0836eed16ce5809c34064893681f12c77da9f (diff) | |
download | mariadb-git-9af177042ed0e297b8f26f2c2f8ae00b3a814a90.tar.gz |
10.0-base merge.
Partitioning/InnoDB changes are *not* merged (they'll come from 5.6)
TokuDB does not compile (not updated to 10.0 SE API)
Diffstat (limited to 'mysql-test/r/subselect2.result')
-rw-r--r-- | mysql-test/r/subselect2.result | 53 |
1 files changed, 53 insertions, 0 deletions
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 38e955e349c..890ba2bb2d7 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -237,6 +237,34 @@ a b DROP TABLE t1,t2,t3; set optimizer_switch=@tmp_mdev567; # +# MDEV-4996: degenerate OR formula in WHERE of a subquery +# +CREATE TABLE t1 (a int, c1 varchar(1)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (2,'x'), (8,'d'); +CREATE TABLE t2 (m int, n int, c2 varchar(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (0, 5, 'x'), (1, 4,'p'); +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +a c1 +2 x +8 d +EXPLAIN EXTENDED +SELECT * FROM t1 WHERE c1 NOT IN ( +SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c +WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND +( t2b.m != a OR t2b.m = t2a.m )); +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 +2 DEPENDENT SUBQUERY t2a ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t2b ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (((`test`.`t2b`.`m` <> `test`.`t1`.`a`) or (`test`.`t2b`.`m` = `test`.`t2a`.`m`)) and trigcond(((<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2`) or isnull(`test`.`t2a`.`c2`))) and (`test`.`t2c`.`c2` = `test`.`t2b`.`c2`) and (`test`.`t2b`.`n` = `test`.`t2a`.`m`)) having trigcond(<is_not_null_test>(`test`.`t2a`.`c2`))))))) +DROP TABLE t1,t2; +# # MDEV-614, also MDEV-536, also LP:1050806: # different result for a query using subquery between 5.5.25 and 5.5.27 # @@ -295,4 +323,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; |