1set @save_storage_engine= @@default_storage_engine; 2set default_storage_engine=MyISAM; 3set end_markers_in_json=on; 4# new "FORMAT" keyword doesn't conflict with the FORMAT() function name: 5SELECT FORMAT(1, 2), FORMAT(1, 2, 3); 6FORMAT(1, 2) FORMAT(1, 2, 3) 71.00 1.00 8Warnings: 9Warning 1649 Unknown locale: '3' 10# new "FORMAT" keyword is a valid identifier: 11SET @FORMAT=10; 12SELECT @FORMAT; 13@FORMAT 1410 15CREATE TABLE t1 (format INT); 16SELECT format FROM t1; 17format 18DROP TABLE t1; 19# different ways of format name writing: 20EXPLAIN FORMAT=traditional SELECT 1; 21id select_type table partitions type possible_keys key key_len ref rows filtered Extra 221 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 23Warnings: 24Note 1003 /* select#1 */ select 1 AS `1` 25EXPLAIN FORMAT='TrAdItIoNaL' SELECT 1; 26id select_type table partitions type possible_keys key key_len ref rows filtered Extra 271 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 28Warnings: 29Note 1003 /* select#1 */ select 1 AS `1` 30EXPLAIN FORMAT=JSON SELECT 1; 31EXPLAIN 32{ 33 "query_block": { 34 "select_id": 1, 35 "message": "No tables used" 36 } /* query_block */ 37} 38Warnings: 39Note 1003 /* select#1 */ select 1 AS `1` 40EXPLAIN FORMAT=foo SELECT 1; 41ERROR HY000: Unknown EXPLAIN format name: 'foo' 42# various EXPLAIN output 43CREATE TABLE t1 (i INT); 44CREATE TABLE t2 (i INT); 45CREATE TABLE t3 (i INT); 46CREATE TABLE t4 (i INT); 47# no end markers in JSON: 48set end_markers_in_json=off; 49EXPLAIN FORMAT=JSON SELECT * FROM t1; 50EXPLAIN 51{ 52 "query_block": { 53 "select_id": 1, 54 "message": "no matching row in const table" 55 } 56} 57Warnings: 58Note 1003 /* select#1 */ select NULL AS `i` from `test`.`t1` 59set end_markers_in_json=on; 60EXPLAIN INSERT INTO t1 VALUES (10); 61id select_type table partitions type possible_keys key key_len ref rows filtered Extra 621 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL 63EXPLAIN FORMAT=JSON INSERT INTO t1 VALUES (10); 64EXPLAIN 65{ 66 "query_block": { 67 "select_id": 1, 68 "table": { 69 "insert": true, 70 "table_name": "t1", 71 "access_type": "ALL" 72 } /* table */ 73 } /* query_block */ 74} 75EXPLAIN SELECT * FROM t1; 76id select_type table partitions type possible_keys key key_len ref rows filtered Extra 771 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 78Warnings: 79Note 1003 /* select#1 */ select NULL AS `i` from `test`.`t1` 80PREPARE stmt FROM 'EXPLAIN FORMAT=JSON SELECT * FROM t1'; 81EXECUTE stmt; 82EXPLAIN 83{ 84 "query_block": { 85 "select_id": 1, 86 "message": "no matching row in const table" 87 } /* query_block */ 88} 89Warnings: 90Note 1003 /* select#1 */ select NULL AS `i` from `test`.`t1` 91EXECUTE stmt; 92EXPLAIN 93{ 94 "query_block": { 95 "select_id": 1, 96 "message": "no matching row in const table" 97 } /* query_block */ 98} 99Warnings: 100Note 1003 /* select#1 */ select NULL AS `i` from `test`.`t1` 101INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7); 102INSERT INTO t2 VALUES (1), (2); 103# Check materialized derived table 104set @optimizer_switch_saved= @@optimizer_switch; 105set optimizer_switch='derived_merge=off'; 106EXPLAIN 107SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT a1.i FROM (SELECT * FROM t1) a1, t2) a2) a3) a4; 108id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1091 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 14 100.00 NULL 1102 DERIVED <derived3> NULL ALL NULL NULL NULL NULL 14 100.00 NULL 1113 DERIVED <derived4> NULL ALL NULL NULL NULL NULL 14 100.00 NULL 1124 DERIVED t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1134 DERIVED <derived5> NULL ALL NULL NULL NULL NULL 7 100.00 Using join buffer (Block Nested Loop) 1145 DERIVED t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 115Warnings: 116Note 1003 /* select#1 */ select `a4`.`i` AS `i` from (/* select#2 */ select `a3`.`i` AS `i` from (/* select#3 */ select `a2`.`i` AS `i` from (/* select#4 */ select `a1`.`i` AS `i` from (/* select#5 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) `a1` join `test`.`t2`) `a2`) `a3`) `a4` 117EXPLAIN FORMAT=JSON 118SELECT * FROM (SELECT * FROM (SELECT * FROM (SELECT a1.i FROM (SELECT * FROM t1) a1, t2) a2) a3) a4; 119EXPLAIN 120{ 121 "query_block": { 122 "select_id": 1, 123 "cost_info": { 124 "query_cost": "13.50" 125 } /* cost_info */, 126 "table": { 127 "table_name": "a4", 128 "access_type": "ALL", 129 "rows_examined_per_scan": 14, 130 "rows_produced_per_join": 14, 131 "filtered": "100.00", 132 "cost_info": { 133 "read_cost": "10.70", 134 "eval_cost": "2.80", 135 "prefix_cost": "13.50", 136 "data_read_per_join": "224" 137 } /* cost_info */, 138 "used_columns": [ 139 "i" 140 ] /* used_columns */, 141 "materialized_from_subquery": { 142 "using_temporary_table": true, 143 "dependent": false, 144 "cacheable": true, 145 "query_block": { 146 "select_id": 2, 147 "cost_info": { 148 "query_cost": "13.50" 149 } /* cost_info */, 150 "table": { 151 "table_name": "a3", 152 "access_type": "ALL", 153 "rows_examined_per_scan": 14, 154 "rows_produced_per_join": 14, 155 "filtered": "100.00", 156 "cost_info": { 157 "read_cost": "10.70", 158 "eval_cost": "2.80", 159 "prefix_cost": "13.50", 160 "data_read_per_join": "224" 161 } /* cost_info */, 162 "used_columns": [ 163 "i" 164 ] /* used_columns */, 165 "materialized_from_subquery": { 166 "using_temporary_table": true, 167 "dependent": false, 168 "cacheable": true, 169 "query_block": { 170 "select_id": 3, 171 "cost_info": { 172 "query_cost": "13.50" 173 } /* cost_info */, 174 "table": { 175 "table_name": "a2", 176 "access_type": "ALL", 177 "rows_examined_per_scan": 14, 178 "rows_produced_per_join": 14, 179 "filtered": "100.00", 180 "cost_info": { 181 "read_cost": "10.70", 182 "eval_cost": "2.80", 183 "prefix_cost": "13.50", 184 "data_read_per_join": "224" 185 } /* cost_info */, 186 "used_columns": [ 187 "i" 188 ] /* used_columns */, 189 "materialized_from_subquery": { 190 "using_temporary_table": true, 191 "dependent": false, 192 "cacheable": true, 193 "query_block": { 194 "select_id": 4, 195 "cost_info": { 196 "query_cost": "15.55" 197 } /* cost_info */, 198 "nested_loop": [ 199 { 200 "table": { 201 "table_name": "t2", 202 "access_type": "ALL", 203 "rows_examined_per_scan": 2, 204 "rows_produced_per_join": 2, 205 "filtered": "100.00", 206 "cost_info": { 207 "read_cost": "2.00", 208 "eval_cost": "0.40", 209 "prefix_cost": "2.40", 210 "data_read_per_join": "16" 211 } /* cost_info */ 212 } /* table */ 213 }, 214 { 215 "table": { 216 "table_name": "a1", 217 "access_type": "ALL", 218 "rows_examined_per_scan": 7, 219 "rows_produced_per_join": 14, 220 "filtered": "100.00", 221 "using_join_buffer": "Block Nested Loop", 222 "cost_info": { 223 "read_cost": "10.35", 224 "eval_cost": "2.80", 225 "prefix_cost": "15.55", 226 "data_read_per_join": "224" 227 } /* cost_info */, 228 "used_columns": [ 229 "i" 230 ] /* used_columns */, 231 "materialized_from_subquery": { 232 "using_temporary_table": true, 233 "dependent": false, 234 "cacheable": true, 235 "query_block": { 236 "select_id": 5, 237 "cost_info": { 238 "query_cost": "3.41" 239 } /* cost_info */, 240 "table": { 241 "table_name": "t1", 242 "access_type": "ALL", 243 "rows_examined_per_scan": 7, 244 "rows_produced_per_join": 7, 245 "filtered": "100.00", 246 "cost_info": { 247 "read_cost": "2.01", 248 "eval_cost": "1.40", 249 "prefix_cost": "3.41", 250 "data_read_per_join": "56" 251 } /* cost_info */, 252 "used_columns": [ 253 "i" 254 ] /* used_columns */ 255 } /* table */ 256 } /* query_block */ 257 } /* materialized_from_subquery */ 258 } /* table */ 259 } 260 ] /* nested_loop */ 261 } /* query_block */ 262 } /* materialized_from_subquery */ 263 } /* table */ 264 } /* query_block */ 265 } /* materialized_from_subquery */ 266 } /* table */ 267 } /* query_block */ 268 } /* materialized_from_subquery */ 269 } /* table */ 270 } /* query_block */ 271} 272Warnings: 273Note 1003 /* select#1 */ select `a4`.`i` AS `i` from (/* select#2 */ select `a3`.`i` AS `i` from (/* select#3 */ select `a2`.`i` AS `i` from (/* select#4 */ select `a1`.`i` AS `i` from (/* select#5 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) `a1` join `test`.`t2`) `a2`) `a3`) `a4` 274set optimizer_switch= @optimizer_switch_saved; 275# subquery in WHERE 276EXPLAIN SELECT * FROM t1 WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND()); 277id select_type table partitions type possible_keys key key_len ref rows filtered Extra 2781 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 2792 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 280Warnings: 281Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 282Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) 283EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND()); 284EXPLAIN 285{ 286 "query_block": { 287 "select_id": 1, 288 "cost_info": { 289 "query_cost": "3.41" 290 } /* cost_info */, 291 "table": { 292 "table_name": "t1", 293 "access_type": "ALL", 294 "rows_examined_per_scan": 7, 295 "rows_produced_per_join": 7, 296 "filtered": "100.00", 297 "cost_info": { 298 "read_cost": "2.01", 299 "eval_cost": "1.40", 300 "prefix_cost": "3.41", 301 "data_read_per_join": "56" 302 } /* cost_info */, 303 "used_columns": [ 304 "i" 305 ] /* used_columns */, 306 "attached_condition": "<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`))))", 307 "attached_subqueries": [ 308 { 309 "dependent": true, 310 "cacheable": false, 311 "query_block": { 312 "select_id": 2, 313 "cost_info": { 314 "query_cost": "2.40" 315 } /* cost_info */, 316 "table": { 317 "table_name": "t2", 318 "access_type": "ALL", 319 "rows_examined_per_scan": 2, 320 "rows_produced_per_join": 1, 321 "filtered": "50.00", 322 "cost_info": { 323 "read_cost": "2.00", 324 "eval_cost": "0.20", 325 "prefix_cost": "2.40", 326 "data_read_per_join": "8" 327 } /* cost_info */, 328 "used_columns": [ 329 "i" 330 ] /* used_columns */, 331 "attached_condition": "((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`))" 332 } /* table */ 333 } /* query_block */ 334 } 335 ] /* attached_subqueries */ 336 } /* table */ 337 } /* query_block */ 338} 339Warnings: 340Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 341Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) 342# two subqueries in WHERE 343EXPLAIN SELECT * FROM t1 344WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND()) 345OR i IN (SELECT i FROM t4 ORDER BY RAND()); 346id select_type table partitions type possible_keys key key_len ref rows filtered Extra 3471 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using where 3483 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 3492 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 350Warnings: 351Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 352Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = NULL)))) 353EXPLAIN FORMAT=JSON SELECT * FROM t1 354WHERE i IN (SELECT i FROM t2 WHERE t1.i = 10 ORDER BY RAND()) 355OR i IN (SELECT i FROM t4 ORDER BY RAND()); 356EXPLAIN 357{ 358 "query_block": { 359 "select_id": 1, 360 "cost_info": { 361 "query_cost": "3.41" 362 } /* cost_info */, 363 "table": { 364 "table_name": "t1", 365 "access_type": "ALL", 366 "rows_examined_per_scan": 7, 367 "rows_produced_per_join": 7, 368 "filtered": "100.00", 369 "cost_info": { 370 "read_cost": "2.01", 371 "eval_cost": "1.40", 372 "prefix_cost": "3.41", 373 "data_read_per_join": "56" 374 } /* cost_info */, 375 "used_columns": [ 376 "i" 377 ] /* used_columns */, 378 "attached_condition": "(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = NULL))))", 379 "attached_subqueries": [ 380 { 381 "dependent": true, 382 "cacheable": false, 383 "query_block": { 384 "select_id": 3, 385 "message": "no matching row in const table" 386 } /* query_block */ 387 }, 388 { 389 "dependent": true, 390 "cacheable": false, 391 "query_block": { 392 "select_id": 2, 393 "cost_info": { 394 "query_cost": "2.40" 395 } /* cost_info */, 396 "table": { 397 "table_name": "t2", 398 "access_type": "ALL", 399 "rows_examined_per_scan": 2, 400 "rows_produced_per_join": 1, 401 "filtered": "50.00", 402 "cost_info": { 403 "read_cost": "2.00", 404 "eval_cost": "0.20", 405 "prefix_cost": "2.40", 406 "data_read_per_join": "8" 407 } /* cost_info */, 408 "used_columns": [ 409 "i" 410 ] /* used_columns */, 411 "attached_condition": "((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`))" 412 } /* table */ 413 } /* query_block */ 414 } 415 ] /* attached_subqueries */ 416 } /* table */ 417 } /* query_block */ 418} 419Warnings: 420Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 421Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t1`.`i` = 10) and (<cache>(`test`.`t1`.`i`) = `test`.`t2`.`i`)))) or <in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(`test`.`t1`.`i`) = NULL)))) 422# simple UNION 423EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; 424id select_type table partitions type possible_keys key key_len ref rows filtered Extra 4251 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 4262 UNION t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 4273 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 428NULL UNION RESULT <union1,2,3> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary 429Warnings: 430Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` union /* select#3 */ select NULL AS `i` from `test`.`t3` 431EXPLAIN FORMAT=JSON SELECT * FROM t1 UNION SELECT * FROM t2 UNION SELECT * FROM t3; 432EXPLAIN 433{ 434 "query_block": { 435 "union_result": { 436 "using_temporary_table": true, 437 "table_name": "<union1,2,3>", 438 "access_type": "ALL", 439 "query_specifications": [ 440 { 441 "dependent": false, 442 "cacheable": true, 443 "query_block": { 444 "select_id": 1, 445 "cost_info": { 446 "query_cost": "3.41" 447 } /* cost_info */, 448 "table": { 449 "table_name": "t1", 450 "access_type": "ALL", 451 "rows_examined_per_scan": 7, 452 "rows_produced_per_join": 7, 453 "filtered": "100.00", 454 "cost_info": { 455 "read_cost": "2.01", 456 "eval_cost": "1.40", 457 "prefix_cost": "3.41", 458 "data_read_per_join": "56" 459 } /* cost_info */, 460 "used_columns": [ 461 "i" 462 ] /* used_columns */ 463 } /* table */ 464 } /* query_block */ 465 }, 466 { 467 "dependent": false, 468 "cacheable": true, 469 "query_block": { 470 "select_id": 2, 471 "cost_info": { 472 "query_cost": "2.40" 473 } /* cost_info */, 474 "table": { 475 "table_name": "t2", 476 "access_type": "ALL", 477 "rows_examined_per_scan": 2, 478 "rows_produced_per_join": 2, 479 "filtered": "100.00", 480 "cost_info": { 481 "read_cost": "2.00", 482 "eval_cost": "0.40", 483 "prefix_cost": "2.40", 484 "data_read_per_join": "16" 485 } /* cost_info */, 486 "used_columns": [ 487 "i" 488 ] /* used_columns */ 489 } /* table */ 490 } /* query_block */ 491 }, 492 { 493 "dependent": false, 494 "cacheable": true, 495 "query_block": { 496 "select_id": 3, 497 "message": "no matching row in const table" 498 } /* query_block */ 499 } 500 ] /* query_specifications */ 501 } /* union_result */ 502 } /* query_block */ 503} 504Warnings: 505Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` union /* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2` union /* select#3 */ select NULL AS `i` from `test`.`t3` 506# more complex UNION 507EXPLAIN (SELECT t1.i FROM t1 JOIN t2) UNION ALL (SELECT * FROM t3 WHERE i IN (SELECT i FROM t4 ORDER BY RAND())); 508id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5091 PRIMARY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 5101 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using join buffer (Block Nested Loop) 5112 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5123 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 513Warnings: 514Note 1003 (/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t2`) union all (/* select#2 */ select NULL AS `i` from `test`.`t3` where <in_optimizer>(NULL,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(NULL) = NULL)))) 515EXPLAIN FORMAT=JSON (SELECT t1.i FROM t1 JOIN t2) UNION ALL (SELECT * FROM t3 WHERE i IN (SELECT i FROM t4 ORDER BY RAND())); 516EXPLAIN 517{ 518 "query_block": { 519 "union_result": { 520 "using_temporary_table": false, 521 "query_specifications": [ 522 { 523 "dependent": false, 524 "cacheable": true, 525 "query_block": { 526 "select_id": 1, 527 "cost_info": { 528 "query_cost": "7.21" 529 } /* cost_info */, 530 "nested_loop": [ 531 { 532 "table": { 533 "table_name": "t2", 534 "access_type": "ALL", 535 "rows_examined_per_scan": 2, 536 "rows_produced_per_join": 2, 537 "filtered": "100.00", 538 "cost_info": { 539 "read_cost": "2.00", 540 "eval_cost": "0.40", 541 "prefix_cost": "2.40", 542 "data_read_per_join": "16" 543 } /* cost_info */ 544 } /* table */ 545 }, 546 { 547 "table": { 548 "table_name": "t1", 549 "access_type": "ALL", 550 "rows_examined_per_scan": 7, 551 "rows_produced_per_join": 14, 552 "filtered": "100.00", 553 "using_join_buffer": "Block Nested Loop", 554 "cost_info": { 555 "read_cost": "2.01", 556 "eval_cost": "2.80", 557 "prefix_cost": "7.22", 558 "data_read_per_join": "112" 559 } /* cost_info */, 560 "used_columns": [ 561 "i" 562 ] /* used_columns */ 563 } /* table */ 564 } 565 ] /* nested_loop */ 566 } /* query_block */ 567 }, 568 { 569 "dependent": false, 570 "cacheable": true, 571 "query_block": { 572 "select_id": 2, 573 "message": "no matching row in const table", 574 "optimized_away_subqueries": [ 575 { 576 "dependent": true, 577 "cacheable": false, 578 "query_block": { 579 "select_id": 3, 580 "message": "no matching row in const table" 581 } /* query_block */ 582 } 583 ] /* optimized_away_subqueries */ 584 } /* query_block */ 585 } 586 ] /* query_specifications */ 587 } /* union_result */ 588 } /* query_block */ 589} 590Warnings: 591Note 1003 (/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` join `test`.`t2`) union all (/* select#2 */ select NULL AS `i` from `test`.`t3` where <in_optimizer>(NULL,<exists>(/* select#3 */ select 1 from `test`.`t4` where (<cache>(NULL) = NULL)))) 592# UNION with subquery in outer ORDER BY 593EXPLAIN (SELECT * FROM t1) UNION (SELECT * FROM t2) ORDER BY (SELECT i LIMIT 1); 594id select_type table partitions type possible_keys key key_len ref rows filtered Extra 5951 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 5962 UNION t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 597NULL UNION RESULT <union1,2> NULL ALL NULL NULL NULL NULL NULL NULL Using temporary; Using filesort 5983 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 599Warnings: 600Note 1276 Field or reference 'i' of SELECT #3 was resolved in SELECT #1 601Note 1003 (/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) union (/* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2`) order by (/* select#3 */ select `i` limit 1) 602EXPLAIN FORMAT=JSON (SELECT * FROM t1) UNION (SELECT * FROM t2) ORDER BY (SELECT i LIMIT 1); 603EXPLAIN 604{ 605 "query_block": { 606 "ordering_operation": { 607 "using_filesort": true, 608 "union_result": { 609 "using_temporary_table": true, 610 "table_name": "<union1,2>", 611 "access_type": "ALL", 612 "query_specifications": [ 613 { 614 "dependent": false, 615 "cacheable": true, 616 "query_block": { 617 "select_id": 1, 618 "cost_info": { 619 "query_cost": "3.41" 620 } /* cost_info */, 621 "table": { 622 "table_name": "t1", 623 "access_type": "ALL", 624 "rows_examined_per_scan": 7, 625 "rows_produced_per_join": 7, 626 "filtered": "100.00", 627 "cost_info": { 628 "read_cost": "2.01", 629 "eval_cost": "1.40", 630 "prefix_cost": "3.41", 631 "data_read_per_join": "56" 632 } /* cost_info */, 633 "used_columns": [ 634 "i" 635 ] /* used_columns */ 636 } /* table */ 637 } /* query_block */ 638 }, 639 { 640 "dependent": false, 641 "cacheable": true, 642 "query_block": { 643 "select_id": 2, 644 "cost_info": { 645 "query_cost": "2.40" 646 } /* cost_info */, 647 "table": { 648 "table_name": "t2", 649 "access_type": "ALL", 650 "rows_examined_per_scan": 2, 651 "rows_produced_per_join": 2, 652 "filtered": "100.00", 653 "cost_info": { 654 "read_cost": "2.00", 655 "eval_cost": "0.40", 656 "prefix_cost": "2.40", 657 "data_read_per_join": "16" 658 } /* cost_info */, 659 "used_columns": [ 660 "i" 661 ] /* used_columns */ 662 } /* table */ 663 } /* query_block */ 664 } 665 ] /* query_specifications */ 666 } /* union_result */, 667 "order_by_subqueries": [ 668 { 669 "dependent": true, 670 "cacheable": false, 671 "query_block": { 672 "select_id": 3, 673 "message": "No tables used" 674 } /* query_block */ 675 } 676 ] /* order_by_subqueries */ 677 } /* ordering_operation */ 678 } /* query_block */ 679} 680Warnings: 681Note 1276 Field or reference 'i' of SELECT #3 was resolved in SELECT #1 682Note 1003 (/* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1`) union (/* select#2 */ select `test`.`t2`.`i` AS `i` from `test`.`t2`) order by (/* select#3 */ select `i` limit 1) 683# optimizer-time subquery 684EXPLAIN SELECT * FROM t1 ORDER BY (SELECT LENGTH(1) FROM t2 LIMIT 1); 685id select_type table partitions type possible_keys key key_len ref rows filtered Extra 6861 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 6872 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 688Warnings: 689Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select length(1) from `test`.`t2` limit 1) 690EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY (SELECT LENGTH(1) FROM t2 LIMIT 1); 691EXPLAIN 692{ 693 "query_block": { 694 "select_id": 1, 695 "cost_info": { 696 "query_cost": "3.41" 697 } /* cost_info */, 698 "ordering_operation": { 699 "using_filesort": false, 700 "table": { 701 "table_name": "t1", 702 "access_type": "ALL", 703 "rows_examined_per_scan": 7, 704 "rows_produced_per_join": 7, 705 "filtered": "100.00", 706 "cost_info": { 707 "read_cost": "2.01", 708 "eval_cost": "1.40", 709 "prefix_cost": "3.41", 710 "data_read_per_join": "56" 711 } /* cost_info */, 712 "used_columns": [ 713 "i" 714 ] /* used_columns */ 715 } /* table */, 716 "optimized_away_subqueries": [ 717 { 718 "dependent": false, 719 "cacheable": true, 720 "query_block": { 721 "select_id": 2, 722 "cost_info": { 723 "query_cost": "2.40" 724 } /* cost_info */, 725 "table": { 726 "table_name": "t2", 727 "access_type": "ALL", 728 "rows_examined_per_scan": 2, 729 "rows_produced_per_join": 2, 730 "filtered": "100.00", 731 "cost_info": { 732 "read_cost": "2.00", 733 "eval_cost": "0.40", 734 "prefix_cost": "2.40", 735 "data_read_per_join": "16" 736 } /* cost_info */ 737 } /* table */ 738 } /* query_block */ 739 } 740 ] /* optimized_away_subqueries */ 741 } /* ordering_operation */ 742 } /* query_block */ 743} 744Warnings: 745Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select length(1) from `test`.`t2` limit 1) 746# subquery in the HAVING clause 747EXPLAIN SELECT * FROM t1 HAVING i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);; 748id select_type table partitions type possible_keys key key_len ref rows filtered Extra 7491 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 7503 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 7512 SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 752Warnings: 753Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` having (<not>((`test`.`t1`.`i` <= <max>(/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`))) or <not>((`test`.`t1`.`i` >= <min>(/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`)))) 754EXPLAIN FORMAT=JSON SELECT * FROM t1 HAVING i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);; 755EXPLAIN 756{ 757 "query_block": { 758 "select_id": 1, 759 "cost_info": { 760 "query_cost": "3.41" 761 } /* cost_info */, 762 "table": { 763 "table_name": "t1", 764 "access_type": "ALL", 765 "rows_examined_per_scan": 7, 766 "rows_produced_per_join": 7, 767 "filtered": "100.00", 768 "cost_info": { 769 "read_cost": "2.01", 770 "eval_cost": "1.40", 771 "prefix_cost": "3.41", 772 "data_read_per_join": "56" 773 } /* cost_info */, 774 "used_columns": [ 775 "i" 776 ] /* used_columns */ 777 } /* table */, 778 "having_subqueries": [ 779 { 780 "dependent": false, 781 "cacheable": true, 782 "query_block": { 783 "select_id": 3, 784 "cost_info": { 785 "query_cost": "2.40" 786 } /* cost_info */, 787 "table": { 788 "table_name": "t2", 789 "access_type": "ALL", 790 "rows_examined_per_scan": 2, 791 "rows_produced_per_join": 2, 792 "filtered": "100.00", 793 "cost_info": { 794 "read_cost": "2.00", 795 "eval_cost": "0.40", 796 "prefix_cost": "2.40", 797 "data_read_per_join": "16" 798 } /* cost_info */, 799 "used_columns": [ 800 "i" 801 ] /* used_columns */ 802 } /* table */ 803 } /* query_block */ 804 }, 805 { 806 "dependent": false, 807 "cacheable": true, 808 "query_block": { 809 "select_id": 2, 810 "cost_info": { 811 "query_cost": "2.40" 812 } /* cost_info */, 813 "table": { 814 "table_name": "t2", 815 "access_type": "ALL", 816 "rows_examined_per_scan": 2, 817 "rows_produced_per_join": 2, 818 "filtered": "100.00", 819 "cost_info": { 820 "read_cost": "2.00", 821 "eval_cost": "0.40", 822 "prefix_cost": "2.40", 823 "data_read_per_join": "16" 824 } /* cost_info */, 825 "used_columns": [ 826 "i" 827 ] /* used_columns */ 828 } /* table */ 829 } /* query_block */ 830 } 831 ] /* having_subqueries */ 832 } /* query_block */ 833} 834Warnings: 835Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` having (<not>((`test`.`t1`.`i` <= <max>(/* select#2 */ select `test`.`t2`.`i` from `test`.`t2`))) or <not>((`test`.`t1`.`i` >= <min>(/* select#3 */ select `test`.`t2`.`i` from `test`.`t2`)))) 836# subquery in the GROUP BY clause 837EXPLAIN SELECT * FROM t1 GROUP BY i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);; 838id select_type table partitions type possible_keys key key_len ref rows filtered Extra 8391 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 8403 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 8412 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 842Warnings: 843Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))) or <not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true))))) 844EXPLAIN FORMAT=JSON SELECT * FROM t1 GROUP BY i > ALL (SELECT i FROM t2) OR i < ALL (SELECT i FROM t2);; 845EXPLAIN 846{ 847 "query_block": { 848 "select_id": 1, 849 "cost_info": { 850 "query_cost": "10.41" 851 } /* cost_info */, 852 "grouping_operation": { 853 "using_temporary_table": true, 854 "using_filesort": true, 855 "cost_info": { 856 "sort_cost": "7.00" 857 } /* cost_info */, 858 "table": { 859 "table_name": "t1", 860 "access_type": "ALL", 861 "rows_examined_per_scan": 7, 862 "rows_produced_per_join": 7, 863 "filtered": "100.00", 864 "cost_info": { 865 "read_cost": "2.01", 866 "eval_cost": "1.40", 867 "prefix_cost": "3.41", 868 "data_read_per_join": "56" 869 } /* cost_info */, 870 "used_columns": [ 871 "i" 872 ] /* used_columns */ 873 } /* table */, 874 "group_by_subqueries": [ 875 { 876 "dependent": true, 877 "cacheable": false, 878 "query_block": { 879 "select_id": 3, 880 "cost_info": { 881 "query_cost": "2.40" 882 } /* cost_info */, 883 "table": { 884 "table_name": "t2", 885 "access_type": "ALL", 886 "rows_examined_per_scan": 2, 887 "rows_produced_per_join": 2, 888 "filtered": "100.00", 889 "cost_info": { 890 "read_cost": "2.00", 891 "eval_cost": "0.40", 892 "prefix_cost": "2.40", 893 "data_read_per_join": "16" 894 } /* cost_info */, 895 "used_columns": [ 896 "i" 897 ] /* used_columns */, 898 "attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true)" 899 } /* table */ 900 } /* query_block */ 901 }, 902 { 903 "dependent": true, 904 "cacheable": false, 905 "query_block": { 906 "select_id": 2, 907 "cost_info": { 908 "query_cost": "2.40" 909 } /* cost_info */, 910 "table": { 911 "table_name": "t2", 912 "access_type": "ALL", 913 "rows_examined_per_scan": 2, 914 "rows_produced_per_join": 2, 915 "filtered": "100.00", 916 "cost_info": { 917 "read_cost": "2.00", 918 "eval_cost": "0.40", 919 "prefix_cost": "2.40", 920 "data_read_per_join": "16" 921 } /* cost_info */, 922 "used_columns": [ 923 "i" 924 ] /* used_columns */, 925 "attached_condition": "<if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true)" 926 } /* table */ 927 } /* query_block */ 928 } 929 ] /* group_by_subqueries */ 930 } /* grouping_operation */ 931 } /* query_block */ 932} 933Warnings: 934Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (<not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#2 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) <= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true)))) or <not>(<in_optimizer>(`test`.`t1`.`i`,<exists>(/* select#3 */ select 1 from `test`.`t2` where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`i`) >= `test`.`t2`.`i`) or isnull(`test`.`t2`.`i`)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`i`), true))))) 935# subquery in the SELECT list 936EXPLAIN SELECT (SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1), i FROM t1;; 937id select_type table partitions type possible_keys key key_len ref rows filtered Extra 9381 PRIMARY t1 NULL ALL NULL NULL NULL NULL 7 100.00 NULL 9392 UNCACHEABLE SUBQUERY t1 NULL ALL NULL NULL NULL NULL 7 100.00 Using temporary; Using filesort 940Warnings: 941Note 1003 /* select#1 */ select (/* select#2 */ select (`test`.`t1`.`i` + 1) from `test`.`t1` order by rand() limit 1) AS `(SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1)`,`test`.`t1`.`i` AS `i` from `test`.`t1` 942EXPLAIN FORMAT=JSON SELECT (SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1), i FROM t1;; 943EXPLAIN 944{ 945 "query_block": { 946 "select_id": 1, 947 "cost_info": { 948 "query_cost": "3.41" 949 } /* cost_info */, 950 "table": { 951 "table_name": "t1", 952 "access_type": "ALL", 953 "rows_examined_per_scan": 7, 954 "rows_produced_per_join": 7, 955 "filtered": "100.00", 956 "cost_info": { 957 "read_cost": "2.01", 958 "eval_cost": "1.40", 959 "prefix_cost": "3.41", 960 "data_read_per_join": "56" 961 } /* cost_info */, 962 "used_columns": [ 963 "i" 964 ] /* used_columns */ 965 } /* table */, 966 "select_list_subqueries": [ 967 { 968 "dependent": false, 969 "cacheable": false, 970 "query_block": { 971 "select_id": 2, 972 "cost_info": { 973 "query_cost": "3.41" 974 } /* cost_info */, 975 "ordering_operation": { 976 "using_temporary_table": true, 977 "using_filesort": true, 978 "table": { 979 "table_name": "t1", 980 "access_type": "ALL", 981 "rows_examined_per_scan": 7, 982 "rows_produced_per_join": 7, 983 "filtered": "100.00", 984 "cost_info": { 985 "read_cost": "2.01", 986 "eval_cost": "1.40", 987 "prefix_cost": "3.41", 988 "data_read_per_join": "56" 989 } /* cost_info */, 990 "used_columns": [ 991 "i" 992 ] /* used_columns */ 993 } /* table */ 994 } /* ordering_operation */ 995 } /* query_block */ 996 } 997 ] /* select_list_subqueries */ 998 } /* query_block */ 999} 1000Warnings: 1001Note 1003 /* select#1 */ select (/* select#2 */ select (`test`.`t1`.`i` + 1) from `test`.`t1` order by rand() limit 1) AS `(SELECT i + 1 FROM t1 ORDER BY RAND() LIMIT 1)`,`test`.`t1`.`i` AS `i` from `test`.`t1` 1002DROP TABLE t1, t2, t3, t4; 1003# derived table that is optimized out 1004CREATE TABLE t1 (i INT); 1005EXPLAIN SELECT 1 FROM (SELECT 1 AS x FROM t1) tt WHERE x; 1006id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10071 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1008Warnings: 1009Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 1 1010EXPLAIN FORMAT= JSON SELECT 1 FROM (SELECT 1 AS x FROM t1) tt WHERE x; 1011EXPLAIN 1012{ 1013 "query_block": { 1014 "select_id": 1, 1015 "message": "no matching row in const table" 1016 } /* query_block */ 1017} 1018Warnings: 1019Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 1 1020DROP TABLE t1; 1021# complex subqueries 1022CREATE TABLE t1 (a INT, b INT); 1023CREATE TABLE t2 (c INT, d INT); 1024CREATE TABLE t3 (e INT); 1025CREATE TABLE t4 (f INT, g INT); 1026INSERT INTO t1 VALUES (1,10), (2,10); 1027INSERT INTO t2 VALUES (2,10), (2,20); 1028INSERT INTO t3 VALUES (10), (30); 1029INSERT INTO t4 VALUES (2,10), (2,10); 1030EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT c FROM t2 WHERE (SELECT e FROM t3) < SOME(SELECT e FROM t3 WHERE t1.b));; 1031id select_type table partitions type possible_keys key key_len ref rows filtered Extra 10321 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 10332 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 2 50.00 Using where 10344 DEPENDENT SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 Using where 10353 SUBQUERY t3 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 1036Warnings: 1037Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1 1038Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 1039EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE t1.a IN (SELECT c FROM t2 WHERE (SELECT e FROM t3) < SOME(SELECT e FROM t3 WHERE t1.b));; 1040EXPLAIN 1041{ 1042 "query_block": { 1043 "select_id": 1, 1044 "cost_info": { 1045 "query_cost": "2.40" 1046 } /* cost_info */, 1047 "table": { 1048 "table_name": "t1", 1049 "access_type": "ALL", 1050 "rows_examined_per_scan": 2, 1051 "rows_produced_per_join": 2, 1052 "filtered": "100.00", 1053 "cost_info": { 1054 "read_cost": "2.00", 1055 "eval_cost": "0.40", 1056 "prefix_cost": "2.40", 1057 "data_read_per_join": "32" 1058 } /* cost_info */, 1059 "used_columns": [ 1060 "a", 1061 "b" 1062 ] /* used_columns */, 1063 "attached_condition": "<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))))", 1064 "attached_subqueries": [ 1065 { 1066 "dependent": true, 1067 "cacheable": false, 1068 "query_block": { 1069 "select_id": 2, 1070 "cost_info": { 1071 "query_cost": "2.40" 1072 } /* cost_info */, 1073 "table": { 1074 "table_name": "t2", 1075 "access_type": "ALL", 1076 "rows_examined_per_scan": 2, 1077 "rows_produced_per_join": 1, 1078 "filtered": "50.00", 1079 "cost_info": { 1080 "read_cost": "2.00", 1081 "eval_cost": "0.20", 1082 "prefix_cost": "2.40", 1083 "data_read_per_join": "16" 1084 } /* cost_info */, 1085 "used_columns": [ 1086 "c" 1087 ] /* used_columns */, 1088 "attached_condition": "(<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`))", 1089 "attached_subqueries": [ 1090 { 1091 "dependent": true, 1092 "cacheable": false, 1093 "query_block": { 1094 "select_id": 4, 1095 "cost_info": { 1096 "query_cost": "2.40" 1097 } /* cost_info */, 1098 "table": { 1099 "table_name": "t3", 1100 "access_type": "ALL", 1101 "rows_examined_per_scan": 2, 1102 "rows_produced_per_join": 2, 1103 "filtered": "100.00", 1104 "cost_info": { 1105 "read_cost": "2.00", 1106 "eval_cost": "0.40", 1107 "prefix_cost": "2.40", 1108 "data_read_per_join": "16" 1109 } /* cost_info */, 1110 "used_columns": [ 1111 "e" 1112 ] /* used_columns */, 1113 "attached_condition": "(`test`.`t1`.`b` and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true))" 1114 } /* table */ 1115 } /* query_block */ 1116 }, 1117 { 1118 "dependent": false, 1119 "cacheable": true, 1120 "query_block": { 1121 "select_id": 3, 1122 "cost_info": { 1123 "query_cost": "2.40" 1124 } /* cost_info */, 1125 "table": { 1126 "table_name": "t3", 1127 "access_type": "ALL", 1128 "rows_examined_per_scan": 2, 1129 "rows_produced_per_join": 2, 1130 "filtered": "100.00", 1131 "cost_info": { 1132 "read_cost": "2.00", 1133 "eval_cost": "0.40", 1134 "prefix_cost": "2.40", 1135 "data_read_per_join": "16" 1136 } /* cost_info */, 1137 "used_columns": [ 1138 "e" 1139 ] /* used_columns */ 1140 } /* table */ 1141 } /* query_block */ 1142 } 1143 ] /* attached_subqueries */ 1144 } /* table */ 1145 } /* query_block */ 1146 } 1147 ] /* attached_subqueries */ 1148 } /* table */ 1149 } /* query_block */ 1150} 1151Warnings: 1152Note 1276 Field or reference 'test.t1.b' of SELECT #4 was resolved in SELECT #1 1153Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where (<nop>(<in_optimizer>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`),<exists>(/* select#4 */ select 1 from `test`.`t3` where (`test`.`t1`.`b` and <if>(outer_field_is_not_null, ((<cache>((/* select#3 */ select `test`.`t3`.`e` from `test`.`t3`)) < `test`.`t3`.`e`) or isnull(`test`.`t3`.`e`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`e`), true)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`c`)))) 1154DROP TABLE t1, t2, t3, t4; 1155# semi-join materialization (if enabled) 1156CREATE TABLE t1 (a INT); 1157INSERT INTO t1 VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1); 1158CREATE TABLE t2 (a INT) SELECT * FROM t1; 1159CREATE TABLE t3 (a INT) SELECT * FROM t1; 1160CREATE TABLE t4 (a INT) SELECT * FROM t1; 1161EXPLAIN FORMAT=JSON 1162SELECT * FROM t1 1163WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.a > 0) AND 1164t1.a IN (SELECT t3.a FROM t3 WHERE t3.a IN 1165(SELECT t4.a FROM t4 WHERE a > 0)); 1166EXPLAIN 1167{ 1168 "query_block": { 1169 "select_id": 1, 1170 "cost_info": { 1171 "query_cost": "4.42" 1172 } /* cost_info */, 1173 "table": { 1174 "table_name": "t1", 1175 "access_type": "ALL", 1176 "rows_examined_per_scan": 12, 1177 "rows_produced_per_join": 12, 1178 "filtered": "100.00", 1179 "cost_info": { 1180 "read_cost": "2.02", 1181 "eval_cost": "2.40", 1182 "prefix_cost": "4.42", 1183 "data_read_per_join": "96" 1184 } /* cost_info */, 1185 "used_columns": [ 1186 "a" 1187 ] /* used_columns */, 1188 "attached_condition": "(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` > 0) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)))) and <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#4 */ select 1 from `test`.`t4` where ((`test`.`t4`.`a` > 0) and (<cache>(`test`.`t3`.`a`) = `test`.`t4`.`a`)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`)))))", 1189 "attached_subqueries": [ 1190 { 1191 "dependent": true, 1192 "cacheable": false, 1193 "query_block": { 1194 "select_id": 3, 1195 "cost_info": { 1196 "query_cost": "4.42" 1197 } /* cost_info */, 1198 "table": { 1199 "table_name": "t3", 1200 "access_type": "ALL", 1201 "rows_examined_per_scan": 12, 1202 "rows_produced_per_join": 1, 1203 "filtered": "10.00", 1204 "cost_info": { 1205 "read_cost": "2.02", 1206 "eval_cost": "0.24", 1207 "prefix_cost": "4.42", 1208 "data_read_per_join": "9" 1209 } /* cost_info */, 1210 "used_columns": [ 1211 "a" 1212 ] /* used_columns */, 1213 "attached_condition": "(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#4 */ select 1 from `test`.`t4` where ((`test`.`t4`.`a` > 0) and (<cache>(`test`.`t3`.`a`) = `test`.`t4`.`a`)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`))", 1214 "attached_subqueries": [ 1215 { 1216 "dependent": true, 1217 "cacheable": false, 1218 "query_block": { 1219 "select_id": 4, 1220 "cost_info": { 1221 "query_cost": "4.42" 1222 } /* cost_info */, 1223 "table": { 1224 "table_name": "t4", 1225 "access_type": "ALL", 1226 "rows_examined_per_scan": 12, 1227 "rows_produced_per_join": 0, 1228 "filtered": "8.33", 1229 "cost_info": { 1230 "read_cost": "3.62", 1231 "eval_cost": "0.20", 1232 "prefix_cost": "4.42", 1233 "data_read_per_join": "7" 1234 } /* cost_info */, 1235 "used_columns": [ 1236 "a" 1237 ] /* used_columns */, 1238 "attached_condition": "((`test`.`t4`.`a` > 0) and (<cache>(`test`.`t3`.`a`) = `test`.`t4`.`a`))" 1239 } /* table */ 1240 } /* query_block */ 1241 } 1242 ] /* attached_subqueries */ 1243 } /* table */ 1244 } /* query_block */ 1245 }, 1246 { 1247 "dependent": true, 1248 "cacheable": false, 1249 "query_block": { 1250 "select_id": 2, 1251 "cost_info": { 1252 "query_cost": "4.42" 1253 } /* cost_info */, 1254 "table": { 1255 "table_name": "t2", 1256 "access_type": "ALL", 1257 "rows_examined_per_scan": 12, 1258 "rows_produced_per_join": 0, 1259 "filtered": "8.33", 1260 "cost_info": { 1261 "read_cost": "3.62", 1262 "eval_cost": "0.20", 1263 "prefix_cost": "4.42", 1264 "data_read_per_join": "7" 1265 } /* cost_info */, 1266 "used_columns": [ 1267 "a" 1268 ] /* used_columns */, 1269 "attached_condition": "((`test`.`t2`.`a` > 0) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`))" 1270 } /* table */ 1271 } /* query_block */ 1272 } 1273 ] /* attached_subqueries */ 1274 } /* table */ 1275 } /* query_block */ 1276} 1277Warnings: 1278Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select 1 from `test`.`t2` where ((`test`.`t2`.`a` > 0) and (<cache>(`test`.`t1`.`a`) = `test`.`t2`.`a`)))) and <in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select 1 from `test`.`t3` where (<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#4 */ select 1 from `test`.`t4` where ((`test`.`t4`.`a` > 0) and (<cache>(`test`.`t3`.`a`) = `test`.`t4`.`a`)))) and (<cache>(`test`.`t1`.`a`) = `test`.`t3`.`a`))))) 1279DROP TABLE t1, t2, t3, t4; 1280# the same subquery is associated with two different JOIN_TABs 1281CREATE TABLE t1 ( 1282i1 INTEGER NOT NULL, 1283c1 VARCHAR(1) NOT NULL 1284) ENGINE=InnoDB; 1285INSERT INTO t1 VALUES (2,'w'); 1286CREATE TABLE t2 ( 1287i1 INTEGER NOT NULL, 1288c1 VARCHAR(1) NOT NULL, 1289c2 VARCHAR(1) NOT NULL, 1290KEY (c1, i1) 1291) ENGINE=InnoDB; 1292INSERT INTO t2 VALUES (8,'d','d'); 1293INSERT INTO t2 VALUES (4,'v','v'); 1294CREATE TABLE t3 ( 1295c1 VARCHAR(1) NOT NULL 1296) ENGINE=InnoDB; 1297INSERT INTO t3 VALUES ('v'); 1298EXPLAIN FORMAT=json 1299SELECT i1 1300FROM t1 1301WHERE EXISTS (SELECT t2.c1 1302FROM (t2 INNER JOIN t3 ON (t3.c1 = t2.c1)) 1303WHERE t2.c2 != t1.c1 AND t2.c2 = (SELECT MIN(t3.c1) 1304FROM t3)); 1305EXPLAIN 1306{ 1307 "query_block": { 1308 "select_id": 1, 1309 "cost_info": { 1310 "query_cost": "1.20" 1311 } /* cost_info */, 1312 "table": { 1313 "table_name": "t1", 1314 "access_type": "ALL", 1315 "rows_examined_per_scan": 1, 1316 "rows_produced_per_join": 1, 1317 "filtered": "100.00", 1318 "cost_info": { 1319 "read_cost": "1.00", 1320 "eval_cost": "0.20", 1321 "prefix_cost": "1.20", 1322 "data_read_per_join": "8" 1323 } /* cost_info */, 1324 "used_columns": [ 1325 "i1", 1326 "c1" 1327 ] /* used_columns */, 1328 "attached_condition": "exists(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`c1` = `test`.`t3`.`c1`) and (`test`.`t2`.`c2` = (/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`)) and ((/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)))", 1329 "attached_subqueries": [ 1330 { 1331 "dependent": true, 1332 "cacheable": false, 1333 "query_block": { 1334 "select_id": 2, 1335 "cost_info": { 1336 "query_cost": "2.40" 1337 } /* cost_info */, 1338 "nested_loop": [ 1339 { 1340 "table": { 1341 "table_name": "t3", 1342 "access_type": "ALL", 1343 "rows_examined_per_scan": 1, 1344 "rows_produced_per_join": 1, 1345 "filtered": "100.00", 1346 "cost_info": { 1347 "read_cost": "1.00", 1348 "eval_cost": "0.20", 1349 "prefix_cost": "1.20", 1350 "data_read_per_join": "8" 1351 } /* cost_info */, 1352 "used_columns": [ 1353 "c1" 1354 ] /* used_columns */, 1355 "attached_condition": "((/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`)", 1356 "attached_subqueries": [ 1357 { 1358 "dependent": false, 1359 "cacheable": true, 1360 "query_block": { 1361 "select_id": 3, 1362 "cost_info": { 1363 "query_cost": "1.20" 1364 } /* cost_info */, 1365 "table": { 1366 "table_name": "t3", 1367 "access_type": "ALL", 1368 "rows_examined_per_scan": 1, 1369 "rows_produced_per_join": 1, 1370 "filtered": "100.00", 1371 "cost_info": { 1372 "read_cost": "1.00", 1373 "eval_cost": "0.20", 1374 "prefix_cost": "1.20", 1375 "data_read_per_join": "8" 1376 } /* cost_info */, 1377 "used_columns": [ 1378 "c1" 1379 ] /* used_columns */ 1380 } /* table */ 1381 } /* query_block */ 1382 } 1383 ] /* attached_subqueries */ 1384 } /* table */ 1385 }, 1386 { 1387 "table": { 1388 "table_name": "t2", 1389 "access_type": "ref", 1390 "possible_keys": [ 1391 "c1" 1392 ] /* possible_keys */, 1393 "key": "c1", 1394 "used_key_parts": [ 1395 "c1" 1396 ] /* used_key_parts */, 1397 "key_length": "3", 1398 "ref": [ 1399 "test.t3.c1" 1400 ] /* ref */, 1401 "rows_examined_per_scan": 1, 1402 "rows_produced_per_join": 0, 1403 "filtered": "50.00", 1404 "cost_info": { 1405 "read_cost": "1.00", 1406 "eval_cost": "0.10", 1407 "prefix_cost": "2.40", 1408 "data_read_per_join": "8" 1409 } /* cost_info */, 1410 "used_columns": [ 1411 "c1", 1412 "c2" 1413 ] /* used_columns */, 1414 "attached_condition": "(`test`.`t2`.`c2` = (/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`))", 1415 "attached_subqueries": [ 1416 { 1417 "dependent": false, 1418 "cacheable": true, 1419 "query_block": { 1420 "select_id": 3, 1421 "cost_info": { 1422 "query_cost": "1.20" 1423 } /* cost_info */, 1424 "table": { 1425 "table_name": "t3", 1426 "access_type": "ALL", 1427 "rows_examined_per_scan": 1, 1428 "rows_produced_per_join": 1, 1429 "filtered": "100.00", 1430 "cost_info": { 1431 "read_cost": "1.00", 1432 "eval_cost": "0.20", 1433 "prefix_cost": "1.20", 1434 "data_read_per_join": "8" 1435 } /* cost_info */, 1436 "used_columns": [ 1437 "c1" 1438 ] /* used_columns */ 1439 } /* table */ 1440 } /* query_block */ 1441 } 1442 ] /* attached_subqueries */ 1443 } /* table */ 1444 } 1445 ] /* nested_loop */ 1446 } /* query_block */ 1447 } 1448 ] /* attached_subqueries */ 1449 } /* table */ 1450 } /* query_block */ 1451} 1452Warnings: 1453Note 1276 Field or reference 'test.t1.c1' of SELECT #2 was resolved in SELECT #1 1454Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where exists(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t3` where ((`test`.`t2`.`c1` = `test`.`t3`.`c1`) and (`test`.`t2`.`c2` = (/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`)) and ((/* select#3 */ select min(`test`.`t3`.`c1`) from `test`.`t3`) <> `test`.`t1`.`c1`))) 1455DROP TABLE t1, t2, t3; 1456# multiple materialization groups 1457CREATE TABLE t1 (c_key INT, KEY c_key (c_key)); 1458INSERT INTO t1 VALUES (1), (2), (3); 1459CREATE TABLE t2 (c INT, c_key INT); 1460INSERT INTO t2 VALUES (8,5),(4,5),(8,1); 1461CREATE TABLE t3 LIKE t1; 1462INSERT INTO t3 SELECT * FROM t1; 1463CREATE TABLE t4 LIKE t2; 1464INSERT INTO t4 SELECT * FROM t2; 1465CREATE TABLE t5 (c INT); 1466INSERT INTO t5 VALUES (1), (2), (3); 1467# This should show two materialization groups where applicable 1468EXPLAIN SELECT * FROM t5 1469WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key) 1470AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key); 1471id select_type table partitions type possible_keys key key_len ref rows filtered Extra 14721 PRIMARY t5 NULL ALL NULL NULL NULL NULL 3 100.00 Using where 14733 DEPENDENT SUBQUERY t4 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 14743 DEPENDENT SUBQUERY t3 NULL ref c_key c_key 5 test.t4.c_key 1 100.00 Using index 14752 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 3 33.33 Using where 14762 DEPENDENT SUBQUERY t1 NULL ref c_key c_key 5 test.t2.c_key 2 100.00 Using index 1477Warnings: 1478Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where (<in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t2`.`c`)))) and <in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#3 */ select 1 from `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t4`.`c`))))) 1479EXPLAIN FORMAT=JSON SELECT * FROM t5 1480WHERE c IN (SELECT t2.c FROM t1 JOIN t2 ON t2.c_key = t1.c_key) 1481AND c IN (SELECT t4.c FROM t3 JOIN t4 ON t4.c_key = t3.c_key); 1482EXPLAIN 1483{ 1484 "query_block": { 1485 "select_id": 1, 1486 "cost_info": { 1487 "query_cost": "2.60" 1488 } /* cost_info */, 1489 "table": { 1490 "table_name": "t5", 1491 "access_type": "ALL", 1492 "rows_examined_per_scan": 3, 1493 "rows_produced_per_join": 3, 1494 "filtered": "100.00", 1495 "cost_info": { 1496 "read_cost": "2.01", 1497 "eval_cost": "0.60", 1498 "prefix_cost": "2.61", 1499 "data_read_per_join": "24" 1500 } /* cost_info */, 1501 "used_columns": [ 1502 "c" 1503 ] /* used_columns */, 1504 "attached_condition": "(<in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t2`.`c`)))) and <in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#3 */ select 1 from `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t4`.`c`)))))", 1505 "attached_subqueries": [ 1506 { 1507 "dependent": true, 1508 "cacheable": false, 1509 "query_block": { 1510 "select_id": 3, 1511 "cost_info": { 1512 "query_cost": "3.81" 1513 } /* cost_info */, 1514 "nested_loop": [ 1515 { 1516 "table": { 1517 "table_name": "t4", 1518 "access_type": "ALL", 1519 "rows_examined_per_scan": 3, 1520 "rows_produced_per_join": 1, 1521 "filtered": "33.33", 1522 "cost_info": { 1523 "read_cost": "2.01", 1524 "eval_cost": "0.20", 1525 "prefix_cost": "2.61", 1526 "data_read_per_join": "16" 1527 } /* cost_info */, 1528 "used_columns": [ 1529 "c", 1530 "c_key" 1531 ] /* used_columns */, 1532 "attached_condition": "((<cache>(`test`.`t5`.`c`) = `test`.`t4`.`c`) and (`test`.`t4`.`c_key` is not null))" 1533 } /* table */ 1534 }, 1535 { 1536 "table": { 1537 "table_name": "t3", 1538 "access_type": "ref", 1539 "possible_keys": [ 1540 "c_key" 1541 ] /* possible_keys */, 1542 "key": "c_key", 1543 "used_key_parts": [ 1544 "c_key" 1545 ] /* used_key_parts */, 1546 "key_length": "5", 1547 "ref": [ 1548 "test.t4.c_key" 1549 ] /* ref */, 1550 "rows_examined_per_scan": 1, 1551 "rows_produced_per_join": 1, 1552 "filtered": "100.00", 1553 "using_index": true, 1554 "cost_info": { 1555 "read_cost": "1.00", 1556 "eval_cost": "0.20", 1557 "prefix_cost": "3.81", 1558 "data_read_per_join": "8" 1559 } /* cost_info */, 1560 "used_columns": [ 1561 "c_key" 1562 ] /* used_columns */ 1563 } /* table */ 1564 } 1565 ] /* nested_loop */ 1566 } /* query_block */ 1567 }, 1568 { 1569 "dependent": true, 1570 "cacheable": false, 1571 "query_block": { 1572 "select_id": 2, 1573 "cost_info": { 1574 "query_cost": "4.03" 1575 } /* cost_info */, 1576 "nested_loop": [ 1577 { 1578 "table": { 1579 "table_name": "t2", 1580 "access_type": "ALL", 1581 "rows_examined_per_scan": 3, 1582 "rows_produced_per_join": 1, 1583 "filtered": "33.33", 1584 "cost_info": { 1585 "read_cost": "2.01", 1586 "eval_cost": "0.20", 1587 "prefix_cost": "2.61", 1588 "data_read_per_join": "16" 1589 } /* cost_info */, 1590 "used_columns": [ 1591 "c", 1592 "c_key" 1593 ] /* used_columns */, 1594 "attached_condition": "((<cache>(`test`.`t5`.`c`) = `test`.`t2`.`c`) and (`test`.`t2`.`c_key` is not null))" 1595 } /* table */ 1596 }, 1597 { 1598 "table": { 1599 "table_name": "t1", 1600 "access_type": "ref", 1601 "possible_keys": [ 1602 "c_key" 1603 ] /* possible_keys */, 1604 "key": "c_key", 1605 "used_key_parts": [ 1606 "c_key" 1607 ] /* used_key_parts */, 1608 "key_length": "5", 1609 "ref": [ 1610 "test.t2.c_key" 1611 ] /* ref */, 1612 "rows_examined_per_scan": 2, 1613 "rows_produced_per_join": 2, 1614 "filtered": "100.00", 1615 "using_index": true, 1616 "cost_info": { 1617 "read_cost": "1.02", 1618 "eval_cost": "0.40", 1619 "prefix_cost": "4.03", 1620 "data_read_per_join": "16" 1621 } /* cost_info */, 1622 "used_columns": [ 1623 "c_key" 1624 ] /* used_columns */ 1625 } /* table */ 1626 } 1627 ] /* nested_loop */ 1628 } /* query_block */ 1629 } 1630 ] /* attached_subqueries */ 1631 } /* table */ 1632 } /* query_block */ 1633} 1634Warnings: 1635Note 1003 /* select#1 */ select `test`.`t5`.`c` AS `c` from `test`.`t5` where (<in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#2 */ select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c_key` = `test`.`t2`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t2`.`c`)))) and <in_optimizer>(`test`.`t5`.`c`,<exists>(/* select#3 */ select 1 from `test`.`t3` join `test`.`t4` where ((`test`.`t3`.`c_key` = `test`.`t4`.`c_key`) and (<cache>(`test`.`t5`.`c`) = `test`.`t4`.`c`))))) 1636DROP TABLE t1, t2, t3, t4, t5; 1637CREATE TABLE t1 (i INT); 1638CREATE TABLE t2 (i INT); 1639CREATE TABLE t3 (i INT); 1640INSERT INTO t1 VALUES (1); 1641INSERT INTO t2 VALUES (1); 1642INSERT INTO t3 VALUES (1); 1643# Subqueries in UPDATE values list 1644EXPLAIN UPDATE t1 SET i=(SELECT i FROM t2); 1645id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16461 UPDATE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL 16472 SUBQUERY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 1648EXPLAIN FORMAT=JSON UPDATE t1 SET i=(SELECT i FROM t2); 1649EXPLAIN 1650{ 1651 "query_block": { 1652 "select_id": 1, 1653 "table": { 1654 "update": true, 1655 "table_name": "t1", 1656 "access_type": "ALL", 1657 "rows_examined_per_scan": 1, 1658 "filtered": "100.00" 1659 } /* table */, 1660 "update_value_subqueries": [ 1661 { 1662 "dependent": false, 1663 "cacheable": true, 1664 "query_block": { 1665 "select_id": 2, 1666 "cost_info": { 1667 "query_cost": "1.00" 1668 } /* cost_info */, 1669 "table": { 1670 "table_name": "t2", 1671 "access_type": "system", 1672 "rows_examined_per_scan": 1, 1673 "rows_produced_per_join": 1, 1674 "filtered": "100.00", 1675 "cost_info": { 1676 "read_cost": "0.00", 1677 "eval_cost": "0.20", 1678 "prefix_cost": "0.00", 1679 "data_read_per_join": "8" 1680 } /* cost_info */, 1681 "used_columns": [ 1682 "i" 1683 ] /* used_columns */ 1684 } /* table */ 1685 } /* query_block */ 1686 } 1687 ] /* update_value_subqueries */ 1688 } /* query_block */ 1689} 1690EXPLAIN UPDATE t1, t2 SET t1.i=(SELECT i FROM t3); 1691id select_type table partitions type possible_keys key key_len ref rows filtered Extra 16921 UPDATE t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 16931 PRIMARY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 16942 SUBQUERY t3 NULL system NULL NULL NULL NULL 1 100.00 NULL 1695EXPLAIN FORMAT=JSON UPDATE t1, t2 SET t1.i=(SELECT i FROM t3); 1696EXPLAIN 1697{ 1698 "query_block": { 1699 "select_id": 1, 1700 "cost_info": { 1701 "query_cost": "1.00" 1702 } /* cost_info */, 1703 "nested_loop": [ 1704 { 1705 "table": { 1706 "update": true, 1707 "table_name": "t1", 1708 "access_type": "system", 1709 "rows_examined_per_scan": 1, 1710 "rows_produced_per_join": 1, 1711 "filtered": "100.00", 1712 "cost_info": { 1713 "read_cost": "0.00", 1714 "eval_cost": "0.20", 1715 "prefix_cost": "0.00", 1716 "data_read_per_join": "8" 1717 } /* cost_info */, 1718 "used_columns": [ 1719 "i" 1720 ] /* used_columns */ 1721 } /* table */ 1722 }, 1723 { 1724 "table": { 1725 "table_name": "t2", 1726 "access_type": "system", 1727 "rows_examined_per_scan": 1, 1728 "rows_produced_per_join": 1, 1729 "filtered": "100.00", 1730 "cost_info": { 1731 "read_cost": "0.00", 1732 "eval_cost": "0.20", 1733 "prefix_cost": "0.00", 1734 "data_read_per_join": "8" 1735 } /* cost_info */ 1736 } /* table */ 1737 } 1738 ] /* nested_loop */, 1739 "update_value_subqueries": [ 1740 { 1741 "dependent": false, 1742 "cacheable": true, 1743 "query_block": { 1744 "select_id": 2, 1745 "cost_info": { 1746 "query_cost": "1.00" 1747 } /* cost_info */, 1748 "table": { 1749 "table_name": "t3", 1750 "access_type": "system", 1751 "rows_examined_per_scan": 1, 1752 "rows_produced_per_join": 1, 1753 "filtered": "100.00", 1754 "cost_info": { 1755 "read_cost": "0.00", 1756 "eval_cost": "0.20", 1757 "prefix_cost": "0.00", 1758 "data_read_per_join": "8" 1759 } /* cost_info */, 1760 "used_columns": [ 1761 "i" 1762 ] /* used_columns */ 1763 } /* table */ 1764 } /* query_block */ 1765 } 1766 ] /* update_value_subqueries */ 1767 } /* query_block */ 1768} 1769# INSERT ... ON DUPLICATE KEY UPDATE x=(SELECT ...) value list 1770EXPLAIN INSERT INTO t1 (i) 1771SELECT i FROM t2 ON DUPLICATE KEY UPDATE i=(SELECT i FROM t2); 1772id select_type table partitions type possible_keys key key_len ref rows filtered Extra 17731 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL 17741 PRIMARY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 17752 SUBQUERY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 1776EXPLAIN FORMAT=JSON INSERT INTO t1 (i) 1777SELECT i FROM t2 ON DUPLICATE KEY UPDATE i=(SELECT i FROM t2); 1778EXPLAIN 1779{ 1780 "query_block": { 1781 "select_id": 1, 1782 "cost_info": { 1783 "query_cost": "1.00" 1784 } /* cost_info */, 1785 "table": { 1786 "insert": true, 1787 "table_name": "t1", 1788 "access_type": "ALL" 1789 } /* table */, 1790 "insert_from": { 1791 "table": { 1792 "table_name": "t2", 1793 "access_type": "system", 1794 "rows_examined_per_scan": 1, 1795 "rows_produced_per_join": 1, 1796 "filtered": "100.00", 1797 "cost_info": { 1798 "read_cost": "0.00", 1799 "eval_cost": "0.20", 1800 "prefix_cost": "0.00", 1801 "data_read_per_join": "8" 1802 } /* cost_info */, 1803 "used_columns": [ 1804 "i" 1805 ] /* used_columns */ 1806 } /* table */ 1807 } /* insert_from */, 1808 "update_value_subqueries": [ 1809 { 1810 "dependent": false, 1811 "cacheable": true, 1812 "query_block": { 1813 "select_id": 2, 1814 "cost_info": { 1815 "query_cost": "1.00" 1816 } /* cost_info */, 1817 "table": { 1818 "table_name": "t2", 1819 "access_type": "system", 1820 "rows_examined_per_scan": 1, 1821 "rows_produced_per_join": 1, 1822 "filtered": "100.00", 1823 "cost_info": { 1824 "read_cost": "0.00", 1825 "eval_cost": "0.20", 1826 "prefix_cost": "0.00", 1827 "data_read_per_join": "8" 1828 } /* cost_info */, 1829 "used_columns": [ 1830 "i" 1831 ] /* used_columns */ 1832 } /* table */ 1833 } /* query_block */ 1834 } 1835 ] /* update_value_subqueries */ 1836 } /* query_block */ 1837} 1838EXPLAIN INSERT INTO t1 VALUES (1) 1839ON DUPLICATE KEY UPDATE i = (SELECT i FROM t2); 1840id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18411 INSERT t1 NULL ALL NULL NULL NULL NULL NULL NULL NULL 18422 SUBQUERY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 1843EXPLAIN FORMAT=JSON INSERT INTO t1 VALUES (1) 1844ON DUPLICATE KEY UPDATE i = (SELECT i FROM t2); 1845EXPLAIN 1846{ 1847 "query_block": { 1848 "select_id": 1, 1849 "table": { 1850 "insert": true, 1851 "table_name": "t1", 1852 "access_type": "ALL" 1853 } /* table */, 1854 "update_value_subqueries": [ 1855 { 1856 "dependent": false, 1857 "cacheable": true, 1858 "query_block": { 1859 "select_id": 2, 1860 "cost_info": { 1861 "query_cost": "1.00" 1862 } /* cost_info */, 1863 "table": { 1864 "table_name": "t2", 1865 "access_type": "system", 1866 "rows_examined_per_scan": 1, 1867 "rows_produced_per_join": 1, 1868 "filtered": "100.00", 1869 "cost_info": { 1870 "read_cost": "0.00", 1871 "eval_cost": "0.20", 1872 "prefix_cost": "0.00", 1873 "data_read_per_join": "8" 1874 } /* cost_info */, 1875 "used_columns": [ 1876 "i" 1877 ] /* used_columns */ 1878 } /* table */ 1879 } /* query_block */ 1880 } 1881 ] /* update_value_subqueries */ 1882 } /* query_block */ 1883} 1884# Subqueries in INSERT VALUES tuples: 1885EXPLAIN INSERT INTO t3 VALUES((SELECT i FROM t1)), ((SELECT i FROM t2)); 1886id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18871 INSERT t3 NULL ALL NULL NULL NULL NULL NULL NULL NULL 18883 SUBQUERY t2 NULL system NULL NULL NULL NULL 1 100.00 NULL 18892 SUBQUERY t1 NULL system NULL NULL NULL NULL 1 100.00 NULL 1890EXPLAIN FORMAT=JSON INSERT INTO t3 VALUES((SELECT i FROM t1)), ((SELECT i FROM t2)); 1891EXPLAIN 1892{ 1893 "query_block": { 1894 "select_id": 1, 1895 "table": { 1896 "insert": true, 1897 "table_name": "t3", 1898 "access_type": "ALL" 1899 } /* table */, 1900 "optimized_away_subqueries": [ 1901 { 1902 "dependent": false, 1903 "cacheable": true, 1904 "query_block": { 1905 "select_id": 3, 1906 "cost_info": { 1907 "query_cost": "1.00" 1908 } /* cost_info */, 1909 "table": { 1910 "table_name": "t2", 1911 "access_type": "system", 1912 "rows_examined_per_scan": 1, 1913 "rows_produced_per_join": 1, 1914 "filtered": "100.00", 1915 "cost_info": { 1916 "read_cost": "0.00", 1917 "eval_cost": "0.20", 1918 "prefix_cost": "0.00", 1919 "data_read_per_join": "8" 1920 } /* cost_info */, 1921 "used_columns": [ 1922 "i" 1923 ] /* used_columns */ 1924 } /* table */ 1925 } /* query_block */ 1926 }, 1927 { 1928 "dependent": false, 1929 "cacheable": true, 1930 "query_block": { 1931 "select_id": 2, 1932 "cost_info": { 1933 "query_cost": "1.00" 1934 } /* cost_info */, 1935 "table": { 1936 "table_name": "t1", 1937 "access_type": "system", 1938 "rows_examined_per_scan": 1, 1939 "rows_produced_per_join": 1, 1940 "filtered": "100.00", 1941 "cost_info": { 1942 "read_cost": "0.00", 1943 "eval_cost": "0.20", 1944 "prefix_cost": "0.00", 1945 "data_read_per_join": "8" 1946 } /* cost_info */, 1947 "used_columns": [ 1948 "i" 1949 ] /* used_columns */ 1950 } /* table */ 1951 } /* query_block */ 1952 } 1953 ] /* optimized_away_subqueries */ 1954 } /* query_block */ 1955} 1956DROP TABLE t1, t2, t3; 1957# Various queries 1958EXPLAIN SELECT a, b FROM 1959(SELECT 1 AS a, 2 AS b 1960UNION ALL 1961SELECT 1 AS a, 2 AS b) t1 1962GROUP BY a 1963ORDER BY b DESC; 1964id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19651 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 19662 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 19673 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used 1968Warnings: 1969Note 1003 /* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select 1 AS `a`,2 AS `b` union all /* select#3 */ select 1 AS `a`,2 AS `b`) `t1` group by `t1`.`a` order by `t1`.`b` desc 1970EXPLAIN FORMAT=JSON SELECT a, b FROM 1971(SELECT 1 AS a, 2 AS b 1972UNION ALL 1973SELECT 1 AS a, 2 AS b) t1 1974GROUP BY a 1975ORDER BY b DESC; 1976EXPLAIN 1977{ 1978 "query_block": { 1979 "select_id": 1, 1980 "cost_info": { 1981 "query_cost": "10.50" 1982 } /* cost_info */, 1983 "ordering_operation": { 1984 "using_filesort": true, 1985 "grouping_operation": { 1986 "using_temporary_table": true, 1987 "using_filesort": false, 1988 "table": { 1989 "table_name": "t1", 1990 "access_type": "ALL", 1991 "rows_examined_per_scan": 2, 1992 "rows_produced_per_join": 2, 1993 "filtered": "100.00", 1994 "cost_info": { 1995 "read_cost": "10.10", 1996 "eval_cost": "0.40", 1997 "prefix_cost": "10.50", 1998 "data_read_per_join": "48" 1999 } /* cost_info */, 2000 "used_columns": [ 2001 "a", 2002 "b" 2003 ] /* used_columns */, 2004 "materialized_from_subquery": { 2005 "using_temporary_table": true, 2006 "dependent": false, 2007 "cacheable": true, 2008 "query_block": { 2009 "union_result": { 2010 "using_temporary_table": false, 2011 "query_specifications": [ 2012 { 2013 "dependent": false, 2014 "cacheable": true, 2015 "query_block": { 2016 "select_id": 2, 2017 "message": "No tables used" 2018 } /* query_block */ 2019 }, 2020 { 2021 "dependent": false, 2022 "cacheable": true, 2023 "query_block": { 2024 "select_id": 3, 2025 "message": "No tables used" 2026 } /* query_block */ 2027 } 2028 ] /* query_specifications */ 2029 } /* union_result */ 2030 } /* query_block */ 2031 } /* materialized_from_subquery */ 2032 } /* table */ 2033 } /* grouping_operation */ 2034 } /* ordering_operation */ 2035 } /* query_block */ 2036} 2037Warnings: 2038Note 1003 /* select#1 */ select `t1`.`a` AS `a`,`t1`.`b` AS `b` from (/* select#2 */ select 1 AS `a`,2 AS `b` union all /* select#3 */ select 1 AS `a`,2 AS `b`) `t1` group by `t1`.`a` order by `t1`.`b` desc 2039# 2040CREATE TABLE t1(a INT, b INT); 2041INSERT INTO t1 VALUES (), (); 2042EXPLAIN SELECT 1 FROM t1 GROUP BY GREATEST(t1.a, (SELECT 1 FROM (SELECT t1.b FROM t1, t1 t2 ORDER BY t1.a, t1.a LIMIT 1) AS d)); 2043id select_type table partitions type possible_keys key key_len ref rows filtered Extra 20441 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 20452 SUBQUERY <derived3> NULL system NULL NULL NULL NULL 1 100.00 NULL 20463 DERIVED t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 20473 DERIVED t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (Block Nested Loop) 2048Warnings: 2049Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` group by greatest(`test`.`t1`.`a`,(/* select#2 */ select 1 from dual)) 2050EXPLAIN FORMAT=JSON SELECT 1 FROM t1 GROUP BY GREATEST(t1.a, (SELECT 1 FROM (SELECT t1.b FROM t1, t1 t2 ORDER BY t1.a, t1.a LIMIT 1) AS d)); 2051EXPLAIN 2052{ 2053 "query_block": { 2054 "select_id": 1, 2055 "cost_info": { 2056 "query_cost": "4.40" 2057 } /* cost_info */, 2058 "grouping_operation": { 2059 "using_temporary_table": true, 2060 "using_filesort": true, 2061 "cost_info": { 2062 "sort_cost": "2.00" 2063 } /* cost_info */, 2064 "table": { 2065 "table_name": "t1", 2066 "access_type": "ALL", 2067 "rows_examined_per_scan": 2, 2068 "rows_produced_per_join": 2, 2069 "filtered": "100.00", 2070 "cost_info": { 2071 "read_cost": "2.00", 2072 "eval_cost": "0.40", 2073 "prefix_cost": "2.40", 2074 "data_read_per_join": "32" 2075 } /* cost_info */, 2076 "used_columns": [ 2077 "a" 2078 ] /* used_columns */ 2079 } /* table */, 2080 "group_by_subqueries": [ 2081 { 2082 "dependent": false, 2083 "cacheable": true, 2084 "query_block": { 2085 "select_id": 2, 2086 "cost_info": { 2087 "query_cost": "1.00" 2088 } /* cost_info */, 2089 "table": { 2090 "table_name": "d", 2091 "access_type": "system", 2092 "rows_examined_per_scan": 1, 2093 "rows_produced_per_join": 1, 2094 "filtered": "100.00", 2095 "cost_info": { 2096 "read_cost": "0.00", 2097 "eval_cost": "0.20", 2098 "prefix_cost": "0.00", 2099 "data_read_per_join": "16" 2100 } /* cost_info */, 2101 "used_columns": [ 2102 "b" 2103 ] /* used_columns */, 2104 "materialized_from_subquery": { 2105 "using_temporary_table": true, 2106 "dependent": false, 2107 "cacheable": true, 2108 "query_block": { 2109 "select_id": 3, 2110 "cost_info": { 2111 "query_cost": "5.21" 2112 } /* cost_info */, 2113 "ordering_operation": { 2114 "using_temporary_table": true, 2115 "using_filesort": true, 2116 "nested_loop": [ 2117 { 2118 "table": { 2119 "table_name": "t1", 2120 "access_type": "ALL", 2121 "rows_examined_per_scan": 2, 2122 "rows_produced_per_join": 2, 2123 "filtered": "100.00", 2124 "cost_info": { 2125 "read_cost": "2.00", 2126 "eval_cost": "0.40", 2127 "prefix_cost": "2.40", 2128 "data_read_per_join": "32" 2129 } /* cost_info */, 2130 "used_columns": [ 2131 "a", 2132 "b" 2133 ] /* used_columns */ 2134 } /* table */ 2135 }, 2136 { 2137 "table": { 2138 "table_name": "t2", 2139 "access_type": "ALL", 2140 "rows_examined_per_scan": 2, 2141 "rows_produced_per_join": 4, 2142 "filtered": "100.00", 2143 "using_join_buffer": "Block Nested Loop", 2144 "cost_info": { 2145 "read_cost": "2.00", 2146 "eval_cost": "0.80", 2147 "prefix_cost": "5.21", 2148 "data_read_per_join": "64" 2149 } /* cost_info */ 2150 } /* table */ 2151 } 2152 ] /* nested_loop */ 2153 } /* ordering_operation */ 2154 } /* query_block */ 2155 } /* materialized_from_subquery */ 2156 } /* table */ 2157 } /* query_block */ 2158 } 2159 ] /* group_by_subqueries */ 2160 } /* grouping_operation */ 2161 } /* query_block */ 2162} 2163Warnings: 2164Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` group by greatest(`test`.`t1`.`a`,(/* select#2 */ select 1 from dual)) 2165DROP TABLE t1; 2166# 2167CREATE TABLE t1(f1 INT); 2168INSERT INTO t1 VALUES (1),(1); 2169EXPLAIN SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1)); 2170id select_type table partitions type possible_keys key key_len ref rows filtered Extra 21711 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL 21723 SUBQUERY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort 2173Warnings: 2174Note 1249 Select 2 was reduced during optimization 2175Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 1 2176EXPLAIN FORMAT=JSON SELECT 1 FROM t1 WHERE (SELECT (SELECT 1 FROM t1 GROUP BY f1)); 2177EXPLAIN 2178{ 2179 "query_block": { 2180 "select_id": 1, 2181 "cost_info": { 2182 "query_cost": "2.40" 2183 } /* cost_info */, 2184 "table": { 2185 "table_name": "t1", 2186 "access_type": "ALL", 2187 "rows_examined_per_scan": 2, 2188 "rows_produced_per_join": 2, 2189 "filtered": "100.00", 2190 "cost_info": { 2191 "read_cost": "2.00", 2192 "eval_cost": "0.40", 2193 "prefix_cost": "2.40", 2194 "data_read_per_join": "16" 2195 } /* cost_info */ 2196 } /* table */, 2197 "optimized_away_subqueries": [ 2198 { 2199 "dependent": false, 2200 "cacheable": true, 2201 "query_block": { 2202 "select_id": 3, 2203 "cost_info": { 2204 "query_cost": "4.40" 2205 } /* cost_info */, 2206 "grouping_operation": { 2207 "using_temporary_table": true, 2208 "using_filesort": true, 2209 "cost_info": { 2210 "sort_cost": "2.00" 2211 } /* cost_info */, 2212 "table": { 2213 "table_name": "t1", 2214 "access_type": "ALL", 2215 "rows_examined_per_scan": 2, 2216 "rows_produced_per_join": 2, 2217 "filtered": "100.00", 2218 "cost_info": { 2219 "read_cost": "2.00", 2220 "eval_cost": "0.40", 2221 "prefix_cost": "2.40", 2222 "data_read_per_join": "16" 2223 } /* cost_info */, 2224 "used_columns": [ 2225 "f1" 2226 ] /* used_columns */ 2227 } /* table */ 2228 } /* grouping_operation */ 2229 } /* query_block */ 2230 } 2231 ] /* optimized_away_subqueries */ 2232 } /* query_block */ 2233} 2234Warnings: 2235Note 1249 Select 2 was reduced during optimization 2236Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where 1 2237DROP TABLE t1; 2238# 2239CREATE TABLE t1 (i INT); 2240CREATE TABLE t2 (i INT, j INT); 2241INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); 2242INSERT INTO t2 SELECT i, i * 10 FROM t1; 2243EXPLAIN SELECT * FROM t1 ORDER BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i); 2244id select_type table partitions type possible_keys key key_len ref rows filtered Extra 22451 PRIMARY t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using filesort 22462 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 10 10.00 Using where 2247Warnings: 2248Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 2249Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`)) 2250EXPLAIN FORMAT=JSON SELECT * FROM t1 ORDER BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i); 2251EXPLAIN 2252{ 2253 "query_block": { 2254 "select_id": 1, 2255 "cost_info": { 2256 "query_cost": "4.02" 2257 } /* cost_info */, 2258 "ordering_operation": { 2259 "using_filesort": true, 2260 "table": { 2261 "table_name": "t1", 2262 "access_type": "ALL", 2263 "rows_examined_per_scan": 10, 2264 "rows_produced_per_join": 10, 2265 "filtered": "100.00", 2266 "cost_info": { 2267 "read_cost": "2.02", 2268 "eval_cost": "2.00", 2269 "prefix_cost": "4.02", 2270 "data_read_per_join": "80" 2271 } /* cost_info */, 2272 "used_columns": [ 2273 "i" 2274 ] /* used_columns */ 2275 } /* table */, 2276 "order_by_subqueries": [ 2277 { 2278 "dependent": true, 2279 "cacheable": false, 2280 "query_block": { 2281 "select_id": 2, 2282 "cost_info": { 2283 "query_cost": "4.02" 2284 } /* cost_info */, 2285 "table": { 2286 "table_name": "t2", 2287 "access_type": "ALL", 2288 "rows_examined_per_scan": 10, 2289 "rows_produced_per_join": 1, 2290 "filtered": "10.00", 2291 "cost_info": { 2292 "read_cost": "2.02", 2293 "eval_cost": "0.20", 2294 "prefix_cost": "4.02", 2295 "data_read_per_join": "16" 2296 } /* cost_info */, 2297 "used_columns": [ 2298 "i", 2299 "j" 2300 ] /* used_columns */, 2301 "attached_condition": "(`test`.`t2`.`i` = `test`.`t1`.`i`)" 2302 } /* table */ 2303 } /* query_block */ 2304 } 2305 ] /* order_by_subqueries */ 2306 } /* ordering_operation */ 2307 } /* query_block */ 2308} 2309Warnings: 2310Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 2311Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` order by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`)) 2312EXPLAIN SELECT * FROM t1 GROUP BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i); 2313id select_type table partitions type possible_keys key key_len ref rows filtered Extra 23141 PRIMARY t1 NULL ALL NULL NULL NULL NULL 10 100.00 Using temporary; Using filesort 23152 DEPENDENT SUBQUERY t2 NULL ALL NULL NULL NULL NULL 10 10.00 Using where 2316Warnings: 2317Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 2318Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`)) 2319EXPLAIN FORMAT=JSON SELECT * FROM t1 GROUP BY (SELECT t2.j FROM t2 WHERE t2.i = t1.i); 2320EXPLAIN 2321{ 2322 "query_block": { 2323 "select_id": 1, 2324 "cost_info": { 2325 "query_cost": "4.02" 2326 } /* cost_info */, 2327 "grouping_operation": { 2328 "using_temporary_table": true, 2329 "using_filesort": true, 2330 "table": { 2331 "table_name": "t1", 2332 "access_type": "ALL", 2333 "rows_examined_per_scan": 10, 2334 "rows_produced_per_join": 10, 2335 "filtered": "100.00", 2336 "cost_info": { 2337 "read_cost": "2.02", 2338 "eval_cost": "2.00", 2339 "prefix_cost": "4.02", 2340 "data_read_per_join": "80" 2341 } /* cost_info */, 2342 "used_columns": [ 2343 "i" 2344 ] /* used_columns */ 2345 } /* table */, 2346 "group_by_subqueries": [ 2347 { 2348 "dependent": true, 2349 "cacheable": false, 2350 "query_block": { 2351 "select_id": 2, 2352 "cost_info": { 2353 "query_cost": "4.02" 2354 } /* cost_info */, 2355 "table": { 2356 "table_name": "t2", 2357 "access_type": "ALL", 2358 "rows_examined_per_scan": 10, 2359 "rows_produced_per_join": 1, 2360 "filtered": "10.00", 2361 "cost_info": { 2362 "read_cost": "2.02", 2363 "eval_cost": "0.20", 2364 "prefix_cost": "4.02", 2365 "data_read_per_join": "16" 2366 } /* cost_info */, 2367 "used_columns": [ 2368 "i", 2369 "j" 2370 ] /* used_columns */, 2371 "attached_condition": "(`test`.`t2`.`i` = `test`.`t1`.`i`)" 2372 } /* table */ 2373 } /* query_block */ 2374 } 2375 ] /* group_by_subqueries */ 2376 } /* grouping_operation */ 2377 } /* query_block */ 2378} 2379Warnings: 2380Note 1276 Field or reference 'test.t1.i' of SELECT #2 was resolved in SELECT #1 2381Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by (/* select#2 */ select `test`.`t2`.`j` from `test`.`t2` where (`test`.`t2`.`i` = `test`.`t1`.`i`)) 2382DROP TABLE t1, t2; 2383CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k1 (a, b)); 2384INSERT INTO t1 VALUES (10,1),(10,2),(10,3),(20,4),(20,5),(20,6), 2385(30,7),(30,8),(30,9),(40,10),(40,11),(40,12),(40,13), 2386(40,14),(40,15),(40,16),(40,17),(40,18),(40,19),(40,20); 2387EXPLAIN FORMAT=JSON SELECT a, MIN(b) AS b FROM t1 GROUP BY a ORDER BY b; 2388EXPLAIN 2389{ 2390 "query_block": { 2391 "select_id": 1, 2392 "cost_info": { 2393 "query_cost": "6.50" 2394 } /* cost_info */, 2395 "ordering_operation": { 2396 "using_temporary_table": true, 2397 "using_filesort": true, 2398 "grouping_operation": { 2399 "using_filesort": false, 2400 "table": { 2401 "table_name": "t1", 2402 "access_type": "range", 2403 "possible_keys": [ 2404 "k1" 2405 ] /* possible_keys */, 2406 "key": "k1", 2407 "used_key_parts": [ 2408 "a" 2409 ] /* used_key_parts */, 2410 "key_length": "4", 2411 "rows_examined_per_scan": 11, 2412 "rows_produced_per_join": 11, 2413 "filtered": "100.00", 2414 "using_index_for_group_by": true, 2415 "cost_info": { 2416 "read_cost": "4.30", 2417 "eval_cost": "2.20", 2418 "prefix_cost": "6.50", 2419 "data_read_per_join": "176" 2420 } /* cost_info */, 2421 "used_columns": [ 2422 "a", 2423 "b" 2424 ] /* used_columns */ 2425 } /* table */ 2426 } /* grouping_operation */ 2427 } /* ordering_operation */ 2428 } /* query_block */ 2429} 2430Warnings: 2431Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `b` from `test`.`t1` group by `test`.`t1`.`a` order by `b` 2432DROP TABLE t1; 2433# 2434CREATE TABLE t1 (a INT NOT NULL, b CHAR(3) NOT NULL, PRIMARY KEY (a)); 2435INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ'); 2436CREATE TABLE t2 (a INT NOT NULL,b CHAR(3) NOT NULL,PRIMARY KEY (a, b)); 2437INSERT INTO t2 VALUES (1,'a'),(1,'b'),(3,'F'); 2438EXPLAIN FORMAT=JSON SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b; 2439EXPLAIN 2440{ 2441 "query_block": { 2442 "select_id": 1, 2443 "cost_info": { 2444 "query_cost": "6.20" 2445 } /* cost_info */, 2446 "ordering_operation": { 2447 "using_temporary_table": true, 2448 "using_filesort": true, 2449 "grouping_operation": { 2450 "using_filesort": true, 2451 "nested_loop": [ 2452 { 2453 "table": { 2454 "table_name": "t1", 2455 "access_type": "ALL", 2456 "possible_keys": [ 2457 "PRIMARY" 2458 ] /* possible_keys */, 2459 "rows_examined_per_scan": 3, 2460 "rows_produced_per_join": 3, 2461 "filtered": "100.00", 2462 "cost_info": { 2463 "read_cost": "2.01", 2464 "eval_cost": "0.60", 2465 "prefix_cost": "2.61", 2466 "data_read_per_join": "48" 2467 } /* cost_info */, 2468 "used_columns": [ 2469 "a", 2470 "b" 2471 ] /* used_columns */ 2472 } /* table */ 2473 }, 2474 { 2475 "table": { 2476 "table_name": "t2", 2477 "access_type": "ref", 2478 "possible_keys": [ 2479 "PRIMARY" 2480 ] /* possible_keys */, 2481 "key": "PRIMARY", 2482 "used_key_parts": [ 2483 "a" 2484 ] /* used_key_parts */, 2485 "key_length": "4", 2486 "ref": [ 2487 "test.t1.a" 2488 ] /* ref */, 2489 "rows_examined_per_scan": 1, 2490 "rows_produced_per_join": 3, 2491 "filtered": "100.00", 2492 "using_index": true, 2493 "cost_info": { 2494 "read_cost": "3.00", 2495 "eval_cost": "0.60", 2496 "prefix_cost": "6.21", 2497 "data_read_per_join": "48" 2498 } /* cost_info */, 2499 "used_columns": [ 2500 "a", 2501 "b" 2502 ] /* used_columns */ 2503 } /* table */ 2504 } 2505 ] /* nested_loop */ 2506 } /* grouping_operation */ 2507 } /* ordering_operation */ 2508 } /* query_block */ 2509} 2510Warnings: 2511Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,group_concat(`test`.`t2`.`b` separator ',') AS `b` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) where 1 group by `test`.`t1`.`a` order by `test`.`t1`.`b` 2512DROP TABLE t1; 2513DROP TABLE t2; 2514# 2515CREATE TABLE t1 (a INT, b INT); 2516INSERT INTO t1 VALUES 2517(1,4), 2518(2,2), (2,2), 2519(4,1), (4,1), (4,1), (4,1), 2520(2,1), (2,1); 2521EXPLAIN FORMAT=JSON SELECT SUM(b) FROM t1 GROUP BY a WITH ROLLUP; 2522EXPLAIN 2523{ 2524 "query_block": { 2525 "select_id": 1, 2526 "cost_info": { 2527 "query_cost": "12.82" 2528 } /* cost_info */, 2529 "grouping_operation": { 2530 "using_filesort": true, 2531 "cost_info": { 2532 "sort_cost": "9.00" 2533 } /* cost_info */, 2534 "table": { 2535 "table_name": "t1", 2536 "access_type": "ALL", 2537 "rows_examined_per_scan": 9, 2538 "rows_produced_per_join": 9, 2539 "filtered": "100.00", 2540 "cost_info": { 2541 "read_cost": "2.02", 2542 "eval_cost": "1.80", 2543 "prefix_cost": "3.82", 2544 "data_read_per_join": "144" 2545 } /* cost_info */, 2546 "used_columns": [ 2547 "a", 2548 "b" 2549 ] /* used_columns */ 2550 } /* table */ 2551 } /* grouping_operation */ 2552 } /* query_block */ 2553} 2554Warnings: 2555Note 1003 /* select#1 */ select sum(`test`.`t1`.`b`) AS `SUM(b)` from `test`.`t1` group by `test`.`t1`.`a` with rollup 2556DROP TABLE t1; 2557# Composition of DISTINCT, GROUP BY and ORDER BY 2558CREATE TABLE t1 (a INT, b INT); 2559INSERT INTO t1 VALUES (1, 1), (1, 2), (2, 1), (2, 2), (3, 1); 2560EXPLAIN FORMAT=JSON SELECT DISTINCT SUM(b) s FROM t1 GROUP BY a ORDER BY s; 2561EXPLAIN 2562{ 2563 "query_block": { 2564 "select_id": 1, 2565 "cost_info": { 2566 "query_cost": "3.01" 2567 } /* cost_info */, 2568 "ordering_operation": { 2569 "using_filesort": true, 2570 "duplicates_removal": { 2571 "using_temporary_table": true, 2572 "using_filesort": false, 2573 "grouping_operation": { 2574 "using_temporary_table": true, 2575 "using_filesort": false, 2576 "table": { 2577 "table_name": "t1", 2578 "access_type": "ALL", 2579 "rows_examined_per_scan": 5, 2580 "rows_produced_per_join": 5, 2581 "filtered": "100.00", 2582 "cost_info": { 2583 "read_cost": "2.01", 2584 "eval_cost": "1.00", 2585 "prefix_cost": "3.01", 2586 "data_read_per_join": "80" 2587 } /* cost_info */, 2588 "used_columns": [ 2589 "a", 2590 "b" 2591 ] /* used_columns */ 2592 } /* table */ 2593 } /* grouping_operation */ 2594 } /* duplicates_removal */ 2595 } /* ordering_operation */ 2596 } /* query_block */ 2597} 2598Warnings: 2599Note 1003 /* select#1 */ select distinct sum(`test`.`t1`.`b`) AS `s` from `test`.`t1` group by `test`.`t1`.`a` order by `s` 2600FLUSH STATUS; 2601SELECT DISTINCT SUM(b) s FROM t1 GROUP BY a ORDER BY s; 2602s 26031 26043 2605SHOW SESSION STATUS WHERE (Variable_name LIKE 'Sort_%' OR Variable_name LIKE 'Created_%_tables') AND Value > 0; 2606Variable_name Value 2607Created_tmp_tables 1 2608Sort_rows 2 2609Sort_scan 1 2610DROP TABLE t1; 2611# "buffer_result" node 2612CREATE TABLE t1 (a INT NOT NULL); 2613CREATE TABLE t2 (a INT NOT NULL, PRIMARY KEY (a)); 2614INSERT INTO t1 VALUES (1); 2615INSERT INTO t2 VALUES (1),(2); 2616EXPLAIN FORMAT=JSON SELECT SQL_BIG_RESULT DISTINCT t1.a FROM t1,t2 ORDER BY t2.a; 2617EXPLAIN 2618{ 2619 "query_block": { 2620 "select_id": 1, 2621 "cost_info": { 2622 "query_cost": "2.40" 2623 } /* cost_info */, 2624 "ordering_operation": { 2625 "using_filesort": false, 2626 "duplicates_removal": { 2627 "using_temporary_table": true, 2628 "using_filesort": false, 2629 "buffer_result": { 2630 "using_temporary_table": true, 2631 "nested_loop": [ 2632 { 2633 "table": { 2634 "table_name": "t1", 2635 "access_type": "system", 2636 "rows_examined_per_scan": 1, 2637 "rows_produced_per_join": 1, 2638 "filtered": "100.00", 2639 "cost_info": { 2640 "read_cost": "0.00", 2641 "eval_cost": "0.20", 2642 "prefix_cost": "0.00", 2643 "data_read_per_join": "8" 2644 } /* cost_info */, 2645 "used_columns": [ 2646 "a" 2647 ] /* used_columns */ 2648 } /* table */ 2649 }, 2650 { 2651 "table": { 2652 "table_name": "t2", 2653 "access_type": "index", 2654 "key": "PRIMARY", 2655 "used_key_parts": [ 2656 "a" 2657 ] /* used_key_parts */, 2658 "key_length": "4", 2659 "rows_examined_per_scan": 2, 2660 "rows_produced_per_join": 2, 2661 "filtered": "100.00", 2662 "using_index": true, 2663 "distinct": true, 2664 "cost_info": { 2665 "read_cost": "2.00", 2666 "eval_cost": "0.40", 2667 "prefix_cost": "2.40", 2668 "data_read_per_join": "16" 2669 } /* cost_info */, 2670 "used_columns": [ 2671 "a" 2672 ] /* used_columns */ 2673 } /* table */ 2674 } 2675 ] /* nested_loop */ 2676 } /* buffer_result */ 2677 } /* duplicates_removal */ 2678 } /* ordering_operation */ 2679 } /* query_block */ 2680} 2681Warnings: 2682Note 1003 /* select#1 */ select distinct sql_big_result '1' AS `a` from `test`.`t2` order by `test`.`t2`.`a` 2683DROP TABLE t1, t2; 2684# 2685CREATE TABLE t1 (a INT NOT NULL, b INT, PRIMARY KEY (a)); 2686CREATE TABLE t2 (a INT NOT NULL, PRIMARY KEY (a)); 2687INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); 2688INSERT INTO t2 VALUES (2), (3), (4), (5); 2689EXPLAIN FORMAT=JSON SELECT * FROM t2 WHERE t2.a IN (SELECT a FROM t1 WHERE t1.b <> 30); 2690EXPLAIN 2691{ 2692 "query_block": { 2693 "select_id": 1, 2694 "cost_info": { 2695 "query_cost": "2.81" 2696 } /* cost_info */, 2697 "table": { 2698 "table_name": "t2", 2699 "access_type": "index", 2700 "key": "PRIMARY", 2701 "used_key_parts": [ 2702 "a" 2703 ] /* used_key_parts */, 2704 "key_length": "4", 2705 "rows_examined_per_scan": 4, 2706 "rows_produced_per_join": 4, 2707 "filtered": "100.00", 2708 "using_index": true, 2709 "cost_info": { 2710 "read_cost": "2.01", 2711 "eval_cost": "0.80", 2712 "prefix_cost": "2.81", 2713 "data_read_per_join": "32" 2714 } /* cost_info */, 2715 "used_columns": [ 2716 "a" 2717 ] /* used_columns */, 2718 "attached_condition": "<in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))", 2719 "attached_subqueries": [ 2720 { 2721 "dependent": true, 2722 "cacheable": false, 2723 "query_block": { 2724 "select_id": 2, 2725 "cost_info": { 2726 "query_cost": "1.20" 2727 } /* cost_info */, 2728 "table": { 2729 "table_name": "t1", 2730 "access_type": "unique_subquery", 2731 "possible_keys": [ 2732 "PRIMARY" 2733 ] /* possible_keys */, 2734 "key": "PRIMARY", 2735 "used_key_parts": [ 2736 "a" 2737 ] /* used_key_parts */, 2738 "key_length": "4", 2739 "ref": [ 2740 "func" 2741 ] /* ref */, 2742 "rows_examined_per_scan": 1, 2743 "rows_produced_per_join": 0, 2744 "filtered": "75.00", 2745 "cost_info": { 2746 "read_cost": "1.00", 2747 "eval_cost": "0.15", 2748 "prefix_cost": "1.20", 2749 "data_read_per_join": "12" 2750 } /* cost_info */, 2751 "used_columns": [ 2752 "a", 2753 "b" 2754 ] /* used_columns */, 2755 "attached_condition": "((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))" 2756 } /* table */ 2757 } /* query_block */ 2758 } 2759 ] /* attached_subqueries */ 2760 } /* table */ 2761 } /* query_block */ 2762} 2763Warnings: 2764Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))) 2765DROP TABLE t1, t2; 2766set default_storage_engine= @save_storage_engine; 2767set optimizer_switch=default; 2768