matrixorigin / matrixorigin/matrixone

[Bug]: Prepared UPDATE on partitioned table with secondary indexes panics when key parameters are strings

Open
#25,418 2 comments 0 reactions 1 assignee Claimed by @ck89119 View on GitHub
area/partition deferred kind/bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.