diff options
| author | Igor Babaev <igor@askmonty.org> | 2013-09-06 10:34:38 -0700 |
|---|---|---|
| committer | Igor Babaev <igor@askmonty.org> | 2013-09-06 10:34:38 -0700 |
| commit | 824db55ce53963a64fcf648b54500df22c57e9b2 (patch) | |
| tree | e23237d0f15ea3b67f1b643914942de7c7e0f7c9 /mysql-test/r/subselect2.result | |
| parent | bbc9e57981d8aa420d7bcf58e3fb2d7c1bf25ca9 (diff) | |
| parent | 02338228dd48752a48d611b03ce35efd35d143d7 (diff) | |
| download | mariadb-git-824db55ce53963a64fcf648b54500df22c57e9b2.tar.gz | |
Merge 5.3->5.5
Diffstat (limited to 'mysql-test/r/subselect2.result')
| -rw-r--r-- | mysql-test/r/subselect2.result | 28 |
1 files changed, 28 insertions, 0 deletions
diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index e06d799845e..72ca9d33354 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 # |