summaryrefslogtreecommitdiff
path: root/mysql-test/r/explain.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/explain.result')
-rw-r--r--mysql-test/r/explain.result59
1 files changed, 39 insertions, 20 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 378032d94c3..c7b30e4d4fb 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;
@@ -176,11 +176,15 @@ SELECT @@session.sql_mode INTO @old_sql_mode;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
EXPLAIN EXTENDED SELECT 1 FROM t1
WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t );
-ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 SUBQUERY t1 system NULL NULL NULL NULL 0 0.00 const row not found
+2 SUBQUERY t system NULL NULL NULL NULL 0 0.00 const row not found
+Warnings:
+Note 1003 select 1 AS `1` from `test`.`t1` where 0
SHOW WARNINGS;
Level Code Message
-Error 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
-Note 1003 select 1 AS `1` from `test`.`t1` where <not>(<exists>(...))
+Note 1003 select 1 AS `1` from `test`.`t1` where 0
SET SESSION sql_mode=@old_sql_mode;
DROP TABLE t1;
End of 5.0 tests.
@@ -195,16 +199,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 +228,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..
@@ -262,7 +266,7 @@ WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST (""))
WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
@@ -272,12 +276,12 @@ PREPARE stmt FROM
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
@@ -288,13 +292,28 @@ PREPARE stmt FROM
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
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;
View Lorry Controller Status