Bug #121736 Undefined user variable reads as 0, not NULL, in a trigger first run by another session
Description:
Inside a trigger, a user variable that the current session has never assigned reads as 0 and `@v IS NULL` is false, if another session ran the same trigger first while `@v` held an integer. At the top level of the same session, `SELECT @v IS NULL` returns 1. No error or warning is raised.
This silently breaks triggers guarded by a session variable, such as `IF @disable_triggers IS NULL THEN ...`.
It happens only when binary logging is disabled, or when the session has `sql_log_bin = 0`. It started in 8.0.22: 8.0.21 and 5.7.44 return NULL for the same test. Both sessions must use the same cached table, so the test below uses `--table-open-cache-instances=1`. With the default setting it affects sessions that land on the same table cache instance. FLUSH TABLES on the table clears it until the next integer-valued run.
How to repeat:
Start the server with `--skip-log-bin --table-open-cache-instances=1`.
CREATE TABLE t1 (a INT);
CREATE TABLE log (v VARCHAR(20), v_is_null INT);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
INSERT INTO log VALUES (@v, @v IS NULL);
-- Session 1
SET @v = 1; INSERT INTO t1 VALUES (1);
-- Session 2 (new connection, @v never set)
SELECT @v IS NULL; -- 1
INSERT INTO t1 VALUES (2);
SELECT * FROM log;
Expected row 2: v = NULL, v_is_null = 1 (what 8.0.21 returns).
Actual on 8.0.46, 8.4.13 and 9.7.4:
+------+-----------+
| v | v_is_null |
+------+-----------+
| 1 | 0 |
| 0 | 0 |
+------+-----------+
With binary logging enabled, row 2 is NULL / 1 as expected, unless the session runs `SET SESSION sql_log_bin = 0` first.
Suggested fix:
Since 8.0.22 the trigger's Item_func_get_user_var keeps the INT type resolved on its first execution. For a variable that does not exist, `val_int()`, `val_real()` and `val_decimal()` return 0 without setting `null_value`, while `val_str()` sets it. With binary logging on, `get_var_with_binlog()` creates the variable as NULL first, which hides the problem. Setting `null_value = true` in those three methods when the variable does not exist should fix it.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.047 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Inside a trigger, a user variable that the current session has never assigned reads as 0 and `@v IS NULL` is false, if another session ran the same trigger first while `@v` held an integer. At the top level of the same session, `SELECT @v IS NULL` returns 1. No error or warning is raised. This silently breaks triggers guarded by a session variable, such as `IF @disable_triggers IS NULL THEN ...`. It happens only when binary logging is disabled, or when the session has `sql_log_bin = 0`. It started in 8.0.22: 8.0.21 and 5.7.44 return NULL for the same test. Both sessions must use the same cached table, so the test below uses `--table-open-cache-instances=1`. With the default setting it affects sessions that land on the same table cache instance. FLUSH TABLES on the table clears it until the next integer-valued run. How to repeat: Start the server with `--skip-log-bin --table-open-cache-instances=1`. CREATE TABLE t1 (a INT); CREATE TABLE log (v VARCHAR(20), v_is_null INT); CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW INSERT INTO log VALUES (@v, @v IS NULL); -- Session 1 SET @v = 1; INSERT INTO t1 VALUES (1); -- Session 2 (new connection, @v never set) SELECT @v IS NULL; -- 1 INSERT INTO t1 VALUES (2); SELECT * FROM log; Expected row 2: v = NULL, v_is_null = 1 (what 8.0.21 returns). Actual on 8.0.46, 8.4.13 and 9.7.4: +------+-----------+ | v | v_is_null | +------+-----------+ | 1 | 0 | | 0 | 0 | +------+-----------+ With binary logging enabled, row 2 is NULL / 1 as expected, unless the session runs `SET SESSION sql_log_bin = 0` first. Suggested fix: Since 8.0.22 the trigger's Item_func_get_user_var keeps the INT type resolved on its first execution. For a variable that does not exist, `val_int()`, `val_real()` and `val_decimal()` return 0 without setting `null_value`, while `val_str()` sets it. With binary logging on, `get_var_with_binlog()` creates the variable as NULL first, which hides the problem. Setting `null_value = true` in those three methods when the variable does not exist should fix it.