Description:
Update operation fails with a result indicating 0 rows affected, despite expected matches.
1. Affected Table: Partitioned table
2. Issue Context: Occurs during UPDATE or DELETE operations involving multiple tables.
3. Indexes: Present on multiple columns.
4. Optimizer Path: Chooses Intersect rows sorted by row ID path.
in debug build will abort
```
#6 0x00007f5d99639e96 in __GI___assert_fail (
assertion=0x560ba00c0fe0 "bitmap_is_set(key_info->table->read_set, key_info->key_part->field->field_index())", file=0x560ba00c0e98 "/db/sql/key.cc", line=593,
function=0x560ba00c0fb8 "int key_rec_cmp(KEY**, uchar*, uchar*)") at ./assert/assert.c:101
#7 0x0000560ba2e68754 in key_rec_cmp (key=0x7f5cd0ccdd18, first_rec=0x7f5cd0d06cac "@2 ",
second_rec=0x7f5cd0d07b0d "@\202 ") at /db/sql/key.cc:593
#8 0x0000560ba2fab17e in Key_rec_less::operator() (this=0x7f5cd0b0ece0,
first=0x7f5cd0d06caa "\016", second=0x7f5cd0d07b0b "\021")
at /db/sql/partitioning/partition_handler.h:364
```
How to repeat:
create table t15( id int , cc int ,m varchar(8) COLLATE utf8mb4_bin DEFAULT NULL, t text, PRIMARY key(id) ,key(cc), key(m) ) PARTITION BY RANGE COLUMNS(id) (PARTITION p202008 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p202009 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p202012 VALUES LESS THAN (100) ENGINE = InnoDB) ;
create table t2( id int PRIMARY key , cc int );
insert into t2 values ( 1 , 2);
insert into t2 values (0, 2 );
INSERT INTO `t15` VALUES (4,1,'123','abcdef'),
(5,1,'123','abcdef'),
(8,2,'122','abcdef'),
(9,2,'122','abcdef');
explain analyze update t15 a,t2 b set a.t ='test' where b.id <> a.id and a.cc = 1 and a.m = '123';
Suggested fix:
1. set key read
diff --git a/sql/range_optimizer/rowid_ordered_retrieval.cc b/sql/range_optimizer/rowid_ordered_retrieval.cc
index 11e1d773477d..64893fc2b5a8 100644
--- a/sql/range_optimizer/rowid_ordered_retrieval.cc
+++ b/sql/range_optimizer/rowid_ordered_retrieval.cc
@@ -139,7 +139,16 @@ end:
org_file = table()->file;
table()->file = file;
/* We don't have to set 'table()->keyread' here as the 'file' is unique */
- if (!table()->no_keyread) table()->mark_columns_used_by_index(index);
+ if (!table()->no_keyread) {
+ table()->mark_columns_used_by_index(index);
+ } else {
+ // only partition table need read
+ if (table()->part_info) {
+ bitmap_clear_all(table()->read_set);
+ table()->mark_columns_used_by_index_no_reset(index, table()->read_set);
+ }
+ }
+
table()->prepare_for_position();
table()->file = org_file;
bitmap_copy(&column_bitmap, table()->read_set);
2. Since the fragmented table needs to be re-sorted, through explain that there is a clear gap between the time consumption and the cost estimate.
In this case, this access method should not be used when the optimizer selects it.
diff --git a/sql/range_optimizer/rowid_ordered_retrieval_plan.cc b/sql/range_optimizer/rowid_ordered_retrieval_plan.cc
index 1de9abb79a06..5e25efa3650e 100644
--- a/sql/range_optimizer/rowid_ordered_retrieval_plan.cc
+++ b/sql/range_optimizer/rowid_ordered_retrieval_plan.cc
@@ -795,6 +795,12 @@ AccessPath *get_best_ror_intersect(
return nullptr;
}
+ if (table && table->no_keyread && table->part_info) {
+ trace_ror.add("usable", false);
+ trace_ror.add_alnum("cause", "partition_table_with_no_keyread");
+ return nullptr;
+ }
+
/*
Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of
Description: Update operation fails with a result indicating 0 rows affected, despite expected matches. 1. Affected Table: Partitioned table 2. Issue Context: Occurs during UPDATE or DELETE operations involving multiple tables. 3. Indexes: Present on multiple columns. 4. Optimizer Path: Chooses Intersect rows sorted by row ID path. in debug build will abort ``` #6 0x00007f5d99639e96 in __GI___assert_fail ( assertion=0x560ba00c0fe0 "bitmap_is_set(key_info->table->read_set, key_info->key_part->field->field_index())", file=0x560ba00c0e98 "/db/sql/key.cc", line=593, function=0x560ba00c0fb8 "int key_rec_cmp(KEY**, uchar*, uchar*)") at ./assert/assert.c:101 #7 0x0000560ba2e68754 in key_rec_cmp (key=0x7f5cd0ccdd18, first_rec=0x7f5cd0d06cac "@2 ", second_rec=0x7f5cd0d07b0d "@\202 ") at /db/sql/key.cc:593 #8 0x0000560ba2fab17e in Key_rec_less::operator() (this=0x7f5cd0b0ece0, first=0x7f5cd0d06caa "\016", second=0x7f5cd0d07b0b "\021") at /db/sql/partitioning/partition_handler.h:364 ``` How to repeat: create table t15( id int , cc int ,m varchar(8) COLLATE utf8mb4_bin DEFAULT NULL, t text, PRIMARY key(id) ,key(cc), key(m) ) PARTITION BY RANGE COLUMNS(id) (PARTITION p202008 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION p202009 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p202012 VALUES LESS THAN (100) ENGINE = InnoDB) ; create table t2( id int PRIMARY key , cc int ); insert into t2 values ( 1 , 2); insert into t2 values (0, 2 ); INSERT INTO `t15` VALUES (4,1,'123','abcdef'), (5,1,'123','abcdef'), (8,2,'122','abcdef'), (9,2,'122','abcdef'); explain analyze update t15 a,t2 b set a.t ='test' where b.id <> a.id and a.cc = 1 and a.m = '123'; Suggested fix: 1. set key read diff --git a/sql/range_optimizer/rowid_ordered_retrieval.cc b/sql/range_optimizer/rowid_ordered_retrieval.cc index 11e1d773477d..64893fc2b5a8 100644 --- a/sql/range_optimizer/rowid_ordered_retrieval.cc +++ b/sql/range_optimizer/rowid_ordered_retrieval.cc @@ -139,7 +139,16 @@ end: org_file = table()->file; table()->file = file; /* We don't have to set 'table()->keyread' here as the 'file' is unique */ - if (!table()->no_keyread) table()->mark_columns_used_by_index(index); + if (!table()->no_keyread) { + table()->mark_columns_used_by_index(index); + } else { + // only partition table need read + if (table()->part_info) { + bitmap_clear_all(table()->read_set); + table()->mark_columns_used_by_index_no_reset(index, table()->read_set); + } + } + table()->prepare_for_position(); table()->file = org_file; bitmap_copy(&column_bitmap, table()->read_set); 2. Since the fragmented table needs to be re-sorted, through explain that there is a clear gap between the time consumption and the cost estimate. In this case, this access method should not be used when the optimizer selects it. diff --git a/sql/range_optimizer/rowid_ordered_retrieval_plan.cc b/sql/range_optimizer/rowid_ordered_retrieval_plan.cc index 1de9abb79a06..5e25efa3650e 100644 --- a/sql/range_optimizer/rowid_ordered_retrieval_plan.cc +++ b/sql/range_optimizer/rowid_ordered_retrieval_plan.cc @@ -795,6 +795,12 @@ AccessPath *get_best_ror_intersect( return nullptr; } + if (table && table->no_keyread && table->part_info) { + trace_ror.add("usable", false); + trace_ror.add_alnum("cause", "partition_table_with_no_keyread"); + return nullptr; + } + /* Step1: Collect ROR-able SEL_ARGs and create ROR_SCAN_INFO for each of