1SET SESSION DEFAULT_STORAGE_ENGINE='InnoDB'; 2DROP DATABASE IF EXISTS dbt3_s001; 3CREATE DATABASE dbt3_s001; 4use dbt3_s001; 5CREATE INDEX i_l_quantity ON lineitem(l_quantity); 6CREATE INDEX i_o_totalprice ON orders(o_totalprice); 7set @save_use_stat_tables= @@use_stat_tables; 8set @@use_stat_tables=preferably; 9ANALYZE TABLE lineitem, orders; 10show create table lineitem; 11Table Create Table 12lineitem CREATE TABLE `lineitem` ( 13 `l_orderkey` int(11) NOT NULL DEFAULT 0, 14 `l_partkey` int(11) DEFAULT NULL, 15 `l_suppkey` int(11) DEFAULT NULL, 16 `l_linenumber` int(11) NOT NULL DEFAULT 0, 17 `l_quantity` double DEFAULT NULL, 18 `l_extendedprice` double DEFAULT NULL, 19 `l_discount` double DEFAULT NULL, 20 `l_tax` double DEFAULT NULL, 21 `l_returnflag` char(1) DEFAULT NULL, 22 `l_linestatus` char(1) DEFAULT NULL, 23 `l_shipDATE` date DEFAULT NULL, 24 `l_commitDATE` date DEFAULT NULL, 25 `l_receiptDATE` date DEFAULT NULL, 26 `l_shipinstruct` char(25) DEFAULT NULL, 27 `l_shipmode` char(10) DEFAULT NULL, 28 `l_comment` varchar(44) DEFAULT NULL, 29 PRIMARY KEY (`l_orderkey`,`l_linenumber`), 30 KEY `i_l_shipdate` (`l_shipDATE`), 31 KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), 32 KEY `i_l_partkey` (`l_partkey`), 33 KEY `i_l_suppkey` (`l_suppkey`), 34 KEY `i_l_receiptdate` (`l_receiptDATE`), 35 KEY `i_l_orderkey` (`l_orderkey`), 36 KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`), 37 KEY `i_l_commitdate` (`l_commitDATE`), 38 KEY `i_l_quantity` (`l_quantity`) 39) ENGINE=InnoDB DEFAULT CHARSET=latin1 40show create table orders; 41Table Create Table 42orders CREATE TABLE `orders` ( 43 `o_orderkey` int(11) NOT NULL, 44 `o_custkey` int(11) DEFAULT NULL, 45 `o_orderstatus` char(1) DEFAULT NULL, 46 `o_totalprice` double DEFAULT NULL, 47 `o_orderDATE` date DEFAULT NULL, 48 `o_orderpriority` char(15) DEFAULT NULL, 49 `o_clerk` char(15) DEFAULT NULL, 50 `o_shippriority` int(11) DEFAULT NULL, 51 `o_comment` varchar(79) DEFAULT NULL, 52 PRIMARY KEY (`o_orderkey`), 53 KEY `i_o_orderdate` (`o_orderDATE`), 54 KEY `i_o_custkey` (`o_custkey`), 55 KEY `i_o_totalprice` (`o_totalprice`) 56) ENGINE=InnoDB DEFAULT CHARSET=latin1 57set optimizer_use_condition_selectivity=2; 58select 59100 * 60(select count(*) from lineitem 61WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45 62) 63/ 64(select count(*) from lineitem 65where l_shipdate BETWEEN '1997-01-01' AND '1997-06-30') 66as correct_r_filtered_when_using_l_shipdate; 67correct_r_filtered_when_using_l_shipdate 6811.7647 69set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 70WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 71l_quantity > 45; 72id select_type table type possible_keys key key_len ref rows Extra 731 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) Using index condition; Using where; Using rowid filter 74set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 75WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 76l_quantity > 45; 77EXPLAIN 78{ 79 "query_block": { 80 "select_id": 1, 81 "table": { 82 "table_name": "lineitem", 83 "access_type": "range", 84 "possible_keys": ["i_l_shipdate", "i_l_quantity"], 85 "key": "i_l_shipdate", 86 "key_length": "4", 87 "used_key_parts": ["l_shipDATE"], 88 "rowid_filter": { 89 "range": { 90 "key": "i_l_quantity", 91 "used_key_parts": ["l_quantity"] 92 }, 93 "rows": 605, 94 "selectivity_pct": 10.07493755 95 }, 96 "rows": 510, 97 "filtered": 10.07493782, 98 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 99 "attached_condition": "lineitem.l_quantity > 45" 100 } 101 } 102} 103set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 104WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 105l_quantity > 45; 106id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1071 SIMPLE lineitem range|filter i_l_shipdate,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) 60.00 (11%) 10.07 100.00 Using index condition; Using where; Using rowid filter 108set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 109WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 110l_quantity > 45; 111ANALYZE 112{ 113 "query_block": { 114 "select_id": 1, 115 "r_loops": 1, 116 "r_total_time_ms": "REPLACED", 117 "table": { 118 "table_name": "lineitem", 119 "access_type": "range", 120 "possible_keys": ["i_l_shipdate", "i_l_quantity"], 121 "key": "i_l_shipdate", 122 "key_length": "4", 123 "used_key_parts": ["l_shipDATE"], 124 "rowid_filter": { 125 "range": { 126 "key": "i_l_quantity", 127 "used_key_parts": ["l_quantity"] 128 }, 129 "rows": 605, 130 "selectivity_pct": 10.07493755, 131 "r_rows": 605, 132 "r_selectivity_pct": 11.76470588, 133 "r_buffer_size": "REPLACED", 134 "r_filling_time_ms": "REPLACED" 135 }, 136 "r_loops": 1, 137 "rows": 510, 138 "r_rows": 60, 139 "r_table_time_ms": "REPLACED", 140 "r_other_time_ms": "REPLACED", 141 "filtered": 10.07493782, 142 "r_filtered": 100, 143 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 144 "attached_condition": "lineitem.l_quantity > 45" 145 } 146 } 147} 148set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 149WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 150l_quantity > 45; 151l_orderkey l_linenumber l_shipdate l_quantity 1521121 5 1997-04-27 47 1531121 6 1997-04-21 50 1541441 7 1997-06-07 50 1551443 1 1997-02-05 47 1561473 1 1997-05-05 50 1571568 2 1997-04-06 46 1581632 1 1997-01-25 47 1591632 3 1997-01-29 47 1601954 7 1997-06-04 49 1611959 1 1997-05-05 46 1622151 3 1997-01-20 49 1632177 5 1997-05-10 46 1642369 2 1997-01-02 47 1652469 3 1997-01-11 48 1662469 6 1997-03-03 49 1672470 2 1997-06-02 50 168260 1 1997-03-24 50 169288 2 1997-04-19 49 170289 4 1997-03-14 48 1713009 1 1997-03-19 48 1723105 3 1997-02-28 48 1733106 2 1997-02-27 49 1743429 1 1997-04-08 48 1753490 2 1997-06-27 50 1763619 1 1997-01-22 49 1773619 3 1997-01-31 46 1783969 3 1997-05-29 46 1794005 4 1997-01-31 49 1804036 1 1997-06-21 46 1814066 4 1997-02-17 49 1824098 1 1997-01-26 46 183422 3 1997-06-21 46 1844258 3 1997-01-02 46 1854421 2 1997-04-21 46 1864421 3 1997-05-25 46 1874453 3 1997-05-29 48 1884484 7 1997-03-17 50 1894609 3 1997-02-11 46 190484 1 1997-03-06 49 191484 3 1997-01-24 50 192484 5 1997-03-05 48 193485 1 1997-03-28 50 1944868 1 1997-04-29 47 1954868 3 1997-04-23 49 1964934 1 1997-05-20 48 1974967 1 1997-05-27 50 1985090 2 1997-04-05 46 1995152 2 1997-03-10 50 2005158 4 1997-04-10 49 2015606 3 1997-03-11 46 2025606 7 1997-02-01 46 2035762 4 1997-03-02 47 204581 3 1997-02-27 49 2055829 5 1997-01-31 49 2065831 4 1997-02-24 46 2075895 2 1997-04-27 47 2085895 3 1997-03-15 49 2095952 1 1997-06-30 49 210705 1 1997-04-18 46 211836 3 1997-03-21 46 212set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 213WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 214l_quantity > 45; 215id select_type table type possible_keys key key_len ref rows Extra 2161 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 510 Using index condition; Using where 217set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 218WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 219l_quantity > 45; 220EXPLAIN 221{ 222 "query_block": { 223 "select_id": 1, 224 "table": { 225 "table_name": "lineitem", 226 "access_type": "range", 227 "possible_keys": ["i_l_shipdate", "i_l_quantity"], 228 "key": "i_l_shipdate", 229 "key_length": "4", 230 "used_key_parts": ["l_shipDATE"], 231 "rows": 510, 232 "filtered": 10.07493782, 233 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 234 "attached_condition": "lineitem.l_quantity > 45" 235 } 236 } 237} 238set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 239WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 240l_quantity > 45; 241id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2421 SIMPLE lineitem range i_l_shipdate,i_l_quantity i_l_shipdate 4 NULL 510 510.00 10.07 11.76 Using index condition; Using where 243set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 244WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 245l_quantity > 45; 246ANALYZE 247{ 248 "query_block": { 249 "select_id": 1, 250 "r_loops": 1, 251 "r_total_time_ms": "REPLACED", 252 "table": { 253 "table_name": "lineitem", 254 "access_type": "range", 255 "possible_keys": ["i_l_shipdate", "i_l_quantity"], 256 "key": "i_l_shipdate", 257 "key_length": "4", 258 "used_key_parts": ["l_shipDATE"], 259 "r_loops": 1, 260 "rows": 510, 261 "r_rows": 510, 262 "r_table_time_ms": "REPLACED", 263 "r_other_time_ms": "REPLACED", 264 "filtered": 10.07493782, 265 "r_filtered": 11.76470588, 266 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 267 "attached_condition": "lineitem.l_quantity > 45" 268 } 269 } 270} 271set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem 272WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 273l_quantity > 45; 274l_orderkey l_linenumber l_shipdate l_quantity 2751121 5 1997-04-27 47 2761121 6 1997-04-21 50 2771441 7 1997-06-07 50 2781443 1 1997-02-05 47 2791473 1 1997-05-05 50 2801568 2 1997-04-06 46 2811632 1 1997-01-25 47 2821632 3 1997-01-29 47 2831954 7 1997-06-04 49 2841959 1 1997-05-05 46 2852151 3 1997-01-20 49 2862177 5 1997-05-10 46 2872369 2 1997-01-02 47 2882469 3 1997-01-11 48 2892469 6 1997-03-03 49 2902470 2 1997-06-02 50 291260 1 1997-03-24 50 292288 2 1997-04-19 49 293289 4 1997-03-14 48 2943009 1 1997-03-19 48 2953105 3 1997-02-28 48 2963106 2 1997-02-27 49 2973429 1 1997-04-08 48 2983490 2 1997-06-27 50 2993619 1 1997-01-22 49 3003619 3 1997-01-31 46 3013969 3 1997-05-29 46 3024005 4 1997-01-31 49 3034036 1 1997-06-21 46 3044066 4 1997-02-17 49 3054098 1 1997-01-26 46 306422 3 1997-06-21 46 3074258 3 1997-01-02 46 3084421 2 1997-04-21 46 3094421 3 1997-05-25 46 3104453 3 1997-05-29 48 3114484 7 1997-03-17 50 3124609 3 1997-02-11 46 313484 1 1997-03-06 49 314484 3 1997-01-24 50 315484 5 1997-03-05 48 316485 1 1997-03-28 50 3174868 1 1997-04-29 47 3184868 3 1997-04-23 49 3194934 1 1997-05-20 48 3204967 1 1997-05-27 50 3215090 2 1997-04-05 46 3225152 2 1997-03-10 50 3235158 4 1997-04-10 49 3245606 3 1997-03-11 46 3255606 7 1997-02-01 46 3265762 4 1997-03-02 47 327581 3 1997-02-27 49 3285829 5 1997-01-31 49 3295831 4 1997-02-24 46 3305895 2 1997-04-27 47 3315895 3 1997-03-15 49 3325952 1 1997-06-30 49 333705 1 1997-04-18 46 334836 3 1997-03-21 46 335set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 336FROM orders JOIN lineitem ON o_orderkey=l_orderkey 337WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 338o_totalprice between 200000 and 230000; 339id select_type table type possible_keys key key_len ref rows Extra 3401 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index 3411 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 342set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 343FROM orders JOIN lineitem ON o_orderkey=l_orderkey 344WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 345o_totalprice between 200000 and 230000; 346EXPLAIN 347{ 348 "query_block": { 349 "select_id": 1, 350 "table": { 351 "table_name": "lineitem", 352 "access_type": "range", 353 "possible_keys": [ 354 "PRIMARY", 355 "i_l_shipdate", 356 "i_l_orderkey", 357 "i_l_orderkey_quantity" 358 ], 359 "key": "i_l_shipdate", 360 "key_length": "4", 361 "used_key_parts": ["l_shipDATE"], 362 "rows": 98, 363 "filtered": 100, 364 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", 365 "using_index": true 366 }, 367 "table": { 368 "table_name": "orders", 369 "access_type": "eq_ref", 370 "possible_keys": ["PRIMARY", "i_o_totalprice"], 371 "key": "PRIMARY", 372 "key_length": "4", 373 "used_key_parts": ["o_orderkey"], 374 "ref": ["dbt3_s001.lineitem.l_orderkey"], 375 "rows": 1, 376 "filtered": 4.733333111, 377 "attached_condition": "orders.o_totalprice between 200000 and 230000" 378 } 379 } 380} 381set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 382FROM orders JOIN lineitem ON o_orderkey=l_orderkey 383WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 384o_totalprice between 200000 and 230000; 385id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 3861 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using where; Using index 3871 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 11.22 Using where 388set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 389FROM orders JOIN lineitem ON o_orderkey=l_orderkey 390WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 391o_totalprice between 200000 and 230000; 392ANALYZE 393{ 394 "query_block": { 395 "select_id": 1, 396 "r_loops": 1, 397 "r_total_time_ms": "REPLACED", 398 "table": { 399 "table_name": "lineitem", 400 "access_type": "range", 401 "possible_keys": [ 402 "PRIMARY", 403 "i_l_shipdate", 404 "i_l_orderkey", 405 "i_l_orderkey_quantity" 406 ], 407 "key": "i_l_shipdate", 408 "key_length": "4", 409 "used_key_parts": ["l_shipDATE"], 410 "r_loops": 1, 411 "rows": 98, 412 "r_rows": 98, 413 "r_table_time_ms": "REPLACED", 414 "r_other_time_ms": "REPLACED", 415 "filtered": 100, 416 "r_filtered": 100, 417 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", 418 "using_index": true 419 }, 420 "table": { 421 "table_name": "orders", 422 "access_type": "eq_ref", 423 "possible_keys": ["PRIMARY", "i_o_totalprice"], 424 "key": "PRIMARY", 425 "key_length": "4", 426 "used_key_parts": ["o_orderkey"], 427 "ref": ["dbt3_s001.lineitem.l_orderkey"], 428 "r_loops": 98, 429 "rows": 1, 430 "r_rows": 1, 431 "r_table_time_ms": "REPLACED", 432 "r_other_time_ms": "REPLACED", 433 "filtered": 4.733333111, 434 "r_filtered": 11.2244898, 435 "attached_condition": "orders.o_totalprice between 200000 and 230000" 436 } 437 } 438} 439set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 440FROM orders JOIN lineitem ON o_orderkey=l_orderkey 441WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 442o_totalprice between 200000 and 230000; 443o_orderkey l_linenumber l_shipdate o_totalprice 4441156 3 1997-01-24 217682.81 4451156 4 1997-01-18 217682.81 4461156 6 1997-01-27 217682.81 4471156 7 1997-01-01 217682.81 4482180 2 1997-01-03 208481.57 4492180 3 1997-01-03 208481.57 4503619 1 1997-01-22 222274.54 4513619 3 1997-01-31 222274.54 4523619 6 1997-01-25 222274.54 453484 3 1997-01-24 219920.62 4545606 6 1997-01-11 219959.08 455set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 456FROM orders JOIN lineitem ON o_orderkey=l_orderkey 457WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 458o_totalprice between 200000 and 230000; 459id select_type table type possible_keys key key_len ref rows Extra 4601 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using where; Using index 4611 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 462set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 463FROM orders JOIN lineitem ON o_orderkey=l_orderkey 464WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 465o_totalprice between 200000 and 230000; 466EXPLAIN 467{ 468 "query_block": { 469 "select_id": 1, 470 "table": { 471 "table_name": "lineitem", 472 "access_type": "range", 473 "possible_keys": [ 474 "PRIMARY", 475 "i_l_shipdate", 476 "i_l_orderkey", 477 "i_l_orderkey_quantity" 478 ], 479 "key": "i_l_shipdate", 480 "key_length": "4", 481 "used_key_parts": ["l_shipDATE"], 482 "rows": 98, 483 "filtered": 100, 484 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", 485 "using_index": true 486 }, 487 "table": { 488 "table_name": "orders", 489 "access_type": "eq_ref", 490 "possible_keys": ["PRIMARY", "i_o_totalprice"], 491 "key": "PRIMARY", 492 "key_length": "4", 493 "used_key_parts": ["o_orderkey"], 494 "ref": ["dbt3_s001.lineitem.l_orderkey"], 495 "rows": 1, 496 "filtered": 4.733333111, 497 "attached_condition": "orders.o_totalprice between 200000 and 230000" 498 } 499 } 500} 501set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 502FROM orders JOIN lineitem ON o_orderkey=l_orderkey 503WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 504o_totalprice between 200000 and 230000; 505id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 5061 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using where; Using index 5071 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 11.22 Using where 508set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 509FROM orders JOIN lineitem ON o_orderkey=l_orderkey 510WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 511o_totalprice between 200000 and 230000; 512ANALYZE 513{ 514 "query_block": { 515 "select_id": 1, 516 "r_loops": 1, 517 "r_total_time_ms": "REPLACED", 518 "table": { 519 "table_name": "lineitem", 520 "access_type": "range", 521 "possible_keys": [ 522 "PRIMARY", 523 "i_l_shipdate", 524 "i_l_orderkey", 525 "i_l_orderkey_quantity" 526 ], 527 "key": "i_l_shipdate", 528 "key_length": "4", 529 "used_key_parts": ["l_shipDATE"], 530 "r_loops": 1, 531 "rows": 98, 532 "r_rows": 98, 533 "r_table_time_ms": "REPLACED", 534 "r_other_time_ms": "REPLACED", 535 "filtered": 100, 536 "r_filtered": 100, 537 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", 538 "using_index": true 539 }, 540 "table": { 541 "table_name": "orders", 542 "access_type": "eq_ref", 543 "possible_keys": ["PRIMARY", "i_o_totalprice"], 544 "key": "PRIMARY", 545 "key_length": "4", 546 "used_key_parts": ["o_orderkey"], 547 "ref": ["dbt3_s001.lineitem.l_orderkey"], 548 "r_loops": 98, 549 "rows": 1, 550 "r_rows": 1, 551 "r_table_time_ms": "REPLACED", 552 "r_other_time_ms": "REPLACED", 553 "filtered": 4.733333111, 554 "r_filtered": 11.2244898, 555 "attached_condition": "orders.o_totalprice between 200000 and 230000" 556 } 557 } 558} 559set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 560FROM orders JOIN lineitem ON o_orderkey=l_orderkey 561WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND 562o_totalprice between 200000 and 230000; 563o_orderkey l_linenumber l_shipdate o_totalprice 5641156 3 1997-01-24 217682.81 5651156 4 1997-01-18 217682.81 5661156 6 1997-01-27 217682.81 5671156 7 1997-01-01 217682.81 5682180 2 1997-01-03 208481.57 5692180 3 1997-01-03 208481.57 5703619 1 1997-01-22 222274.54 5713619 3 1997-01-31 222274.54 5723619 6 1997-01-25 222274.54 573484 3 1997-01-24 219920.62 5745606 6 1997-01-11 219959.08 575set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 576FROM orders JOIN lineitem ON o_orderkey=l_orderkey 577WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 578l_quantity > 45 AND 579o_totalprice between 180000 and 230000; 580id select_type table type possible_keys key key_len ref rows Extra 5811 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) Using index condition; Using where; Using rowid filter 5821 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 583set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 584FROM orders JOIN lineitem ON o_orderkey=l_orderkey 585WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 586l_quantity > 45 AND 587o_totalprice between 180000 and 230000; 588EXPLAIN 589{ 590 "query_block": { 591 "select_id": 1, 592 "table": { 593 "table_name": "lineitem", 594 "access_type": "range", 595 "possible_keys": [ 596 "PRIMARY", 597 "i_l_shipdate", 598 "i_l_orderkey", 599 "i_l_orderkey_quantity", 600 "i_l_quantity" 601 ], 602 "key": "i_l_shipdate", 603 "key_length": "4", 604 "used_key_parts": ["l_shipDATE"], 605 "rowid_filter": { 606 "range": { 607 "key": "i_l_quantity", 608 "used_key_parts": ["l_quantity"] 609 }, 610 "rows": 605, 611 "selectivity_pct": 10.07493755 612 }, 613 "rows": 510, 614 "filtered": 10.07493782, 615 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 616 "attached_condition": "lineitem.l_quantity > 45" 617 }, 618 "table": { 619 "table_name": "orders", 620 "access_type": "eq_ref", 621 "possible_keys": ["PRIMARY", "i_o_totalprice"], 622 "key": "PRIMARY", 623 "key_length": "4", 624 "used_key_parts": ["o_orderkey"], 625 "ref": ["dbt3_s001.lineitem.l_orderkey"], 626 "rows": 1, 627 "filtered": 9.600000381, 628 "attached_condition": "orders.o_totalprice between 180000 and 230000" 629 } 630 } 631} 632set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 633FROM orders JOIN lineitem ON o_orderkey=l_orderkey 634WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 635l_quantity > 45 AND 636o_totalprice between 180000 and 230000; 637id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 6381 SIMPLE lineitem range|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity i_l_shipdate|i_l_quantity 4|9 NULL 510 (10%) 60.00 (11%) 10.07 100.00 Using index condition; Using where; Using rowid filter 6391 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 9.60 26.67 Using where 640set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 641FROM orders JOIN lineitem ON o_orderkey=l_orderkey 642WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 643l_quantity > 45 AND 644o_totalprice between 180000 and 230000; 645ANALYZE 646{ 647 "query_block": { 648 "select_id": 1, 649 "r_loops": 1, 650 "r_total_time_ms": "REPLACED", 651 "table": { 652 "table_name": "lineitem", 653 "access_type": "range", 654 "possible_keys": [ 655 "PRIMARY", 656 "i_l_shipdate", 657 "i_l_orderkey", 658 "i_l_orderkey_quantity", 659 "i_l_quantity" 660 ], 661 "key": "i_l_shipdate", 662 "key_length": "4", 663 "used_key_parts": ["l_shipDATE"], 664 "rowid_filter": { 665 "range": { 666 "key": "i_l_quantity", 667 "used_key_parts": ["l_quantity"] 668 }, 669 "rows": 605, 670 "selectivity_pct": 10.07493755, 671 "r_rows": 605, 672 "r_selectivity_pct": 11.76470588, 673 "r_buffer_size": "REPLACED", 674 "r_filling_time_ms": "REPLACED" 675 }, 676 "r_loops": 1, 677 "rows": 510, 678 "r_rows": 60, 679 "r_table_time_ms": "REPLACED", 680 "r_other_time_ms": "REPLACED", 681 "filtered": 10.07493782, 682 "r_filtered": 100, 683 "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", 684 "attached_condition": "lineitem.l_quantity > 45" 685 }, 686 "table": { 687 "table_name": "orders", 688 "access_type": "eq_ref", 689 "possible_keys": ["PRIMARY", "i_o_totalprice"], 690 "key": "PRIMARY", 691 "key_length": "4", 692 "used_key_parts": ["o_orderkey"], 693 "ref": ["dbt3_s001.lineitem.l_orderkey"], 694 "r_loops": 60, 695 "rows": 1, 696 "r_rows": 1, 697 "r_table_time_ms": "REPLACED", 698 "r_other_time_ms": "REPLACED", 699 "filtered": 9.600000381, 700 "r_filtered": 26.66666667, 701 "attached_condition": "orders.o_totalprice between 180000 and 230000" 702 } 703 } 704} 705set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 706FROM orders JOIN lineitem ON o_orderkey=l_orderkey 707WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 708l_quantity > 45 AND 709o_totalprice between 180000 and 230000; 710o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 7111632 1 1997-01-25 47 183286.33 7121632 3 1997-01-29 47 183286.33 7132177 5 1997-05-10 46 183493.42 7142469 3 1997-01-11 48 192074.23 7152469 6 1997-03-03 49 192074.23 7163619 1 1997-01-22 49 222274.54 7173619 3 1997-01-31 46 222274.54 718484 1 1997-03-06 49 219920.62 719484 3 1997-01-24 50 219920.62 720484 5 1997-03-05 48 219920.62 7214934 1 1997-05-20 48 180478.16 7225606 3 1997-03-11 46 219959.08 7235606 7 1997-02-01 46 219959.08 7245829 5 1997-01-31 49 183734.56 7255895 2 1997-04-27 47 201419.83 7265895 3 1997-03-15 49 201419.83 727set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 728FROM orders JOIN lineitem ON o_orderkey=l_orderkey 729WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 730l_quantity > 45 AND 731o_totalprice between 180000 and 230000; 732id select_type table type possible_keys key key_len ref rows Extra 7331 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 Using where; Using index 7341 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 735set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 736FROM orders JOIN lineitem ON o_orderkey=l_orderkey 737WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 738l_quantity > 45 AND 739o_totalprice between 180000 and 230000; 740EXPLAIN 741{ 742 "query_block": { 743 "select_id": 1, 744 "table": { 745 "table_name": "orders", 746 "access_type": "range", 747 "possible_keys": ["PRIMARY", "i_o_totalprice"], 748 "key": "i_o_totalprice", 749 "key_length": "9", 750 "used_key_parts": ["o_totalprice"], 751 "rows": 144, 752 "filtered": 100, 753 "attached_condition": "orders.o_totalprice between 180000 and 230000", 754 "using_index": true 755 }, 756 "table": { 757 "table_name": "lineitem", 758 "access_type": "ref", 759 "possible_keys": [ 760 "PRIMARY", 761 "i_l_shipdate", 762 "i_l_orderkey", 763 "i_l_orderkey_quantity", 764 "i_l_quantity" 765 ], 766 "key": "PRIMARY", 767 "key_length": "4", 768 "used_key_parts": ["l_orderkey"], 769 "ref": ["dbt3_s001.orders.o_orderkey"], 770 "rows": 4, 771 "filtered": 0.855656624, 772 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" 773 } 774 } 775} 776set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 777FROM orders JOIN lineitem ON o_orderkey=l_orderkey 778WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 779l_quantity > 45 AND 780o_totalprice between 180000 and 230000; 781id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 7821 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 144.00 100.00 100.00 Using where; Using index 7831 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity,i_l_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.62 0.86 1.68 Using where 784set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 785FROM orders JOIN lineitem ON o_orderkey=l_orderkey 786WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 787l_quantity > 45 AND 788o_totalprice between 180000 and 230000; 789ANALYZE 790{ 791 "query_block": { 792 "select_id": 1, 793 "r_loops": 1, 794 "r_total_time_ms": "REPLACED", 795 "table": { 796 "table_name": "orders", 797 "access_type": "range", 798 "possible_keys": ["PRIMARY", "i_o_totalprice"], 799 "key": "i_o_totalprice", 800 "key_length": "9", 801 "used_key_parts": ["o_totalprice"], 802 "r_loops": 1, 803 "rows": 144, 804 "r_rows": 144, 805 "r_table_time_ms": "REPLACED", 806 "r_other_time_ms": "REPLACED", 807 "filtered": 100, 808 "r_filtered": 100, 809 "attached_condition": "orders.o_totalprice between 180000 and 230000", 810 "using_index": true 811 }, 812 "table": { 813 "table_name": "lineitem", 814 "access_type": "ref", 815 "possible_keys": [ 816 "PRIMARY", 817 "i_l_shipdate", 818 "i_l_orderkey", 819 "i_l_orderkey_quantity", 820 "i_l_quantity" 821 ], 822 "key": "PRIMARY", 823 "key_length": "4", 824 "used_key_parts": ["l_orderkey"], 825 "ref": ["dbt3_s001.orders.o_orderkey"], 826 "r_loops": 144, 827 "rows": 4, 828 "r_rows": 6.625, 829 "r_table_time_ms": "REPLACED", 830 "r_other_time_ms": "REPLACED", 831 "filtered": 0.855656624, 832 "r_filtered": 1.677148847, 833 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" 834 } 835 } 836} 837set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice 838FROM orders JOIN lineitem ON o_orderkey=l_orderkey 839WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 840l_quantity > 45 AND 841o_totalprice between 180000 and 230000; 842o_orderkey l_linenumber l_shipdate l_quantity o_totalprice 8431632 1 1997-01-25 47 183286.33 8441632 3 1997-01-29 47 183286.33 8452177 5 1997-05-10 46 183493.42 8462469 3 1997-01-11 48 192074.23 8472469 6 1997-03-03 49 192074.23 8483619 1 1997-01-22 49 222274.54 8493619 3 1997-01-31 46 222274.54 850484 1 1997-03-06 49 219920.62 851484 3 1997-01-24 50 219920.62 852484 5 1997-03-05 48 219920.62 8534934 1 1997-05-20 48 180478.16 8545606 3 1997-03-11 46 219959.08 8555606 7 1997-02-01 46 219959.08 8565829 5 1997-01-31 49 183734.56 8575895 2 1997-04-27 47 201419.83 8585895 3 1997-03-15 49 201419.83 859set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 860FROM orders JOIN lineitem ON o_orderkey=l_orderkey 861WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 862o_totalprice between 200000 and 230000; 863id select_type table type possible_keys key key_len ref rows Extra 8641 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index 8651 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 866set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 867FROM orders JOIN lineitem ON o_orderkey=l_orderkey 868WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 869o_totalprice between 200000 and 230000; 870EXPLAIN 871{ 872 "query_block": { 873 "select_id": 1, 874 "table": { 875 "table_name": "orders", 876 "access_type": "range", 877 "possible_keys": ["PRIMARY", "i_o_totalprice"], 878 "key": "i_o_totalprice", 879 "key_length": "9", 880 "used_key_parts": ["o_totalprice"], 881 "rows": 71, 882 "filtered": 100, 883 "attached_condition": "orders.o_totalprice between 200000 and 230000", 884 "using_index": true 885 }, 886 "table": { 887 "table_name": "lineitem", 888 "access_type": "ref", 889 "possible_keys": [ 890 "PRIMARY", 891 "i_l_shipdate", 892 "i_l_orderkey", 893 "i_l_orderkey_quantity" 894 ], 895 "key": "PRIMARY", 896 "key_length": "4", 897 "used_key_parts": ["l_orderkey"], 898 "ref": ["dbt3_s001.orders.o_orderkey"], 899 "rows": 4, 900 "filtered": 8.492922783, 901 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" 902 } 903 } 904} 905set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 906FROM orders JOIN lineitem ON o_orderkey=l_orderkey 907WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 908o_totalprice between 200000 and 230000; 909id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 9101 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 71.00 100.00 100.00 Using where; Using index 9111 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.49 7.77 Using where 912set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 913FROM orders JOIN lineitem ON o_orderkey=l_orderkey 914WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 915o_totalprice between 200000 and 230000; 916ANALYZE 917{ 918 "query_block": { 919 "select_id": 1, 920 "r_loops": 1, 921 "r_total_time_ms": "REPLACED", 922 "table": { 923 "table_name": "orders", 924 "access_type": "range", 925 "possible_keys": ["PRIMARY", "i_o_totalprice"], 926 "key": "i_o_totalprice", 927 "key_length": "9", 928 "used_key_parts": ["o_totalprice"], 929 "r_loops": 1, 930 "rows": 71, 931 "r_rows": 71, 932 "r_table_time_ms": "REPLACED", 933 "r_other_time_ms": "REPLACED", 934 "filtered": 100, 935 "r_filtered": 100, 936 "attached_condition": "orders.o_totalprice between 200000 and 230000", 937 "using_index": true 938 }, 939 "table": { 940 "table_name": "lineitem", 941 "access_type": "ref", 942 "possible_keys": [ 943 "PRIMARY", 944 "i_l_shipdate", 945 "i_l_orderkey", 946 "i_l_orderkey_quantity" 947 ], 948 "key": "PRIMARY", 949 "key_length": "4", 950 "used_key_parts": ["l_orderkey"], 951 "ref": ["dbt3_s001.orders.o_orderkey"], 952 "r_loops": 71, 953 "rows": 4, 954 "r_rows": 6.704225352, 955 "r_table_time_ms": "REPLACED", 956 "r_other_time_ms": "REPLACED", 957 "filtered": 8.492922783, 958 "r_filtered": 7.773109244, 959 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" 960 } 961 } 962} 963set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 964FROM orders JOIN lineitem ON o_orderkey=l_orderkey 965WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 966o_totalprice between 200000 and 230000; 967o_orderkey l_linenumber l_shipdate o_totalprice 9681156 3 1997-01-24 217682.81 9691156 4 1997-01-18 217682.81 9701156 6 1997-01-27 217682.81 9711156 7 1997-01-01 217682.81 9721890 1 1997-04-02 202364.58 9731890 3 1997-02-09 202364.58 9741890 4 1997-04-08 202364.58 9751890 5 1997-04-15 202364.58 9761890 6 1997-02-13 202364.58 9772180 2 1997-01-03 208481.57 9782180 3 1997-01-03 208481.57 9793619 1 1997-01-22 222274.54 9803619 3 1997-01-31 222274.54 9813619 4 1997-03-18 222274.54 9823619 6 1997-01-25 222274.54 983453 1 1997-06-30 216826.73 984453 2 1997-06-30 216826.73 985484 1 1997-03-06 219920.62 986484 2 1997-04-09 219920.62 987484 3 1997-01-24 219920.62 988484 4 1997-04-29 219920.62 989484 5 1997-03-05 219920.62 990484 6 1997-04-06 219920.62 9915606 2 1997-02-23 219959.08 9925606 3 1997-03-11 219959.08 9935606 4 1997-02-06 219959.08 9945606 6 1997-01-11 219959.08 9955606 7 1997-02-01 219959.08 9965859 2 1997-05-15 210643.96 9975859 5 1997-05-28 210643.96 9985859 6 1997-06-15 210643.96 9995895 1 1997-04-05 201419.83 10005895 2 1997-04-27 201419.83 10015895 3 1997-03-15 201419.83 10025895 4 1997-03-03 201419.83 10035895 5 1997-04-30 201419.83 10045895 6 1997-04-19 201419.83 1005set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 1006FROM orders JOIN lineitem ON o_orderkey=l_orderkey 1007WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 1008o_totalprice between 200000 and 230000; 1009id select_type table type possible_keys key key_len ref rows Extra 10101 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 Using where; Using index 10111 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1012set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 1013FROM orders JOIN lineitem ON o_orderkey=l_orderkey 1014WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 1015o_totalprice between 200000 and 230000; 1016EXPLAIN 1017{ 1018 "query_block": { 1019 "select_id": 1, 1020 "table": { 1021 "table_name": "orders", 1022 "access_type": "range", 1023 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1024 "key": "i_o_totalprice", 1025 "key_length": "9", 1026 "used_key_parts": ["o_totalprice"], 1027 "rows": 71, 1028 "filtered": 100, 1029 "attached_condition": "orders.o_totalprice between 200000 and 230000", 1030 "using_index": true 1031 }, 1032 "table": { 1033 "table_name": "lineitem", 1034 "access_type": "ref", 1035 "possible_keys": [ 1036 "PRIMARY", 1037 "i_l_shipdate", 1038 "i_l_orderkey", 1039 "i_l_orderkey_quantity" 1040 ], 1041 "key": "PRIMARY", 1042 "key_length": "4", 1043 "used_key_parts": ["l_orderkey"], 1044 "ref": ["dbt3_s001.orders.o_orderkey"], 1045 "rows": 4, 1046 "filtered": 8.492922783, 1047 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" 1048 } 1049 } 1050} 1051set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 1052FROM orders JOIN lineitem ON o_orderkey=l_orderkey 1053WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 1054o_totalprice between 200000 and 230000; 1055id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 10561 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 71.00 100.00 100.00 Using where; Using index 10571 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.70 8.49 7.77 Using where 1058set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 1059FROM orders JOIN lineitem ON o_orderkey=l_orderkey 1060WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 1061o_totalprice between 200000 and 230000; 1062ANALYZE 1063{ 1064 "query_block": { 1065 "select_id": 1, 1066 "r_loops": 1, 1067 "r_total_time_ms": "REPLACED", 1068 "table": { 1069 "table_name": "orders", 1070 "access_type": "range", 1071 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1072 "key": "i_o_totalprice", 1073 "key_length": "9", 1074 "used_key_parts": ["o_totalprice"], 1075 "r_loops": 1, 1076 "rows": 71, 1077 "r_rows": 71, 1078 "r_table_time_ms": "REPLACED", 1079 "r_other_time_ms": "REPLACED", 1080 "filtered": 100, 1081 "r_filtered": 100, 1082 "attached_condition": "orders.o_totalprice between 200000 and 230000", 1083 "using_index": true 1084 }, 1085 "table": { 1086 "table_name": "lineitem", 1087 "access_type": "ref", 1088 "possible_keys": [ 1089 "PRIMARY", 1090 "i_l_shipdate", 1091 "i_l_orderkey", 1092 "i_l_orderkey_quantity" 1093 ], 1094 "key": "PRIMARY", 1095 "key_length": "4", 1096 "used_key_parts": ["l_orderkey"], 1097 "ref": ["dbt3_s001.orders.o_orderkey"], 1098 "r_loops": 71, 1099 "rows": 4, 1100 "r_rows": 6.704225352, 1101 "r_table_time_ms": "REPLACED", 1102 "r_other_time_ms": "REPLACED", 1103 "filtered": 8.492922783, 1104 "r_filtered": 7.773109244, 1105 "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" 1106 } 1107 } 1108} 1109set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice 1110FROM orders JOIN lineitem ON o_orderkey=l_orderkey 1111WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND 1112o_totalprice between 200000 and 230000; 1113o_orderkey l_linenumber l_shipdate o_totalprice 11141156 3 1997-01-24 217682.81 11151156 4 1997-01-18 217682.81 11161156 6 1997-01-27 217682.81 11171156 7 1997-01-01 217682.81 11181890 1 1997-04-02 202364.58 11191890 3 1997-02-09 202364.58 11201890 4 1997-04-08 202364.58 11211890 5 1997-04-15 202364.58 11221890 6 1997-02-13 202364.58 11232180 2 1997-01-03 208481.57 11242180 3 1997-01-03 208481.57 11253619 1 1997-01-22 222274.54 11263619 3 1997-01-31 222274.54 11273619 4 1997-03-18 222274.54 11283619 6 1997-01-25 222274.54 1129453 1 1997-06-30 216826.73 1130453 2 1997-06-30 216826.73 1131484 1 1997-03-06 219920.62 1132484 2 1997-04-09 219920.62 1133484 3 1997-01-24 219920.62 1134484 4 1997-04-29 219920.62 1135484 5 1997-03-05 219920.62 1136484 6 1997-04-06 219920.62 11375606 2 1997-02-23 219959.08 11385606 3 1997-03-11 219959.08 11395606 4 1997-02-06 219959.08 11405606 6 1997-01-11 219959.08 11415606 7 1997-02-01 219959.08 11425859 2 1997-05-15 210643.96 11435859 5 1997-05-28 210643.96 11445859 6 1997-06-15 210643.96 11455895 1 1997-04-05 201419.83 11465895 2 1997-04-27 201419.83 11475895 3 1997-03-15 201419.83 11485895 4 1997-03-03 201419.83 11495895 5 1997-04-30 201419.83 11505895 6 1997-04-19 201419.83 1151# 1152# MDEV-18413: find constraint correlated indexes 1153# 1154ALTER TABLE lineitem ADD CONSTRAINT l_date CHECK(l_shipdate < l_receiptdate); 1155# Filter on l_shipdate is not used because it participates in 1156# the same constraint as l_receiptdate. 1157# Access is made on l_receiptdate. 1158set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice 1159FROM orders, lineitem 1160WHERE o_orderkey=l_orderkey AND 1161l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1162l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1163o_totalprice BETWEEN 200000 AND 250000; 1164id select_type table type possible_keys key key_len ref rows Extra 11651 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where 11661 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 1167set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice 1168FROM orders, lineitem 1169WHERE o_orderkey=l_orderkey AND 1170l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1171l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1172o_totalprice BETWEEN 200000 AND 250000; 1173EXPLAIN 1174{ 1175 "query_block": { 1176 "select_id": 1, 1177 "table": { 1178 "table_name": "lineitem", 1179 "access_type": "range", 1180 "possible_keys": [ 1181 "PRIMARY", 1182 "i_l_shipdate", 1183 "i_l_receiptdate", 1184 "i_l_orderkey", 1185 "i_l_orderkey_quantity" 1186 ], 1187 "key": "i_l_receiptdate", 1188 "key_length": "4", 1189 "used_key_parts": ["l_receiptDATE"], 1190 "rows": 18, 1191 "filtered": 0.566194832, 1192 "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", 1193 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" 1194 }, 1195 "table": { 1196 "table_name": "orders", 1197 "access_type": "eq_ref", 1198 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1199 "key": "PRIMARY", 1200 "key_length": "4", 1201 "used_key_parts": ["o_orderkey"], 1202 "ref": ["dbt3_s001.lineitem.l_orderkey"], 1203 "rows": 1, 1204 "filtered": 5.666666508, 1205 "attached_condition": "orders.o_totalprice between 200000 and 250000" 1206 } 1207 } 1208} 1209set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice 1210FROM orders, lineitem 1211WHERE o_orderkey=l_orderkey AND 1212l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1213l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1214o_totalprice BETWEEN 200000 AND 250000; 1215id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 12161 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where 12171 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where 1218set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice 1219FROM orders, lineitem 1220WHERE o_orderkey=l_orderkey AND 1221l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1222l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1223o_totalprice BETWEEN 200000 AND 250000; 1224ANALYZE 1225{ 1226 "query_block": { 1227 "select_id": 1, 1228 "r_loops": 1, 1229 "r_total_time_ms": "REPLACED", 1230 "table": { 1231 "table_name": "lineitem", 1232 "access_type": "range", 1233 "possible_keys": [ 1234 "PRIMARY", 1235 "i_l_shipdate", 1236 "i_l_receiptdate", 1237 "i_l_orderkey", 1238 "i_l_orderkey_quantity" 1239 ], 1240 "key": "i_l_receiptdate", 1241 "key_length": "4", 1242 "used_key_parts": ["l_receiptDATE"], 1243 "r_loops": 1, 1244 "rows": 18, 1245 "r_rows": 18, 1246 "r_table_time_ms": "REPLACED", 1247 "r_other_time_ms": "REPLACED", 1248 "filtered": 0.566194832, 1249 "r_filtered": 38.88888889, 1250 "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", 1251 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" 1252 }, 1253 "table": { 1254 "table_name": "orders", 1255 "access_type": "eq_ref", 1256 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1257 "key": "PRIMARY", 1258 "key_length": "4", 1259 "used_key_parts": ["o_orderkey"], 1260 "ref": ["dbt3_s001.lineitem.l_orderkey"], 1261 "r_loops": 7, 1262 "rows": 1, 1263 "r_rows": 1, 1264 "r_table_time_ms": "REPLACED", 1265 "r_other_time_ms": "REPLACED", 1266 "filtered": 5.666666508, 1267 "r_filtered": 14.28571429, 1268 "attached_condition": "orders.o_totalprice between 200000 and 250000" 1269 } 1270 } 1271} 1272set statement optimizer_switch='rowid_filter=on' for SELECT l_shipdate, l_receiptdate, o_totalprice 1273FROM orders, lineitem 1274WHERE o_orderkey=l_orderkey AND 1275l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1276l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1277o_totalprice BETWEEN 200000 AND 250000; 1278l_shipdate l_receiptdate o_totalprice 12791996-10-07 1996-10-08 202623.92 1280set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT l_shipdate, l_receiptdate, o_totalprice 1281FROM orders, lineitem 1282WHERE o_orderkey=l_orderkey AND 1283l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1284l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1285o_totalprice BETWEEN 200000 AND 250000; 1286id select_type table type possible_keys key key_len ref rows Extra 12871 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 Using index condition; Using where 12881 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 Using where 1289set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice 1290FROM orders, lineitem 1291WHERE o_orderkey=l_orderkey AND 1292l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1293l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1294o_totalprice BETWEEN 200000 AND 250000; 1295EXPLAIN 1296{ 1297 "query_block": { 1298 "select_id": 1, 1299 "table": { 1300 "table_name": "lineitem", 1301 "access_type": "range", 1302 "possible_keys": [ 1303 "PRIMARY", 1304 "i_l_shipdate", 1305 "i_l_receiptdate", 1306 "i_l_orderkey", 1307 "i_l_orderkey_quantity" 1308 ], 1309 "key": "i_l_receiptdate", 1310 "key_length": "4", 1311 "used_key_parts": ["l_receiptDATE"], 1312 "rows": 18, 1313 "filtered": 0.566194832, 1314 "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", 1315 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" 1316 }, 1317 "table": { 1318 "table_name": "orders", 1319 "access_type": "eq_ref", 1320 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1321 "key": "PRIMARY", 1322 "key_length": "4", 1323 "used_key_parts": ["o_orderkey"], 1324 "ref": ["dbt3_s001.lineitem.l_orderkey"], 1325 "rows": 1, 1326 "filtered": 5.666666508, 1327 "attached_condition": "orders.o_totalprice between 200000 and 250000" 1328 } 1329 } 1330} 1331set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_shipdate, l_receiptdate, o_totalprice 1332FROM orders, lineitem 1333WHERE o_orderkey=l_orderkey AND 1334l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1335l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1336o_totalprice BETWEEN 200000 AND 250000; 1337id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 13381 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity i_l_receiptdate 4 NULL 18 18.00 0.57 38.89 Using index condition; Using where 13391 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 5.67 14.29 Using where 1340set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_shipdate, l_receiptdate, o_totalprice 1341FROM orders, lineitem 1342WHERE o_orderkey=l_orderkey AND 1343l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1344l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1345o_totalprice BETWEEN 200000 AND 250000; 1346ANALYZE 1347{ 1348 "query_block": { 1349 "select_id": 1, 1350 "r_loops": 1, 1351 "r_total_time_ms": "REPLACED", 1352 "table": { 1353 "table_name": "lineitem", 1354 "access_type": "range", 1355 "possible_keys": [ 1356 "PRIMARY", 1357 "i_l_shipdate", 1358 "i_l_receiptdate", 1359 "i_l_orderkey", 1360 "i_l_orderkey_quantity" 1361 ], 1362 "key": "i_l_receiptdate", 1363 "key_length": "4", 1364 "used_key_parts": ["l_receiptDATE"], 1365 "r_loops": 1, 1366 "rows": 18, 1367 "r_rows": 18, 1368 "r_table_time_ms": "REPLACED", 1369 "r_other_time_ms": "REPLACED", 1370 "filtered": 0.566194832, 1371 "r_filtered": 38.88888889, 1372 "index_condition": "lineitem.l_receiptDATE between '1996-10-05' and '1996-10-10'", 1373 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-10-10'" 1374 }, 1375 "table": { 1376 "table_name": "orders", 1377 "access_type": "eq_ref", 1378 "possible_keys": ["PRIMARY", "i_o_totalprice"], 1379 "key": "PRIMARY", 1380 "key_length": "4", 1381 "used_key_parts": ["o_orderkey"], 1382 "ref": ["dbt3_s001.lineitem.l_orderkey"], 1383 "r_loops": 7, 1384 "rows": 1, 1385 "r_rows": 1, 1386 "r_table_time_ms": "REPLACED", 1387 "r_other_time_ms": "REPLACED", 1388 "filtered": 5.666666508, 1389 "r_filtered": 14.28571429, 1390 "attached_condition": "orders.o_totalprice between 200000 and 250000" 1391 } 1392 } 1393} 1394set statement optimizer_switch='rowid_filter=off' for SELECT l_shipdate, l_receiptdate, o_totalprice 1395FROM orders, lineitem 1396WHERE o_orderkey=l_orderkey AND 1397l_shipdate BETWEEN '1996-10-01' AND '1996-10-10' AND 1398l_receiptdate BETWEEN '1996-10-05' AND '1996-10-10' AND 1399o_totalprice BETWEEN 200000 AND 250000; 1400l_shipdate l_receiptdate o_totalprice 14011996-10-07 1996-10-08 202623.92 1402ALTER TABLE orders ADD COLUMN o_totaldiscount double; 1403UPDATE orders SET o_totaldiscount = o_totalprice*(o_custkey/1000); 1404CREATE INDEX i_o_totaldiscount on orders(o_totaldiscount); 1405ALTER TABLE orders ADD CONSTRAINT o_price CHECK(o_totalprice > o_totaldiscount); 1406# Filter on o_totalprice is not used because it participates in 1407# the same constraint as o_discount. 1408# Access is made on o_discount. 1409set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate 1410FROM orders, lineitem 1411WHERE o_orderkey=l_orderkey AND 1412o_totaldiscount BETWEEN 18000 AND 20000 AND 1413o_totalprice BETWEEN 200000 AND 220000 AND 1414l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1415id select_type table type possible_keys key key_len ref rows Extra 14161 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where 14171 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1418set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1419FROM orders, lineitem 1420WHERE o_orderkey=l_orderkey AND 1421o_totaldiscount BETWEEN 18000 AND 20000 AND 1422o_totalprice BETWEEN 200000 AND 220000 AND 1423l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1424EXPLAIN 1425{ 1426 "query_block": { 1427 "select_id": 1, 1428 "table": { 1429 "table_name": "orders", 1430 "access_type": "range", 1431 "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], 1432 "key": "i_o_totaldiscount", 1433 "key_length": "9", 1434 "used_key_parts": ["o_totaldiscount"], 1435 "rows": 41, 1436 "filtered": 3.333333254, 1437 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1438 "attached_condition": "orders.o_totalprice between 200000 and 220000" 1439 }, 1440 "table": { 1441 "table_name": "lineitem", 1442 "access_type": "ref", 1443 "possible_keys": [ 1444 "PRIMARY", 1445 "i_l_shipdate", 1446 "i_l_orderkey", 1447 "i_l_orderkey_quantity" 1448 ], 1449 "key": "PRIMARY", 1450 "key_length": "4", 1451 "used_key_parts": ["l_orderkey"], 1452 "ref": ["dbt3_s001.orders.o_orderkey"], 1453 "rows": 4, 1454 "filtered": 3.047460556, 1455 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1456 } 1457 } 1458} 1459set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate 1460FROM orders, lineitem 1461WHERE o_orderkey=l_orderkey AND 1462o_totaldiscount BETWEEN 18000 AND 20000 AND 1463o_totalprice BETWEEN 200000 AND 220000 AND 1464l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1465id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 14661 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where 14671 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where 1468set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1469FROM orders, lineitem 1470WHERE o_orderkey=l_orderkey AND 1471o_totaldiscount BETWEEN 18000 AND 20000 AND 1472o_totalprice BETWEEN 200000 AND 220000 AND 1473l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1474ANALYZE 1475{ 1476 "query_block": { 1477 "select_id": 1, 1478 "r_loops": 1, 1479 "r_total_time_ms": "REPLACED", 1480 "table": { 1481 "table_name": "orders", 1482 "access_type": "range", 1483 "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], 1484 "key": "i_o_totaldiscount", 1485 "key_length": "9", 1486 "used_key_parts": ["o_totaldiscount"], 1487 "r_loops": 1, 1488 "rows": 41, 1489 "r_rows": 41, 1490 "r_table_time_ms": "REPLACED", 1491 "r_other_time_ms": "REPLACED", 1492 "filtered": 3.333333254, 1493 "r_filtered": 2.43902439, 1494 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1495 "attached_condition": "orders.o_totalprice between 200000 and 220000" 1496 }, 1497 "table": { 1498 "table_name": "lineitem", 1499 "access_type": "ref", 1500 "possible_keys": [ 1501 "PRIMARY", 1502 "i_l_shipdate", 1503 "i_l_orderkey", 1504 "i_l_orderkey_quantity" 1505 ], 1506 "key": "PRIMARY", 1507 "key_length": "4", 1508 "used_key_parts": ["l_orderkey"], 1509 "ref": ["dbt3_s001.orders.o_orderkey"], 1510 "r_loops": 1, 1511 "rows": 4, 1512 "r_rows": 6, 1513 "r_table_time_ms": "REPLACED", 1514 "r_other_time_ms": "REPLACED", 1515 "filtered": 3.047460556, 1516 "r_filtered": 66.66666667, 1517 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1518 } 1519 } 1520} 1521set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate 1522FROM orders, lineitem 1523WHERE o_orderkey=l_orderkey AND 1524o_totaldiscount BETWEEN 18000 AND 20000 AND 1525o_totalprice BETWEEN 200000 AND 220000 AND 1526l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1527o_totaldiscount o_totalprice l_shipdate 152818016.04288 219707.84 1996-10-02 152918016.04288 219707.84 1996-10-17 153018016.04288 219707.84 1996-11-04 153118016.04288 219707.84 1996-11-14 1532set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate 1533FROM orders, lineitem 1534WHERE o_orderkey=l_orderkey AND 1535o_totaldiscount BETWEEN 18000 AND 20000 AND 1536o_totalprice BETWEEN 200000 AND 220000 AND 1537l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1538id select_type table type possible_keys key key_len ref rows Extra 15391 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where 15401 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1541set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1542FROM orders, lineitem 1543WHERE o_orderkey=l_orderkey AND 1544o_totaldiscount BETWEEN 18000 AND 20000 AND 1545o_totalprice BETWEEN 200000 AND 220000 AND 1546l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1547EXPLAIN 1548{ 1549 "query_block": { 1550 "select_id": 1, 1551 "table": { 1552 "table_name": "orders", 1553 "access_type": "range", 1554 "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], 1555 "key": "i_o_totaldiscount", 1556 "key_length": "9", 1557 "used_key_parts": ["o_totaldiscount"], 1558 "rows": 41, 1559 "filtered": 3.333333254, 1560 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1561 "attached_condition": "orders.o_totalprice between 200000 and 220000" 1562 }, 1563 "table": { 1564 "table_name": "lineitem", 1565 "access_type": "ref", 1566 "possible_keys": [ 1567 "PRIMARY", 1568 "i_l_shipdate", 1569 "i_l_orderkey", 1570 "i_l_orderkey_quantity" 1571 ], 1572 "key": "PRIMARY", 1573 "key_length": "4", 1574 "used_key_parts": ["l_orderkey"], 1575 "ref": ["dbt3_s001.orders.o_orderkey"], 1576 "rows": 4, 1577 "filtered": 3.047460556, 1578 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1579 } 1580 } 1581} 1582set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate 1583FROM orders, lineitem 1584WHERE o_orderkey=l_orderkey AND 1585o_totaldiscount BETWEEN 18000 AND 20000 AND 1586o_totalprice BETWEEN 200000 AND 220000 AND 1587l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1588id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 15891 SIMPLE orders range PRIMARY,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 3.33 2.44 Using index condition; Using where 15901 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 3.05 66.67 Using where 1591set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1592FROM orders, lineitem 1593WHERE o_orderkey=l_orderkey AND 1594o_totaldiscount BETWEEN 18000 AND 20000 AND 1595o_totalprice BETWEEN 200000 AND 220000 AND 1596l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1597ANALYZE 1598{ 1599 "query_block": { 1600 "select_id": 1, 1601 "r_loops": 1, 1602 "r_total_time_ms": "REPLACED", 1603 "table": { 1604 "table_name": "orders", 1605 "access_type": "range", 1606 "possible_keys": ["PRIMARY", "i_o_totalprice", "i_o_totaldiscount"], 1607 "key": "i_o_totaldiscount", 1608 "key_length": "9", 1609 "used_key_parts": ["o_totaldiscount"], 1610 "r_loops": 1, 1611 "rows": 41, 1612 "r_rows": 41, 1613 "r_table_time_ms": "REPLACED", 1614 "r_other_time_ms": "REPLACED", 1615 "filtered": 3.333333254, 1616 "r_filtered": 2.43902439, 1617 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1618 "attached_condition": "orders.o_totalprice between 200000 and 220000" 1619 }, 1620 "table": { 1621 "table_name": "lineitem", 1622 "access_type": "ref", 1623 "possible_keys": [ 1624 "PRIMARY", 1625 "i_l_shipdate", 1626 "i_l_orderkey", 1627 "i_l_orderkey_quantity" 1628 ], 1629 "key": "PRIMARY", 1630 "key_length": "4", 1631 "used_key_parts": ["l_orderkey"], 1632 "ref": ["dbt3_s001.orders.o_orderkey"], 1633 "r_loops": 1, 1634 "rows": 4, 1635 "r_rows": 6, 1636 "r_table_time_ms": "REPLACED", 1637 "r_other_time_ms": "REPLACED", 1638 "filtered": 3.047460556, 1639 "r_filtered": 66.66666667, 1640 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1641 } 1642 } 1643} 1644set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate 1645FROM orders, lineitem 1646WHERE o_orderkey=l_orderkey AND 1647o_totaldiscount BETWEEN 18000 AND 20000 AND 1648o_totalprice BETWEEN 200000 AND 220000 AND 1649l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1650o_totaldiscount o_totalprice l_shipdate 165118016.04288 219707.84 1996-10-02 165218016.04288 219707.84 1996-10-17 165318016.04288 219707.84 1996-11-04 165418016.04288 219707.84 1996-11-14 1655CREATE VIEW v1 AS 1656SELECT * FROM orders 1657WHERE o_orderdate BETWEEN '1992-12-01' AND '1997-01-01'; 1658set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate 1659FROM v1, lineitem 1660WHERE o_orderkey=l_orderkey AND 1661o_totaldiscount BETWEEN 18000 AND 20000 AND 1662o_totalprice BETWEEN 200000 AND 220000 AND 1663l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1664id select_type table type possible_keys key key_len ref rows Extra 16651 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where 16661 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1667set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1668FROM v1, lineitem 1669WHERE o_orderkey=l_orderkey AND 1670o_totaldiscount BETWEEN 18000 AND 20000 AND 1671o_totalprice BETWEEN 200000 AND 220000 AND 1672l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1673EXPLAIN 1674{ 1675 "query_block": { 1676 "select_id": 1, 1677 "table": { 1678 "table_name": "orders", 1679 "access_type": "range", 1680 "possible_keys": [ 1681 "PRIMARY", 1682 "i_o_orderdate", 1683 "i_o_totalprice", 1684 "i_o_totaldiscount" 1685 ], 1686 "key": "i_o_totaldiscount", 1687 "key_length": "9", 1688 "used_key_parts": ["o_totaldiscount"], 1689 "rows": 41, 1690 "filtered": "REPLACED", 1691 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1692 "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" 1693 }, 1694 "table": { 1695 "table_name": "lineitem", 1696 "access_type": "ref", 1697 "possible_keys": [ 1698 "PRIMARY", 1699 "i_l_shipdate", 1700 "i_l_orderkey", 1701 "i_l_orderkey_quantity" 1702 ], 1703 "key": "PRIMARY", 1704 "key_length": "4", 1705 "used_key_parts": ["l_orderkey"], 1706 "ref": ["dbt3_s001.orders.o_orderkey"], 1707 "rows": 4, 1708 "filtered": "REPLACED", 1709 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1710 } 1711 } 1712} 1713set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate 1714FROM v1, lineitem 1715WHERE o_orderkey=l_orderkey AND 1716o_totaldiscount BETWEEN 18000 AND 20000 AND 1717o_totalprice BETWEEN 200000 AND 220000 AND 1718l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1719id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 17201 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 # 2.44 Using index condition; Using where 17211 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 # 66.67 Using where 1722set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1723FROM v1, lineitem 1724WHERE o_orderkey=l_orderkey AND 1725o_totaldiscount BETWEEN 18000 AND 20000 AND 1726o_totalprice BETWEEN 200000 AND 220000 AND 1727l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1728ANALYZE 1729{ 1730 "query_block": { 1731 "select_id": 1, 1732 "r_loops": 1, 1733 "r_total_time_ms": "REPLACED", 1734 "table": { 1735 "table_name": "orders", 1736 "access_type": "range", 1737 "possible_keys": [ 1738 "PRIMARY", 1739 "i_o_orderdate", 1740 "i_o_totalprice", 1741 "i_o_totaldiscount" 1742 ], 1743 "key": "i_o_totaldiscount", 1744 "key_length": "9", 1745 "used_key_parts": ["o_totaldiscount"], 1746 "r_loops": 1, 1747 "rows": 41, 1748 "r_rows": 41, 1749 "r_table_time_ms": "REPLACED", 1750 "r_other_time_ms": "REPLACED", 1751 "filtered": "REPLACED", 1752 "r_filtered": 2.43902439, 1753 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1754 "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" 1755 }, 1756 "table": { 1757 "table_name": "lineitem", 1758 "access_type": "ref", 1759 "possible_keys": [ 1760 "PRIMARY", 1761 "i_l_shipdate", 1762 "i_l_orderkey", 1763 "i_l_orderkey_quantity" 1764 ], 1765 "key": "PRIMARY", 1766 "key_length": "4", 1767 "used_key_parts": ["l_orderkey"], 1768 "ref": ["dbt3_s001.orders.o_orderkey"], 1769 "r_loops": 1, 1770 "rows": 4, 1771 "r_rows": 6, 1772 "r_table_time_ms": "REPLACED", 1773 "r_other_time_ms": "REPLACED", 1774 "filtered": "REPLACED", 1775 "r_filtered": 66.66666667, 1776 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1777 } 1778 } 1779} 1780set statement optimizer_switch='rowid_filter=on' for SELECT o_totaldiscount, o_totalprice, l_shipdate 1781FROM v1, lineitem 1782WHERE o_orderkey=l_orderkey AND 1783o_totaldiscount BETWEEN 18000 AND 20000 AND 1784o_totalprice BETWEEN 200000 AND 220000 AND 1785l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1786o_totaldiscount o_totalprice l_shipdate 178718016.04288 219707.84 1996-10-02 178818016.04288 219707.84 1996-10-17 178918016.04288 219707.84 1996-11-04 179018016.04288 219707.84 1996-11-14 1791set statement optimizer_switch='rowid_filter=off' for EXPLAIN SELECT o_totaldiscount, o_totalprice, l_shipdate 1792FROM v1, lineitem 1793WHERE o_orderkey=l_orderkey AND 1794o_totaldiscount BETWEEN 18000 AND 20000 AND 1795o_totalprice BETWEEN 200000 AND 220000 AND 1796l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1797id select_type table type possible_keys key key_len ref rows Extra 17981 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 Using index condition; Using where 17991 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where 1800set statement optimizer_switch='rowid_filter=off' for EXPLAIN FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1801FROM v1, lineitem 1802WHERE o_orderkey=l_orderkey AND 1803o_totaldiscount BETWEEN 18000 AND 20000 AND 1804o_totalprice BETWEEN 200000 AND 220000 AND 1805l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1806EXPLAIN 1807{ 1808 "query_block": { 1809 "select_id": 1, 1810 "table": { 1811 "table_name": "orders", 1812 "access_type": "range", 1813 "possible_keys": [ 1814 "PRIMARY", 1815 "i_o_orderdate", 1816 "i_o_totalprice", 1817 "i_o_totaldiscount" 1818 ], 1819 "key": "i_o_totaldiscount", 1820 "key_length": "9", 1821 "used_key_parts": ["o_totaldiscount"], 1822 "rows": 41, 1823 "filtered": "REPLACED", 1824 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1825 "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" 1826 }, 1827 "table": { 1828 "table_name": "lineitem", 1829 "access_type": "ref", 1830 "possible_keys": [ 1831 "PRIMARY", 1832 "i_l_shipdate", 1833 "i_l_orderkey", 1834 "i_l_orderkey_quantity" 1835 ], 1836 "key": "PRIMARY", 1837 "key_length": "4", 1838 "used_key_parts": ["l_orderkey"], 1839 "ref": ["dbt3_s001.orders.o_orderkey"], 1840 "rows": 4, 1841 "filtered": "REPLACED", 1842 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1843 } 1844 } 1845} 1846set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_totaldiscount, o_totalprice, l_shipdate 1847FROM v1, lineitem 1848WHERE o_orderkey=l_orderkey AND 1849o_totaldiscount BETWEEN 18000 AND 20000 AND 1850o_totalprice BETWEEN 200000 AND 220000 AND 1851l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1852id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 18531 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_totalprice,i_o_totaldiscount i_o_totaldiscount 9 NULL 41 41.00 # 2.44 Using index condition; Using where 18541 SIMPLE lineitem ref PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 6.00 # 66.67 Using where 1855set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_totaldiscount, o_totalprice, l_shipdate 1856FROM v1, lineitem 1857WHERE o_orderkey=l_orderkey AND 1858o_totaldiscount BETWEEN 18000 AND 20000 AND 1859o_totalprice BETWEEN 200000 AND 220000 AND 1860l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1861ANALYZE 1862{ 1863 "query_block": { 1864 "select_id": 1, 1865 "r_loops": 1, 1866 "r_total_time_ms": "REPLACED", 1867 "table": { 1868 "table_name": "orders", 1869 "access_type": "range", 1870 "possible_keys": [ 1871 "PRIMARY", 1872 "i_o_orderdate", 1873 "i_o_totalprice", 1874 "i_o_totaldiscount" 1875 ], 1876 "key": "i_o_totaldiscount", 1877 "key_length": "9", 1878 "used_key_parts": ["o_totaldiscount"], 1879 "r_loops": 1, 1880 "rows": 41, 1881 "r_rows": 41, 1882 "r_table_time_ms": "REPLACED", 1883 "r_other_time_ms": "REPLACED", 1884 "filtered": "REPLACED", 1885 "r_filtered": 2.43902439, 1886 "index_condition": "orders.o_totaldiscount between 18000 and 20000", 1887 "attached_condition": "orders.o_totalprice between 200000 and 220000 and orders.o_orderDATE between '1992-12-01' and '1997-01-01'" 1888 }, 1889 "table": { 1890 "table_name": "lineitem", 1891 "access_type": "ref", 1892 "possible_keys": [ 1893 "PRIMARY", 1894 "i_l_shipdate", 1895 "i_l_orderkey", 1896 "i_l_orderkey_quantity" 1897 ], 1898 "key": "PRIMARY", 1899 "key_length": "4", 1900 "used_key_parts": ["l_orderkey"], 1901 "ref": ["dbt3_s001.orders.o_orderkey"], 1902 "r_loops": 1, 1903 "rows": 4, 1904 "r_rows": 6, 1905 "r_table_time_ms": "REPLACED", 1906 "r_other_time_ms": "REPLACED", 1907 "filtered": "REPLACED", 1908 "r_filtered": 66.66666667, 1909 "attached_condition": "lineitem.l_shipDATE between '1996-10-01' and '1996-12-01'" 1910 } 1911 } 1912} 1913set statement optimizer_switch='rowid_filter=off' for SELECT o_totaldiscount, o_totalprice, l_shipdate 1914FROM v1, lineitem 1915WHERE o_orderkey=l_orderkey AND 1916o_totaldiscount BETWEEN 18000 AND 20000 AND 1917o_totalprice BETWEEN 200000 AND 220000 AND 1918l_shipdate BETWEEN '1996-10-01' AND '1996-12-01'; 1919o_totaldiscount o_totalprice l_shipdate 192018016.04288 219707.84 1996-10-02 192118016.04288 219707.84 1996-10-17 192218016.04288 219707.84 1996-11-04 192318016.04288 219707.84 1996-11-14 1924ALTER TABLE lineitem DROP CONSTRAINT l_date; 1925ALTER TABLE orders DROP CONSTRAINT o_price; 1926ALTER TABLE orders DROP COLUMN o_totaldiscount; 1927DROP VIEW v1; 1928DROP DATABASE dbt3_s001; 1929use test; 1930# 1931# MDEV-18816: potential range filter for one join table with 1932# impossible WHERE for another 1933# 1934create table t1 ( 1935pk int not null primary key, c2 varchar(10) , i1 int,key (c2) 1936) engine=myisam; 1937insert into t1 values (1,'a',-5),(2,'a',null); 1938create table t2 ( 1939pk int, i1 int, c1 varchar(30) , key c1 (c1(30)), key i1 (i1) 1940) engine=myisam; 1941insert into t2 values 1942(1,-5,'a'),(2,null,'a'),(3,null,'a'),(4,null,'a'),(5,5,'a'),(6,null,'a'), 1943(7,4,'a'),(8,55,'a'),(9,null,'a'),(10,null,'a'),(11,null,'a'),(12,-5,'a'), 1944(13,-5,'a'),(14,null,'a'),(15,null,'a'),(16,-5,'a'),(17,-5,'a'); 1945select 1 1946from t1 1947left join 1948t2 join t1 as t1_a on t2.i1 = t1_a.pk 1949on t1.c2 = t2.c1 1950where t1_a.pk is null and t1_a.i1 != 3; 19511 1952explain extended select 1 1953from t1 1954left join 1955t2 join t1 as t1_a on t2.i1 = t1_a.pk 1956on t1.c2 = t2.c1 1957where t1_a.pk is null and t1_a.i1 != 3; 1958id select_type table type possible_keys key key_len ref rows filtered Extra 19591 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1960Warnings: 1961Note 1003 select 1 AS `1` from `test`.`t1` join `test`.`t2` join `test`.`t1` `t1_a` where 0 1962drop table t1,t2; 1963# 1964# MDEV-18640: TABLE::prune_range_rowid_filters: Conditional jump or 1965# move depends on uninitialized value 1966# 1967CREATE TABLE t1 ( 1968pk INT, i INT, PRIMARY KEY (pk), KEY (pk,i) 1969) ENGINE=MyISAM; 1970INSERT INTO t1 VALUES (1,10), (7,70), (2,20); 1971SELECT * FROM t1 WHERE pk < 5; 1972pk i 19731 10 19742 20 1975DROP TABLE t1; 1976# 1977# MDEV-18956: Possible rowid filter for subquery for which 1978# in_to_exists strategy has been chosen 1979# 1980CREATE TABLE t1 (pk int) engine=myisam ; 1981INSERT INTO t1 VALUES (1),(2); 1982CREATE TABLE t2 ( 1983pk int auto_increment PRIMARY KEY, 1984i1 int, i2 int, c2 varchar(1), 1985KEY (i1), KEY (i2) 1986) engine=myisam; 1987INSERT INTO t2 VALUES 1988(1,8,6,'t'),(2,5,7,'i'),(3,4,4,'h'),(4,207,38,'d'),(5,183,206,'b'), 1989(6,7,null,'o'),(7,1,2,'j'),(8,17,36,'s'),(9,4,5,'q'),(10,0,6,'l'), 1990(11,1,9,'j'),(12,5,6,'y'),(13,null,0,'i'),(14,7,7,'x'),(15,5,2,'u'); 1991SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); 1992pk 1993EXPLAIN EXTENDED 1994SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); 1995id select_type table type possible_keys key key_len ref rows filtered Extra 19961 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING 19972 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 1998Warnings: 1999Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 2000DROP TABLE t1,t2; 2001# 2002# MDEV-19255: rowid range filter built for range condition 2003# that uses in expensive subquery 2004# 2005CREATE TABLE t1 ( 2006pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) 2007) ENGINE=MyISAM; 2008INSERT INTO t1 VALUES 2009(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), 2010(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), 2011(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), 2012(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), 2013(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), 2014(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), 2015(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), 2016(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), 2017(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), 2018(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), 2019(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), 2020(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), 2021(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), 2022(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), 2023(107,8,'z'),(108,3,'k'),(109,65,NULL); 2024CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; 2025INSERT INTO t2 VALUES (1,1,'x'); 2026INSERT INTO t2 SELECT * FROM t1; 2027SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) 2028WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); 2029pk1 a1 b1 pk2 a2 b2 203065 2 a 109 65 NULL 2031EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) 2032WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); 2033id select_type table type possible_keys key key_len ref rows filtered Extra 20341 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where 20351 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter 20362 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition 2037Warnings: 2038Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` 2039EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) 2040WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); 2041EXPLAIN 2042{ 2043 "query_block": { 2044 "select_id": 1, 2045 "table": { 2046 "table_name": "t2", 2047 "access_type": "ALL", 2048 "rows": 101, 2049 "filtered": 100, 2050 "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" 2051 }, 2052 "table": { 2053 "table_name": "t1", 2054 "access_type": "eq_ref", 2055 "possible_keys": ["PRIMARY", "b1"], 2056 "key": "PRIMARY", 2057 "key_length": "4", 2058 "used_key_parts": ["pk1"], 2059 "ref": ["test.t2.a2"], 2060 "rowid_filter": { 2061 "range": { 2062 "key": "b1", 2063 "used_key_parts": ["b1"] 2064 }, 2065 "rows": 87, 2066 "selectivity_pct": 87 2067 }, 2068 "rows": 1, 2069 "filtered": 87, 2070 "attached_condition": "t1.b1 <= (subquery#2)" 2071 }, 2072 "subqueries": [ 2073 { 2074 "query_block": { 2075 "select_id": 2, 2076 "table": { 2077 "table_name": "t2", 2078 "access_type": "range", 2079 "possible_keys": ["PRIMARY"], 2080 "key": "PRIMARY", 2081 "key_length": "4", 2082 "used_key_parts": ["pk2"], 2083 "rows": 1, 2084 "filtered": 100, 2085 "index_condition": "t2.pk2 <= 1" 2086 } 2087 } 2088 } 2089 ] 2090 } 2091} 2092DROP TABLE t1,t2; 2093# 2094# MDEV-21794: Optimizer flag rowid_filter leads to long query 2095# 2096create table t10(a int); 2097insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 2098create table t11(a int); 2099insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; 2100CREATE TABLE t1 ( 2101el_id int(10) unsigned NOT NULL , 2102el_index blob NOT NULL, 2103el_index_60 varbinary(60) NOT NULL, 2104filler blob, 2105PRIMARY KEY (el_id), 2106KEY el_index (el_index(60)), 2107KEY el_index_60 (el_index_60,el_id) 2108); 2109insert into t1 2110select 2111A.a+1000*B.a, 2112A.a+1000*B.a + 10000, 2113A.a+1000*B.a + 10000, 2114'filler-data-filler-data' 2115from 2116t11 A, t10 B; 2117analyze table t1 persistent for all; 2118Table Op Msg_type Msg_text 2119test.t1 analyze status Engine-independent statistics collected 2120test.t1 analyze Warning Engine-independent statistics are not collected for column 'el_index' 2121test.t1 analyze Warning Engine-independent statistics are not collected for column 'filler' 2122test.t1 analyze status OK 2123# This must not use rowid_filter with key=el_index|el_index_60: 2124explain 2125select * from t1 2126where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); 2127id select_type table type possible_keys key key_len ref rows Extra 21281 SIMPLE t1 range el_index,el_index_60 el_index 62 NULL 1000 Using where 2129drop table t10, t11, t1; 2130# 2131# MDEV-22160: SIGSEGV in st_join_table::save_explain_data on SELECT 2132# 2133set @save_optimizer_switch= @@optimizer_switch; 2134SET @@optimizer_switch="index_merge_sort_union=OFF"; 2135CREATE TABLE t1 (a INT, b INT, INDEX(a), INDEX(b)); 2136INSERT INTO t1 VALUES (0,0),(1,0),(-1,1), (-2,1), (-2,3), (-3,4), (-2,4); 2137explain 2138SELECT * FROM t1 WHERE a > 0 AND b=0; 2139id select_type table type possible_keys key key_len ref rows Extra 21401 SIMPLE t1 ref|filter a,b b|a 5|5 const 2 (14%) Using where; Using rowid filter 2141SELECT * FROM t1 WHERE a > 0 AND b=0; 2142a b 21431 0 2144drop table t1; 2145SET @@optimizer_switch=@save_optimizer_switch; 2146set @@use_stat_tables=@save_use_stat_tables; 2147# 2148# MDEV-18755: possible RORI-plan and possible plan with range filter 2149# 2150create table t1 ( 2151pk int not null primary key, f1 varchar(10), f2 varchar(30), a int(10), 2152key (f1), key (f2) 2153) engine=innodb; 2154insert into t1 values 2155(2,'a','a',2),(3,'a','a',null),(4,'a','a',55),(5,'a','a',4),(6,'a','a',0), 2156(7,'a','a',1),(8,'a','a',4),(9,'a','a',null),(10,'a','a',0),(11,'a','a',0), 2157(12,'a','a',null),(13,'a','a',49778),(14,'a','a',6),(15,'a','a',3), 2158(16,'a','a',233),(17,'a','a',-1),(18,'a','a',5),(19,'a','a',-1), 2159(20,'a','a',null),(21,'a','a',0),(22,'a','a',null),(23,'a','a',53840), 2160(24,'a','a',null),(25,'a','a',null),(26,'a','a',5),(27,'a','a',43454), 2161(28,'a','a',0),(29,'a','a',0),(30,'a','a',null),(59,'a','a',null), 2162(60,'a','a',null),(61,'a','a',-1),(62,'a','a',null),(63,'a','a',0), 2163(64,'a','a',14468),(65,'a','a',0),(66,'a','a',28),(67,'a','a',null), 2164(68,'a','a',14983),(69,'a','a',null),(70,'a','a',3),(71,'a','a',null), 2165(72,'a','a',null),(73,'a','a',237),(74,'a','a',2),(75,'a','a',0), 2166(76,'a','a',6),(77,'a','a',5),(78,'a','a',0),(79,'a','a',1),(80,'a','a',-1), 2167(81,'a','a',20),(82,'a','a',0),(83,'a','a',0),(84,'a','a',null), 2168(85,'a','a',-1),(86,'a','a',5),(87,'a','a',null),(88,'a','a',160), 2169(89,null,null,null),(90,'a','a',14785),(91,'a','a',0),(92,'a','a',null); 2170( select * from t1 2171where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) 2172union 2173( select * from t1 2174where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); 2175pk f1 f2 a 2176explain ( select * from t1 2177where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) 2178union 2179( select * from t1 2180where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); 2181id select_type table type possible_keys key key_len ref rows Extra 21821 PRIMARY t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter 21832 UNION t1 ref|filter f1,f2 f1|f1 13|13 const 1 (2%) Using index condition; Using where; Using rowid filter 2184NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2185explain format=json ( select * from t1 2186where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))) 2187union 2188( select * from t1 2189where (f1 is null and f2 is null) and (f2 between 'a' and 'z' or f1 in ('a'))); 2190EXPLAIN 2191{ 2192 "query_block": { 2193 "union_result": { 2194 "table_name": "<union1,2>", 2195 "access_type": "ALL", 2196 "query_specifications": [ 2197 { 2198 "query_block": { 2199 "select_id": 1, 2200 "table": { 2201 "table_name": "t1", 2202 "access_type": "ref", 2203 "possible_keys": ["f1", "f2"], 2204 "key": "f1", 2205 "key_length": "13", 2206 "used_key_parts": ["f1"], 2207 "ref": ["const"], 2208 "rowid_filter": { 2209 "range": { 2210 "key": "f1", 2211 "used_key_parts": ["f1"] 2212 }, 2213 "rows": 1, 2214 "selectivity_pct": 1.587301587 2215 }, 2216 "rows": 1, 2217 "filtered": 1.587301612, 2218 "index_condition": "t1.f1 is null", 2219 "attached_condition": "t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" 2220 } 2221 } 2222 }, 2223 { 2224 "query_block": { 2225 "select_id": 2, 2226 "operation": "UNION", 2227 "table": { 2228 "table_name": "t1", 2229 "access_type": "ref", 2230 "possible_keys": ["f1", "f2"], 2231 "key": "f1", 2232 "key_length": "13", 2233 "used_key_parts": ["f1"], 2234 "ref": ["const"], 2235 "rowid_filter": { 2236 "range": { 2237 "key": "f1", 2238 "used_key_parts": ["f1"] 2239 }, 2240 "rows": 1, 2241 "selectivity_pct": 1.587301587 2242 }, 2243 "rows": 1, 2244 "filtered": 1.587301612, 2245 "index_condition": "t1.f1 is null", 2246 "attached_condition": "t1.f2 is null and (t1.f2 between 'a' and 'z' or t1.f1 = 'a')" 2247 } 2248 } 2249 } 2250 ] 2251 } 2252 } 2253} 2254drop table t1; 2255# 2256# MDEV-19195: possible RORI-plan and possible plan with range filter 2257# for not first joined table 2258# 2259create table t1 (id int not null primary key) engine=innodb; 2260insert into t1 values (2),(1); 2261create table t2 (y int,x int,index (x),index (y)) engine=innodb; 2262insert into t2 values 2263(4,1),(4,777),(2,1),(2,888),(111,1),(222,1),(333,345),(444,1), 2264(555,555),(666,1); 2265select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; 2266id y x 22671 2 1 2268explain extended select * from t1 join t2 on t1.id = t2.x where t2.y = 2 and t1.id = 1; 2269id select_type table type possible_keys key key_len ref rows filtered Extra 22701 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 Using index 22711 SIMPLE t2 ref x,y y 5 const 2 100.00 Using where 2272Warnings: 2273Note 1003 select 1 AS `id`,`test`.`t2`.`y` AS `y`,`test`.`t2`.`x` AS `x` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`y` = 2 and `test`.`t2`.`x` = 1 2274drop table t1, t2; 2275# 2276# MDEV-19820: use of rowid filter for innodb table without primary key 2277# 2278create table t1 (a int, b int, key (b), key (a)) engine=innodb; 2279insert into t1 2280select (rand(1)*1000)/10, (rand(1001)*1000)/50 from seq_1_to_1000; 2281analyze table t1; 2282Table Op Msg_type Msg_text 2283test.t1 analyze status Engine-independent statistics collected 2284test.t1 analyze status OK 2285set @save_optimizer_switch= @@optimizer_switch; 2286set optimizer_switch='rowid_filter=off'; 2287select count(*) from t1 where a in (22,83,11) and b=2; 2288count(*) 22896 2290explain extended select count(*) from t1 where a in (22,83,11) and b=2; 2291id select_type table type possible_keys key key_len ref rows filtered Extra 22921 SIMPLE t1 range b,a a 5 NULL 33 5.90 Using index condition; Using where 2293Warnings: 2294Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) 2295select * from t1 where a in (22,83,11) and b=2; 2296a b 229711 2 229811 2 229911 2 230022 2 230183 2 230283 2 2303set optimizer_switch='rowid_filter=on'; 2304select count(*) from t1 where a in (22,83,11) and b=2; 2305count(*) 23066 2307explain extended select count(*) from t1 where a in (22,83,11) and b=2; 2308id select_type table type possible_keys key key_len ref rows filtered Extra 23091 SIMPLE t1 ref|filter b,a b|a 5|5 const 59 (3%) 3.30 Using where; Using rowid filter 2310Warnings: 2311Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (22,83,11) 2312select * from t1 where a in (22,83,11) and b=2; 2313a b 231411 2 231511 2 231683 2 231711 2 231883 2 231922 2 2320drop table t1; 2321set optimizer_switch=@save_optimizer_switch; 2322SET SESSION DEFAULT_STORAGE_ENGINE=DEFAULT; 2323# 2324# MDEV-19919: use of rowid filter for innodb table + ORDER BY 2325# 2326SET @stats.save= @@innodb_stats_persistent; 2327SET GLOBAL innodb_stats_persistent= ON; 2328CREATE TABLE t1 ( 2329a INT, 2330b VARCHAR(10), 2331c VARCHAR(1024), 2332KEY (b), 2333KEY (c) 2334) ENGINE=InnoDB; 2335INSERT INTO t1 VALUES 2336(1,'w','z'), (1,'X','o'), (1,'q','c'), (5,'w','c'), (2,'j','m'), 2337(2,'Q','s'), (9,'e','J'), (2,'p','W'), (9,'o','F'), (2,'g','S'), 2338(1,'Y','a'), (NULL,'Y','p'), (NULL,'s','x'), (NULL,'i','S'), 2339(1,'l','q'), (7,'r','e'), (4,'b','h'), (NULL,'E','c'), 2340(NULL,'M','a'), (3,'e','X'), (NULL,'p','r'), (9,'e','i'), 2341(3,'g','x'), (2,'h','y'); 2342ANALYZE TABLE t1; 2343Table Op Msg_type Msg_text 2344test.t1 analyze status Engine-independent statistics collected 2345test.t1 analyze status OK 2346EXPLAIN EXTENDED 2347SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; 2348id select_type table type possible_keys key key_len ref rows filtered Extra 23491 SIMPLE t1 range|filter b,c b|c 13|1027 NULL 5 (42%) 41.67 Using index condition; Using where; Using filesort; Using rowid filter 2350Warnings: 2351Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`c` < 'k' and `test`.`t1`.`b` > 't' order by `test`.`t1`.`a` 2352SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; 2353a 23541 23555 2356DROP TABLE t1; 2357SET GLOBAL innodb_stats_persistent= @stats.save; 2358# 2359# MDEV-20056: index to build range filter should not be 2360# the same as table access index 2361# 2362SET @stats.save= @@innodb_stats_persistent; 2363SET GLOBAL innodb_stats_persistent= ON; 2364CREATE TABLE t1 (ch varchar(1), id int, id2 int) ENGINE=InnoDB; 2365INSERT INTO t1 VALUES 2366('l',3,2), ('e',NULL,NULL), ('r',7,3), ('h',NULL,2), (NULL,4,4), ('c',4,NULL), 2367('k',NULL,NULL), ('h',NULL,NULL), ('b',9,NULL), ('f',6,NULL); 2368CREATE TABLE t2 ( 2369pk int NOT NULL, col_date_key date, ch2 varchar(1), id2 int, 2370PRIMARY KEY (pk), KEY (col_date_key), KEY (ch2), KEY (id2) 2371) ENGINE=InnoDB; 2372INSERT INTO t2 VALUES 2373(1,'2034-04-21','g',9), (2,'2006-09-08','y',1), (3,NULL,'h',2), 2374(4,'1987-03-02','s',2), (5,'2019-07-02','u',NULL),(6,'2012-12-18','z',1), 2375(7,NULL,'w',4), (8,'2005-03-10','o',8), (9,'1987-02-12','d',4); 2376CREATE TABLE t3 (id int) ENGINE=InnoDB; 2377INSERT INTO t3 VALUES (6); 2378ANALYZE TABLE t1,t2,t3; 2379Table Op Msg_type Msg_text 2380test.t1 analyze status Engine-independent statistics collected 2381test.t1 analyze status OK 2382test.t2 analyze status Engine-independent statistics collected 2383test.t2 analyze status OK 2384test.t3 analyze status Engine-independent statistics collected 2385test.t3 analyze status OK 2386EXPLAIN EXTENDED SELECT 1 FROM t3 2387WHERE EXISTS ( SELECT 1 FROM t1 2388WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 2389WHERE bt1.id = t2.pk AND 2390t2.ch2 <= 'g' ) OR 2391t1.id2 = t1.id); 2392id select_type table type possible_keys key key_len ref rows filtered Extra 23931 PRIMARY t3 ALL NULL NULL NULL NULL 1 100.00 Using where 23942 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 10 100.00 Using where 23953 MATERIALIZED t2 range PRIMARY,col_date_key,ch2,id2 ch2 4 NULL 2 100.00 Using where; Using index 23963 MATERIALIZED bt1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) 2397Warnings: 2398Note 1276 Field or reference 'test.t3.id' of SELECT #2 was resolved in SELECT #1 2399Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t3` where <in_optimizer>(1,<expr_cache><`test`.`t3`.`id`>(exists(/* select#2 */ select 1 from `test`.`t1` where <expr_cache><`test`.`t3`.`id`>(<in_optimizer>(`test`.`t3`.`id`,`test`.`t3`.`id` in ( <materialize> (/* select#3 */ select `test`.`bt1`.`id` from `test`.`t2` join `test`.`t1` `bt1` where `test`.`bt1`.`id` = `test`.`t2`.`pk` and `test`.`t2`.`ch2` <= 'g' ), <primary_index_lookup>(`test`.`t3`.`id` in <temporary table> on distinct_key where `test`.`t3`.`id` = `<subquery3>`.`id`)))) or `test`.`t1`.`id2` = `test`.`t1`.`id` limit 1))) 2400SELECT 1 FROM t3 2401WHERE EXISTS ( SELECT 1 FROM t1 2402WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 2403WHERE bt1.id = t2.pk AND 2404t2.ch2 <= 'g' ) OR 2405t1.id2 = t1.id); 24061 24071 2408EXPLAIN EXTENDED SELECT 1 FROM t3 2409WHERE EXISTS ( SELECT 1 FROM t1 2410WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 2411WHERE bt1.ch = t2.ch2 AND 2412bt1.id = t2.pk AND 2413t2.ch2 <= 'g' ) OR 2414t1.id2 = t1.id); 2415id select_type table type possible_keys key key_len ref rows filtered Extra 24161 PRIMARY t3 ALL NULL NULL NULL NULL 1 100.00 Using where 24172 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 10 100.00 Using where 24183 MATERIALIZED t2 range PRIMARY,col_date_key,ch2,id2 ch2 4 NULL 2 100.00 Using where; Using index 24193 MATERIALIZED bt1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) 2420Warnings: 2421Note 1276 Field or reference 'test.t3.id' of SELECT #2 was resolved in SELECT #1 2422Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t3` where <in_optimizer>(1,<expr_cache><`test`.`t3`.`id`>(exists(/* select#2 */ select 1 from `test`.`t1` where <expr_cache><`test`.`t3`.`id`>(<in_optimizer>(`test`.`t3`.`id`,`test`.`t3`.`id` in ( <materialize> (/* select#3 */ select `test`.`bt1`.`id` from `test`.`t2` join `test`.`t1` `bt1` where `test`.`bt1`.`ch` = `test`.`t2`.`ch2` and `test`.`bt1`.`id` = `test`.`t2`.`pk` and `test`.`t2`.`ch2` <= 'g' ), <primary_index_lookup>(`test`.`t3`.`id` in <temporary table> on distinct_key where `test`.`t3`.`id` = `<subquery3>`.`id`)))) or `test`.`t1`.`id2` = `test`.`t1`.`id` limit 1))) 2423SELECT 1 FROM t3 2424WHERE EXISTS ( SELECT 1 FROM t1 2425WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 2426WHERE bt1.ch = t2.ch2 AND 2427bt1.id = t2.pk AND 2428t2.ch2 <= 'g' ) OR 2429t1.id2 = t1.id); 24301 24311 2432DROP TABLE t1, t2, t3; 2433SET GLOBAL innodb_stats_persistent= @stats.save; 2434# 2435# MDEV-20407: usage of range filter is not supported when 2436# the joined table is accessed by a full text index 2437# 2438set @stats.save= @@innodb_stats_persistent; 2439set global innodb_stats_persistent=on; 2440create table t1(id int, s text, key (id), fulltext key (s)) engine=innodb; 2441insert into t1 values 2442(1119,'t'),(1134,'t'),(1134,'t'),(1143,'t'),(1143,'t'),(1187,'t'),(1187,'t'), 2443(1187,'t'),(1187,'t'),(1187,'t'),(1187,'t'),(1187,'t'),(1187,'t'),(1187,'t'), 2444(1187,'t'),(1210,'t'),(1210,'t'),(1210,'t'),(1210,'t'),(1210,'t'),(1210,'t'), 2445(1214,'t'),(1214,'t'),(1215,'t'),(1215,'t'),(1215,'t'),(1216,'t'),(1218,'t'), 2446(1220,'t'),(1220,'t'),(1220,'t'),(1222,'t'),(1223,'t'),(1223,'t'),(1224,'t'), 2447(1225,'t'),(1225,'t'),(1226,'t'),(1226,'t'),(1227,'t'),(1227,'t'),(1228,'t'), 2448(1229,'t'),(1230,'t'),(1230,'t'),(1231,'t'),(1231,'t'),(1232,'t'),(1232,'t'), 2449(1232,'t'),(1232,'t'),(1233,'t'),(1241,'t'),(1245,'t'),(1247,'t'),(1247,'t'), 2450(1247,'t'),(1247,'t'),(1247,'t'),(1247,'t'),(1248,'like fttest'); 2451analyze table t1; 2452Table Op Msg_type Msg_text 2453test.t1 analyze status Engine-independent statistics collected 2454test.t1 analyze Warning Engine-independent statistics are not collected for column 's' 2455test.t1 analyze status OK 2456explain extended select count(0) from t1 2457where id=15066 and (match s against ('+"fttest"' in boolean mode)); 2458id select_type table type possible_keys key key_len ref rows filtered Extra 24591 SIMPLE t1 fulltext id,s s 0 1 1.64 Using where 2460Warnings: 2461Note 1003 select count(0) AS `count(0)` from `test`.`t1` where `test`.`t1`.`id` = 15066 and (match `test`.`t1`.`s` against ('+"fttest"' in boolean mode)) 2462select count(0) from t1 2463where id=15066 and (match s against ('+"fttest"' in boolean mode)); 2464count(0) 24650 2466drop table t1; 2467set global innodb_stats_persistent= @stats.save; 2468# 2469# MDEV-21356: usage of range filter with range access employing 2470# optimizer_switch='mrr=on,mrr_sort_keys=on'; 2471# 2472CREATE TABLE t1 ( 2473id int(11) unsigned NOT NULL AUTO_INCREMENT, 2474domain varchar(255) NOT NULL, 2475registrant_name varchar(255) DEFAULT NULL, 2476registrant_organization varchar(255) DEFAULT NULL, 2477registrant_street1 varchar(255) DEFAULT NULL, 2478registrant_street2 varchar(255) DEFAULT NULL, 2479registrant_street3 varchar(255) DEFAULT NULL, 2480registrant_street4 varchar(255) DEFAULT NULL, 2481registrant_street5 varchar(255) DEFAULT NULL, 2482registrant_city varchar(255) DEFAULT NULL, 2483registrant_postal_code varchar(255) DEFAULT NULL, 2484registrant_country varchar(255) DEFAULT NULL, 2485registrant_email varchar(255) DEFAULT NULL, 2486registrant_telephone varchar(255) DEFAULT NULL, 2487administrative_name varchar(255) DEFAULT NULL, 2488administrative_organization varchar(255) DEFAULT NULL, 2489administrative_street1 varchar(255) DEFAULT NULL, 2490administrative_street2 varchar(255) DEFAULT NULL, 2491administrative_street3 varchar(255) DEFAULT NULL, 2492administrative_street4 varchar(255) DEFAULT NULL, 2493administrative_street5 varchar(255) DEFAULT NULL, 2494administrative_city varchar(255) DEFAULT NULL, 2495administrative_postal_code varchar(255) DEFAULT NULL, 2496administrative_country varchar(255) DEFAULT NULL, 2497administrative_email varchar(255) DEFAULT NULL, 2498administrative_telephone varchar(255) DEFAULT NULL, 2499technical_name varchar(255) DEFAULT NULL, 2500technical_organization varchar(255) DEFAULT NULL, 2501technical_street1 varchar(255) DEFAULT NULL, 2502technical_street2 varchar(255) DEFAULT NULL, 2503technical_street3 varchar(255) DEFAULT NULL, 2504technical_street4 varchar(255) DEFAULT NULL, 2505technical_street5 varchar(255) DEFAULT NULL, 2506technical_city varchar(255) DEFAULT NULL, 2507technical_postal_code varchar(255) DEFAULT NULL, 2508technical_country varchar(255) DEFAULT NULL, 2509technical_email varchar(255) DEFAULT NULL, 2510technical_telephone varchar(255) DEFAULT NULL, 2511json longblob NOT NULL, 2512timestamp timestamp NOT NULL DEFAULT current_timestamp(), 2513PRIMARY KEY (id), 2514KEY ixEventWhoisDomainDomain (domain), 2515KEY ixEventWhoisDomainTimestamp (timestamp) 2516) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2517INSERT INTO t1 ( 2518id, domain, registrant_name, registrant_organization, registrant_street1, 2519registrant_street2, registrant_street3, registrant_street4, registrant_street5, 2520registrant_city, registrant_postal_code, registrant_country, registrant_email, 2521registrant_telephone, administrative_name, administrative_organization, 2522administrative_street1, administrative_street2, administrative_street3, 2523administrative_street4, administrative_street5, administrative_city, 2524administrative_postal_code, administrative_country, administrative_email, 2525administrative_telephone, technical_name, technical_organization, 2526technical_street1, technical_street2, technical_street3, technical_street4, 2527technical_street5, technical_city, technical_postal_code, technical_country, 2528technical_email, technical_telephone, json, timestamp) VALUES 2529(60380, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null, 2530null, null, null, null, null, null, null, null, null, null, null, null, null, 2531null, null, null, null, null, null, null, null, null, null, null, null, null, 2532null, null, '', '2016-12-22 09:18:28'), 2533(60383, 'www.bestwestern.fr', null, null, null, null, null, null, null, null, 2534null, null, null, null, null, null, null, null, null, null, null, null, null, 2535null, null, null, null, null, null, null, null, null, null, null, null, null, 2536null, null, '', '2016-12-22 09:27:06'), 2537(80392, 'www.dfinitions.fr', null, null, null, null, null, null, null, null, 2538null, null, null, null, null, null, null, null, null, null, null, null, null, 2539null, null, null, null, null, null, null, null, null, null, null, null, null, 2540null, null, '', '2017-01-30 08:02:01'), 2541(80407, 'www.firma.o2.pl', null, null, null, null, null, null, null, null, 2542null, null, null, null, null, null, null, null, null, null, null, null, null, 2543null, null, null, null, 'AZ.pl Sp. z o.o.', 'Al. Papieza Jana Pawla II 19/2', 2544null, null, null, null, '70-453 Szczecin', null, 'POLAND', null, 2545'48914243780', '', '2017-01-30 08:24:51'), 2546(80551, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, 2547null, null, null, null, null, null, null, null, null, null, null, null, 2548null, null, null, null, null, null, null, null, null, null, null, null, 2549null, null, null, null, null, '', '2017-01-30 10:00:56'), 2550(80560, 'www.blackmer-mouvex.com', 'MARIE-PIERRE PRODEAU', 'MOUVEX', 2551'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 2552'PRODEAU@MOUVEX.COM', null, 'MARIE-PIERRE PRODEAU', 'MOUVEX', 2553'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 2554'PRODEAU@MOUVEX.COM', '33 386498630', 'LAURENT SOUCHELEAU', 'MOUVEX', 2555'2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 2556'SOUCHELEAU@MOUVEX.COM', '33 386498643', '', '2017-01-30 10:04:38'), 2557(80566, 'www.inup.com', 'MAXIMILIAN V. KETELHODT', null, 2558'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY', 2559'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 'MAXIMILIAN V. KETELHODT', 2560null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 2561'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 2562'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null, 2563null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', 2564'492214307580', '', '2017-01-30 10:08:29'); 2565SET @save_optimizer_switch=@@optimizer_switch; 2566SET optimizer_switch='mrr=on,mrr_sort_keys=on'; 2567SELECT * FROM t1 2568WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND 2569timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) 2570ORDER BY timestamp DESC; 2571id domain registrant_name registrant_organization registrant_street1 registrant_street2 registrant_street3 registrant_street4 registrant_street5 registrant_city registrant_postal_code registrant_country registrant_email registrant_telephone administrative_name administrative_organization administrative_street1 administrative_street2 administrative_street3 administrative_street4 administrative_street5 administrative_city administrative_postal_code administrative_country administrative_email administrative_telephone technical_name technical_organization technical_street1 technical_street2 technical_street3 technical_street4 technical_street5 technical_city technical_postal_code technical_country technical_email technical_telephone json timestamp 2572EXPLAIN EXTENDED SELECT * FROM t1 2573WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND 2574timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) 2575ORDER BY timestamp DESC; 2576id select_type table type possible_keys key key_len ref rows filtered Extra 25771 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 767|4 const 2 (14%) 14.29 Using index condition; Using where; Using filesort; Using rowid filter 2578Warnings: 2579Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_street2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test`.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`t1`.`timestamp` desc 2580SET optimizer_switch=@save_optimizer_switch; 2581DROP TABLE t1; 2582# 2583# MDEV-21446: index to access the table is changed for primary key 2584# 2585SET @stats.save= @@innodb_stats_persistent; 2586SET global innodb_stats_persistent=on; 2587CREATE TABLE t1 ( 2588pk int auto_increment, 2589a int, 2590b int, 2591primary key (pk), 2592key (a), 2593key (b) 2594) ENGINE=InnoDB; 2595INSERT INTO t1 (a,b) VALUES 2596(0,0), (0,9), (0,NULL), (1,2), (4,0), (2,9), (1,0), (NULL,0), (5,NULL), (5,1), 2597(0,7), (NULL,5), (NULL,0), (2,1), (2,5), (6,NULL), (0,NULL), (NULL,8), (8,5), 2598(2,NULL), (2,3), (NULL,8), (NULL,6), (1,1), (5,1), (NULL,5), (4,4), (2,4), 2599(2,5), (1,9), (NULL,0), (3,7), (0,4), (2,8), (1,2), (1,4), (2,1), 2600(NULL,7), (6,6), (3,0), (4,5), (5,2), (8,2), (NULL,NULL), (8,NULL), 2601(0,1),(0,7); 2602INSERT INTO t1(a,b) SELECT a, b FROM t1; 2603INSERT INTO t1(a,b) SELECT a, b FROM t1; 2604INSERT INTO t1(a,b) SELECT a, b FROM t1; 2605INSERT INTO t1(a,b) SELECT a, b FROM t1; 2606INSERT INTO t1(a,b) SELECT a, b FROM t1; 2607INSERT INTO t1(a,b) SELECT a, b FROM t1; 2608ANALYZE TABLE t1; 2609Table Op Msg_type Msg_text 2610test.t1 analyze status Engine-independent statistics collected 2611test.t1 analyze status OK 2612EXPLAIN EXTENDED 2613SELECT * FROM t1 2614WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9); 2615id select_type table type possible_keys key key_len ref rows filtered Extra 26161 SIMPLE t1 range|filter a,b b|a 5|5 NULL 192 (21%) 21.31 Using index condition; Using where; Using rowid filter 2617Warnings: 2618Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between 9 and 10 or `test`.`t1`.`a` is null) and (`test`.`t1`.`b` between 9 and 10 or `test`.`t1`.`b` = 9) 2619EXPLAIN EXTENDED 2620SELECT * FROM t1 2621WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) 2622ORDER BY pk LIMIT 1; 2623id select_type table type possible_keys key key_len ref rows filtered Extra 26241 SIMPLE t1 index a,b PRIMARY 4 NULL 75 54.55 Using where 2625Warnings: 2626Note 1003 select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`a` between 9 and 10 or `test`.`t1`.`a` is null) and (`test`.`t1`.`b` between 9 and 10 or `test`.`t1`.`b` = 9) order by `test`.`t1`.`pk` limit 1 2627ANALYZE 2628SELECT * FROM t1 2629WHERE (a BETWEEN 9 AND 10 OR a IS NULL) AND (b BETWEEN 9 AND 10 OR b = 9) 2630ORDER BY pk LIMIT 1; 2631id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 26321 SIMPLE t1 index a,b PRIMARY 4 NULL 3008 3008.00 1.36 0.00 Using where 2633DROP TABLE t1; 2634SET global innodb_stats_persistent= @stats.save; 2635# 2636# MDEV-21610: Using rowid filter with BKA+MRR 2637# 2638set @stats.save= @@innodb_stats_persistent; 2639set global innodb_stats_persistent=on; 2640CREATE TABLE acli ( 2641id bigint(20) NOT NULL, 2642rid varchar(255) NOT NULL, 2643tp smallint(6) NOT NULL DEFAULT 0, 2644PRIMARY KEY (id), 2645KEY acli_rid (rid), 2646KEY acli_tp (tp) 2647) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2648insert into acli(id,rid,tp) values 2649(184929059698905997,'ABABABABABABABABAB',103), 2650(184929059698905998,'ABABABABABABABABAB',121), 2651(283586039035985921,'00000000000000000000000000000000',103), 2652(2216474704108064678,'020BED6D07B741CE9B10AB2200FEF1DF',103), 2653(2216474704108064679,'020BED6D07B741CE9B10AB2200FEF1DF',121), 2654(3080602882609775593,'B5FCC8C7111E4E3CBC21AAF5012F59C2',103), 2655(3080602882609775594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121), 2656(3080602882609776594,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121), 2657(3080602882609777595,'B5FCC8C7111E4E3CBC21AAF5012F59C2',121), 2658(4269412446747236214,'SCSCSCSCSCSCSCSC',103), 2659(4269412446747236215,'SCSCSCSCSCSCSCSC',121), 2660(6341490487802728356,'6072D47E513F4A4794BBAB2200FDB67D',103), 2661(6341490487802728357,'6072D47E513F4A4794BBAB2200FDB67D',121); 2662CREATE TABLE acei ( 2663id bigint(20) NOT NULL, 2664aclid bigint(20) NOT NULL DEFAULT 0, 2665atp smallint(6) NOT NULL DEFAULT 0, 2666clus smallint(6) NOT NULL DEFAULT 0, 2667PRIMARY KEY (id), 2668KEY acei_aclid (aclid), 2669KEY acei_clus (clus) 2670) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2671insert into acei(id,aclid,atp,clus) values 2672(184929059698905999,184929059698905997,0,1), 2673(184929059698906000,184929059698905997,0,1), 2674(184929059698906001,184929059698905997,1,1), 2675(184929059698906002,184929059698905998,1,1), 2676(283586039035985922,283586039035985921,1,1), 2677(2216474704108064684,2216474704108064678,0,1), 2678(2216474704108064685,2216474704108064678,0,1), 2679(2216474704108064686,2216474704108064678,1,1), 2680(2216474704108064687,2216474704108064679,1,1), 2681(3080602882609775595,3080602882609775593,0,1), 2682(3080602882609775596,3080602882609775593,0,1), 2683(3080602882609775597,3080602882609775593,1,1), 2684(3080602882609775598,3080602882609775594,1,1), 2685(3080602882609776595,3080602882609776594,1,1), 2686(3080602882609777596,3080602882609777595,1,1), 2687(4269412446747236216,4269412446747236214,0,1), 2688(4269412446747236217,4269412446747236214,0,1), 2689(4269412446747236218,4269412446747236214,1,1), 2690(4269412446747236219,4269412446747236215,1,1), 2691(6341490487802728358,6341490487802728356,0,1), 2692(6341490487802728359,6341490487802728356,0,1), 2693(6341490487802728360,6341490487802728356,1,1), 2694(6341490487802728361,6341490487802728357,1,1); 2695CREATE TABLE filt ( 2696id bigint(20) NOT NULL, 2697aceid bigint(20) NOT NULL DEFAULT 0, 2698clid smallint(6) NOT NULL DEFAULT 0, 2699fh bigint(20) NOT NULL DEFAULT 0, 2700PRIMARY KEY (id), 2701KEY filt_aceid (aceid), 2702KEY filt_clid (clid), 2703KEY filt_fh (fh) 2704) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2705insert into filt(id,aceid,clid,fh) values 2706(184929059698905999,184929059698905999,1,8948400944397203540), 2707(184929059698906000,184929059698906000,1,-3516039679025944536), 2708(184929059698906001,184929059698906001,1,-3516039679025944536), 2709(184929059698906002,184929059698906001,1,2965370193075218252), 2710(184929059698906003,184929059698906001,1,8948400944397203540), 2711(184929059698906004,184929059698906002,1,2478709353550777738), 2712(283586039035985922,283586039035985922,1,5902600816362013271), 2713(2216474704108064686,2216474704108064684,1,8948400944397203540), 2714(2216474704108064687,2216474704108064685,1,-7244708939311117030), 2715(2216474704108064688,2216474704108064686,1,-7244708939311117030), 2716(2216474704108064689,2216474704108064686,1,7489060986210282479), 2717(2216474704108064690,2216474704108064686,1,8948400944397203540), 2718(2216474704108064691,2216474704108064687,1,-3575268945274980038), 2719(3080602882609775595,3080602882609775595,1,8948400944397203540), 2720(3080602882609775596,3080602882609775596,1,-5420422472375069774), 2721(3080602882609775597,3080602882609775597,1,-5420422472375069774), 2722(3080602882609775598,3080602882609775597,1,8518228073041491534), 2723(3080602882609775599,3080602882609775597,1,8948400944397203540), 2724(3080602882609775600,3080602882609775598,1,6311439873746261694), 2725(3080602882609775601,3080602882609775598,1,6311439873746261694), 2726(3080602882609776595,3080602882609776595,1,-661101805245999843), 2727(3080602882609777596,3080602882609777596,1,-661101805245999843), 2728(3080602882609777597,3080602882609777596,1,2216865386202464067), 2729(4269412446747236216,4269412446747236216,1,8948400944397203540), 2730(4269412446747236217,4269412446747236217,1,-1143096194892676000), 2731(4269412446747236218,4269412446747236218,1,-1143096194892676000), 2732(4269412446747236219,4269412446747236218,1,5313391811364818290), 2733(4269412446747236220,4269412446747236218,1,8948400944397203540), 2734(4269412446747236221,4269412446747236219,1,7624499822621753835), 2735(6341490487802728358,6341490487802728358,1,8948400944397203540), 2736(6341490487802728359,6341490487802728359,1,8141092449587136068), 2737(6341490487802728360,6341490487802728360,1,8141092449587136068), 2738(6341490487802728361,6341490487802728360,1,1291319099896431785), 2739(6341490487802728362,6341490487802728360,1,8948400944397203540), 2740(6341490487802728363,6341490487802728361,1,6701841652906431497); 2741analyze table filt, acei, acli; 2742Table Op Msg_type Msg_text 2743test.filt analyze status Engine-independent statistics collected 2744test.filt analyze status OK 2745test.acei analyze status Engine-independent statistics collected 2746test.acei analyze status OK 2747test.acli analyze status Engine-independent statistics collected 2748test.acli analyze status OK 2749set @save_optimizer_switch=@@optimizer_switch; 2750set @save_join_cache_level=@@join_cache_level; 2751set optimizer_switch='mrr=off'; 2752set join_cache_level=2; 2753set statement optimizer_switch='rowid_filter=off' for explain extended select t.id, fi.* 2754from (acli t inner join acei a on a.aclid = t.id) 2755inner join filt fi on a.id = fi.aceid 2756where 2757t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2758t.tp = 121 and 2759a.atp = 1 and 2760fi.fh in (6311439873746261694,-397087483897438286, 27618518228073041491534,-5420422472375069774); 2762id select_type table type possible_keys key key_len ref rows filtered Extra 27631 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 27641 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where 27651 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 1 17.14 Using where 2766Warnings: 2767Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) 2768set statement optimizer_switch='rowid_filter=off' for select t.id, fi.* 2769from (acli t inner join acei a on a.aclid = t.id) 2770inner join filt fi on a.id = fi.aceid 2771where 2772t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2773t.tp = 121 and 2774a.atp = 1 and 2775fi.fh in (6311439873746261694,-397087483897438286, 27768518228073041491534,-5420422472375069774); 2777id id aceid clid fh 27783080602882609775594 3080602882609775600 3080602882609775598 1 6311439873746261694 27793080602882609775594 3080602882609775601 3080602882609775598 1 6311439873746261694 2780set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* 2781from (acli t inner join acei a on a.aclid = t.id) 2782inner join filt fi on a.id = fi.aceid 2783where 2784t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2785t.tp = 121 and 2786a.atp = 1 and 2787fi.fh in (6311439873746261694,-397087483897438286, 27888518228073041491534,-5420422472375069774); 2789id select_type table type possible_keys key key_len ref rows filtered Extra 27901 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 27911 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where 27921 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 1 (17%) 17.14 Using where; Using rowid filter 2793Warnings: 2794Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) 2795set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* 2796from (acli t inner join acei a on a.aclid = t.id) 2797inner join filt fi on a.id = fi.aceid 2798where 2799t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2800t.tp = 121 and 2801a.atp = 1 and 2802fi.fh in (6311439873746261694,-397087483897438286, 28038518228073041491534,-5420422472375069774); 2804id id aceid clid fh 28053080602882609775594 3080602882609775600 3080602882609775598 1 6311439873746261694 28063080602882609775594 3080602882609775601 3080602882609775598 1 6311439873746261694 2807set optimizer_switch='mrr=on'; 2808set join_cache_level=6; 2809set statement optimizer_switch='rowid_filter=off' for explain extended select t.id, fi.* 2810from (acli t inner join acei a on a.aclid = t.id) 2811inner join filt fi on a.id = fi.aceid 2812where 2813t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2814t.tp = 121 and 2815a.atp = 1 and 2816fi.fh in (6311439873746261694,-397087483897438286, 28178518228073041491534,-5420422472375069774); 2818id select_type table type possible_keys key key_len ref rows filtered Extra 28191 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 28201 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where; Using join buffer (flat, BKA join); Rowid-ordered scan 28211 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 1 17.14 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan 2822Warnings: 2823Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) 2824set statement optimizer_switch='rowid_filter=off' for select t.id, fi.* 2825from (acli t inner join acei a on a.aclid = t.id) 2826inner join filt fi on a.id = fi.aceid 2827where 2828t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2829t.tp = 121 and 2830a.atp = 1 and 2831fi.fh in (6311439873746261694,-397087483897438286, 28328518228073041491534,-5420422472375069774); 2833id id aceid clid fh 28343080602882609775594 3080602882609775600 3080602882609775598 1 6311439873746261694 28353080602882609775594 3080602882609775601 3080602882609775598 1 6311439873746261694 2836set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* 2837from (acli t inner join acei a on a.aclid = t.id) 2838inner join filt fi on a.id = fi.aceid 2839where 2840t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2841t.tp = 121 and 2842a.atp = 1 and 2843fi.fh in (6311439873746261694,-397087483897438286, 28448518228073041491534,-5420422472375069774); 2845id select_type table type possible_keys key key_len ref rows filtered Extra 28461 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 28471 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where; Using join buffer (flat, BKA join); Rowid-ordered scan 28481 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 1 (17%) 17.14 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter 2849Warnings: 2850Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) 2851set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* 2852from (acli t inner join acei a on a.aclid = t.id) 2853inner join filt fi on a.id = fi.aceid 2854where 2855t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2856t.tp = 121 and 2857a.atp = 1 and 2858fi.fh in (6311439873746261694,-397087483897438286, 28598518228073041491534,-5420422472375069774); 2860id id aceid clid fh 28613080602882609775594 3080602882609775600 3080602882609775598 1 6311439873746261694 28623080602882609775594 3080602882609775601 3080602882609775598 1 6311439873746261694 2863set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.* 2864from (acli t inner join acei a on a.aclid = t.id) 2865inner join filt fi on a.id = fi.aceid 2866where 2867t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and 2868t.tp = 121 and 2869a.atp = 1 and 2870fi.fh in (6311439873746261694,-397087483897438286, 28718518228073041491534,-5420422472375069774); 2872ANALYZE 2873{ 2874 "query_block": { 2875 "select_id": 1, 2876 "r_loops": 1, 2877 "r_total_time_ms": "REPLACED", 2878 "table": { 2879 "table_name": "t", 2880 "access_type": "index_merge", 2881 "possible_keys": ["PRIMARY", "acli_rid", "acli_tp"], 2882 "key_length": "2,767", 2883 "index_merge": { 2884 "intersect": { 2885 "range": { 2886 "key": "acli_tp", 2887 "used_key_parts": ["tp"] 2888 }, 2889 "range": { 2890 "key": "acli_rid", 2891 "used_key_parts": ["rid"] 2892 } 2893 } 2894 }, 2895 "r_loops": 1, 2896 "rows": 2, 2897 "r_rows": 3, 2898 "r_table_time_ms": "REPLACED", 2899 "r_other_time_ms": "REPLACED", 2900 "filtered": 100, 2901 "r_filtered": 100, 2902 "attached_condition": "t.tp = 121 and t.rid = 'B5FCC8C7111E4E3CBC21AAF5012F59C2'", 2903 "using_index": true 2904 }, 2905 "block-nl-join": { 2906 "table": { 2907 "table_name": "a", 2908 "access_type": "ref", 2909 "possible_keys": ["PRIMARY", "acei_aclid"], 2910 "key": "acei_aclid", 2911 "key_length": "8", 2912 "used_key_parts": ["aclid"], 2913 "ref": ["test.t.id"], 2914 "r_loops": 1, 2915 "rows": 1, 2916 "r_rows": 3, 2917 "r_table_time_ms": "REPLACED", 2918 "r_other_time_ms": "REPLACED", 2919 "filtered": 100, 2920 "r_filtered": 100 2921 }, 2922 "buffer_type": "flat", 2923 "buffer_size": "8Kb", 2924 "join_type": "BKA", 2925 "mrr_type": "Rowid-ordered scan", 2926 "attached_condition": "a.atp = 1", 2927 "r_filtered": 100 2928 }, 2929 "block-nl-join": { 2930 "table": { 2931 "table_name": "fi", 2932 "access_type": "ref", 2933 "possible_keys": ["filt_aceid", "filt_fh"], 2934 "key": "filt_aceid", 2935 "key_length": "8", 2936 "used_key_parts": ["aceid"], 2937 "ref": ["test.a.id"], 2938 "rowid_filter": { 2939 "range": { 2940 "key": "filt_fh", 2941 "used_key_parts": ["fh"] 2942 }, 2943 "rows": 6, 2944 "selectivity_pct": 17.14285714, 2945 "r_rows": 5, 2946 "r_selectivity_pct": 40, 2947 "r_buffer_size": "REPLACED", 2948 "r_filling_time_ms": "REPLACED" 2949 }, 2950 "r_loops": 1, 2951 "rows": 1, 2952 "r_rows": 2, 2953 "r_table_time_ms": "REPLACED", 2954 "r_other_time_ms": "REPLACED", 2955 "filtered": 17.1428566, 2956 "r_filtered": 100 2957 }, 2958 "buffer_type": "incremental", 2959 "buffer_size": "603", 2960 "join_type": "BKA", 2961 "mrr_type": "Rowid-ordered scan", 2962 "attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)", 2963 "r_filtered": 100 2964 } 2965 } 2966} 2967set optimizer_switch=@save_optimizer_switch; 2968set join_cache_level=@save_join_cache_level; 2969drop table filt, acei, acli; 2970set global innodb_stats_persistent= @stats.save; 2971# 2972# MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter 2973# 2974CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb; 2975INSERT INTO t1 VALUES 2976(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'), 2977(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); 2978CREATE TABLE t2 ( 2979i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) 2980) engine=innodb; 2981INSERT INTO t2 VALUES 2982(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), 2983(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); 2984INSERT INTO t2 SELECT * FROM t2; 2985SELECT * FROM t1 2986WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 2987WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); 2988pk c1 298915 o 299016 x 299119 t 299235 k 299336 h 299443 h 299553 l 299662 a 299771 NULL 2998128 y 2999129 NULL 3000133 NULL 3001EXPLAIN EXTENDED SELECT * FROM t1 3002WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 3003WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); 3004id select_type table type possible_keys key key_len ref rows filtered Extra 30051 PRIMARY t1 ALL NULL NULL NULL NULL 15 100.00 Using where 30062 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 6 (33%) 33.33 Using where; Full scan on NULL key; Using rowid filter 30072 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 15 100.00 Using join buffer (flat, BNL join) 3008Warnings: 3009Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 3010Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2`.`c1` from `test`.`t2` join `test`.`t1` `a1` where `test`.`t2`.`i1` = `test`.`t1`.`pk` and `test`.`t2`.`i1` is not null and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2`.`c1`)))) 3011DROP TABLE t1,t2; 3012# End of 10.4 tests 3013