1# 2# Test of different EXPLAINs 3 4--disable_warnings 5drop table if exists t1; 6--enable_warnings 7create table t1 (id int not null, str char(10), unique(str)); 8explain select * from t1; 9insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar"); 10select * from t1 where str is null; 11select * from t1 where str="foo"; 12explain select * from t1 where str is null; 13explain select * from t1 where str="foo"; 14explain select * from t1 ignore key (str) where str="foo"; 15explain select * from t1 use key (str,str) where str="foo"; 16 17#The following should give errors 18--error 1176 19explain select * from t1 use key (str,str,foo) where str="foo"; 20--error 1176 21explain select * from t1 ignore key (str,str,foo) where str="foo"; 22drop table t1; 23 24explain select 1; 25 26create table t1 (a int not null); 27explain select count(*) from t1; 28insert into t1 values(1); 29explain select count(*) from t1; 30insert into t1 values(1); 31explain select count(*) from t1; 32drop table t1; 33 34# 35# Bug #3403 Wrong encoding in EXPLAIN SELECT output 36# 37set names koi8r; 38create table ��� (���0 int, ���1 int, key ���0 (���0), key ���01 (���0,���1)); 39insert into ��� (���0) values (1); 40insert into ��� (���0) values (2); 41explain select ���0 from ��� where ���0=1; 42drop table ���; 43set names latin1; 44 45# End of 4.1 tests 46 47 48# 49# Bug#15463: EXPLAIN SELECT..INTO hangs the client (QB, command line) 50# 51select 3 into @v1; 52explain select 3 into @v1; 53 54# 55# Bug#22331: Wrong WHERE in EXPLAIN EXTENDED when all expressions were 56# optimized away. 57# 58create table t1(f1 int, f2 int); 59insert into t1 values (1,1); 60create view v1 as select * from t1 where f1=1; 61explain extended select * from v1 where f2=1; 62explain extended select * from t1 where 0; 63explain extended select * from t1 where 1; 64explain extended select * from t1 having 0; 65explain extended select * from t1 having 1; 66drop view v1; 67drop table t1; 68 69# 70# Bug #32241: memory corruption due to large index map in 'Range checked for 71# each record' 72# 73 74CREATE TABLE t1(c INT); 75INSERT INTO t1 VALUES (),(); 76 77CREATE TABLE t2 (b INT, 78KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 79KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 80KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 81KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 82KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 83KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 84KEY(b),KEY(b),KEY(b),KEY(b),KEY(b), 85KEY(b),KEY(b),KEY(b),KEY(b),KEY(b)); 86 87INSERT INTO t2 VALUES (),(),(); 88 89# We only need to make sure that there is no buffer overrun and the index map 90# is displayed correctly 91--replace_column 1 X 2 X 3 X 4 X 5 X 6 X 7 X 8 X 9 X 92EXPLAIN SELECT 1 FROM 93 (SELECT 1 FROM t2,t1 WHERE b < c GROUP BY 1 LIMIT 1) AS d2; 94DROP TABLE t2; 95DROP TABLE t1; 96 97# 98# Bug #34773: query with explain extended and derived table / other table 99# crashes server 100# 101 102CREATE TABLE t1(a INT); 103CREATE TABLE t2(a INT); 104INSERT INTO t1 VALUES (1),(2); 105INSERT INTO t2 VALUES (1),(2); 106 107EXPLAIN EXTENDED SELECT 1 108 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; 109 110EXPLAIN EXTENDED SELECT 1 111 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1; 112 113prepare s1 from 114'EXPLAIN EXTENDED SELECT 1 115 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; 116execute s1; 117 118prepare s1 from 119'EXPLAIN EXTENDED SELECT 1 120 FROM (SELECT COUNT(DISTINCT t1.a) FROM t1,t2 GROUP BY t1.a) AS s1'; 121execute s1; 122execute s1; 123 124DROP TABLE t1,t2; 125 126 127# 128# Bug #43354: Use key hint can crash server in explain extended query 129# 130 131CREATE TABLE t1 (a INT PRIMARY KEY); 132 133--error ER_KEY_DOES_NOT_EXISTS 134EXPLAIN EXTENDED SELECT COUNT(a) FROM t1 USE KEY(a); 135 136DROP TABLE t1; 137 138# 139# Bug#45989 memory leak after explain encounters an error in the query 140# 141CREATE TABLE t1(a LONGTEXT); 142INSERT INTO t1 VALUES (repeat('a',@@global.max_allowed_packet)); 143INSERT INTO t1 VALUES (repeat('b',@@global.max_allowed_packet)); 144--error ER_BAD_FIELD_ERROR 145EXPLAIN SELECT DISTINCT 1 FROM t1, 146 (SELECT DISTINCTROW a AS away FROM t1 GROUP BY a WITH ROLLUP) as d1 147 WHERE t1.a = d1.a; 148DROP TABLE t1; 149 150--echo # 151--echo # Bug#48295: 152--echo # explain extended crash with subquery and ONLY_FULL_GROUP_BY sql_mode 153--echo # 154 155CREATE TABLE t1 (f1 INT not null); 156 157SELECT @@session.sql_mode INTO @old_sql_mode; 158SET SESSION sql_mode='ONLY_FULL_GROUP_BY'; 159 160# EXPLAIN EXTENDED (with subselect). used to crash. should give NOTICE. 161# Before moving max/min optimization to optimize phase this statement 162# generated error, but as far as original query do not contain aggregate 163# function user should not see error 164# --error ER_MIX_OF_GROUP_FUNC_AND_FIELDS 165EXPLAIN EXTENDED SELECT 1 FROM t1 166 WHERE f1 > ALL( SELECT t.f1 FROM t1,t1 AS t ); 167 168SET SESSION sql_mode=@old_sql_mode; 169 170DROP TABLE t1; 171 172--echo End of 5.0 tests. 173 174--echo # 175--echo # Bug#37870: Usage of uninitialized value caused failed assertion. 176--echo # 177create table t1 (dt datetime not null, t time not null); 178create table t2 (dt datetime not null); 179insert into t1 values ('2001-01-01 1:1:1', '1:1:1'), 180('2001-01-01 1:1:1', '1:1:1'); 181insert into t2 values ('2001-01-01 1:1:1'), ('2001-01-01 1:1:1'); 182SET @save_join_cache_level=@@join_cache_level; 183SET join_cache_level=0; 184flush tables; 185EXPLAIN SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); 186flush tables; 187SELECT OUTR.dt FROM t1 AS OUTR WHERE OUTR.dt IN (SELECT INNR.dt FROM t2 AS INNR WHERE OUTR.dt IS NULL ); 188flush tables; 189EXPLAIN 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' ); 190flush tables; 191SELECT 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' ); 192SET join_cache_level=@save_join_cache_level; 193drop tables t1, t2; 194 195--echo # 196--echo # Bug#47669: Query showed by EXPLAIN EXTENDED gives different result from original query 197--echo # 198 199CREATE TABLE t1 (c int); 200INSERT INTO t1 VALUES (NULL); 201CREATE TABLE t2 (d int); 202INSERT INTO t2 VALUES (NULL), (0); 203EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; 204DROP TABLE t1, t2; 205 206--echo # 207--echo # Bug#30302: Tables that were optimized away are printed in the 208--echo # EXPLAIN EXTENDED warning. 209--echo # 210create table t1(f1 int); 211create table t2(f2 int); 212insert into t1 values(1); 213insert into t2 values(1),(2); 214explain extended select * from t1 where f1=1; 215explain extended select * from t1 join t2 on f1=f2 where f1=1; 216drop table t1,t2; 217 218--echo # 219--echo # Bug #48419: another explain crash.. 220--echo # 221CREATE TABLE t1 (a INT); 222CREATE TABLE t2 (b BLOB, KEY b(b(100))); 223INSERT INTO t2 VALUES ('1'), ('2'), ('3'); 224 225FLUSH TABLES; 226 227EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT 1 FROM t1 t JOIN t2 WHERE b <= 1 AND t.a); 228 229DROP TABLE t1, t2; 230 231--echo # 232--echo # Bug #48573: difference of index selection between rpm binary and 233--echo # .tar.gz, windows vs linux.. 234--echo # 235 236CREATE TABLE t1(c1 INT, c2 INT, c4 INT, c5 INT, KEY(c2, c5), KEY(c2, c4, c5)); 237INSERT INTO t1 VALUES(4, 1, 1, 1); 238INSERT INTO t1 VALUES(3, 1, 1, 1); 239INSERT INTO t1 VALUES(2, 1, 1, 1); 240INSERT INTO t1 VALUES(1, 1, 1, 1); 241INSERT INTO t1 VALUES(5, 2, 1, 1); 242INSERT INTO t1 VALUES(6, 2, 1, 1); 243INSERT INTO t1 VALUES(7, 3, 1, 1); 244INSERT INTO t1 VALUES(9, 3, 1, 1); 245INSERT INTO t1 VALUES(10, 4, 1, 1); 246INSERT INTO t1 VALUES(11, 4, 1, 1); 247 248EXPLAIN SELECT c1 FROM t1 WHERE c2 = 1 AND c4 = 1 AND c5 = 1; 249 250DROP TABLE t1; 251 252--echo # 253--echo # Bug#56814 Explain + subselect + fulltext crashes server 254--echo # 255 256CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, 257FULLTEXT KEY(f1),UNIQUE(f1)); 258INSERT INTO t1 VALUES ('test'); 259 260EXPLAIN SELECT 1 FROM t1 261WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) 262WHERE t1.f1 GROUP BY t1.f1)); 263 264PREPARE stmt FROM 265'EXPLAIN SELECT 1 FROM t1 266 WHERE 1 > ALL((SELECT t1.f1 FROM t1 RIGHT OUTER JOIN t1 a 267 ON (MATCH(t1.f1) AGAINST ("")) 268 WHERE t1.f1 GROUP BY t1.f1))'; 269 270EXECUTE stmt; 271EXECUTE stmt; 272 273DEALLOCATE PREPARE stmt; 274 275PREPARE stmt FROM 276'EXPLAIN SELECT 1 FROM t1 277 WHERE 1 > ALL((SELECT t1.f1 FROM t1 JOIN t1 a 278 ON (MATCH(t1.f1) AGAINST ("")) 279 WHERE t1.f1 GROUP BY t1.f1))'; 280 281EXECUTE stmt; 282EXECUTE stmt; 283 284DEALLOCATE PREPARE stmt; 285 286DROP TABLE t1; 287 288--echo End of 5.1 tests. 289 290--echo # 291--echo # Bug#11829785 EXPLAIN EXTENDED CRASH WITH RIGHT OUTER JOIN, SUBQUERIES 292--echo # 293 294CREATE TABLE t1(a INT); 295 296INSERT INTO t1 VALUES (0), (0); 297 298PREPARE s FROM 299'EXPLAIN EXTENDED 300SELECT SUBSTRING(1, (SELECT 1 FROM t1 a1 RIGHT OUTER JOIN t1 ON 0)) AS d 301FROM t1 WHERE 0 > ANY (SELECT @a FROM t1)'; 302 303--error ER_SUBQUERY_NO_1_ROW 304EXECUTE s; 305 306DEALLOCATE PREPARE s; 307DROP TABLE t1; 308 309--echo # 310--echo # Bug#776295: EXPLAIN EXTENDED with always false multiple equality 311--echo # in the WHERE condition of a derived table 312--echo # 313 314CREATE TABLE t1 (a int) ; 315 316CREATE TABLE t2 (a int) ; 317INSERT INTO t2 VALUES (8); 318 319set @tmp_optimizer_switch=@@optimizer_switch; 320set optimizer_switch='derived_merge=off,derived_with_keys=off'; 321EXPLAIN EXTENDED 322SELECT * FROM ( SELECT t1.a FROM t1,t2 WHERE t2.a = t1.a ) AS t; 323set optimizer_switch=@tmp_optimizer_switch; 324 325DROP TABLE t1,t2; 326 327--echo # 328--echo # MDEV-7215: EXPLAIN REPLACE produces an error: 329--echo # Column count doesn't match value count 330--echo # 331create table t1 (a int); 332insert into t1 values (1); 333create table t2 (b int, c int); 334 335replace into t2 select 100, (select a from t1); 336explain replace into t2 select 100, (select a from t1); 337#analyze replace into t2 select 100, (select a from t1); 338 339drop table t1, t2; 340 341--echo # End of 10.1 tests 342