[Commits] 2536ec0: Drop sbtest database on start; pretty-print perf_context counters
by Sergei Petrunia 01 Mar '20
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
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
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
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
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
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
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
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
[Commits] e637355156c: MDEV-21610 Different query results from 10.4.11 to 10.4.12
by psergey 20 Feb '20
by psergey 20 Feb '20
20 Feb '20
revision-id: e637355156cb28388a291b0e3a5e9ee863b2854d (mariadb-10.4.11-86-ge637355156c)
parent(s): adcfea710ff0dcd8d223d41440225ebf6e64f796
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2020-02-20 13:35:19 +0300
message:
MDEV-21610 Different query results from 10.4.11 to 10.4.12
Part#2: cleanup:
In the part 1 of the fix, DS-MRR implementation would peek into
the JOIN_TAB to get the rowid filter from
table->reginfo.join_tab->rowid_filter
This doesn't look good from code isolation standpoint (why should a
storage engine assume it is used through a JOIN_TAB?).
Fixed this by storing the 'un-pushed' rowid_filter in the DsMrr_impl
structure. The filter survives across multi_range_read_init() calls.
It is discarded when somebody calls index_end() or rnd_end() and cleans
up the DsMrr_impl.
---
sql/multi_range_read.cc | 9 +++++++--
sql/multi_range_read.h | 11 +++++++++--
2 files changed, 16 insertions(+), 4 deletions(-)
diff --git a/sql/multi_range_read.cc b/sql/multi_range_read.cc
index 51c3dc7a205..c000187626f 100644
--- a/sql/multi_range_read.cc
+++ b/sql/multi_range_read.cc
@@ -1015,6 +1015,7 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs,
primary key. Use the rowid filter outside the engine code (see
Mrr_ordered_rndpos_reader::refill_from_index_reader).
*/
+ rowid_filter= h_arg->pushed_rowid_filter;
h_arg->cancel_pushed_rowid_filter();
}
}
@@ -1096,15 +1097,18 @@ int DsMrr_impl::dsmrr_init(handler *h_arg, RANGE_SEQ_IF *seq_funcs,
goto use_default_impl;
}
+ // setup_two_handlers() will call dsmrr_close() will clears the filter.
+ // Save its value and restore afterwards.
+ Rowid_filter *tmp = rowid_filter;
if ((res= setup_two_handlers()))
goto error;
+ rowid_filter= tmp;
if ((res= index_strategy->init(secondary_file, seq_funcs, seq_init_param,
n_ranges, mode, &keypar, key_buffer,
&buf_manager)) ||
(res= disk_strategy->init(primary_file, index_strategy, mode,
- &rowid_buffer,
- table->reginfo.join_tab->rowid_filter)))
+ &rowid_buffer, rowid_filter)))
{
goto error;
}
@@ -1286,6 +1290,7 @@ void DsMrr_impl::close_second_handler()
void DsMrr_impl::dsmrr_close()
{
DBUG_ENTER("DsMrr_impl::dsmrr_close");
+ rowid_filter= NULL;
close_second_handler();
strategy= NULL;
DBUG_VOID_RETURN;
diff --git a/sql/multi_range_read.h b/sql/multi_range_read.h
index 6be95371bb2..37a00e3086f 100644
--- a/sql/multi_range_read.h
+++ b/sql/multi_range_read.h
@@ -557,7 +557,8 @@ class DsMrr_impl
typedef void (handler::*range_check_toggle_func_t)(bool on);
DsMrr_impl()
- : secondary_file(NULL) {};
+ : secondary_file(NULL),
+ rowid_filter(NULL) {};
void init(handler *h_arg, TABLE *table_arg)
{
@@ -594,7 +595,13 @@ class DsMrr_impl
to run both index scan and rnd_pos() scan at the same time)
*/
handler *secondary_file;
-
+
+ /*
+ The rowid filter that DS-MRR has "unpushed" from the storage engine.
+ If it's present, DS-MRR will use it.
+ */
+ Rowid_filter *rowid_filter;
+
uint keyno; /* index we're running the scan on */
/* TRUE <=> need range association, buffers hold {rowid, range_id} pairs */
bool is_mrr_assoc;
1
0
[Commits] affe7fabc7b: MDEV-21628: Index condition pushdown condition ... not used with BKA
by psergey 19 Feb '20
by psergey 19 Feb '20
19 Feb '20
revision-id: affe7fabc7baa36083e7632eb6c3611578d74b48 (mariadb-10.3.21-46-gaffe7fabc7b)
parent(s): 85d4a45d15078512562d84273f489e8d225c1da7
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2020-02-19 21:29:49 +0300
message:
MDEV-21628: Index condition pushdown condition ... not used with BKA
(Backport to 10.3)
Partitioning storage now supports MRR but doesn't support Index Condition
Pushdown (aka ICP). This causes counter-intuitive query plans for queries
that use BKA and conditions that depend on index fields:
- If the condition refers to other tables, BKA's variant of ICP is used
to handle it.
- If the condition depends on this table only, the optimizer will try to
use regular ICP for it, which will fail because the storage engine
doesn't support ICP.
Make the optimizer be smarter in the second case: if we were not able to
use regular ICP, use BKA's variant of ICP..
---
mysql-test/main/partition_mrr_myisam.result | 59 +++++++++++++++++++++++++++++
mysql-test/main/partition_mrr_myisam.test | 59 +++++++++++++++++++++++++++++
sql/opt_index_cond_pushdown.cc | 15 ++++++++
3 files changed, 133 insertions(+)
diff --git a/mysql-test/main/partition_mrr_myisam.result b/mysql-test/main/partition_mrr_myisam.result
index 1f1cea8e9d6..d989536324c 100644
--- a/mysql-test/main/partition_mrr_myisam.result
+++ b/mysql-test/main/partition_mrr_myisam.result
@@ -77,3 +77,62 @@ ID part_id key_col col2
28 8 2 123456
29 9 2 123456
drop table t1,t3;
+#
+# MDEV-21628: Index condition pushdown for a simple condition over
+# index fields is not used for ref access of partitioned tables when employing BKA
+#
+create table t0 (
+tp int, a int, b int, 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, c varchar(12), index idx (a,b)
+) engine=myisam
+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)) engine=myisam;
+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 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';
+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
+# 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
+set join_cache_level=@tmp1, optimizer_switch=@tmp2;
+drop table t0,t1,t2;
diff --git a/mysql-test/main/partition_mrr_myisam.test b/mysql-test/main/partition_mrr_myisam.test
index d67a37ab3d2..eb30319d81f 100644
--- a/mysql-test/main/partition_mrr_myisam.test
+++ b/mysql-test/main/partition_mrr_myisam.test
@@ -1,3 +1,62 @@
let $engine_type= myisam;
--source include/partition_mrr.inc
+
+--echo #
+--echo # MDEV-21628: Index condition pushdown for a simple condition over
+--echo # index fields is not used for ref access of partitioned tables when employing BKA
+--echo #
+
+create table t0 (
+tp int, a int, b int, 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, c varchar(12), index idx (a,b)
+) engine=myisam
+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)) engine=myisam;
+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 @tmp1=@@join_cache_level, @tmp2=@@optimizer_switch;
+set join_cache_level=6, optimizer_switch='mrr=on';
+
+explain
+select * from t0,t2 where t2.a in (3,4) and t0.a=t2.a and (t0.b / 10) = 4;
+
+--echo # 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;
+
+set join_cache_level=@tmp1, optimizer_switch=@tmp2;
+
+drop table t0,t1,t2;
+
+
diff --git a/sql/opt_index_cond_pushdown.cc b/sql/opt_index_cond_pushdown.cc
index 8f3f9cdac72..af6a75cdef2 100644
--- a/sql/opt_index_cond_pushdown.cc
+++ b/sql/opt_index_cond_pushdown.cc
@@ -393,8 +393,23 @@ void push_index_cond(JOIN_TAB *tab, uint keyno)
~(tab->table->map | tab->join->const_table_map)))
tab->cache_idx_cond= idx_cond;
else
+ {
idx_remainder_cond= tab->table->file->idx_cond_push(keyno, idx_cond);
+ /*
+ If (1) there is an index condition that we couldn't push using ICP,
+ (2) we are using Join Buffering
+ (3) and we are using BKA
+ then use BKA's Index Condition Pushdown mechanism to check it.
+ */
+ if (idx_remainder_cond && tab->use_join_cache && // (1) && (2)
+ tab->icp_other_tables_ok) // (3)
+ {
+ tab->cache_idx_cond= idx_remainder_cond;
+ idx_remainder_cond= NULL;
+ }
+ }
+
/*
Disable eq_ref's "lookup cache" if we've pushed down an index
condition.
1
0