MySQL Bugs: #109544: replace into a table with unique multi-valued index is confusing

Bug #109544	replace into a table with unique multi-valued index is confusing
Submitted:	6 Jan 2023 8:23	Modified:	10 Jan 2023 2:47
Reporter:	x j	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
replace into says it will remove the old row and insert a new row if the unique index is met. But for a unique multi-valued index, it confuses me in this case.

How to repeat:
mysql> create table t(a json, b int, unique index idx((cast(a as signed array))));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t values ('[10,20]', 1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t values ('[14,24]', 2);
Query OK, 1 row affected (0.00 sec)

mysql> replace into t values ('[10,2]', 10);
ERROR 1032 (HY000): Can't find record in 't'
mysql> replace into t values ('[2,10]', 10);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t;
+----------+------+
| a        | b    |
+----------+------+
| [2, 10]  |   10 |
| [14, 24] |    2 |
+----------+------+
2 rows in set (0.01 sec)

you can see that '[10, 2]' return an error but '[2,10]' is ok

If then replace with [1, 14], MySQL will report a duplicate entry error

mysql> replace into t values ('[1, 14]', 1);
ERROR 1062 (23000): Duplicate entry '[1, 14]' for key 't.idx'

But if there are two unique keys and one record duplicate with 2 rows:

mysql> create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(null,1,1,now());
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(null,0,0,null);
Query OK, 1 row affected (0.01 sec)

mysql> replace into t1 values(null,1,0,null);
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t1;
+---+------+------+------+
| a | b    | c    | d    |
+---+------+------+------+
| 3 |    1 |    0 | NULL |
+---+------+------+------+
1 row in set (0.00 sec)

I do think the multi-valued index should follow this behavior: No error, delete two rows and insert one.

EDIT: replace with [2, 14]

Hi Mr. j,

Thank you for your bug report.

We were able to repeat your test case, but this is still not a bug.

If you read the following page from our Reference Manual:

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

you can easily conclude that the only specific JSON fields can be specified as members of the array.

If you come up with a test case that depicts a bug with such expressions, then we shall try to repeat it and we shall change the status of this bug report.

I follow the doc:

create table t(a json, unique index idx((cast(a->'$.zip' as signed array))));
insert into t values ('{"zip": [1]}');
insert into t values ('{"zip": [2]}');

in this case, a is a JSON column and there is a unique index on `a->'$.zip', then insert two JSON values with the key 'zip', and the value is an array.

mysql> replace into t values ('{"zip": [1,3]}');
ERROR 1032 (HY000): Can't find record in 't'
mysql> replace into t values ('{"zip": [3,1]}');
Query OK, 2 rows affected (0.02 sec)

mysql> select * from t;
+-----------------+
| a               |
+-----------------+
| {"zip": [3, 1]} |
| {"zip": [2]}    |
+-----------------+
2 rows in set (0.00 sec)

you can see I executed the replace statement, the first one reported an error but the second one gets worked.

mysql> replace into t values ('{"zip": [1,2]}');
ERROR 1062 (23000): Duplicate entry '[1, 2]' for key 't.idx'

If I replace it with '{"zip": [1,2]}', it also reports an error. Is it by design?

Hi Mr. j,

Yes, it is so by design.

On that same URL, you will find a chapter named:

Using multi-valued Indexes

where you can learn, at its very end, how to use the index that you created.