matrixorigin / matrixorigin/matrixone
[Bug]: Prepared UPDATE on partitioned table with secondary indexes panics when key parameters are strings
- Dominant language
- Go
- Stars
- 1.9k
- Forks
- 311
- Avg merge
- 1d 3h
- Merged PRs (30d)
- 768
Description
### Environment
- Branch: latest `main`
- MO git version: `51656deb6`
- MySQL oracle: `8.0.45`
### Reproduce
```sql
drop database if exists mysql_compat_model14_prepare_partition_idx_min;
create database mysql_compat_model14_prepare_partition_idx_min;
use mysql_compat_model14_prepare_partition_idx_min;
create table orders_p (
tenant int not null,
order_id int not null,
customer_id int not null,
status varchar(12),
amount decimal(10,2),
created_at date,
primary key (tenant, order_id),
key idx_t_status_amount(tenant, status, amount),
key idx_customer(customer_id)
)
partition by hash(tenant) partitions 4;
insert into orders_p values
(2,202,2002,'hold',40.00,'2024-02-02'),
(3,301,3001,'paid',50.00,'2024-03-01');
prepare s_upd from
'update orders_p set amount = amount + ?, status = ? where tenant = ? and order_id = ?';
set @delta=5.25,@new_status='paid',@t=2,@oid=202;
execute s_upd using @delta,@new_status,@t,@oid;
set @delta='1.00',@new_status='closed',@t='3',@oid='301';
execute s_upd using @delta,@new_status,@t,@oid;
select tenant,order_id,status,amount from orders_p order by tenant,order_id;
```
### MySQL result
```text
tenant order_id status amount
2 202 paid 45.25
3 301 closed 51.00
```
### MO result
The first numeric-parameter execution succeeds. The second execution, using string variables for the partition/key predicates, fails:
```text
ERROR 20101 (HY000): internal error: panic BUG:
github.com/matrixorigin/matrixone/pkg/sql/colexec/multi_update.(*PartitionMultiUpdate).getPartitionIndex
pkg/sql/colexec/multi_update/multi_update_partition.go:387
github.com/matrixorigin/matrixone/pkg/sql/colexec/multi_update.(*PartitionMultiUpdate).writeTable.func1
pkg/sql/colexec/multi_update/multi_update_partition.go:192
github.com/matrixorigin/matrixone/pkg/partitionservice.PruneResult
```
The row is not updated:
```text
tenant order_id status amount
2 202 paid 45.25
3 301 paid 50.00
```
### Notes
The same prepared update on a partitioned table **without** secondary indexes did not reproduce the panic. The secondary indexes appear to be part of the trigger condition.
### Expected
Prepared `UPDATE` should update the matching partitioned row and maintain secondary indexes, even when parameter values are supplied as string user variables that MySQL coerces to the target numeric key type.
Contributor guide
Assessment
This issue has not been assessed yet.