MySQL Bugs: #98088: Unique index is functioned unexpectedly when entire records are updated target

Bug #98088	Unique index is functioned unexpectedly when entire records are updated target
Submitted:	28 Dec 2019 5:54	Modified:	25 Feb 2020 13:34
Reporter:	Hiroyuki Nakatsuka	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	x86

Description:
I found the exception as the violation on duplication of the unique index.
The situation is below:
1) Create table has an another unique index except the primary key.
2) Iterate these step.
  2a) Insert record.
  2b) update record to update the unique index.
3) When 11th record has inserted(2a) and the exception has occured(2b).

The UPDATE statement(2b) is below:
<code>
UPDATE `accounts` SET
  `pos_x` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_X_PRT` + 1
      WHEN `pos_x` > @`POS_X_PRT` THEN `pos_x` + 1
      ELSE `pos_x`
    END),
  `pos_y` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_Y_PRT` + 1
      ELSE `pos_y`
    END
  );
</code>

The column `pos_x` is the referenced column of the unique key
`accounts_uk_pos_x`. By updating the column `pos_x` of the target
record, `pos_x` of all records must be updated at the same time
on this single statement, so I chose 'CASE' expression.

To check the consistency of the behaviour on the unique key
`accounts_uk_pos_x`, I done following statements:
<code>
ALTER TABLE `accounts` DROP INDEX `accounts_uk_pos_x`;
/* (2b) statement here */
ALTER TABLE `accounts` ADD UNIQUE KEY `accounts_uk_pos_x` (`pos_x`);
</code>

This trial causes NO error. So that means the update statement(2b)
is not illegal but updating entire index range may causes this
exception(ERROR 1062), I think. By the way, the idea to replace
each WHEN clause of the CASE expression on `pos_x` is finished in vain.

I found this phenomenon on MySQL Community Server 8.0.18 on Linux and Windows.

How to repeat:
/*[begin] Preparation. */
/*  [begin] Create table and import dump. */
CREATE SCHEMA `test`;
USE `test`;
CREATE TABLE `accounts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pos_x` INT SIGNED NOT NULL,
  `pos_y` INT UNSIGNED NOT NULL,
  `is_group` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,
  `name` VARCHAR(31) CHARSET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_uk_pos_x` (`pos_x`),
  INDEX `accounts_uk_position` (`pos_x`, `pos_y`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
LOCK TABLES `accounts` WRITE;
REPLACE INTO `accounts` VALUES (1,0,0,1,'uncontrolled group'),(2,1,0,1,'controlled group'),(3,2,1,1,'G1'),(4,9,1,1,'G2'),(5,8,2,0,'S1'),(6,3,2,1,'G3'),(7,4,3,1,'G4'),(8,7,4,1,'S3'),(9,5,4,1,'G5'),(10,6,5,0,'S4');
UNLOCK TABLES;
/*  [end] Create table and import dump. */
INSERT INTO `accounts` (`pos_x`, `pos_y`, `is_group`, `name`) VALUES
  (
    (SELECT MIN(`pos_x`) - 1 FROM `accounts` AS `TGT`),
    1,
    FALSE,
    'S2'
  );
SELECT `id` INTO @`PRT_ID` FROM `accounts` WHERE `name` = 'controlled group';
SELECT `pos_x` INTO @`POS_X_PRT` FROM `accounts` WHERE `id` = @`PRT_ID`;
SELECT `pos_y` INTO @`POS_Y_PRT` FROM `accounts` WHERE `id` = @`PRT_ID`;
SELECT LAST_INSERT_ID() INTO @`TGT_ID`;
/*[end] Preparation.

/*[begin] DML has problem. */
UPDATE `accounts` SET
  `pos_x` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_X_PRT` + 1
      WHEN `pos_x` > @`POS_X_PRT` THEN `pos_x` + 1
      ELSE `pos_x`
    END),
  `pos_y` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_Y_PRT` + 1
      ELSE `pos_y`
    END
  );
/* MySQL Server raises exception below:
Error Code: 1062. Duplicate entry '3' for key 'accounts_uk_pos_x'*/
/*[end] DML has problem. */

Suggested fix:
So sorry, I have no idea about behaviours of the consistency checking
on Unique Key.

Thank for the bug report.
How to repeat:
/*[begin] Preparation. */
/*  [begin] Create table and import dump. */

Please attach here the import dump.

Dump made by Workench before the exception.

Attachment: Dump20191228.sql (application/octet-stream, text), 1.66 KiB.

Sorry. This dump file contains full structure and data for the preparation section.

Attachment: Dump20191231-3.sql (application/octet-stream, text), 2.76 KiB.

How to repeat with recent dump file:

<code>
SELECT `id` INTO @`PRT_ID` FROM `accounts` WHERE `name` = 'controlled group';
SELECT `pos_x` INTO @`POS_X_PRT` FROM `accounts` WHERE `id` = @`PRT_ID`;
SELECT `pos_y` INTO @`POS_Y_PRT` FROM `accounts` WHERE `id` = @`PRT_ID`;
SELECT `id` INTO @`TGT_ID` FROM `accounts` WHERE `name` = 'S2';
UPDATE `accounts` SET
  `pos_x` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_X_PRT` + 1
      WHEN `pos_x` > @`POS_X_PRT` THEN `pos_x` + 1
      ELSE `pos_x`
    END),
  `pos_y` = (
    CASE
      WHEN `id` = @`TGT_ID` THEN @`POS_Y_PRT` + 1
      ELSE `pos_y`
    END
  );
