1call mtr.add_suppression("Sort aborted.*"); 2set @save_join_cache_level = @@join_cache_level; 3create table t1 (c1 char(2)); 4create table t2 (c2 char(2)); 5insert into t1 values ('bb'), ('cc'), ('aa'), ('dd'); 6insert into t2 values ('bb'), ('cc'), ('dd'), ('ff'); 7create table t1i (c1 char(2) key); 8create table t2i (c2 char(2) key); 9insert into t1i values ('bb'), ('cc'), ('aa'), ('dd'); 10insert into t2i values ('bb'), ('cc'), ('dd'), ('ff'); 11========================================================================= 12Simple joins 13========================================================================= 14Simple nested loops join without blocking 15set @@join_cache_level=0; 16explain 17select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 2; 18id select_type table type possible_keys key key_len ref rows Extra 191 SIMPLE t1 ALL NULL NULL NULL NULL 4 201 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where 21select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 2; 22c1 c2 23bb bb 24Warnings: 25Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 26explain 27select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 4; 28id select_type table type possible_keys key key_len ref rows Extra 291 SIMPLE t1i index PRIMARY PRIMARY 2 NULL 4 Using index 301 SIMPLE t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index 31select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 4; 32c1 c2 33bb bb 34Warnings: 35Warning 1931 Query execution was interrupted. The query examined at least 5 rows, which exceeds LIMIT ROWS EXAMINED (4). The query result may be incomplete 36Blocked nested loops join, empty result set because of blocking 37set @@join_cache_level=1; 38explain 39select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 6; 40id select_type table type possible_keys key key_len ref rows Extra 411 SIMPLE t1 ALL NULL NULL NULL NULL 4 421 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 43select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 6; 44c1 c2 45bb bb 46Warnings: 47Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 48explain 49select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; 50id select_type table type possible_keys key key_len ref rows Extra 511 SIMPLE t1i index PRIMARY PRIMARY 2 NULL 4 Using index 521 SIMPLE t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index 53select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; 54c1 c2 55bb bb 56cc cc 57Warnings: 58Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 59set @@join_cache_level=6; 60explain 61select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 3; 62id select_type table type possible_keys key key_len ref rows Extra 631 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 641 SIMPLE t2 hash_ALL NULL #hash#$hj 3 test.t1.c1 4 Using where; Using join buffer (flat, BNLH join) 65select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 3; 66c1 c2 67Warnings: 68Warning 1931 Query execution was interrupted. The query examined at least 4 rows, which exceeds LIMIT ROWS EXAMINED (3). The query result may be incomplete 69explain 70select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; 71id select_type table type possible_keys key key_len ref rows Extra 721 SIMPLE t1i index PRIMARY PRIMARY 2 NULL 4 Using index 731 SIMPLE t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index 74select * from t1i, t2i where c1 = c2 LIMIT ROWS EXAMINED 6; 75c1 c2 76bb bb 77cc cc 78Warnings: 79Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 80Mix LIMIT ROWS EXAMINED with LIMIT 81set @@join_cache_level=0; 82explain 83select * from t1, t2 where c1 < c2 LIMIT 1 ROWS EXAMINED 4; 84id select_type table type possible_keys key key_len ref rows Extra 851 SIMPLE t1 ALL NULL NULL NULL NULL 4 861 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where 87select * from t1, t2 where c1 < c2 LIMIT 1 ROWS EXAMINED 4; 88c1 c2 89bb cc 90explain 91select * from t1, t2 where c1 < c2 LIMIT 1,1 ROWS EXAMINED 4; 92id select_type table type possible_keys key key_len ref rows Extra 931 SIMPLE t1 ALL NULL NULL NULL NULL 4 941 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where 95select * from t1, t2 where c1 < c2 LIMIT 1,1 ROWS EXAMINED 4; 96c1 c2 97bb dd 98Empty table optimized away during constant optimization 99create table t0 (c0 int); 100explain 101select * from t0 LIMIT ROWS EXAMINED 0; 102id select_type table type possible_keys key key_len ref rows Extra 1031 SIMPLE t0 system NULL NULL NULL NULL 0 Const row not found 104explain 105select * from t0 LIMIT ROWS EXAMINED 1; 106id select_type table type possible_keys key key_len ref rows Extra 1071 SIMPLE t0 system NULL NULL NULL NULL 0 Const row not found 108select * from t0 LIMIT ROWS EXAMINED 1; 109c0 110drop table t0; 111create table t0 (c0 char(2) primary key); 112insert into t0 values ('bb'), ('cc'), ('aa'); 113explain 114select * from t0 where c0 = 'bb' LIMIT ROWS EXAMINED 0; 115id select_type table type possible_keys key key_len ref rows Extra 1161 SIMPLE t0 const PRIMARY PRIMARY 2 const 1 Using index 117select * from t0 where c0 = 'bb' LIMIT ROWS EXAMINED 0; 118c0 119bb 120explain 121select * from t0, t1 where c0 = 'bb' and c1 > c0 LIMIT ROWS EXAMINED 0; 122id select_type table type possible_keys key key_len ref rows Extra 1231 SIMPLE t0 const PRIMARY PRIMARY 2 const 1 Using index 1241 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using where 125select * from t0, t1 where c0 = 'bb' and c1 > c0 LIMIT ROWS EXAMINED 0; 126c0 c1 127Warnings: 128Warning 1931 Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 129set @@join_cache_level = @save_join_cache_level; 130drop table t0; 131========================================================================= 132LIMIT ROWS EXAMINED with parameter argument 133========================================================================= 134set @@join_cache_level=0; 135set @l = 2; 136Prepared statement parameter 137prepare st1 from "select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED ?"; 138execute st1 using @l; 139c1 c2 140bb bb 141Warnings: 142Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 143deallocate prepare st1; 144User variable (not supported for LIMIT in MariaDB 5.3/MySQL 5.1) 145select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED @l; 146ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@l' at line 1 147Stored procedure parameter 148create procedure test_limit_rows(l int) 149begin 150select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED l; 151end| 152call test_limit_rows(3); 153c1 c2 154bb bb 155Warnings: 156Warning 1931 Query execution was interrupted. The query examined at least 4 rows, which exceeds LIMIT ROWS EXAMINED (3). The query result may be incomplete 157drop procedure test_limit_rows; 158set @@join_cache_level = @save_join_cache_level; 159========================================================================= 160UNIONs (with several LIMIT ROWS EXAMINED clauses) 161========================================================================= 162(select * from t1, t2 where c1 = c2) 163UNION 164(select * from t1, t2 where c1 < c2) LIMIT ROWS EXAMINED 6; 165c1 c2 166bb bb 167Warnings: 168Warning 1931 Query execution was interrupted. The query examined at least 8 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 169(select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 0) 170UNION 171(select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 6); 172c1 c2 173bb bb 174Warnings: 175Warning 1931 Query execution was interrupted. The query examined at least 8 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 176select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 0 177UNION 178select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 6; 179ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNION 180select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 6' at line 2 181(select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 0) 182UNION 183(select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 0) 184LIMIT ROWS EXAMINED 6; 185c1 c2 186bb bb 187Warnings: 188Warning 1931 Query execution was interrupted. The query examined at least 8 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 189(select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 0) 190UNION 191(select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 0) 192LIMIT 1 ROWS EXAMINED 6; 193c1 c2 194bb bb 195Warnings: 196Warning 1931 Query execution was interrupted. The query examined at least 8 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 197(select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 0) 198UNION 199(select * from t1, t2 where c1 < c2 LIMIT ROWS EXAMINED 0) 200LIMIT 2 ROWS EXAMINED 10; 201c1 c2 202bb bb 203cc cc 204Warnings: 205Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (10). The query result may be incomplete 206========================================================================= 207Subqueries (with several LIMIT ROWS EXAMINED clauses) 208========================================================================= 209Subqueries, semi-join 210explain 211select * from t1 212where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 11); 213id select_type table type possible_keys key key_len ref rows Extra 2141 PRIMARY t1 ALL NULL NULL NULL NULL 4 2151 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 2162 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 217select * from t1 218where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 11); 219c1 220bb 221Warnings: 222Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (11). The query result may be incomplete 223explain 224select * from t1 225where c1 IN (select * from t2 where c2 > ' ') 226LIMIT ROWS EXAMINED 11; 227id select_type table type possible_keys key key_len ref rows Extra 2281 PRIMARY t1 ALL NULL NULL NULL NULL 4 2291 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 2302 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 231select * from t1 232where c1 IN (select * from t2 where c2 > ' ') 233LIMIT ROWS EXAMINED 11; 234c1 235bb 236Warnings: 237Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (11). The query result may be incomplete 238explain 239select * from t1 240where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 241LIMIT ROWS EXAMINED 11; 242id select_type table type possible_keys key key_len ref rows Extra 2431 PRIMARY t1 ALL NULL NULL NULL NULL 4 2441 PRIMARY <subquery2> eq_ref distinct_key distinct_key 2 func 1 2452 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 246select * from t1 247where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 248LIMIT ROWS EXAMINED 11; 249c1 250bb 251Warnings: 252Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (11). The query result may be incomplete 253explain 254select * from t1i 255where c1 IN (select * from t2i where c2 > ' ') 256LIMIT ROWS EXAMINED 6; 257id select_type table type possible_keys key key_len ref rows Extra 2581 PRIMARY t1i range PRIMARY PRIMARY 2 NULL 4 Using where; Using index 2591 PRIMARY t2i eq_ref PRIMARY PRIMARY 2 test.t1i.c1 1 Using index 260select * from t1i 261where c1 IN (select * from t2i where c2 > ' ') 262LIMIT ROWS EXAMINED 6; 263c1 264bb 265cc 266Warnings: 267Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 268Subqueries with IN-TO-EXISTS 269set @@optimizer_switch='semijoin=off,in_to_exists=on,materialization=off'; 270explain 271select * from t1 272where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 4); 273id select_type table type possible_keys key key_len ref rows Extra 2741 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2752 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where 276select * from t1 277where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 4); 278c1 279bb 280Warnings: 281Warning 1931 Query execution was interrupted. The query examined at least 5 rows, which exceeds LIMIT ROWS EXAMINED (4). The query result may be incomplete 282explain 283select * from t1 284where c1 IN (select * from t2 where c2 > ' ') 285LIMIT ROWS EXAMINED 4; 286id select_type table type possible_keys key key_len ref rows Extra 2871 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 2882 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where 289select * from t1 290where c1 IN (select * from t2 where c2 > ' ') 291LIMIT ROWS EXAMINED 4; 292c1 293bb 294Warnings: 295Warning 1931 Query execution was interrupted. The query examined at least 5 rows, which exceeds LIMIT ROWS EXAMINED (4). The query result may be incomplete 296explain 297select * from t1 298where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 299LIMIT ROWS EXAMINED 4; 300id select_type table type possible_keys key key_len ref rows Extra 3011 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 3022 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 4 Using where 303select * from t1 304where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 305LIMIT ROWS EXAMINED 4; 306c1 307bb 308Warnings: 309Warning 1931 Query execution was interrupted. The query examined at least 5 rows, which exceeds LIMIT ROWS EXAMINED (4). The query result may be incomplete 310explain 311select * from t1i 312where c1 IN (select * from t2i where c2 > ' ') 313LIMIT ROWS EXAMINED 9; 314id select_type table type possible_keys key key_len ref rows Extra 3151 PRIMARY t1i index NULL PRIMARY 2 NULL 4 Using where; Using index 3162 DEPENDENT SUBQUERY t2i unique_subquery PRIMARY PRIMARY 2 func 1 Using index; Using where 317select * from t1i 318where c1 IN (select * from t2i where c2 > ' ') 319LIMIT ROWS EXAMINED 9; 320c1 321bb 322Warnings: 323Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (9). The query result may be incomplete 324Same as above, without subquery cache 325set @@optimizer_switch='subquery_cache=off'; 326select * from t1 327where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 2); 328c1 329bb 330Warnings: 331Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 332select * from t1 333where c1 IN (select * from t2 where c2 > ' ') 334LIMIT ROWS EXAMINED 2; 335c1 336bb 337Warnings: 338Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 339select * from t1 340where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 341LIMIT ROWS EXAMINED 2; 342c1 343bb 344Warnings: 345Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 346select * from t1i 347where c1 IN (select * from t2i where c2 > ' ') 348LIMIT ROWS EXAMINED 5; 349c1 350bb 351Warnings: 352Warning 1931 Query execution was interrupted. The query examined at least 7 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 353Subqueries with materialization 354set @@optimizer_switch='semijoin=off,in_to_exists=off,materialization=on,subquery_cache=on'; 355explain 356select * from t1 357where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 13); 358id select_type table type possible_keys key key_len ref rows Extra 3591 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 3602 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 361select * from t1 362where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 13); 363c1 364bb 365Warnings: 366Warning 1931 Query execution was interrupted. The query examined at least 14 rows, which exceeds LIMIT ROWS EXAMINED (13). The query result may be incomplete 367explain 368select * from t1 369where c1 IN (select * from t2 where c2 > ' ') LIMIT ROWS EXAMINED 13; 370id select_type table type possible_keys key key_len ref rows Extra 3711 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 3722 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 373select * from t1 374where c1 IN (select * from t2 where c2 > ' ') LIMIT ROWS EXAMINED 13; 375c1 376bb 377Warnings: 378Warning 1931 Query execution was interrupted. The query examined at least 14 rows, which exceeds LIMIT ROWS EXAMINED (13). The query result may be incomplete 379explain 380select * from t1 381where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 382LIMIT ROWS EXAMINED 13; 383id select_type table type possible_keys key key_len ref rows Extra 3841 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where 3852 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 386select * from t1 387where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0) 388LIMIT ROWS EXAMINED 13; 389c1 390bb 391Warnings: 392Warning 1931 Query execution was interrupted. The query examined at least 14 rows, which exceeds LIMIT ROWS EXAMINED (13). The query result may be incomplete 393explain 394select * from t1i 395where c1 IN (select * from t2i where c2 > ' ') LIMIT ROWS EXAMINED 17; 396id select_type table type possible_keys key key_len ref rows Extra 3971 PRIMARY t1i index NULL PRIMARY 2 NULL 4 Using where; Using index 3982 MATERIALIZED t2i range PRIMARY PRIMARY 2 NULL 4 Using where; Using index 399select * from t1i 400where c1 IN (select * from t2i where c2 > ' ') LIMIT ROWS EXAMINED 17; 401c1 402bb 403Warnings: 404Warning 1931 Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (17). The query result may be incomplete 405set @@optimizer_switch='default'; 406========================================================================= 407Views and derived tables 408========================================================================= 409create view v1 as 410select * from t1 where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 13); 411ERROR 42000: This version of MariaDB doesn't yet support 'LIMIT ROWS EXAMINED inside views' 412create view v1 as 413select * from t1 where c1 IN (select * from t2 where c2 > ' '); 414select * from v1; 415c1 416bb 417cc 418dd 419select * from v1 LIMIT ROWS EXAMINED 17; 420c1 421bb 422cc 423dd 424Warnings: 425Warning 1931 Query execution was interrupted. The query examined at least 18 rows, which exceeds LIMIT ROWS EXAMINED (17). The query result may be incomplete 426select * from v1 LIMIT ROWS EXAMINED 16; 427c1 428bb 429cc 430Warnings: 431Warning 1931 Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete 432select * from v1 LIMIT ROWS EXAMINED 11; 433c1 434bb 435Warnings: 436Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (11). The query result may be incomplete 437drop view v1; 438explain 439select * 440from (select * from t1 441where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp 442LIMIT ROWS EXAMINED 11; 443id select_type table type possible_keys key key_len ref rows Extra 4441 PRIMARY t1 ALL NULL NULL NULL NULL 4 4451 PRIMARY <subquery3> eq_ref distinct_key distinct_key 2 func 1 4463 MATERIALIZED t2 ALL NULL NULL NULL NULL 4 Using where 447select * 448from (select * from t1 449where c1 IN (select * from t2 where c2 > ' ' LIMIT ROWS EXAMINED 0)) as tmp 450LIMIT ROWS EXAMINED 11; 451c1 452bb 453Warnings: 454Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (11). The query result may be incomplete 455========================================================================= 456Aggregation 457========================================================================= 458create table t3 (c1 char(2), c2 int); 459insert into t3 values 460('aa', 1), ('aa', 2), 461('bb', 3), ('bb', 4), ('bb', 5); 462explain 463select c1, sum(c2) from t3 group by c1; 464id select_type table type possible_keys key key_len ref rows Extra 4651 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 466select c1, sum(c2) from t3 group by c1; 467c1 sum(c2) 468aa 3 469bb 12 470explain 471select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0; 472id select_type table type possible_keys key key_len ref rows Extra 4731 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 474select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0; 475c1 sum(c2) 476Warnings: 477Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 478select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 1; 479c1 sum(c2) 480Warnings: 481Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete 482select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 20; 483c1 sum(c2) 484aa 3 485Warnings: 486Warning 1931 Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete 487select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 21; 488c1 sum(c2) 489aa 3 490bb 12 491create table t3i (c1 char(2), c2 int); 492create index it3i on t3i(c1); 493create index it3j on t3i(c2,c1); 494insert into t3i values 495('aa', 1), ('aa', 2), 496('bb', 3), ('bb', 4), ('bb', 5); 497explain 498select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0; 499id select_type table type possible_keys key key_len ref rows Extra 5001 SIMPLE t3i index NULL it3j 8 NULL 5 Using index; Using temporary; Using filesort 501select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0; 502c1 sum(c2) 503Warnings: 504Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 505select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 1; 506c1 sum(c2) 507Warnings: 508Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete 509select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 20; 510c1 sum(c2) 511aa 3 512Warnings: 513Warning 1931 Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete 514select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 21; 515c1 sum(c2) 516aa 3 517bb 12 518Aggregation without grouping 519explain 520select min(c2) from t3 LIMIT ROWS EXAMINED 5; 521id select_type table type possible_keys key key_len ref rows Extra 5221 SIMPLE t3 ALL NULL NULL NULL NULL 5 523select min(c2) from t3 LIMIT ROWS EXAMINED 5; 524min(c2) 525Warnings: 526Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 527select max(c2) from t3 LIMIT ROWS EXAMINED 6; 528max(c2) 5295 530select max(c2) from t3 LIMIT ROWS EXAMINED 0; 531max(c2) 532Warnings: 533Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 534explain 535select max(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 5; 536id select_type table type possible_keys key key_len ref rows Extra 5371 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where 538select max(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 5; 539max(c2) 540Warnings: 541Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 542select max(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 6; 543max(c2) 544NULL 545select max(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 0; 546max(c2) 547Warnings: 548Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 549explain 550select count(c2) from t3 LIMIT ROWS EXAMINED 5; 551id select_type table type possible_keys key key_len ref rows Extra 5521 SIMPLE t3 ALL NULL NULL NULL NULL 5 553select count(c2) from t3 LIMIT ROWS EXAMINED 5; 554count(c2) 555Warnings: 556Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 557select count(c2) from t3 LIMIT ROWS EXAMINED 6; 558count(c2) 5595 560select count(c2) from t3 LIMIT ROWS EXAMINED 0; 561count(c2) 562Warnings: 563Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 564explain 565select count(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 5; 566id select_type table type possible_keys key key_len ref rows Extra 5671 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using where 568select count(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 5; 569count(c2) 570Warnings: 571Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 572select count(c2) from t3 where c2 > 10 LIMIT ROWS EXAMINED 6; 573count(c2) 5740 575explain 576select sum(c2) from t3 LIMIT ROWS EXAMINED 5; 577id select_type table type possible_keys key key_len ref rows Extra 5781 SIMPLE t3 ALL NULL NULL NULL NULL 5 579select sum(c2) from t3 LIMIT ROWS EXAMINED 5; 580sum(c2) 581Warnings: 582Warning 1931 Query execution was interrupted. The query examined at least 6 rows, which exceeds LIMIT ROWS EXAMINED (5). The query result may be incomplete 583select sum(c2) from t3 LIMIT ROWS EXAMINED 6; 584sum(c2) 58515 586The query result is found during optimization, LIMIT ROWS EXAMINED has no effect. 587explain 588select max(c1) from t3i LIMIT ROWS EXAMINED 0; 589id select_type table type possible_keys key key_len ref rows Extra 5901 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 591select max(c1) from t3i LIMIT ROWS EXAMINED 0; 592max(c1) 593bb 594create table t3_empty like t3; 595explain 596select max(c1) from t3_empty LIMIT ROWS EXAMINED 0; 597id select_type table type possible_keys key key_len ref rows Extra 5981 SIMPLE t3_empty system NULL NULL NULL NULL 0 Const row not found 599select max(c1) from t3_empty LIMIT ROWS EXAMINED 0; 600max(c1) 601NULL 602drop table t3_empty; 603========================================================================= 604Sorting 605========================================================================= 606explain 607select c1, c2 from t3 order by c2, c1 LIMIT ROWS EXAMINED 2; 608id select_type table type possible_keys key key_len ref rows Extra 6091 SIMPLE t3 ALL NULL NULL NULL NULL 5 Using filesort 610select c1, c2 from t3 order by c2, c1 LIMIT ROWS EXAMINED 2; 611ERROR HY000: Sort aborted: 612explain 613select c1, c2 from t3i order by c2, c1 LIMIT ROWS EXAMINED 2; 614id select_type table type possible_keys key key_len ref rows Extra 6151 SIMPLE t3i index NULL it3j 8 NULL 5 Using index 616select c1, c2 from t3i order by c2, c1 LIMIT ROWS EXAMINED 2; 617c1 c2 618aa 1 619aa 2 620Warnings: 621Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 622explain 623select c1, c2 from t3i order by c2, c1 desc LIMIT ROWS EXAMINED 2; 624id select_type table type possible_keys key key_len ref rows Extra 6251 SIMPLE t3i index NULL it3j 8 NULL 5 Using index; Using filesort 626select c1, c2 from t3i order by c2, c1 desc LIMIT ROWS EXAMINED 2; 627ERROR HY000: Sort aborted: 628drop table t3,t3i; 629========================================================================= 630INSERT/DELETE/UPDATE 631========================================================================= 632INSERT ... SELECT 633CREATE TABLE t4 (a int); 634INSERT INTO t4 values (1), (2); 635INSERT IGNORE INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 0; 636Warnings: 637Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete 638select * from t4; 639a 6401 6412 642INSERT INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 6; 643Warnings: 644Warning 1931 Query execution was interrupted. The query examined at least 8 rows, which exceeds LIMIT ROWS EXAMINED (6). The query result may be incomplete 645select * from t4; 646a 6471 6482 6493 650drop table t4; 651DELETE - LIMIT ROWS EXAMINED not supported 652CREATE TABLE t4 (a int); 653INSERT INTO t4 values (1), (2); 654DELETE FROM t4 WHERE t4.a > 0 LIMIT ROWS EXAMINED 0; 655ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXAMINED 0' at line 1 656DELETE FROM t4 WHERE t4.a > 0 LIMIT 0 ROWS EXAMINED 0; 657ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXAMINED 0' at line 1 658drop table t4; 659UPDATE - LIMIT ROWS EXAMINED not supported 660CREATE TABLE t4 (a int); 661INSERT INTO t4 values (1), (2); 662update t4 set a=a+10 LIMIT ROWS EXAMINED 0; 663ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXAMINED 0' at line 1 664update t4 set a=a+10 LIMIT 0 ROWS EXAMINED 0; 665ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXAMINED 0' at line 1 666drop table t4; 667drop table t1,t2,t1i,t2i; 668========================================================================= 669Test cases for bugs 670========================================================================= 671 672MDEV-115 673 674SET @@optimizer_switch='in_to_exists=on,outer_join_with_cache=on'; 675CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM; 676INSERT INTO t1 VALUES ('USA'),('CAN'); 677CREATE TABLE t2 ( b INT ); 678INSERT INTO t2 VALUES (3899),(3914),(3888); 679CREATE TABLE t3 ( c VARCHAR(33), d INT ); 680INSERT INTO t3 VALUES ('USASpanish',8),('USATagalog',0),('USAVietnamese',0); 681EXPLAIN 682SELECT DISTINCT a AS field1 FROM t1, t2 683WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 684HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20; 685id select_type table type possible_keys key key_len ref rows Extra 6861 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary 6871 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 6882 SUBQUERY t3 ALL NULL NULL NULL NULL 3 6892 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 690SELECT DISTINCT a AS field1 FROM t1, t2 691WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 692HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20; 693field1 694Warnings: 695Warning 1931 Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete 696EXPLAIN 697SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14; 698id select_type table type possible_keys key key_len ref rows Extra 6991 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary 7001 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join) 701SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14; 702a 703USA 704Warnings: 705Warning 1931 Query execution was interrupted. The query examined at least 15 rows, which exceeds LIMIT ROWS EXAMINED (14). The query result may be incomplete 706SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 15; 707a 708USA 709CAN 710Warnings: 711Warning 1931 Query execution was interrupted. The query examined at least 16 rows, which exceeds LIMIT ROWS EXAMINED (15). The query result may be incomplete 712SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 16; 713a 714USA 715CAN 716drop table t1,t2,t3; 717set @@optimizer_switch='default'; 718 719MDEV-153 720 721CREATE TABLE t1 ( a TIME, b DATETIME, KEY(a), KEY(b) ) ENGINE=MyISAM; 722INSERT INTO t1 VALUES 723('21:22:34.025509', '2002-02-13 17:30:06.013935'), 724('10:50:38.059966', '2008-09-27 00:34:58.026613'), 725('00:21:38.058143', '2007-05-28 00:00:00'); 726CREATE TABLE t2 ( c INT, d TIME, e DATETIME, f VARCHAR(1), KEY(c) ) ENGINE=MyISAM; 727INSERT INTO t2 VALUES 728(0, '11:03:22.062907', '2007-06-02 11:16:01.053212', 'a'), 729(0, '08:14:05.001407', '1900-01-01 00:00:00', 'm'), 730(5, '19:03:16.024974', '1900-01-01 00:00:00', 'f'), 731(1, '07:23:34.034234', '2000-11-26 05:01:11.054228', 'z'), 732(6, '12:29:32.019411', '2006-02-13 00:00:00', 'f'), 733(6, '06:07:10.010496', '2007-06-08 04:35:26.020373', 'a'), 734(7, '22:55:09.020772', '2005-04-27 00:00:00', 'i'); 735EXPLAIN 736SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 737FROM t1, t2 AS alias2, t2 AS alias3 738WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) 739GROUP BY field1, field2, field3, field4, field5 740LIMIT ROWS EXAMINED 120; 741id select_type table type possible_keys key key_len ref rows Extra 7421 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort 7431 PRIMARY alias2 ALL NULL NULL NULL NULL 7 Using join buffer (flat, BNL join) 7441 PRIMARY alias3 index NULL c 5 NULL 7 Using where; Using index; Using join buffer (incremental, BNL join) 7452 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used 7463 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used 747NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 748FLUSH STATUS; 749SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 750FROM t1, t2 AS alias2, t2 AS alias3 751WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) 752GROUP BY field1, field2, field3, field4, field5 753LIMIT ROWS EXAMINED 120; 754field1 field2 field3 field4 field5 755Warnings: 756Warning 1931 Query execution was interrupted. The query examined at least 121 rows, which exceeds LIMIT ROWS EXAMINED (120). The query result may be incomplete 757SHOW STATUS LIKE 'Handler_read%'; 758Variable_name Value 759Handler_read_first 1 760Handler_read_key 4 761Handler_read_last 0 762Handler_read_next 4 763Handler_read_prev 0 764Handler_read_retry 0 765Handler_read_rnd 0 766Handler_read_rnd_deleted 0 767Handler_read_rnd_next 46 768SHOW STATUS LIKE 'Handler_tmp%'; 769Variable_name Value 770Handler_tmp_delete 0 771Handler_tmp_update 0 772Handler_tmp_write 66 773FLUSH STATUS; 774SELECT a AS field1, alias2.d AS field2, alias2.f AS field3, alias2.e AS field4, b AS field5 775FROM t1, t2 AS alias2, t2 AS alias3 776WHERE alias3.c IN ( SELECT 1 UNION SELECT 6 ) 777GROUP BY field1, field2, field3, field4, field5 778LIMIT ROWS EXAMINED 124; 779field1 field2 field3 field4 field5 780Warnings: 781Warning 1931 Query execution was interrupted. The query examined at least 125 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete 782Warning 1931 Query execution was interrupted. The query examined at least 127 rows, which exceeds LIMIT ROWS EXAMINED (124). The query result may be incomplete 783SHOW STATUS LIKE 'Handler_read%'; 784Variable_name Value 785Handler_read_first 1 786Handler_read_key 4 787Handler_read_last 0 788Handler_read_next 4 789Handler_read_prev 0 790Handler_read_retry 0 791Handler_read_rnd 0 792Handler_read_rnd_deleted 0 793Handler_read_rnd_next 48 794SHOW STATUS LIKE 'Handler_tmp%'; 795Variable_name Value 796Handler_tmp_delete 0 797Handler_tmp_update 0 798Handler_tmp_write 70 799drop table t1, t2; 800 801MDEV-161 LIMIT_ROWS EXAMINED: query with the limit and NOT EXISTS, without GROUP BY or aggregate, 802returns rows, while the same query without the limit returns empty set 803 804CREATE TABLE t1 ( a INT, b INT ); 805INSERT INTO t1 VALUES (3911,17),(3847,33),(3857,26); 806CREATE TABLE t2 ( c VARCHAR(16) ); 807INSERT INTO t2 VALUES ('English'),('French'),('German'); 808CREATE TABLE t3 ( d INT, e VARCHAR(32) ); 809INSERT INTO t3 VALUES (3813,'United States'),(3814,'United States'); 810SELECT * FROM t1 AS alias1, t2 AS alias2 811WHERE NOT EXISTS ( 812SELECT * FROM t1 LEFT OUTER JOIN t3 813ON (d = a) 814WHERE b <= alias1.b OR e != alias2.c 815); 816a b c 817SELECT * FROM t1 AS alias1, t2 AS alias2 818WHERE NOT EXISTS ( 819SELECT * FROM t1 LEFT OUTER JOIN t3 820ON (d = a) 821WHERE b <= alias1.b OR e != alias2.c 822) LIMIT ROWS EXAMINED 20; 823a b c 824Warnings: 825Warning 1931 Query execution was interrupted. The query examined at least 25 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete 826drop table t1, t2, t3; 827 828MDEV-174: LIMIT ROWS EXAMINED: Assertion `0' failed in net_end_statement(THD*) 829with subquery in SELECT, constant table, aggregate function 830 831CREATE TABLE t1 ( a INT ); 832CREATE TABLE t2 ( b INT, c INT, KEY(c) ); 833INSERT INTO t2 VALUES 834(5, 0),(3, 4),(6, 1), 835(5, 8),(4, 9),(8, 1); 836SELECT (SELECT MAX(c) FROM t1, t2) 837FROM t2 838WHERE c = (SELECT MAX(b) FROM t2) 839LIMIT ROWS EXAMINED 3; 840(SELECT MAX(c) FROM t1, t2) 841Warnings: 842Warning 1931 Query execution was interrupted. The query examined at least 12 rows, which exceeds LIMIT ROWS EXAMINED (3). The query result may be incomplete 843drop table t1, t2; 844 845MDEV-178: LIMIT ROWS EXAMINED: Assertion `0' failed in net_end_statement(THD*) on the 8462nd PS execution, with DISTINCT, FROM subquery or view in SELECT, JOIN in nested subquery 847 848CREATE TABLE t1 ( a INT ); 849INSERT INTO t1 VALUES (2),(3),(150); 850CREATE TABLE t2 ( b INT ); 851INSERT INTO t2 VALUES (2),(17),(3),(6); 852CREATE VIEW v AS 853SELECT DISTINCT * FROM t1 WHERE a > (SELECT COUNT(*) FROM t1, t2 WHERE a = b); 854PREPARE ps FROM 'SELECT * FROM v LIMIT ROWS EXAMINED 21'; 855EXECUTE ps; 856a 8573 858Warnings: 859Warning 1931 Query execution was interrupted. The query examined at least 22 rows, which exceeds LIMIT ROWS EXAMINED (21). The query result may be incomplete 860EXECUTE ps; 861a 8623 863Warnings: 864Warning 1931 Query execution was interrupted. The query examined at least 22 rows, which exceeds LIMIT ROWS EXAMINED (21). The query result may be incomplete 865drop view v; 866drop table t1, t2; 867# 868# 10.1 Test 869# 870# MDEV-17729: Assertion `! is_set() || m_can_overwrite_status' 871# failed in Diagnostics_area::set_error_status 872# 873set @old_mode= @@sql_mode; 874CREATE TABLE t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,k INT, c CHAR(20)); 875INSERT INTO t1 (k,c) VALUES(0,'0'), (0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'); 876SET @@sql_mode='STRICT_TRANS_TABLES'; 877INSERT INTO t1 (c) SELECT k FROM t1 LIMIT ROWS EXAMINED 2; 878Warnings: 879Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (2). The query result may be incomplete 880SET @@sql_mode=@old_mode; 881DROP TABLE t1; 882# 883# MDEV-18117: Crash with Explain extended when using limit rows examined 884# 885create table t1 (c1 char(2)); 886create table t2 (c2 char(2)); 887insert into t1 values ('bb'), ('cc'), ('aa'), ('dd'); 888insert into t2 values ('bb'), ('cc'), ('dd'), ('ff'); 889explain extended 890select * from t1, t2 where c1 = c2 LIMIT ROWS EXAMINED 2; 891id select_type table type possible_keys key key_len ref rows filtered Extra 8921 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 8931 SIMPLE t2 ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) 894Warnings: 895Note 1003 select `test`.`t1`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`c2` = `test`.`t1`.`c1` 896drop table t1,t2; 897# End of 10.4 tests 898