Description:
A multi-table `UPDATE` that writes a `NULL` expression into a `NOT NULL` column can leave `Field::m_is_tmp_null` set on the target table's reusable `Field` object when the target table has a trigger.
The first `UPDATE` correctly fails with:
ERROR 1048 (23000): Column 'val' cannot be null
However, after that failure, later UPDATE or INSERT statements that explicitly provide a non-NULL value for the same column can still fail with the same error:
ERROR 1048 (23000): Column 'val' cannot be null
The issue appears to be caused by temporary nullability state leaking from a failed multi-table UPDATE delayed update path into the table cache.
This appears to affect the multi-table UPDATE delayed update path when all of the following are true:
1. The UPDATE uses the delayed update path for the target table.
2. The target table has a trigger. The trigger does not have to be an UPDATE trigger; a DELETE trigger is enough.
3. The UPDATE expression evaluates to NULL for a NOT NULL column.
How to repeat:
# Run the MTR with the case
CREATE TABLE main (
id int PRIMARY KEY,
val decimal(18,2) NOT NULL,
status int NOT NULL
);
CREATE TABLE item (
id int PRIMARY KEY,
main_id int NOT NULL,
qty int NOT NULL
);
INSERT INTO main VALUES (1, 10.00, 0);
INSERT INTO item VALUES (1, 1, 2);
DELIMITER |;
CREATE TRIGGER bd_main BEFORE DELETE ON main
FOR EACH ROW
BEGIN
SET @dummy = OLD.id;
END|
DELIMITER ;|
#First error is expected: NULL * i.qty is NULL, and val is NOT NULL.
--error ER_BAD_NULL_ERROR
UPDATE item i STRAIGHT_JOIN main m ON m.id = i.main_id
SET m.val = m.val + NULL * i.qty
WHERE m.id = 1;
# Updating another column succeeds because val is not checked here.
UPDATE main SET status = 1 WHERE id = 1;
# BUG!!!
# These should succeed because val is explicitly non-NULL. However, they currently fail if the stale tmp-null flag leaked.
--error ER_BAD_NULL_ERROR
UPDATE main SET val = 20.00 WHERE id = 1;
--error ER_BAD_NULL_ERROR
INSERT INTO main VALUES (2, 10.00, 0);
--error ER_BAD_NULL_ERROR
INSERT INTO main VALUES (3, 10.00, 0);
SELECT * FROM main ORDER BY id;
DROP TABLE main, item;
Suggested fix:
A cause analysis:
=================
In the multi-table UPDATE delayed update path, MySQL stores updated values in a temporary table and later copies them back to the real target table.
When the target table has a trigger, the temporary fields are made temporarily nullable. If the updated expression evaluates to NULL, the temporary field gets marked with m_is_tmp_null=true.
During delayed update, Copy_field::invoke_do_copy() copies this tmp-null state from the temporary field to the real target table field. After that, disable_fields_temporary_nullability() only clears temporary nullability, but does not clear m_is_tmp_null.
So before constraint checking, the real target field is in this state:
m_is_tmp_nullable = false
m_is_tmp_null = true
The first ER_BAD_NULL_ERROR is correct.
The problem is that after this error, m_is_tmp_null remains on the reusable Field object. Later statements may reuse the same polluted Field; since set_notnull() does not clear m_is_tmp_null when m_is_tmp_nullable=false, later non-NULL UPDATE/INSERT statements can still fail with a false ER_BAD_NULL_ERROR.
Suggested fix:
==============
The first failing UPDATE should still report ER_BAD_NULL_ERROR. The fix should ensure that temporary NULL state is cleared after constraint checking or during statement cleanup, before the TABLE / Field object can be reused from the table cache.
