diff options
Diffstat (limited to 'mysql-test/r/explain.result')
| -rw-r--r-- | mysql-test/r/explain.result | 39 |
1 files changed, 27 insertions, 12 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result index 4bc6c0409f3..4bcdf7344a7 100644 --- a/mysql-test/r/explain.result +++ b/mysql-test/r/explain.result @@ -13,7 +13,7 @@ id str 3 foo explain select * from t1 where str is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref str str 11 const 1 Using where +1 SIMPLE t1 ref str str 11 const 1 Using index condition explain select * from t1 where str="foo"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const str str 11 const 1 @@ -50,7 +50,7 @@ insert into таб (кол0) values (1); insert into таб (кол0) values (2); explain select кол0 from таб where кол0=1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE таб ref инд0,инд01 инд0 5 const 1 Using where; Using index +1 SIMPLE таб ref инд0,инд01 инд0 5 const 1 Using index drop table таб; set names latin1; select 3 into @v1; @@ -116,7 +116,7 @@ FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` EXPLAIN EXTENDED SELECT 1 @@ -124,7 +124,7 @@ FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` prepare s1 from @@ -134,7 +134,7 @@ execute s1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` prepare s1 from @@ -144,14 +144,14 @@ execute s1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` execute s1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort -2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer +2 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select 1 AS `1` from (select count(distinct `test`.`t1`.`a`) AS `COUNT(DISTINCT t1.a)` from `test`.`t1` join `test`.`t2` group by `test`.`t1`.`a`) `s1` DROP TABLE t1,t2; @@ -195,16 +195,16 @@ insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); dt flush tables; EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY INNR ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY OUTR ALL NULL NULL NULL NULL 2 Using where; Start temporary +1 PRIMARY INNR ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) flush tables; SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN ( SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.t < '2005-11-13 7:41:31' ); dt @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 -Note 1003 select (select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL)) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where (`test`.`t2`.`d` = NULL))) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` DROP TABLE t1, t2; # # Bug #48419: another explain crash.. @@ -298,3 +298,18 @@ id select_type table type possible_keys key key_len ref rows Extra DEALLOCATE PREPARE stmt; DROP TABLE t1; End of 5.1 tests. +# +# Bug#776295: EXPLAIN EXTENDED with always false multiple equality +# in the WHERE condition of a derived table +# +CREATE TABLE t1 (a int) ; +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (8); +EXPLAIN EXTENDED +SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 0.00 const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +Warnings: +Note 1003 select NULL AS `a` from (select NULL AS `a` from `test`.`t1` join `test`.`t2` where 0) `t` +DROP TABLE t1,t2; |