subselect4.result 18.3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
#
# Bug #46791: Assertion failed:(table->key_read==0),function unknown
#    function,file sql_base.cc
#
CREATE TABLE t1 (a INT, b INT, KEY(a));
INSERT INTO t1 VALUES (1,1),(2,2);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 VALUES (1,1),(2,2);
CREATE TABLE t3 LIKE t1;
# should have 1 impossible where and 2 dependent subqueries
EXPLAIN
SELECT 1 FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
ORDER BY count(*);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
16
1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using where; Using index; Using temporary
17
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
18
3	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
# should not crash the next statement
SELECT 1 FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
ORDER BY count(*);
1
1
# should not crash: the crash is caused by the previous statement
SELECT 1;
1
1
DROP TABLE t1,t2,t3;
#
# Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing 
# query
#
CREATE TABLE t1 (
a INT,
b INT,
PRIMARY KEY (a),
KEY b (b)
);
INSERT INTO t1 VALUES (1, 1), (2, 1);
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;
CREATE TABLE t3 LIKE t1;
INSERT INTO t3 SELECT * FROM t1;
# Should not crash.
# Should have 1 impossible where and 2 dependent subqs.
EXPLAIN
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
FROM t3 WHERE 1 = 0 GROUP BY 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
2	DEPENDENT SUBQUERY	t1	index	NULL	PRIMARY	4	NULL	2	Using index
2	DEPENDENT SUBQUERY	t2	index	b	b	5	NULL	2	Using where; Using index; Using join buffer
# should return 0 rows
SELECT
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
FROM t3 WHERE 1 = 0 GROUP BY 1;
(SELECT 1 FROM t1,t2 WHERE t2.b > t3.b)
DROP TABLE t1,t2,t3;
End of 5.0 tests.
62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83
CREATE TABLE t1 (col_int_nokey int(11) NOT NULL, col_varchar_nokey varchar(1) NOT NULL) engine=myisam;
INSERT INTO t1 VALUES (2,'s'),(0,'v'),(2,'s');
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) NOT NULL,
col_varchar_key varchar(1) NOT NULL,
PRIMARY KEY (pk),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (4,10,'g'), (5,20,'v');
SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1;
col_int_nokey	sub
2	10
0	NULL
2	10
SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) +1 FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1;
col_int_nokey	sub
2	11
0	NULL
2	11
DROP TABLE t1,t2;
84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
#
# Bug#54568: create view cause Assertion failed: 0, 
# file .\item_subselect.cc, line 836
#
EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1249	Select 2 was reduced during optimization
DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
Warnings:
Note	1249	Select 2 was reduced during optimization
# None of the below should crash
CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) );
CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) );
DROP VIEW v1, v2;
#
# End of 5.1 tests.
#
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
#
# BUG#46743 "Azalea processing correlated, aggregate SELECT
# subqueries incorrectly"
#
CREATE TABLE t1 (c int);
INSERT INTO t1 VALUES (NULL);
CREATE TABLE t2 (d int , KEY (d));
INSERT INTO t2 VALUES (NULL),(NULL);
0 rows in subquery
SELECT 1 AS RESULT FROM t2,t1 WHERE d = c;
RESULT
base query
SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ;
RESULT
NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
123
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
124 125
Warnings:
Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
126
Note	1003	select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1`
127 128 129 130 131 132 133
first equivalent variant
SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ;
RESULT
NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
134
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
135 136
Warnings:
Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
137
Note	1003	select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1` group by NULL
138 139 140 141 142 143 144
second equivalent variant
SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
RESULT
NULL
EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
145
2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
146 147
Warnings:
Note	1276	Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1
148
Note	1003	select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1` group by NULL
149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261
DROP TABLE t1,t2;
#
# BUG#45928 "Differing query results depending on MRR and
# engine_condition_pushdown settings"
#
CREATE TABLE `t1` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`time_nokey` time NOT NULL,
`varchar_key` varchar(1) NOT NULL,
`varchar_nokey` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `varchar_key` (`varchar_key`)
) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','','');
set @old_optimizer_switch          = @@session.optimizer_switch,
@old_optimizer_use_mrr         = @@session.optimizer_use_mrr,
@old_engine_condition_pushdown = @@session.engine_condition_pushdown;
SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off';
SET SESSION optimizer_use_mrr = 'force';
SET SESSION engine_condition_pushdown = 1;
SELECT `time_nokey` G1  FROM t1  WHERE ( `varchar_nokey`  , `varchar_key`  )  IN ( 
SELECT `varchar_nokey`  , `varchar_nokey`  )  AND `varchar_key`  >= 'c' HAVING G1  ORDER 
BY `pk`   ;
G1
set @@session.optimizer_switch          = @old_optimizer_switch,
@@session.optimizer_use_mrr         = @old_optimizer_use_mrr,
@@session.engine_condition_pushdown = @old_engine_condition_pushdown;
DROP TABLE t1;
#
# BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(),
#            file item.cc, line 4448"
#
DROP TABLE IF EXISTS C, BB;
CREATE TABLE C (
varchar_nokey varchar(1) NOT NULL
);
INSERT INTO C VALUES
('k'),('a'),(''),('u'),('e'),('v'),('i'),
('t'),('u'),('f'),('u'),('m'),('j'),('f'),
('v'),('j'),('g'),('e'),('h'),('z');
CREATE TABLE BB (
varchar_nokey varchar(1) NOT NULL
);
INSERT INTO BB VALUES ('i'),('t');
SELECT varchar_nokey FROM C
WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey
FROM BB);
ERROR 21000: Operand should contain 2 column(s)
SELECT varchar_nokey FROM C
WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey
FROM BB);
ERROR 42S22: Unknown column 'OUTR' in 'IN/ALL/ANY subquery'
DROP TABLE C,BB;
#
# During work with BUG#45863 I had problems with a query that was
# optimized differently in regular and prepared mode.
# Because there was a bug in one of the selected strategies, I became
# aware of the problem. Adding an EXPLAIN query to catch this.
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1
(EMPNUM   CHAR(3) NOT NULL,
EMPNAME  CHAR(20),
GRADE    DECIMAL(4),
CITY     CHAR(15));
CREATE TABLE t2
(PNUM     CHAR(3) NOT NULL,
PNAME    CHAR(20),
PTYPE    CHAR(6),
BUDGET   DECIMAL(9),
CITY     CHAR(15));
CREATE TABLE t3
(EMPNUM   CHAR(3) NOT NULL,
PNUM     CHAR(3) NOT NULL,
HOURS    DECIMAL(5));
INSERT INTO t1 VALUES ('E1','Alice',12,'Deale');
INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna');
INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna');
INSERT INTO t1 VALUES ('E4','Don',12,'Deale');
INSERT INTO t1 VALUES ('E5','Ed',13,'Akron');
INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale');
INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna');
INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa');
INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale');
INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna');
INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale');
INSERT INTO t3 VALUES  ('E1','P1',40);
INSERT INTO t3 VALUES  ('E1','P2',20);
INSERT INTO t3 VALUES  ('E1','P3',80);
INSERT INTO t3 VALUES  ('E1','P4',20);
INSERT INTO t3 VALUES  ('E1','P5',12);
INSERT INTO t3 VALUES  ('E1','P6',12);
INSERT INTO t3 VALUES  ('E2','P1',40);
INSERT INTO t3 VALUES  ('E2','P2',80);
INSERT INTO t3 VALUES  ('E3','P2',20);
INSERT INTO t3 VALUES  ('E4','P2',20);
INSERT INTO t3 VALUES  ('E4','P4',40);
INSERT INTO t3 VALUES  ('E4','P5',80);
SET @old_optimizer_switch = @@session.optimizer_switch;
SET @old_join_cache_level = @@session.join_cache_level;
SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on';
SET SESSION join_cache_level = 1;
CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM);
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
262 263 264
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
265 266 267 268 269 270 271 272 273 274 275 276
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
   (SELECT EMPNUM
    FROM t3
    WHERE PNUM IN
       (SELECT PNUM
        FROM t2
        WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
277 278 279
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
280 281 282
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
283 284 285
1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
286 287 288 289 290 291 292 293 294 295 296 297 298 299
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
CREATE INDEX t1_IDX ON t1(EMPNUM);
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
300 301 302
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
303 304 305 306 307 308 309 310 311 312 313 314
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
   (SELECT EMPNUM
    FROM t3
    WHERE PNUM IN
       (SELECT PNUM
        FROM t2
        WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
315 316 317
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
318 319 320
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	t1_IDX	NULL	NULL	NULL	5	
321 322 323
1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
324 325 326 327 328 329 330 331 332 333 334 335 336
DEALLOCATE PREPARE stmt;
DROP INDEX t1_IDX ON t1;
EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
(SELECT EMPNUM
FROM t3
WHERE PNUM IN
(SELECT PNUM
FROM t2
WHERE PTYPE = 'Design'));
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
337 338 339
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
340 341 342 343 344 345 346 347 348 349 350 351
PREPARE stmt FROM "EXPLAIN SELECT EMPNAME
FROM t1
WHERE EMPNUM IN
   (SELECT EMPNUM
    FROM t3
    WHERE PNUM IN
       (SELECT PNUM
        FROM t2
        WHERE PTYPE = 'Design'))";
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
352 353 354
1	PRIMARY	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
355 356 357
EXECUTE stmt;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
358 359 360
1	SIMPLE	subselect2	eq_ref	unique_key	unique_key	3	func	1	
2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer
361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
DEALLOCATE PREPARE stmt;
SET SESSION optimizer_switch = @old_optimizer_switch;
SET SESSION join_cache_level = @old_join_cache_level;
DROP TABLE t1, t2, t3;
#
# BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing
#
CREATE TABLE t1 (
i1_key INT,
i2 INT,
i3 INT,
KEY i1_index (i1_key)
);
INSERT INTO t1 VALUES (9,1,2), (9,2,1);
CREATE TABLE t2 (
pk INT NOT NULL,
i1 INT,
PRIMARY KEY (pk)
);
INSERT INTO t2 VALUES (9,1);
# Enable Index condition pushdown
SELECT @old_icp:=@@engine_condition_pushdown;
@old_icp:=@@engine_condition_pushdown
#
SET SESSION engine_condition_pushdown = 'ON';

SELECT pk
FROM t2
WHERE 
pk IN ( 
SELECT i1_key
FROM t1
WHERE t1.i2 < t1.i3 XOR t2.i1 > 1
ORDER BY t1.i2 desc);
pk
9
# Restore old value for Index condition pushdown
SET SESSION engine_condition_pushdown=@old_icp;
DROP TABLE t1,t2;
400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438
#
# LP BUG#641203 Query returns rows where no result is expected (impossible WHERE)
#
CREATE TABLE t1 (c1 varchar(1) DEFAULT NULL);
CREATE TABLE t2 (c1 varchar(1) DEFAULT NULL);
INSERT INTO t2 VALUES ('k'), ('d');
CREATE TABLE t3 (c1 varchar(1) DEFAULT NULL);
INSERT INTO t3 VALUES ('a'), ('b'), ('c');
CREATE TABLE t4 (c1 varchar(1) primary key);
INSERT INTO t4 VALUES ('k'), ('d');
EXPLAIN
SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
c1	c1
EXPLAIN
SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT * FROM t2 LEFT JOIN t1 ON t1.c1 WHERE 's' IN (SELECT c1 FROM t2);
c1	c1
EXPLAIN
SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT * FROM (t2 LEFT JOIN t1 ON t1.c1) LEFT JOIN t3 on t3.c1 WHERE 's' IN (SELECT c1 FROM t2);
c1	c1	c1
EXPLAIN
SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
SELECT * FROM t4 LEFT JOIN t2 ON t4.c1 WHERE 's' IN (SELECT c1 FROM t2);
c1	c1
drop table t1, t2, t3, t4;
439
#
unknown's avatar
unknown committed
440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458
# LP BUG#675981 Assertion `cache != __null' failed in sub_select_cache()
#               on EXPLAIN
#
CREATE TABLE t1 (f1 int,f2 int) ;
INSERT IGNORE INTO t1 VALUES ('2','5'),('2',NULL);
CREATE TABLE t2 (f1 int, f5 int) ;
INSERT IGNORE INTO t2 VALUES (1,0);
CREATE TABLE t3 (f4 int) ;
INSERT IGNORE INTO t3 VALUES (0),(0);
set @@optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';
EXPLAIN
SELECT * FROM t2
WHERE f1 IN (SELECT t1.f2 FROM t1 JOIN t3 ON t3.f4);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
2	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer
drop table t1, t2, t3;
#
unknown's avatar
unknown committed
459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490
# LP BUG#680005 Second assertion `cache != __null' failed in
#                sub_select_cache() on EXPLAIN
#
CREATE TABLE t1 (f1 int,f2 int,f4 int,f6 int,KEY (f4)) ;
INSERT IGNORE INTO t1 VALUES
('1','5','1','0'),('2','1','1','0'),('2','2','2','0'),('0',NULL,'0','0'),
('2','1','2','0'),('2','0','0','0'),('2','2','2','0'),('2','8','2','0'),
('2','7','2','0'),('2','5','2','0'),('2',NULL,'1','0');
CREATE TABLE t2 (f3 int) ;
INSERT IGNORE INTO t2 VALUES ('7');
CREATE TABLE t3 (f3 int) ;
INSERT IGNORE INTO t3 VALUES ('2');
EXPLAIN
SELECT t1.f4
FROM t2 JOIN t1 ON t1.f6
WHERE
( t1.f2 ) IN (SELECT SUBQUERY2_t1.f3
FROM t3 AS SUBQUERY2_t1
JOIN
(t1 AS SUBQUERY2_t2
JOIN
t1 AS SUBQUERY2_t3 ON SUBQUERY2_t3.f1)
ON SUBQUERY2_t3.f2)
GROUP BY t1.f4 ORDER BY t1.f1 LIMIT 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	Using temporary; Using filesort
1	PRIMARY	t1	index	NULL	f4	5	NULL	10	Using where
2	DEPENDENT SUBQUERY	SUBQUERY2_t1	system	NULL	NULL	NULL	NULL	1	
2	DEPENDENT SUBQUERY	SUBQUERY2_t2	index	NULL	f4	5	NULL	11	Using where; Using index
2	DEPENDENT SUBQUERY	SUBQUERY2_t3	ALL	NULL	NULL	NULL	NULL	11	Using where; Using join buffer
drop table t1, t2, t3;
#
491 492 493 494 495 496 497 498 499 500 501 502 503 504
# BUG#52317: Assertion failing in Field_varstring::store() 
# 	    at field.cc:6833
#
CREATE TABLE t1 (i INTEGER);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (i INTEGER, KEY k(i));
INSERT INTO t2 VALUES (1), (2);
EXPLAIN  
SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
2	DEPENDENT SUBQUERY	t2	index_subquery	k	k	5	const	2	Using index
DROP TABLE t2;
DROP TABLE t1;