- commits - lists.mariadb.org

[Commits] 3b7b3843eb4: Fix compile warnings on windows
by psergey 04 Mar '20

04 Mar '20

revision-id: 3b7b3843eb437544735a10bf4a03929430e69dea (mariadb-10.5.0-243-g3b7b3843eb4) parent(s): 219d7f769634b007953b4d16c9d484adfd7abd6e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-04 13:27:34 +0300 message: Fix compile warnings on windows The fix needs a followup: "ulong" is 32-bit on 64-bit Windows, so xpand_connection_cursor::rowdata::length needs to be 64-bit but this causes a cascade of datatype changes all over the code. --- storage/xpand/ha_xpand.cc | 4 ++-- storage/xpand/xpand_connection.cc | 4 ++-- storage/xpand/xpand_connection.h | 2 +- 3 files changed, 5 insertions(+), 5 deletions(-) diff --git a/storage/xpand/ha_xpand.cc b/storage/xpand/ha_xpand.cc index 56053c8ca1f..78a84b7c049 100644 --- a/storage/xpand/ha_xpand.cc +++ b/storage/xpand/ha_xpand.cc @@ -940,7 +940,7 @@ int ha_xpand::index_read(uchar * buf, const uchar * key, uint key_len, uchar *rowdata = NULL; if (exact) { is_scan = false; - ulong rowdata_length; + ulonglong rowdata_length; error_code = trx->key_read(xpand_table_oid, 0, xpd_lock_type, table->read_set, packed_key, packed_key_len, &rowdata, &rowdata_length); @@ -1148,7 +1148,7 @@ int ha_xpand::rnd_pos(uchar * buf, uchar *pos) build_key_packed_row(table->s->primary_key, buf, packed_key, &packed_key_len); uchar *rowdata = NULL; - ulong rowdata_length; + ulonglong rowdata_length; if ((error_code = trx->key_read(xpand_table_oid, 0, xpd_lock_type, table->read_set, packed_key, packed_key_len, &rowdata, &rowdata_length))) diff --git a/storage/xpand/xpand_connection.cc b/storage/xpand/xpand_connection.cc index 8e0b5db9e6d..d8425e603e4 100644 --- a/storage/xpand/xpand_connection.cc +++ b/storage/xpand/xpand_connection.cc @@ -497,7 +497,7 @@ int xpand_connection::key_delete(ulonglong xpand_table_oid, int xpand_connection::key_read(ulonglong xpand_table_oid, uint index, xpand_lock_mode_t lock_mode, MY_BITMAP *read_set, uchar *packed_key, ulong packed_key_length, - uchar **rowdata, ulong *rowdata_length) + uchar **rowdata, ulonglong *rowdata_length) { int error_code; command_length = 0; @@ -594,7 +594,7 @@ class xpand_connection_cursor { } uchar *rowdata = xpand_net->net.read_pos; - ulong rowdata_length = safe_net_field_length_ll(&rowdata, packet_length); + ulong rowdata_length = (ulong) safe_net_field_length_ll(&rowdata, packet_length); if (!rowdata_length) { // We have read all rows in this batch. DBUG_RETURN(0); diff --git a/storage/xpand/xpand_connection.h b/storage/xpand/xpand_connection.h index 419d7f3ef35..541af350dbc 100644 --- a/storage/xpand/xpand_connection.h +++ b/storage/xpand/xpand_connection.h @@ -78,7 +78,7 @@ class xpand_connection int key_read(ulonglong xpand_table_oid, uint index, xpand_lock_mode_t lock_mode, MY_BITMAP *read_set, uchar *packed_key, ulong packed_key_length, uchar **rowdata, - ulong *rowdata_length); + ulonglong *rowdata_length); enum sort_order {SORT_NONE = 0, SORT_ASC = 1, SORT_DESC = 2}; enum scan_type { READ_KEY_OR_NEXT, /* rows with key and greater */

1 0

[Commits] 219d7f76963: Fix federated.federatedx_create_handler with --ps-protocol, for CLX-77
by psergey 04 Mar '20

04 Mar '20

revision-id: 219d7f769634b007953b4d16c9d484adfd7abd6e (mariadb-10.5.0-242-g219d7f76963) parent(s): 826a38adb2be5ffce8f15d71ce8995046eb8f3db author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-04 12:58:06 +0300 message: Fix federated.federatedx_create_handler with --ps-protocol, for CLX-77 CLX-77 code used "thd->lex->result!=NULL" to check if the SELECT has an INTO part. This is not correct, as the condition also holds when we're using the PS protocol. Use a more generic check: check whether the SELECT has any side effect. --- storage/federatedx/federatedx_pushdown.cc | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) diff --git a/storage/federatedx/federatedx_pushdown.cc b/storage/federatedx/federatedx_pushdown.cc index 4e99a56a62f..1b19da48e06 100644 --- a/storage/federatedx/federatedx_pushdown.cc +++ b/storage/federatedx/federatedx_pushdown.cc @@ -185,10 +185,11 @@ create_federatedx_select_handler(THD* thd, SELECT_LEX *sel) /* Currently, ha_federatedx_select_handler::init_scan just takes the thd->query and sends it to the backend. - This obviously won't work if the SELECT has an INTO part. - Refuse to work in this case. + This obviously won't work if the SELECT uses an "INTO @var" or + "INTO OUTFILE". It is also unlikely to work if the select has some + other kind of side effect. */ - if (thd->lex->result) + if (sel->uncacheable & UNCACHEABLE_SIDEEFFECT) return NULL; handler= new ha_federatedx_select_handler(thd, sel);

1 0

[Commits] 2536ec0: Drop sbtest database on start; pretty-print perf_context counters
by Sergei Petrunia 01 Mar '20

01 Mar '20

revision-id: 2536ec0ab4f0355a0b5b60a66c1e12c58f4ad1ec () parent(s): f9595f65bbd846210cedb52d9d1e74fdbc6329d5 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-03-01 16:59:11 +0300 message: Drop sbtest database on start; pretty-print perf_context counters --- run-test.sh | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/run-test.sh b/run-test.sh index d61f421..7a996ca 100755 --- a/run-test.sh +++ b/run-test.sh @@ -104,6 +104,7 @@ while true ; do sleep 5 client_attempts=0 while true ; do + $MYSQL_CMD -e "drop database sbtest" $MYSQL_CMD -e "create database sbtest" if [ $? -eq 0 ]; then @@ -215,7 +216,7 @@ for threads in 1 5 10 20 40 60 80 100; do where B.VARIABLE_NAME=A.VARIABLE_NAME AND B.VARIABLE_VALUE - A.VARIABLE_VALUE >0" > $RESULT_DIR/status-after-test-$threads.txt $MYSQL_CMD -e \ - "select A.STAT_TYPE, B.VALUE - A.VALUE \ + "select A.STAT_TYPE, FORMAT(B.VALUE - A.VALUE,0) \ from information_schema.rocksdb_perf_context_global B, test.rocksdb_perf_context_global A \ where B.STAT_TYPE=A.STAT_TYPE AND B.VALUE - A.VALUE >0" > $RESULT_DIR/perf_context-after-test-$threads.txt

1 0

[Commits] 7d8e48a51fa: MDEV-21838: Add information about packed addon fields in ANALYZE FORMAT=JSON
by Varun 28 Feb '20

28 Feb '20

revision-id: 7d8e48a51faa941f89031cec16b5a0c07ee2524c (mariadb-10.5.0-274-g7d8e48a51fa) parent(s): 8db623038f7158529e804e9607362939bff37337 author: Varun Gupta committer: Varun Gupta timestamp: 2020-02-28 11:46:13 +0530 message: MDEV-21838: Add information about packed addon fields in ANALYZE FORMAT=JSON It is useful to know whether sorting uses addon fields[packed|unpacked] or ROWID. Provide this information in ANALYZE FORMAT=JSON output. --- mysql-test/main/analyze_format_json.result | 6 +++ mysql-test/main/analyze_stmt_orderby.result | 7 +++ mysql-test/main/order_by_pack_big.result | 73 +++++++++++++++++++++++++++++ mysql-test/main/order_by_pack_big.test | 2 + sql/filesort.cc | 4 ++ sql/sql_analyze_stmt.cc | 20 ++++++++ sql/sql_analyze_stmt.h | 14 +++++- 7 files changed, 125 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result index ccef3a63592..c505aae563b 100644 --- a/mysql-test/main/analyze_format_json.result +++ b/mysql-test/main/analyze_format_json.result @@ -517,6 +517,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -548,6 +549,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 256, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -590,6 +592,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 256, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -701,6 +704,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "filesort": { "sort_key": "(subquery#2)", @@ -709,6 +713,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -824,6 +829,7 @@ ANALYZE "r_limit": null, "r_used_priority_queue": null, "r_output_rows": null, + "r_sort_mode": "sort_key,rowid", "table": { "table_name": "t2", "access_type": "ALL", diff --git a/mysql-test/main/analyze_stmt_orderby.result b/mysql-test/main/analyze_stmt_orderby.result index e23813944f4..47bc856ba53 100644 --- a/mysql-test/main/analyze_stmt_orderby.result +++ b/mysql-test/main/analyze_stmt_orderby.result @@ -44,6 +44,7 @@ ANALYZE "r_limit": 5, "r_used_priority_queue": true, "r_output_rows": 6, + "r_sort_mode": "sort_key,rowid", "table": { "update": 1, "table_name": "t2", @@ -145,6 +146,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10000, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "table": { "delete": 1, "table_name": "t2", @@ -211,6 +213,7 @@ ANALYZE "r_limit": 4, "r_used_priority_queue": true, "r_output_rows": 4, + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t0", @@ -300,6 +303,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,addon_fields", "table": { "table_name": "t0", "access_type": "ALL", @@ -359,6 +363,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 10, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t2", @@ -472,6 +477,7 @@ ANALYZE "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, + "r_sort_mode": "sort_key,rowid", "temporary_table": { "filesort": { "sort_key": "t5.a", @@ -480,6 +486,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 6, "r_buffer_size": "REPLACED", + "r_sort_mode": "sort_key,rowid", "temporary_table": { "table": { "table_name": "t6", diff --git a/mysql-test/main/order_by_pack_big.result b/mysql-test/main/order_by_pack_big.result index 0efc2ff150f..ea530e8eac6 100644 --- a/mysql-test/main/order_by_pack_big.result +++ b/mysql-test/main/order_by_pack_big.result @@ -82,6 +82,42 @@ select id, generate_random_string(a), generate_random_string(b) from t2; # All records fit in memory # set sort_buffer_size=262144*10; +analyze format=json select id DIV 100 as x, +MD5(group_concat(substring(names,1,3), substring(address,1,3) +order by id)) +FROM t3 +GROUP BY x; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": 158.46, + "read_sorted_file": { + "r_rows": 10000, + "filesort": { + "sort_key": "t3.`id` DIV 100", + "r_loops": 1, + "r_total_time_ms": 65.276, + "r_used_priority_queue": false, + "r_output_rows": 10000, + "r_buffer_size": "2150Kb", + "r_sort_mode": "sort_key,packed_addon_fields", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 10000, + "r_rows": 10000, + "r_table_time_ms": 33.772, + "r_other_time_ms": 31.087, + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} flush status; select id DIV 100 as x, MD5(group_concat(substring(names,1,3), substring(address,1,3) @@ -203,6 +239,43 @@ set sort_buffer_size=default; # Test for merge_many_buff # set sort_buffer_size=32768; +analyze format=json select id DIV 100 as x, +MD5(group_concat(substring(names,1,3), substring(address,1,3) +order by id)) +FROM t3 +GROUP BY x; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": 283.51, + "read_sorted_file": { + "r_rows": 10000, + "filesort": { + "sort_key": "t3.`id` DIV 100", + "r_loops": 1, + "r_total_time_ms": 106.78, + "r_used_priority_queue": false, + "r_output_rows": 10000, + "r_sort_passes": 4, + "r_buffer_size": "31Kb", + "r_sort_mode": "sort_key,packed_addon_fields", + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 10000, + "r_rows": 10000, + "r_table_time_ms": 38.727, + "r_other_time_ms": 67.97, + "filtered": 100, + "r_filtered": 100 + } + } + } + } +} flush status; select id DIV 100 as x, MD5(group_concat(substring(names,1,3), substring(address,1,3) diff --git a/mysql-test/main/order_by_pack_big.test b/mysql-test/main/order_by_pack_big.test index 89f9f3e539a..0939308593e 100644 --- a/mysql-test/main/order_by_pack_big.test +++ b/mysql-test/main/order_by_pack_big.test @@ -109,6 +109,7 @@ let $query= select id DIV 100 as x, --echo # set sort_buffer_size=262144*10; +eval analyze format=json $query; flush status; eval $query; show status like '%sort%'; @@ -119,6 +120,7 @@ set sort_buffer_size=default; --echo # set sort_buffer_size=32768; +eval analyze format=json $query; flush status; eval $query; show status like '%sort%'; diff --git a/sql/filesort.cc b/sql/filesort.cc index 763f9f59246..ceecc052899 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -298,6 +298,10 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort, tracker->report_sort_buffer_size(sort->sort_buffer_size()); } + if (param.using_addon_fields()) + // report information whether addon fields are packed or not + tracker->report_addon_fields_format(param.using_packed_addons()); + if (open_cached_file(&buffpek_pointers,mysql_tmpdir,TEMP_PREFIX, DISK_BUFFER_SIZE, MYF(MY_WME))) goto err; diff --git a/sql/sql_analyze_stmt.cc b/sql/sql_analyze_stmt.cc index fdabcf1e494..2147d6d7ffc 100644 --- a/sql/sql_analyze_stmt.cc +++ b/sql/sql_analyze_stmt.cc @@ -26,6 +26,7 @@ void Filesort_tracker::print_json_members(Json_writer *writer) { const char *varied_str= "(varied across executions)"; + String str; if (!get_r_loops()) writer->add_member("r_loops").add_null(); @@ -78,6 +79,25 @@ void Filesort_tracker::print_json_members(Json_writer *writer) else writer->add_size(sort_buffer_size); } + + get_data_format(&str); + writer->add_member("r_sort_mode").add_str(str.c_ptr(), str.length()); +} + +void Filesort_tracker::get_data_format(String *str) +{ + str->append("sort_key"); + str->append(","); + + if (r_using_addons) + { + if (r_packed_addon_fields) + str->append("packed_addon_fields"); + else + str->append("addon_fields"); + } + else + str->append("rowid"); } void attach_gap_time_tracker(THD *thd, Gap_time_tracker *gap_tracker, diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h index 9d5151c3be2..dfe29517b63 100644 --- a/sql/sql_analyze_stmt.h +++ b/sql/sql_analyze_stmt.h @@ -221,7 +221,9 @@ class Filesort_tracker : public Sql_alloc time_tracker(do_timing), r_limit(0), r_used_pq(0), r_examined_rows(0), r_sorted_rows(0), r_output_rows(0), sort_passes(0), - sort_buffer_size(0) + sort_buffer_size(0), + r_using_addons(false), + r_packed_addon_fields(false) {} /* Functions that filesort uses to report various things about its execution */ @@ -263,6 +265,14 @@ class Filesort_tracker : public Sql_alloc else sort_buffer_size= bufsize; } + + inline void report_addon_fields_format(bool addons_packed) + { + r_using_addons= true; + r_packed_addon_fields= addons_packed; + } + + void get_data_format(String *str); /* Functions to get the statistics */ void print_json_members(Json_writer *writer); @@ -322,6 +332,8 @@ class Filesort_tracker : public Sql_alloc other - value */ ulonglong sort_buffer_size; + bool r_using_addons; + bool r_packed_addon_fields; };

1 0

[Commits] 92fa873fabf: MDEV-21794: Optimizer flag rowid_filter leads to long query
by psergey 27 Feb '20

27 Feb '20

revision-id: 92fa873fabfe28dc845b8384fe332a8e784d7f6f (mariadb-10.4.11-88-g92fa873fabf) parent(s): a17a327f116302612a889af7c448ef1cd8243f28 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-02-28 01:44:14 +0300 message: MDEV-21794: Optimizer flag rowid_filter leads to long query The issue is the same as Index Condition Pushdown had: before checking pushed index condition (or rowid filter), we must check that we have not walked out of the range we are scanning. If we fail to check this, we may end up scanning till the end of the table which is a huge performance killer for queries that scan small ranges. The solution is the same as in ICP: if we haven't make the end-of-range check for ICP, make it in Rowid Filter callback. --- include/my_compare.h | 3 +- mysql-test/main/rowid_filter_innodb.result | 71 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 43 ++++++++++++++++++ sql/handler.cc | 12 ++++- sql/handler.h | 4 +- storage/innobase/row/row0sel.cc | 17 +++++-- storage/myisam/mi_extra.c | 2 +- storage/myisam/mi_key.c | 5 ++- storage/myisam/mi_rkey.c | 2 +- storage/myisam/mi_rnext.c | 2 +- storage/myisam/mi_rnext_same.c | 2 +- storage/myisam/mi_rprev.c | 2 +- storage/myisam/myisamdef.h | 6 +-- 13 files changed, 154 insertions(+), 17 deletions(-) diff --git a/include/my_compare.h b/include/my_compare.h index 6568e5f2f27..8d6e9ea1de5 100644 --- a/include/my_compare.h +++ b/include/my_compare.h @@ -152,6 +152,7 @@ typedef enum icp_result { } ICP_RESULT; typedef ICP_RESULT (*index_cond_func_t)(void *param); -typedef int (*rowid_filter_func_t)(void *param); +typedef ICP_RESULT (*rowid_filter_func_t)(void *param, my_bool icp_check_done); +typedef int (*rowid_filter_is_active_func_t)(void *param); #endif /* _my_compare_h */ diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 9423fb18fd9..d885f6c4ea8 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2521,6 +2521,77 @@ ORDER BY pk LIMIT 1; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1 SIMPLE t1 index a,b PRIMARY 4 NULL 3008 3008.00 1.36 0.00 Using where DROP TABLE t1; +# +# MDEV-21794: Optimizer flag rowid_filter leads to long query +# +create table t10(a int); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t11(a int); +insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; +CREATE TABLE t1 ( +el_id int(10) unsigned NOT NULL , +el_index blob NOT NULL, +el_index_60 varbinary(60) NOT NULL, +filler blob, +PRIMARY KEY (el_id), +KEY el_index (el_index(60)), +KEY el_index_60 (el_index_60,el_id) +) engine=innodb; +insert into t1 +select +A.a+1000*B.a, +A.a+1000*B.a + 10000, +A.a+1000*B.a + 10000, +'filler-data-filler-data' +from +t11 A, t10 B; +# This must have key=el_index|el_index_60 +explain +select * from t1 +where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range|filter el_index,el_index_60 el_index|el_index_60 62|62 NULL 1000 (10%) Using where; Using rowid filter +# This must show r_filtered=100%. r_filtered=10% means the bug is there. +analyze format=json +select * from t1 +where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "range", + "possible_keys": ["el_index", "el_index_60"], + "key": "el_index", + "key_length": "62", + "used_key_parts": ["el_index"], + "rowid_filter": { + "range": { + "key": "el_index_60", + "used_key_parts": ["el_index_60"] + }, + "rows": "REPLACED", + "selectivity_pct": "REPLACED", + "r_rows": 1000, + "r_selectivity_pct": 100, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, + "r_loops": 1, + "rows": "REPLACED", + "r_rows": 1000, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": "REPLACED", + "r_filtered": 100, + "attached_condition": "t1.el_index like '10%' and (t1.el_index_60 like '10%' or t1.el_index_60 like '20%')" + } + } +} +drop table t10, t11, t1; SET global innodb_stats_persistent= @stats.save; # # MDEV-21610: Using rowid filter with BKA+MRR diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 74349b8c6bb..36af2a30e56 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -380,6 +380,49 @@ SELECT * FROM t1 ORDER BY pk LIMIT 1; DROP TABLE t1; + +--echo # +--echo # MDEV-21794: Optimizer flag rowid_filter leads to long query +--echo # +create table t10(a int); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t11(a int); +insert into t11 select A.a + B.a* 10 + C.a * 100 from t10 A, t10 B, t10 C; + + +CREATE TABLE t1 ( + el_id int(10) unsigned NOT NULL , + el_index blob NOT NULL, + el_index_60 varbinary(60) NOT NULL, + filler blob, + + PRIMARY KEY (el_id), + KEY el_index (el_index(60)), + KEY el_index_60 (el_index_60,el_id) +) engine=innodb; + +insert into t1 +select + A.a+1000*B.a, + A.a+1000*B.a + 10000, + A.a+1000*B.a + 10000, + 'filler-data-filler-data' +from + t11 A, t10 B; + +--echo # This must have key=el_index|el_index_60 +explain +select * from t1 +where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); + +--echo # This must show r_filtered=100%. r_filtered=10% means the bug is there. +--source include/analyze-format-and-estimates.inc +analyze format=json +select * from t1 +where el_index like '10%' and (el_index_60 like '10%' or el_index_60 like '20%'); + +drop table t10, t11, t1; SET global innodb_stats_persistent= @stats.save; --echo # diff --git a/sql/handler.cc b/sql/handler.cc index 77b7f490590..4307a819eae 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -5983,12 +5983,20 @@ extern "C" enum icp_result handler_index_cond_check(void* h_arg) keys of the rows whose data is to be fetched against the used rowid filter */ -extern "C" int handler_rowid_filter_check(void *h_arg) +extern "C" +enum icp_result handler_rowid_filter_check(void *h_arg, my_bool icp_check_done) { handler *h= (handler*) h_arg; TABLE *tab= h->get_table(); + + if (!icp_check_done) + { + if (h->end_range && h->compare_key2(h->end_range) > 0) + return ICP_OUT_OF_RANGE; + } + h->position(tab->record[0]); - return h->pushed_rowid_filter->check((char *) h->ref); + return h->pushed_rowid_filter->check((char*)h->ref)? ICP_MATCH: ICP_NO_MATCH; } diff --git a/sql/handler.h b/sql/handler.h index 421941c0b35..95aef8c4cf8 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -2912,7 +2912,9 @@ class ha_statistics extern "C" enum icp_result handler_index_cond_check(void* h_arg); -extern "C" int handler_rowid_filter_check(void* h_arg); +extern "C" +enum icp_result handler_rowid_filter_check(void* h_arg, + my_bool icp_check_done); extern "C" int handler_rowid_filter_is_active(void* h_arg); uint calculate_key_len(TABLE *, uint, const uchar *, key_part_map); diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc index 7b6df752043..2e35311224c 100644 --- a/storage/innobase/row/row0sel.cc +++ b/storage/innobase/row/row0sel.cc @@ -3952,9 +3952,20 @@ row_search_idx_cond_check( ut_ad(len == DATA_ROW_ID_LEN); memcpy(prebuilt->row_id, data, DATA_ROW_ID_LEN); } - if (!handler_rowid_filter_check(prebuilt->pk_filter)) { - MONITOR_INC(MONITOR_ICP_MATCH); - return(ICP_NO_MATCH); + bool icp_check_done = prebuilt->idx_cond?true:false; + result = handler_rowid_filter_check(prebuilt->pk_filter, + icp_check_done); + switch (result) { + case ICP_NO_MATCH: + MONITOR_INC(MONITOR_ICP_NO_MATCH); + return(result); + case ICP_OUT_OF_RANGE: + MONITOR_INC(MONITOR_ICP_OUT_OF_RANGE); + return(result); + case ICP_MATCH: + break; + default: + ut_error; } } /* Convert the remaining fields to MySQL format. diff --git a/storage/myisam/mi_extra.c b/storage/myisam/mi_extra.c index d1bf903bd46..67cb714e7bf 100644 --- a/storage/myisam/mi_extra.c +++ b/storage/myisam/mi_extra.c @@ -424,7 +424,7 @@ void mi_set_index_cond_func(MI_INFO *info, index_cond_func_t func, void mi_set_rowid_filter_func(MI_INFO *info, rowid_filter_func_t check_func, - rowid_filter_func_t is_active_func, + rowid_filter_is_active_func_t is_active_func, void *func_arg) { info->rowid_filter_func= check_func; diff --git a/storage/myisam/mi_key.c b/storage/myisam/mi_key.c index dd838a05ada..22242d96810 100644 --- a/storage/myisam/mi_key.c +++ b/storage/myisam/mi_key.c @@ -530,9 +530,10 @@ ICP_RESULT mi_check_index_cond(register MI_INFO *info, uint keynr, } -int mi_check_rowid_filter(MI_INFO *info) +ICP_RESULT mi_check_rowid_filter(MI_INFO *info) { - return info->rowid_filter_func(info->rowid_filter_func_arg); + return info->rowid_filter_func(info->rowid_filter_func_arg, + (info->index_cond_func)?1:0); } int mi_check_rowid_filter_is_active(MI_INFO *info) diff --git a/storage/myisam/mi_rkey.c b/storage/myisam/mi_rkey.c index 8ef1d917f38..41edc35cc0f 100644 --- a/storage/myisam/mi_rkey.c +++ b/storage/myisam/mi_rkey.c @@ -122,7 +122,7 @@ int mi_rkey(MI_INFO *info, uchar *buf, int inx, const uchar *key, (info->index_cond_func && (res= mi_check_index_cond(info, inx, buf)) == ICP_NO_MATCH) || (mi_check_rowid_filter_is_active(info) && - !mi_check_rowid_filter(info))) + (res= mi_check_rowid_filter(info)) == ICP_NO_MATCH)) { uint not_used[2]; /* diff --git a/storage/myisam/mi_rnext.c b/storage/myisam/mi_rnext.c index 9f5f4702ed2..69ecc0f021f 100644 --- a/storage/myisam/mi_rnext.c +++ b/storage/myisam/mi_rnext.c @@ -104,7 +104,7 @@ int mi_rnext(MI_INFO *info, uchar *buf, int inx) (info->index_cond_func && (icp_res= mi_check_index_cond(info, inx, buf)) == ICP_NO_MATCH) || (mi_check_rowid_filter_is_active(info) && - !mi_check_rowid_filter(info))) + (icp_res= mi_check_rowid_filter(info)) == ICP_NO_MATCH)) { /* If we are at the last key on the key page, allow writers to diff --git a/storage/myisam/mi_rnext_same.c b/storage/myisam/mi_rnext_same.c index ee6b962c8c3..ededafaecac 100644 --- a/storage/myisam/mi_rnext_same.c +++ b/storage/myisam/mi_rnext_same.c @@ -97,7 +97,7 @@ int mi_rnext_same(MI_INFO *info, uchar *buf) (!info->index_cond_func || (icp_res= mi_check_index_cond(info, inx, buf)) != ICP_NO_MATCH) && (!mi_check_rowid_filter_is_active(info) || - mi_check_rowid_filter(info))) + (icp_res= mi_check_rowid_filter(info)) != ICP_NO_MATCH)) break; } } diff --git a/storage/myisam/mi_rprev.c b/storage/myisam/mi_rprev.c index cac0d672765..edac9e3f74d 100644 --- a/storage/myisam/mi_rprev.c +++ b/storage/myisam/mi_rprev.c @@ -61,7 +61,7 @@ int mi_rprev(MI_INFO *info, uchar *buf, int inx) (info->index_cond_func && (icp_res= mi_check_index_cond(info, inx, buf)) == ICP_NO_MATCH) || (mi_check_rowid_filter_is_active(info) && - !mi_check_rowid_filter(info))) + (icp_res= mi_check_rowid_filter(info)) == ICP_NO_MATCH)) { /* If we are at the last (i.e. first?) key on the key page, diff --git a/storage/myisam/myisamdef.h b/storage/myisam/myisamdef.h index f7b61ae638c..ca00df0a274 100644 --- a/storage/myisam/myisamdef.h +++ b/storage/myisam/myisamdef.h @@ -307,7 +307,7 @@ struct st_myisam_info index_cond_func_t index_cond_func; /* Index condition function */ void *index_cond_func_arg; /* parameter for the func */ rowid_filter_func_t rowid_filter_func; /* rowid filter check function */ - rowid_filter_func_t rowid_filter_is_active_func; /* is activefunction */ + rowid_filter_is_active_func_t rowid_filter_is_active_func; /* is activefunction */ void *rowid_filter_func_arg; /* parameter for the func */ THR_LOCK_DATA lock; uchar *rtree_recursion_state; /* For RTREE */ @@ -726,7 +726,7 @@ int mi_munmap_file(MI_INFO *info); void mi_remap_file(MI_INFO *info, my_off_t size); ICP_RESULT mi_check_index_cond(MI_INFO *info, uint keynr, uchar *record); -int mi_check_rowid_filter(MI_INFO *info); +ICP_RESULT mi_check_rowid_filter(MI_INFO *info); int mi_check_rowid_filter_is_active(MI_INFO *info); /* Functions needed by mi_check */ int killed_ptr(HA_CHECK *param); @@ -738,7 +738,7 @@ extern void mi_set_index_cond_func(MI_INFO *info, index_cond_func_t check_func, void *func_arg); extern void mi_set_rowid_filter_func(MI_INFO *info, rowid_filter_func_t check_func, - rowid_filter_func_t is_active_func, + rowid_filter_is_active_func_t is_active_func, void *func_arg); int flush_blocks(HA_CHECK *param, KEY_CACHE *key_cache, File file, ulonglong *dirty_part_map);

1 0

[Commits] f92ea8de8df: MDEV-21791: Packed Sort Keys: provide details in ANALYZE FORMAT=JSON.
by psergey 27 Feb '20

27 Feb '20

revision-id: f92ea8de8df67515540980a182bda52d35a6946a (mariadb-10.5.0-239-gf92ea8de8df) parent(s): 5eda357ddfa23af6f0d9eb4327e907f59b501fb8 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-02-27 17:42:46 +0300 message: MDEV-21791: Packed Sort Keys: provide details in ANALYZE FORMAT=JSON. It's useful to know whether packed addon fields or sort-keys are used by the query. Unfortunately it's hard show such information in EXPLAIN[FORMAT=JSON] (and even if it was possible, it's not clear whether this level of detail would be appropriate there). Provide this information in ANALYZE FORMAT=JSON output. --- mysql-test/main/analyze_format_json.result | 1 + sql/filesort.cc | 2 ++ sql/sql_analyze_stmt.cc | 5 +++++ sql/sql_analyze_stmt.h | 14 ++++++++++++-- 4 files changed, 20 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/analyze_format_json.result b/mysql-test/main/analyze_format_json.result index ccef3a63592..ec3e21505dd 100644 --- a/mysql-test/main/analyze_format_json.result +++ b/mysql-test/main/analyze_format_json.result @@ -701,6 +701,7 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "r_buffer_size": "REPLACED", + "r_packed_keys": true, "temporary_table": { "filesort": { "sort_key": "(subquery#2)", diff --git a/sql/filesort.cc b/sql/filesort.cc index 33f595c02fb..9e15689f892 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -278,6 +278,8 @@ SORT_INFO *filesort(THD *thd, TABLE *table, Filesort *filesort, param.try_to_pack_sortkeys(); param.try_to_pack_addons(thd->variables.max_length_for_sort_data); + tracker->report_data_format(param.using_packed_sortkeys(), + param.using_packed_addons()); param.using_pq= false; if ((multi_byte_charset || param.using_packed_sortkeys()) && diff --git a/sql/sql_analyze_stmt.cc b/sql/sql_analyze_stmt.cc index fdabcf1e494..6c07c8247e3 100644 --- a/sql/sql_analyze_stmt.cc +++ b/sql/sql_analyze_stmt.cc @@ -78,6 +78,11 @@ void Filesort_tracker::print_json_members(Json_writer *writer) else writer->add_size(sort_buffer_size); } + + if (r_packed_keys) + writer->add_member("r_packed_keys").add_bool(true); + if (r_packed_addon_fields) + writer->add_member("r_packed_addon_fields").add_bool(true); } void attach_gap_time_tracker(THD *thd, Gap_time_tracker *gap_tracker, diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h index 9d5151c3be2..d6589355874 100644 --- a/sql/sql_analyze_stmt.h +++ b/sql/sql_analyze_stmt.h @@ -221,7 +221,8 @@ class Filesort_tracker : public Sql_alloc time_tracker(do_timing), r_limit(0), r_used_pq(0), r_examined_rows(0), r_sorted_rows(0), r_output_rows(0), sort_passes(0), - sort_buffer_size(0) + sort_buffer_size(0), + r_packed_keys(false), r_packed_addon_fields(false) {} /* Functions that filesort uses to report various things about its execution */ @@ -263,7 +264,13 @@ class Filesort_tracker : public Sql_alloc else sort_buffer_size= bufsize; } - + + inline void report_data_format(bool keys_packed, bool addons_packed) + { + r_packed_keys= keys_packed; + r_packed_addon_fields= addons_packed; + } + /* Functions to get the statistics */ void print_json_members(Json_writer *writer); @@ -322,6 +329,9 @@ class Filesort_tracker : public Sql_alloc other - value */ ulonglong sort_buffer_size; + + bool r_packed_keys; + bool r_packed_addon_fields; };

1 0

[Commits] a662cb9b432: Better comments
by psergey 25 Feb '20

25 Feb '20

revision-id: a662cb9b4329abc6f3895fed97c80434e8f78b20 (mariadb-10.3.21-50-ga662cb9b432) parent(s): cfa0506f8a7bc9855cbc8869c9cfbb4e9faf21eb author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-02-25 14:55:15 +0300 message: Better comments --- sql/ha_partition.h | 31 ++++++++++++++++--------------- 1 file changed, 16 insertions(+), 15 deletions(-) diff --git a/sql/ha_partition.h b/sql/ha_partition.h index fc6c8c4d570..6bc067af3e7 100644 --- a/sql/ha_partition.h +++ b/sql/ha_partition.h @@ -224,7 +224,7 @@ typedef struct st_partition_key_multi_range /* - List of ranges to be scanned in a certain [sub]partition. + List of ranges to be scanned in a certain [sub]partition The idea is that there's a list of ranges to be scanned in the table (formed by PARTITION_KEY_MULTI_RANGE structures), @@ -260,10 +260,10 @@ typedef struct st_partition_part_key_multi_range_hld /* Owner object */ ha_partition *partition; - // id of the the partition this structure is for + /* id of the the partition this structure is for */ uint32 part_id; - // Current range we're iterating through. + /* Current range we're iterating through */ PARTITION_PART_KEY_MULTI_RANGE *partition_part_key_multi_range; } PARTITION_PART_KEY_MULTI_RANGE_HLD; @@ -867,28 +867,31 @@ class ha_partition :public handler uint m_mrr_new_full_buffer_size; MY_BITMAP m_mrr_used_partitions; uint *m_stock_range_seq; - // not used: uint m_current_range_seq; + /* not used: uint m_current_range_seq; */ - // Value of mrr_mode passed to ha_partition::multi_range_read_init + /* Value of mrr_mode passed to ha_partition::multi_range_read_init */ uint m_mrr_mode; - // Value of n_ranges passed to ha_partition::multi_range_read_init + /* Value of n_ranges passed to ha_partition::multi_range_read_init */ uint m_mrr_n_ranges; /* Ordered MRR mode: m_range_info[N] has the range_id of the last record that - we've got from partition N. + we've got from partition N */ range_id_t *m_range_info; - // TRUE <=> This ha_partition::multi_range_read_next() call is the first one + /* + TRUE <=> This ha_partition::multi_range_read_next() call is the first one + */ bool m_multi_range_read_first; - // not used: uint m_mrr_range_init_flags; + + /* not used: uint m_mrr_range_init_flags; */ /* Number of elements in the list pointed by m_mrr_range_first. Not used */ uint m_mrr_range_length; - // Linked list of ranges to scan + /* Linked list of ranges to scan */ PARTITION_KEY_MULTI_RANGE *m_mrr_range_first; PARTITION_KEY_MULTI_RANGE *m_mrr_range_current; @@ -897,21 +900,19 @@ class ha_partition :public handler */ uint *m_part_mrr_range_length; - /* - For each partition: List of ranges to scan in this partition. - */ + /* For each partition: List of ranges to scan in this partition */ PARTITION_PART_KEY_MULTI_RANGE **m_part_mrr_range_first; PARTITION_PART_KEY_MULTI_RANGE **m_part_mrr_range_current; PARTITION_PART_KEY_MULTI_RANGE_HLD *m_partition_part_key_multi_range_hld; /* - Sequence of ranges to be scanned (TODO: why not stores this in + Sequence of ranges to be scanned (TODO: why not store this in handler::mrr_{iter,funcs}?) */ range_seq_t m_seq; RANGE_SEQ_IF *m_seq_if; - // Range iterator structure to be supplied to partitions + /* Range iterator structure to be supplied to partitions */ RANGE_SEQ_IF m_part_seq_if; virtual int multi_range_key_create_key(

1 0

[Commits] 8895069: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and
by IgorBabaev 25 Feb '20

25 Feb '20

revision-id: 88950694199277e232852d133506f6342f6efe61 (mariadb-10.3.18-173-g8895069) parent(s): affe7fabc7baa36083e7632eb6c3611578d74b48 author: Igor Babaev committer: Igor Babaev timestamp: 2020-02-25 00:47:03 -0800 message: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and join_cache_level=6+ The patch fixes two similar bugs in the commit 8eeb689e9fc57afe19a8dbff354b5f9f167867a9 that added multi_range_read support to partitions. The commit opened a possibility to join a partition table using BKA+MRR. However in some cases it could lead to wrong results or even crashes. This could happened when - index condition pushdown was used to join the table or - the joined table was an inner table of an outer join and 'not exist' optimization was applied or - the join table was the inner table of a semi-join and the first match optimization was applied The bugs were in the code of the call-back functions - partition_multi_range_key_skip_record() and - partition_multi_range_key_skip_index_tuple(). Each of this function consist only of an invocation of another function. Yet a wrong parameter was passed at this invocation. The fix was suggested by Sergey Petrunia and it is apparently in line with original design. The corresponding comprehensive test cases demonstrating the problems caused by the bugs were constructed by me. --- mysql-test/include/partition_mrr.inc | 92 ++++++++++++++++ mysql-test/main/partition_mrr_aria.result | 157 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_innodb.result | 157 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_myisam.result | 164 +++++++++++++++++++++++++++- sql/ha_partition.cc | 6 +- 5 files changed, 569 insertions(+), 7 deletions(-) diff --git a/mysql-test/include/partition_mrr.inc b/mysql-test/include/partition_mrr.inc index 4c28579..f372ccc 100644 --- a/mysql-test/include/partition_mrr.inc +++ b/mysql-test/include/partition_mrr.inc @@ -36,11 +36,103 @@ insert into t3 select 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; + set optimizer_switch='mrr=on'; --replace_column 9 # explain select * from t3 force index (key_col) where key_col < 3; select * from t3 force index (key_col) where key_col < 3; +set optimizer_switch=@save_optimizer_switch; + drop table t1,t3; +--echo # +--echo # MDEV-21544: partitioned table is joined with BKA+MRR +--echo # + +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; + +create table t0 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); + +create table t1 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3)); +insert into t1 select * from t0; + +# tables t0 and t1 contain the same set of records. + +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; + +analyze table t0,t1,t2; + +set join_cache_level=6; +set optimizer_switch='mrr=on'; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +eval explain extended $q2; +eval $q2; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +eval explain extended $q2; +eval $q2; + +insert into t2 values +(3), (4), (5); +analyze table t2; + +let $q1= +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +eval explain extended $q2; +eval $q2; + +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index 7a0c35a..c2be6ee 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,160 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index c188f7e..ecef678 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,160 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index d989536..ff23982 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,8 +77,164 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; # +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = 4 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = 4 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; +tp a b c a +2 3 40 yxx 3 +2 4 40 xx 4 +2 3 40 yyx 3 +2 4 40 xxx 4 +2 3 40 xzzzz 3 +2 4 40 yyyxx 4 +2 3 40 xxx 3 +2 4 40 xx 4 +2 3 40 yyxzx 3 +2 4 40 xyx 4 +2 3 40 xxxzz 3 +2 4 40 xxz 4 +2 3 40 zzxxx 3 +2 4 40 zxx 4 +2 3 40 xyyxx 3 +2 4 40 xzzzx 4 +2 3 40 yzxxx 3 +2 4 40 xxzy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; +# # MDEV-21628: Index condition pushdown for a simple condition over # index fields is not used for ref access of partitioned tables when employing BKA # @@ -115,11 +272,8 @@ insert into t2 select a+10 from t2; insert into t2 select a+20 from t2; analyze table t0,t1,t2; Table Op Msg_type Msg_text -test.t0 analyze status Engine-independent statistics collected test.t0 analyze status OK -test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; set join_cache_level=6, optimizer_switch='mrr=on'; @@ -127,12 +281,12 @@ explain select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan # This will use "Using index condition(BKA)" explain select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan set join_cache_level=@tmp1, optimizer_switch=@tmp2; drop table t0,t1,t2; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 130c7f7..48610ca 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -6264,9 +6264,10 @@ static bool partition_multi_range_key_skip_record(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_record"); DBUG_RETURN(hld->partition->m_seq_if->skip_record(hld->partition->m_seq, - range_info, rowid)); + pkmr->ptr, rowid)); } @@ -6275,9 +6276,10 @@ static bool partition_multi_range_key_skip_index_tuple(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_index_tuple"); DBUG_RETURN(hld->partition->m_seq_if->skip_index_tuple(hld->partition->m_seq, - range_info)); + pkmr->ptr)); } ha_rows ha_partition::multi_range_read_info_const(uint keyno,

1 0

[Commits] 6979383: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and
by IgorBabaev 25 Feb '20

25 Feb '20

revision-id: 6979383df66829352c4b67928913979b450db9fc (mariadb-10.3.18-173-g6979383) parent(s): affe7fabc7baa36083e7632eb6c3611578d74b48 author: Igor Babaev committer: Igor Babaev timestamp: 2020-02-24 23:57:07 -0800 message: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and join_cache_level=6+ The patch fixes two similar bugs in the commit 8eeb689e9fc57afe19a8dbff354b5f9f167867a9 that added multi_range_read support to partitions. The commit opened a possibility to join a partition table using BKA+MRR. However in some cases it could lead to wrong results or even crashes. This could happened when - index condition pushdown was used to join the table or - the joined table was an inner table of an outer join and 'not exist' optimization was applied or - the join table was the inner table of a semi-join and the first match optimization was applied The bugs were in the code of the call-back functions - partition_multi_range_key_skip_record() and - partition_multi_range_key_skip_index_tuple(). Each of this function consist only of an invocation of another function. Yet a wrong parameter was passed at this invocation. The fix was suggested by Sergey Petrunia and it is apparently in line with original design. The corresponding comprehensive test cases demonstrating the problems caused by the bugs were constructed by me. --- mysql-test/include/partition_mrr.inc | 82 ++++++++++++++++++++ mysql-test/main/partition_mrr_aria.result | 105 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_innodb.result | 105 ++++++++++++++++++++++++++ mysql-test/main/partition_mrr_myisam.result | 112 ++++++++++++++++++++++++++-- sql/ha_partition.cc | 6 +- 5 files changed, 403 insertions(+), 7 deletions(-) diff --git a/mysql-test/include/partition_mrr.inc b/mysql-test/include/partition_mrr.inc index 4c28579..3aa8441 100644 --- a/mysql-test/include/partition_mrr.inc +++ b/mysql-test/include/partition_mrr.inc @@ -36,11 +36,93 @@ insert into t3 select 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; + set optimizer_switch='mrr=on'; --replace_column 9 # explain select * from t3 force index (key_col) where key_col < 3; select * from t3 force index (key_col) where key_col < 3; +set optimizer_switch=@save_optimizer_switch; + drop table t1,t3; +--echo # +--echo # MDEV-21544: partitioned table is joined with BKA+MRR +--echo # + +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; + +create table t0 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); + +create table t1 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3)); +insert into t1 select * from t0; + +# tables t0 and t1 contain the same set of records. + +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; + +analyze table t0,t1,t2; + +set join_cache_level=6; +set optimizer_switch='mrr=on'; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +eval explain extended $q2; +eval $q2; + +insert into t2 values +(3), (4), (5); +analyze table t2; + +let $q1= +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +eval explain extended $q2; +eval $q2; + +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index 7a0c35a..73e1826 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,108 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index c188f7e..7b70de8 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,108 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index d989536..d549a42 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,8 +77,112 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; # +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; +# # MDEV-21628: Index condition pushdown for a simple condition over # index fields is not used for ref access of partitioned tables when employing BKA # @@ -115,11 +220,8 @@ insert into t2 select a+10 from t2; insert into t2 select a+20 from t2; analyze table t0,t1,t2; Table Op Msg_type Msg_text -test.t0 analyze status Engine-independent statistics collected test.t0 analyze status OK -test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK set @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch; set join_cache_level=6, optimizer_switch='mrr=on'; @@ -127,12 +229,12 @@ explain select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t0 ref idx idx 5 test.t2.a 12 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 Using index condition; Using join buffer (flat, BKA join); Rowid-ordered scan # This will use "Using index condition(BKA)" explain select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = 4; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range idx idx 5 NULL 2 Using where; Using index -1 SIMPLE t1 ref idx idx 5 test.t2.a 12 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan set join_cache_level=@tmp1, optimizer_switch=@tmp2; drop table t0,t1,t2; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 130c7f7..48610ca 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -6264,9 +6264,10 @@ static bool partition_multi_range_key_skip_record(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_record"); DBUG_RETURN(hld->partition->m_seq_if->skip_record(hld->partition->m_seq, - range_info, rowid)); + pkmr->ptr, rowid)); } @@ -6275,9 +6276,10 @@ static bool partition_multi_range_key_skip_index_tuple(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_index_tuple"); DBUG_RETURN(hld->partition->m_seq_if->skip_index_tuple(hld->partition->m_seq, - range_info)); + pkmr->ptr)); } ha_rows ha_partition::multi_range_read_info_const(uint keyno,

1 0

[Commits] 5b86c73: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and
by IgorBabaev 25 Feb '20

25 Feb '20

revision-id: 5b86c737499abbaf1ba5e625dd8b226336db7a10 (mariadb-10.3.18-172-g5b86c73) parent(s): 85d4a45d15078512562d84273f489e8d225c1da7 author: Igor Babaev committer: Igor Babaev timestamp: 2020-02-24 21:52:04 -0800 message: MDEV-21554 Crash in JOIN_CACHE_BKAH::skip_index_tuple when mrr=on and join_cache_level=6+ The patch fixes two similar bugs in the commit 8eeb689e9fc57afe19a8dbff354b5f9f167867a9 that added multi_range_read support to partitions. The commit opened a possibility to join a partition table using BKA+MRR. However in some cases it could lead to wrong results or even crashes. This could happened when - index condition pushdown was used to join the table or - the joined table was an inner table of an outer join and 'not exist' optimization was applied or - the join table was the inner table of a semi-join and the first match optimization was applied The bugs were in the code of the call-back functions - partition_multi_range_key_skip_record() and - partition_multi_range_key_skip_index_tuple(). Each of this function consist only of an invocation of another function. Yet a wrong parameter was passed at this invocation. The fix was suggested by Sergey Petrunia and it is apparently in line with original design. The corresponding comprehensive test cases demonstrating the problems caused by the bugs were constructed by me. --- mysql-test/include/partition_mrr.inc | 82 ++++++++++++++++++++++ mysql-test/main/partition_mrr_aria.result | 105 ++++++++++++++++++++++++++++ mysql-test/main/partition_mrr_innodb.result | 105 ++++++++++++++++++++++++++++ mysql-test/main/partition_mrr_myisam.result | 105 ++++++++++++++++++++++++++++ sql/ha_partition.cc | 6 +- 5 files changed, 401 insertions(+), 2 deletions(-) diff --git a/mysql-test/include/partition_mrr.inc b/mysql-test/include/partition_mrr.inc index 4c28579..3aa8441 100644 --- a/mysql-test/include/partition_mrr.inc +++ b/mysql-test/include/partition_mrr.inc @@ -36,11 +36,93 @@ insert into t3 select 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; + set optimizer_switch='mrr=on'; --replace_column 9 # explain select * from t3 force index (key_col) where key_col < 3; select * from t3 force index (key_col) where key_col < 3; +set optimizer_switch=@save_optimizer_switch; + drop table t1,t3; +--echo # +--echo # MDEV-21544: partitioned table is joined with BKA+MRR +--echo # + +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; + +create table t0 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); + +create table t1 ( + tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), + partition p2 values in (2), + partition p3 values in (3)); +insert into t1 select * from t0; + +# tables t0 and t1 contain the same set of records. + +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; + +analyze table t0,t1,t2; + +set join_cache_level=6; +set optimizer_switch='mrr=on'; + +let $q1= +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +eval explain extended $q2; +eval $q2; + +insert into t2 values +(3), (4), (5); +analyze table t2; + +let $q1= +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +eval explain extended $q1; +eval $q1; + +let $q2= +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +eval explain extended $q2; +eval $q2; + +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; + +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_aria.result b/mysql-test/main/partition_mrr_aria.result index 7a0c35a..73e1826 100644 --- a/mysql-test/main/partition_mrr_aria.result +++ b/mysql-test/main/partition_mrr_aria.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,108 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_innodb.result b/mysql-test/main/partition_mrr_innodb.result index c188f7e..7b70de8 100644 --- a/mysql-test/main/partition_mrr_innodb.result +++ b/mysql-test/main/partition_mrr_innodb.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,108 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result index 1f1cea8..5b485e4 100644 --- a/mysql-test/main/partition_mrr_myisam.result +++ b/mysql-test/main/partition_mrr_myisam.result @@ -39,6 +39,7 @@ A.a, B.a, 123456 from t1 A, t1 B; +set @save_optimizer_switch=@@optimizer_switch; set optimizer_switch='mrr=on'; explain select * from t3 force index (key_col) where key_col < 3; @@ -76,4 +77,108 @@ ID part_id key_col col2 27 7 2 123456 28 8 2 123456 29 9 2 123456 +set optimizer_switch=@save_optimizer_switch; drop table t1,t3; +# +# MDEV-21544: partitioned table is joined with BKA+MRR +# +set @save_join_cache_level=@@join_cache_level; +set @save_optimizer_switch=@@optimizer_switch; +create table t0 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +); +insert into t0 values +(1,3,30,'yyzy'), (1,3,30,'yxxyy'), (1,3,30,'yyxy'), (1,3,30,'xxyy'), +(1,3,30,'yyxz'), (1,3,30,'yyzz'), (1,3,30,'xxyzy'), (1,3,30,'yyyy'), +(1,3,30,'yzzy'), (1,93,30,'zzzy'), +(2,3,40,'yxx'), (2,4,40,'xx'), (2,3,10,'zxz'), +(2,3,40,'yyx'), (2,4,40,'xxx'), (2,3,10,'zyyz'), +(2,3,40,'xzzzz'), (2,4,40,'yyyxx'), (2,3,10,'zyz'), +(2,3,40,'xxx'), (2,4,40,'xx'), (2,3,10,'zzz'), +(2,3,40,'yyxzx'), (2,4,40,'xyx'), (2,3,10,'xzz'), +(2,3,40,'xxxzz'), (2,4,40,'xxz'), (2,3,10,'zzzy'), +(2,3,40,'zzxxx'), (2,4,40,'zxx'), (2,3,10,'yzzz'), +(2,3,40,'xyyxx'), (2,4,40,'xzzzx'), (2,3,10,'zzxxz'), +(2,3,40,'yzxxx'), (2,4,40,'xxzy'), (2,3,10,'zzzyx'), +(2,93,40,'xzx'), (2,94,40,'xz'), (2,93,10,'zyyyz'), +(3,4,30,'yx'), (3,4,30,'yyxxx'), (3,4,30,'zzyy'), (3,4,30,'zxyy'), +(3,4,30,'xxyy'), (3,4,30,'yyzx'), (3,4,30,'zyyy'), (3,4,30,'yzy'), +(3,4,30,'zzzyy'), (3,94,30,'yyz'); +create table t1 ( +tp int, a int, b int not null, c varchar(12), index idx (a,b) +) +partition by list (tp) +( partition p1 values in (1), +partition p2 values in (2), +partition p3 values in (3)); +insert into t1 select * from t0; +create table t2 (a int, index idx(a)); +insert into t2 values +(1), (2), (3), (4), (5); +insert into t2 select a+10 from t2; +insert into t2 select a+20 from t2; +analyze table t0,t1,t2; +Table Op Msg_type Msg_text +test.t0 analyze status OK +test.t1 analyze status OK +test.t2 analyze status OK +set join_cache_level=6; +set optimizer_switch='mrr=on'; +explain extended select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t0` join `test`.`t2` where `test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +explain extended select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 8 100.00 Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` / 10 = `test`.`t2`.`a` - 1 +select * from t1,t2 where t2.a in (3,4) and t1.a=t2.a and (t1.b / 10) = t2.a-1; +tp a b c a +3 4 30 yx 4 +3 4 30 yyxxx 4 +3 4 30 zzyy 4 +3 4 30 zxyy 4 +3 4 30 xxyy 4 +3 4 30 yyzx 4 +3 4 30 zyyy 4 +3 4 30 yzy 4 +3 4 30 zzzyy 4 +insert into t2 values +(3), (4), (5); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain extended select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t0 ref idx idx 5 test.t2.a 13 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t0`.`tp` AS `tp`,`test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t0`.`c` AS `c` from `test`.`t2` left join `test`.`t0` on(`test`.`t0`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t0`.`b` is null +select * from t2 left join t0 on t2.a=t0.a where t2.a in (3,4) and t0.b is null; +a tp a b c +explain extended select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 range idx idx 5 NULL 4 100.00 Using where; Using index +1 SIMPLE t1 ref idx idx 5 test.t2.a 5 100.00 Using where; Not exists; Using join buffer (flat, BKA join); Rowid-ordered scan +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t1`.`tp` AS `tp`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t2`.`a` is not null) where `test`.`t2`.`a` in (3,4) and `test`.`t1`.`b` is null +select * from t2 left join t1 on t2.a=t1.a where t2.a in (3,4) and t1.b is null; +a tp a b c +set join_cache_level=@save_join_cache_level; +set optimizer_switch=@save_optimizer_switch; +drop table t0,t1,t2; diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 130c7f7..48610ca 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -6264,9 +6264,10 @@ static bool partition_multi_range_key_skip_record(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_record"); DBUG_RETURN(hld->partition->m_seq_if->skip_record(hld->partition->m_seq, - range_info, rowid)); + pkmr->ptr, rowid)); } @@ -6275,9 +6276,10 @@ static bool partition_multi_range_key_skip_index_tuple(range_seq_t seq, { PARTITION_PART_KEY_MULTI_RANGE_HLD *hld= (PARTITION_PART_KEY_MULTI_RANGE_HLD *)seq; + PARTITION_KEY_MULTI_RANGE *pkmr= (PARTITION_KEY_MULTI_RANGE *)range_info; DBUG_ENTER("partition_multi_range_key_skip_index_tuple"); DBUG_RETURN(hld->partition->m_seq_if->skip_index_tuple(hld->partition->m_seq, - range_info)); + pkmr->ptr)); } ha_rows ha_partition::multi_range_read_info_const(uint keyno,

1 0