Bug #121394 Buffered multi table update stores NULL instead of DEFAULT when asked for DEFAULT

• Submitted: 29 Sep 8:27
• Modified: 30 Sep 7:12
• Reporter: Laurynas Biveinis (OCA)
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.4.11, 9.7.2, 26.10.0-er
• OS: Any
• CPU Architecture: Any

[29 Sep 8:27] Laurynas Biveinis

Description:
# The default must be an expression, not literal
CREATE TABLE t1 (c INT PRIMARY KEY, d INT DEFAULT (42));
CREATE TABLE t2 (c INT PRIMARY KEY);
INSERT INTO t1 VALUES (1, NULL), (2, NULL);
INSERT INTO t2 VALUES (1), (2);

# Needs buffered update execution plan
EXPLAIN FORMAT=TREE
UPDATE t2 STRAIGHT_JOIN t1 ON t2.c = t1.c SET t1.d = DEFAULT WHERE t1.c = 2;

# Works
UPDATE t1 SET d = DEFAULT WHERE c = 1;
# Stores wrong value
UPDATE t2 STRAIGHT_JOIN t1 ON t2.c = t1.c SET t1.d = DEFAULT WHERE t1.c = 2;

# Shows one wrong, one correct value
SELECT * FROM t1;

DROP TABLE t1, t2;

How to repeat:
See above

[30 Sep 7:12] Chaithra Marsur Gopala Reddy

Hi Laurynas Biveinis,

Thank you for the test case. Verified as described.