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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 8.0.30 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[6 Jan 2023 8:23]
x j
[9 Jan 2023 2:33]
x j
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.
[9 Jan 2023 2:35]
x j
EDIT: replace with [2, 14]
[9 Jan 2023 13:14]
MySQL Verification Team
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.
[10 Jan 2023 2:47]
x j
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?
[10 Jan 2023 12:42]
MySQL Verification Team
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.