Bug #117612 EVAL in UPDATE statement changes row value before statement is fully parsed

• Submitted: 3 Mar 22:42
• Modified: 4 Mar 11:05
• Reporter: Dov Endress
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.40, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any

[3 Mar 22:42] Dov Endress

Description:
During an ON DUPLICATE KEY UPDATE if a column is evaluated and a new value assigned to it, subsequent evaluations on that column use the newly set value rather than the original value.

How to repeat:
Create a table:

CREATE TABLE `sample` (
  `a` int NOT NULL,
  `b` text,
  `c` text,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Insert a row:

INSERT INTO sample VALUES(1,'b1','c1');

Data at this point:

SELECT * FROM sample;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 | b1   | c1   |
+---+------+------+
1 row in set (0.00 sec)

Perform an INSERT ... ON DUPLICATE KEY with logic evaluating b and c to set values for both b and subsequently c:

INSERT INTO sample (a, b, c)
    -> VALUES (1, "DONT_CARE_B_VAL", "DONT_CARE_C_VAL")
    -> ON DUPLICATE KEY UPDATE
    -> b = IF(b = "b1" AND c = "c1", "b2", "BAD_B_VAL"),
    -> c = IF(b = "b1" AND c = "c1", "c2", "BAD_C_VAL");

The expected results would be 1, b2, c2 but instead:

SELECT * FROM sample;
+---+------+-----------+
| a | b    | c         |
+---+------+-----------+
| 1 | b2   | BAD_C_VAL |
+---+------+-----------+

This behavior is counter to how SQL operates in other ways. For example:

SELECT
 IF(a=1,2,0) AS a
FROM
 sample
WHERE
 a=1
AND 
 a<2;

Will return 2 as expected, it does not set `a` to 2 after the first evaluation.

Note that the UPDATE is written to the binary log as a single statement, and with the correct values in the field comparisons:

SET TIMESTAMP=1741040611/*!*/;
BEGIN
/*!*/;
# at 2875
#250303 22:23:31 server id 100  end_log_pos 2936 CRC32 0x1e6bc0cd       Table_map: `test`.`sample` mapped to number 99
# has_generated_invisible_primary_key=0
# at 2936
#250303 22:23:31 server id 100  end_log_pos 3005 CRC32 0x3c1fe2e4       Update_rows: table id 99 flags: STMT_END_F
### UPDATE `test`.`sample`
### WHERE
###   @1=1
###   @2='b1'
###   @3='c1'
### SET
###   @1=1
###   @2='b2'
###   @3='BAD_C_VAL'
# at 3005
#250303 22:23:31 server id 100  end_log_pos 3036 CRC32 0x12f0c321       Xid = 64
COMMIT/*!*/;

Suggested fix:
Do not change the transient row object prior to evaluating the entire transaction, or provide a means (e.g. OLD.field) of evaluating constants during the operation.

In any case, this behavior does not appear to be documented anywhere that I could find. Regardless of the decision to change the behavior described or not, please at least provide full documentation on the behavior.

Cheers!
Dov Endress

[4 Mar 6:43] MySQL Verification Team

Hello Dov Endress,

Thank you for the report and feedback.

Thanks,
Umesh

[4 Mar 11:05] Roy Lyseng

Posted by developer:
 
I don't think this should be considered a bug.
It is similar to how an UPDATE statement will process a row, and
this is documented as follows:

If you access a column from the table to be updated in an expression, UPDATE uses the current value
of the column. For example, the following statement sets col1 to one more than its current value:

  UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not
the original col1 value. The result is that col1 and col2 have the same value. This behavior differs
from standard SQL.

  UPDATE t1 SET col1 = col1 + 1, col2 = col1;