revision-id: 9e114455a95c15cd1e37dabef0dff1fe2588d50a (mariadb-10.3.6-107-g9e11445) parent(s): 447e0f023fff0fa2ccfa7e93b77f1da3be3b43f1 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-06 15:56:21 -0800 message: MDEV-16188 Post merge fixes:fixed warnings on Windows Also adjusted some result files after Galina's last patch for ANALYZE. --- mysql-test/main/join_nested_jcl6.result | 2 +- mysql-test/main/key_cache.result | 4 +- mysql-test/main/myisam_icp.result | 2 +- mysql-test/main/mysql_client_test.result | 8 +- mysql-test/main/partition_pruning.result | 2 +- mysql-test/main/range.result | 4 +- mysql-test/main/rowid_filter_innodb.result | 420 ++++++++++++++++++++- mysql-test/main/select.result | 6 +- mysql-test/main/select_jcl6.result | 6 +- mysql-test/main/select_pkeycache.result | 6 +- mysql-test/main/stat_tables.result | 6 +- mysql-test/main/stat_tables_innodb.result | 4 +- .../sys_vars/r/sysvars_server_embedded,32bit.rdiff | 9 + sql/handler.cc | 2 +- sql/multi_range_read.cc | 6 +- sql/rowid_filter.cc | 7 +- sql/rowid_filter.h | 2 +- sql/sql_explain.cc | 3 +- 18 files changed, 465 insertions(+), 34 deletions(-) diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 5791185..67534b2 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -2002,7 +2002,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter +1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 1 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b diff --git a/mysql-test/main/key_cache.result b/mysql-test/main/key_cache.result index b3368b2..e1f8892 100644 --- a/mysql-test/main/key_cache.result +++ b/mysql-test/main/key_cache.result @@ -739,13 +739,13 @@ p 1019 explain select i from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using filter +1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter select i from t2 where a='yyyy' and i=3; i 3 explain select a from t2 where a='yyyy' and i=3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using filter +1 SIMPLE t2 ref|filter k1,k2 k1|k2 5|11 const 189 (27%) Using where; Using rowid filter select a from t2 where a='yyyy' and i=3 ; a yyyy diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index b76a4c2..39c76a2 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using filter +1 SIMPLE t1 range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter DROP TABLE t1; # # diff --git a/mysql-test/main/mysql_client_test.result b/mysql-test/main/mysql_client_test.result index b5100f9..06794a0 100644 --- a/mysql-test/main/mysql_client_test.result +++ b/mysql-test/main/mysql_client_test.result @@ -149,7 +149,7 @@ ANALYZE number of fields: 13 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 + - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 @@ -178,7 +178,7 @@ ANALYZE INSERT number of fields: 13 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 + - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 @@ -207,7 +207,7 @@ ANALYZE UPDATE number of fields: 13 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 + - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 @@ -236,7 +236,7 @@ ANALYZE DELETE number of fields: 13 - 6: name: 'key_len'/''; table: ''/''; db: ''; catalog: 'def'; length: 12288; max_length: 0; type: 253; decimals: 39 - 7: name: 'ref'/''; table: ''/''; db: ''; catalog: 'def'; length: 6144; max_length: 0; type: 253; decimals: 39 - 8: name: 'rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 10 + - 9: name: 'r_rows'/''; table: ''/''; db: ''; catalog: 'def'; length: 192; max_length: 0; type: 253; decimals: 39 - 10: name: 'filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 11: name: 'r_filtered'/''; table: ''/''; db: ''; catalog: 'def'; length: 4; max_length: 0; type: 5; decimals: 2 - 12: name: 'Extra'/''; table: ''/''; db: ''; catalog: 'def'; length: 765; max_length: 0; type: 253; decimals: 39 diff --git a/mysql-test/main/partition_pruning.result b/mysql-test/main/partition_pruning.result index 14e076b..6402f23 100644 --- a/mysql-test/main/partition_pruning.result +++ b/mysql-test/main/partition_pruning.result @@ -2677,7 +2677,7 @@ select * from t1 X, t1 Y where X.b = Y.b and (X.a=1 or X.a=2) and (Y.a=2 or Y.a=3); id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE X p1,p2 range a,b a 4 NULL 4 Using where -1 SIMPLE Y p2,p3 ref|filter a,b b|a 4|4 test.X.b 2 (50%) Using where; Using filter +1 SIMPLE Y p2,p3 ref|filter a,b b|a 4|4 test.X.b 2 (50%) Using where; Using rowid filter explain partitions select * from t1 X, t1 Y where X.a = Y.a and (X.a=1 or X.a=2); id select_type table partitions type possible_keys key key_len ref rows Extra diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 8bef87d..a0e04fc 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -2589,7 +2589,7 @@ insert into t2 values explain select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 8 (14%) Using index condition; Using where; Using filter +1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 8 (14%) Using index condition; Using where; Using rowid filter 1 SIMPLE t1 ref idx idx 5 test.t2.d 8 explain format=json select * from t1,t2 where a = d and (a,e) in ((3,3),(7,7),(8,8)) and length(f) = 1; @@ -2694,7 +2694,7 @@ insert into t1 select * from t1; explain select * from t1,t2 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 7 (7%) Using index condition; Using where; Using filter +1 SIMPLE t2 range|filter idx1,idx2 idx1|idx2 5|5 NULL 7 (7%) Using index condition; Using where; Using rowid filter 1 SIMPLE t1 ref idx idx 5 test.t2.d 11 explain format=json select * from t1,t2 where a = d and (a,e) in ((4,4),(7,7),(8,8)) and length(f) = 1; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 04414a7..f72db6b 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -59,7 +59,7 @@ set statement optimizer_switch='rowid_filter=on' for EXPLAIN SELECT l_orderkey, WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; id select_type table type possible_keys key key_len ref rows Extra -1 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 filter +1 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 set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -89,6 +89,50 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 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 +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "rowid_filter": { + "range": { + "key": "i_l_quantity", + "used_key_parts": ["l_quantity"] + }, + "rows": 605, + "selectivity_pct": 10.075, + "r_rows": 605, + "r_selectivity_pct": 11.765, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 1, + "rows": 510, + "r_rows": 60, + "r_total_time_ms": "REPLACED", + "filtered": 10.075, + "r_filtered": 100, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -179,6 +223,38 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 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 +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": ["i_l_shipdate", "i_l_quantity"], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 510, + "r_rows": 510, + "r_total_time_ms": "REPLACED", + "filtered": 10.075, + "r_filtered": 11.765, + "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'", + "attached_condition": "lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT l_orderkey, l_linenumber, l_shipdate, l_quantity FROM lineitem WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND l_quantity > 45; @@ -289,6 +365,62 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 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 +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 11.22 Using where +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 98, + "r_rows": 98, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", + "using_index": true + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "r_loops": 98, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 4.7333, + "r_filtered": 11.224, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -351,6 +483,62 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 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 +1 SIMPLE orders eq_ref PRIMARY,i_o_totalprice PRIMARY 4 dbt3_s001.lineitem.l_orderkey 1 1.00 4.73 11.22 Using where +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "lineitem", + "access_type": "range", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_shipdate", + "key_length": "4", + "used_key_parts": ["l_shipDATE"], + "r_loops": 1, + "rows": 98, + "r_rows": 98, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'", + "using_index": true + }, + "table": { + "table_name": "orders", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["o_orderkey"], + "ref": ["dbt3_s001.lineitem.l_orderkey"], + "r_loops": 98, + "rows": 1, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 4.7333, + "r_filtered": 11.224, + "attached_condition": "orders.o_totalprice between 200000 and 230000" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -416,6 +604,65 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 144.00 100.00 100.00 Using where; Using index +1 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 +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 144, + "r_rows": 144, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 180000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "r_loops": 144, + "rows": 4, + "r_rows": 6.625, + "r_total_time_ms": "REPLACED", + "filtered": 0.8557, + "r_filtered": 1.6771, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -487,6 +734,65 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 144 144.00 100.00 100.00 Using where; Using index +1 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 +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +l_quantity > 45 AND +o_totalprice between 180000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 144, + "r_rows": 144, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 180000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity", + "i_l_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "r_loops": 144, + "rows": 4, + "r_rows": 6.625, + "r_total_time_ms": "REPLACED", + "filtered": 0.8557, + "r_filtered": 1.6771, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30' and lineitem.l_quantity > 45" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, l_quantity, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -555,6 +861,62 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=on' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 71.00 100.00 100.00 Using where; Using index +1 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 +set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 71, + "r_rows": 71, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 200000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "r_loops": 71, + "rows": 4, + "r_rows": 6.7042, + "r_total_time_ms": "REPLACED", + "filtered": 8.4929, + "r_filtered": 7.7731, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} set statement optimizer_switch='rowid_filter=on' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND @@ -643,6 +1005,62 @@ EXPLAIN } } } +set statement optimizer_switch='rowid_filter=off' for ANALYZE SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 71 71.00 100.00 100.00 Using where; Using index +1 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 +set statement optimizer_switch='rowid_filter=off' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice +FROM orders JOIN lineitem ON o_orderkey=l_orderkey +WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND +o_totalprice between 200000 and 230000; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "orders", + "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "r_loops": 1, + "rows": 71, + "r_rows": 71, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "orders.o_totalprice between 200000 and 230000", + "using_index": true + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], + "r_loops": 71, + "rows": 4, + "r_rows": 6.7042, + "r_total_time_ms": "REPLACED", + "filtered": 8.4929, + "r_filtered": 7.7731, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-06-30'" + } + } +} set statement optimizer_switch='rowid_filter=off' for SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-06-30' AND diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index a73516f..7a71c34 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3617,7 +3617,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3625,7 +3625,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3745,7 +3745,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 3f9a9f4..8b49623 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3628,7 +3628,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3636,7 +3636,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3756,7 +3756,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index a73516f..7a71c34 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3617,7 +3617,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2,t3 WHERE t1.id = 8 AND t2.i BETWEEN t1.b AND t1.e AND @@ -3625,7 +3625,7 @@ t3.a=t2.a AND t3.c IN ('bb','ee') ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 1 SIMPLE t2 range si,ai si 5 NULL 4 Using index condition; Using where -1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using filter +1 SIMPLE t3 eq_ref|filter PRIMARY,ci PRIMARY|ci 4|5 test.t2.a 1 (30%) Using where; Using rowid filter EXPLAIN SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3 WHERE t1.id = 8 AND (t2.i=t1.b OR t2.i=t1.e) AND t3.a=t2.a AND @@ -3745,7 +3745,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using filter +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/stat_tables.result b/mysql-test/main/stat_tables.result index 8cfa838..efa5334 100644 --- a/mysql-test/main/stat_tables.result +++ b/mysql-test/main/stat_tables.result @@ -68,7 +68,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 -1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using filter +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region @@ -175,7 +175,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 -1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using filter +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (12%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region @@ -332,7 +332,7 @@ and o_orderkey=l_orderkey and p_partkey=l_partkey; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE orders ref PRIMARY,i_o_orderdate i_o_orderdate 4 const 1 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey_partkey,i_l_partkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where -1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using filter +1 SIMPLE part eq_ref|filter PRIMARY,i_p_retailprice PRIMARY|i_p_retailprice 4|9 dbt3_s001.lineitem.l_partkey 1 (1%) Using where; Using rowid filter select o_orderkey, p_partkey from part, lineitem, orders where p_retailprice > 1100 and o_orderdate='1997-01-01' diff --git a/mysql-test/main/stat_tables_innodb.result b/mysql-test/main/stat_tables_innodb.result index feb7a4c..c49c925 100644 --- a/mysql-test/main/stat_tables_innodb.result +++ b/mysql-test/main/stat_tables_innodb.result @@ -76,7 +76,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index -1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using filter +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region @@ -207,7 +207,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE nation ref PRIMARY,i_n_regionkey i_n_regionkey 5 dbt3_s001.region.r_regionkey 5 1 SIMPLE supplier ref PRIMARY,i_s_nationkey i_s_nationkey 5 dbt3_s001.nation.n_nationkey 1 Using index 1 SIMPLE customer ref PRIMARY,i_c_nationkey i_c_nationkey 5 dbt3_s001.nation.n_nationkey 6 Using index -1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using filter +1 SIMPLE orders ref|filter PRIMARY,i_o_orderdate,i_o_custkey i_o_custkey|i_o_orderdate 5|4 dbt3_s001.customer.c_custkey 15 (14%) Using where; Using rowid filter 1 SIMPLE lineitem ref PRIMARY,i_l_suppkey,i_l_orderkey,i_l_orderkey_quantity PRIMARY 4 dbt3_s001.orders.o_orderkey 4 Using where select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index 5d47090..e10b2c5 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -507,6 +507,15 @@ VARIABLE_COMMENT Maximum stored procedure recursion depth NUMERIC_MIN_VALUE 0 NUMERIC_MAX_VALUE 255 +@@ -2261,7 +2261,7 @@ + VARIABLE_TYPE BIGINT UNSIGNED + VARIABLE_COMMENT The maximum size of the container of a rowid filter + NUMERIC_MIN_VALUE 1024 +-NUMERIC_MAX_VALUE 18446744073709551615 ++NUMERIC_MAX_VALUE 4294967295 + NUMERIC_BLOCK_SIZE 1 + ENUM_VALUE_LIST NULL + READ_ONLY NO @@ -2284,7 +2284,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME DEFAULT_VALUE 32 diff --git a/sql/handler.cc b/sql/handler.cc index 932797a..b1a2719 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2826,7 +2826,7 @@ double handler::keyread_time(uint index, uint ranges, ha_rows rows) size_t len= table->key_info[index].key_length + ref_length; if (index == table->s->primary_key && table->file->primary_key_is_clustered()) len= table->s->stored_rec_length; - uint keys_per_block= (stats.block_size/2.0/len+1); + uint keys_per_block= (uint) (stats.block_size/2.0/len+1); ulonglong blocks= !rows ? 0 : (rows-1) / keys_per_block + 1; double cost= (double)rows*len/(stats.block_size+1)*IDX_BLOCK_COPY_COST; if (ranges) diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc index cab278f..12f72f5 100644 --- a/sql/multi_range_read.cc +++ b/sql/multi_range_read.cc @@ -20,7 +20,7 @@ #include "key.h" #include "sql_statistics.h" -static ulonglong key_block_no(handler *h, uint keyno, uint keyentry_pos) +static ulonglong key_block_no(handler *h, uint keyno, ha_rows keyentry_pos) { return (ulonglong) (h->keyread_time(keyno, 1, keyentry_pos + 1) - h->keyread_time(keyno, 0, keyentry_pos + 1) + 0.5) + 1; @@ -217,7 +217,9 @@ handler::multi_range_read_info_const(uint keyno, RANGE_SEQ_IF *seq, } else { - cost->io_count= read_time(keyno, total_touched_blocks, (uint) total_rows); + cost->io_count= read_time(keyno, + (uint)total_touched_blocks, + (uint) total_rows); cost->cpu_cost= (double) total_rows / TIME_FOR_COMPARE + 0.01; } } diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index b2349d2..c899236 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -64,7 +64,7 @@ void Range_rowid_filter_cost_info::init(Rowid_filter_container_type cont_type, container_type= cont_type; table= tab; key_no= idx; - est_elements= table->quick_rows[key_no]; + est_elements= (ulonglong) (table->quick_rows[key_no]); b= build_cost(container_type); selectivity= est_elements/((double) table->stat_records()); a= avg_access_and_eval_gain_per_row(container_type); @@ -108,7 +108,8 @@ Rowid_filter_container *Range_rowid_filter_cost_info::create_container() switch (container_type) { case SORTED_ARRAY_CONTAINER: - res= new (thd->mem_root) Rowid_filter_sorted_array(est_elements, elem_sz); + res= new (thd->mem_root) Rowid_filter_sorted_array((uint) est_elements, + elem_sz); break; default: DBUG_ASSERT(0); @@ -245,7 +246,7 @@ void TABLE::prune_range_rowid_filters() Return maximum number of elements that a container allowed to have */ -static uint +static ulonglong get_max_range_rowid_filter_elems_for_table( THD *thd, TABLE *tab, Rowid_filter_container_type cont_type) diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index de50fd3..ddeb13a 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -383,7 +383,7 @@ class Range_rowid_filter_cost_info : public Sql_alloc /* The table for which the range filter is to be built (if needed) */ TABLE *table; /* Estimated number of elements in the filter */ - double est_elements; + ulonglong est_elements; /* The cost of building the range filter */ double b; /* diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 0c2f493..fbec103 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -1313,7 +1313,8 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai if (rowid_filter) { rows_str.append(" ("); - rows_str.append_ulonglong(round(rowid_filter->selectivity * 100.0)); + rows_str.append_ulonglong((ulonglong) (round(rowid_filter->selectivity * + 100.0))); rows_str.append("%)"); } item_list.push_back(new (mem_root)