Bug #109830 Update the table with sub-partition, and the result is wrong

• Submitted: 29 Jan 2023 10:07
• Modified: 30 Jan 2023 7:40
• Reporter: hao chen
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: mysql 8.0.29
• OS: Any
• CPU Architecture: Any
• Tags: update partiton table

[29 Jan 2023 10:07] hao chen

Description:
Execute the following sql in sequence, and the result of the last sql is unexpected。

create table t4(
	range_key int,
	hash_key int
) partition by range(range_key)
  subpartition by hash(hash_key)
(
	partition p1 values less than(100)
	(subpartition h1,  subpartition h2),
	partition p2 values less than(200)
	(subpartition h11, subpartition h22)
);
insert into t4 values (99, 1);
select * from t4;
update t4 set range_key=199 and hash_key = 6 where range_key=99;
select * from t4;

How to repeat:
mysql> create database aa;
Query OK, 1 row affected (0.01 sec)

mysql> use aa;
Database changed
mysql> create table t4(
    -> range_key int,
    -> hash_key int
    -> ) partition by range(range_key)
    ->   subpartition by hash(hash_key)
    -> (
    -> partition p1 values less than(100)
    -> (subpartition h1,  subpartition h2),
    -> partition p2 values less than(200)
    -> (subpartition h11, subpartition h22)
    -> );
Query OK, 0 rows affected (0.39 sec)

mysql> insert into t4 values (99, 1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t4;
+-----------+----------+
| range_key | hash_key |
+-----------+----------+
|        99 |        1 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> update t4 set range_key=199 and hash_key = 6 where range_key=99;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t4;
+-----------+----------+
| range_key | hash_key |
+-----------+----------+
|         0 |        1 |
+-----------+----------+
1 row in set (0.00 sec)

mysql>

Suggested fix:

The final result of executing sql should be: 【199, 6】, which is not the expected result at present。

[30 Jan 2023 7:40] MySQL Verification Team

Hello hao chen,

Thank you for the bug report.
Imho this is not a bug. Your update query "update t4 set range_key=199 and hash_key = 6 where range_key=99" is actually interpreted and executed as "update table_name set range_key = (199 and hash_key=6) where range_key=99"

For example in your very UPDATE case:

mysql> select (199 and 1 = 6);
+-----------------+
| (199 and 1 = 6) |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> update t4 set range_key=0 where range_key=99;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t4;
+-----------+----------+
| range_key | hash_key |
+-----------+----------+
|         0 |        1 |
+-----------+----------+
1 row in set (0.00 sec)

UPDATE syntax, please see https://dev.mysql.com/doc/refman/8.0/en/update.html

So, ideally your UPDATE should be:

mysql> drop table t4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t4(
    -> range_key int,
    -> hash_key int
    -> ) partition by range(range_key)
    ->   subpartition by hash(hash_key)
    -> (
    -> partition p1 values less than(100)
    -> (subpartition h1,  subpartition h2),
    -> partition p2 values less than(200)
    -> (subpartition h11, subpartition h22)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t4 values (99, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+-----------+----------+
| range_key | hash_key |
+-----------+----------+
|        99 |        1 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> update t4 set range_key=199, hash_key = 6 where range_key=99;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  select * from t4;
+-----------+----------+
| range_key | hash_key |
+-----------+----------+
|       199 |        6 |
+-----------+----------+
1 row in set (0.00 sec)

Also, please see Bug #82647 , Bug #7068 , Bug #19398 , Bug #26405

regards,
Umesh