</code>

Thanks.

Hi Mr. 寛幸 中塚,

Thank you for your bug report. 

I do not see anything wrong with your query.

Actually, I get the expected error:

ERROR 1062 (23000) at line 55: Duplicate entry '3' for key 'accounts_uk_pos_x'

Hence, why do you report this as a bug ????/

Hi, Mr.Milivojevic.
So sorry to being late to respond.

I try to inform you why this behaviour is a bug I think.
In my first submitted message:
> To check the consistency of the behaviour on the unique key
> `accounts_uk_pos_x`, I done following statements:
> <code>
> ALTER TABLE `accounts` DROP INDEX `accounts_uk_pos_x`;
> /* (2b) statement here */
> ALTER TABLE `accounts` ADD UNIQUE KEY `accounts_uk_pos_x` (`pos_x`);
> </code>

By executing 'ALTER TABLE DROP INDEX' statement,
the `accounts` table's unique check is only PK.
Next, by executing the 'UPDATE' statement of this problem,
each `pos_x` columns of all records keep uniqueness
because following 'ALTER TABLE ADD UNIQUE KEY' statement
is done with no error.

If this 'UPDATE' statement on the table has UK is illegal,
'ALTER TABLE ADD UNIQUE KEY' statement should raise
unique key duplication error, I think.

One of the assumed reasons for this behaviour is that
the CASE-expression in the 'UPDATE' statement is processed
by the MySQL executor as simple loop and the MySQL server
detects collision per iteration. It seems like 'FOR EACH ROW'
trigger option syntax of the MySQL.

In the case without unique key at executing 'UPDATE' statement,
this problem is not appeared.
Thus the "affection" of the 'UPDATE' statement stored on the table
is not radically illegal.

I think that the inner logic to process SQL statement should
be hidden, so this UK consistency check seems like an violation
in the steps of statement interpretation or optimization or so.

Hi Mr. 寛幸 中塚,

I must admit that I truly do not understand what is it that you are reporting here.

You wrote:

"
By executing 'ALTER TABLE DROP INDEX' statement,
the `accounts` table's unique check is only PK.
"
However, in your test case, DROP INDEX is followed immediately by ADD INDEX, the same one, so this does not make any sense.

Next, if your UPDATE statement did not change column in unique index into any value that already exists, then there is no error. But, as it turns out, your UPDATE statement results in a second identical value, hence, there is an error. This is not a bug, but expected behaviour.

Next, your UPDATE is done on the unique index that exists, hence your duplicate error is reported. Hence, again, I fail to understand what are you reporting here.

Next, to answer your next question, since adding UNIQUE KEY did not report any problems, that means that the logic in your UPDATE statement is flawed. But, that is not a bug !!!!!! That is an error in your own logic, which you should examine and fix.

One of the assumed reasons for this behaviour is that
the CASE-expression in the 'UPDATE' statement is processed
by the MySQL executor as simple loop and the MySQL server
detects collision per iteration. It seems like 'FOR EACH ROW'
trigger option syntax of the MySQL.

Next, you are writing about some trigger. If you have a trigger before or after update, then you should look at it.

Also, the entire CASE clause within UPDATE is applied to each row in the entire table, because you have not set any filtering. So, for each row in the table, entire CASE is run. That is basic SQL.

I must also confess that I do not understand what do you mean by

" that the inner logic to process SQL statement should
be hidden, so this UK consistency check seems like an violation
in the steps of statement interpretation or optimization or so.
"

But, let me try to reply to that ....

A logic in SQL statement is the one provided by the author of the statement. There is no violation with uniqueness consistency checking, because this is obligatory check for any type of database engine.

Hi Mr.Sinisa Milivojevic,

I thank your kind and quick response here.
I'd like to prepare PDF slideshow-styled presentation for this report.
(*) ASAP I will attach file.

Please wait it's completion. Thanks.

Hi Mr. Nakatsuka,

Please, do not send any PDF file.

Just answer our questions. 
Thanks ....

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".