Bug #106286 Null-safe compare works incorrectly with TIMESTAMP in a trigger
Submitted: 26 Jan 2022 6:16 Modified: 10 Feb 2022 1:24
Reporter: Владислав Сокол Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: Null-safe timestamp trigger

[26 Jan 2022 6:16] Владислав Сокол
https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html claims that "For NULL <=> NULL, the result is true."

But this is not true in a trigger while comparing NULLS in new row and existing one for a column of TIMESTAMP datatype on the version 8.0.x.

At the same time, the comparison works correctly on version 5.x.

How to repeat:
-- the table and initial data
                   ts TIMESTAMP);
INSERT INTO test (id, ts) VALUES (2, NULL);

-- the trigger which should provide (id, ts) uniqueness
-- including NULL values
CREATE TRIGGER tr_bi_check_uniqueness_with_nulls
ON test
                FROM test
                WHERE test.id <=> NEW.id
                  AND test.ts <=> NEW.ts ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicated values not allowed.';
    END IF;

-- an attempt to insert duplicated value
INSERT INTO test (id, ts) VALUES (2, NULL);
-- the row is errorneously inserted


The insertion is performed. I.e. the condition 'AND test.ts <=> NEW.ts' does not detect that NULL in new row is equal to NULL value in existing row.

The online fiddle which reproduces the issue: https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=301f2a03906b2dbf171db554485c2...

Suggested fix:
Perform any additional action with NEW value. For example, CAST it to TIMESTAMP explicitly in the query, or assign to local / user-defined variable and use this variable instead of NEW column in the conditional expression.
[26 Jan 2022 6:19] Владислав Сокол
Sorry, the link to online fiddle which reproduces the issue is incorrect.

Correct link: https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=mysql_5.7&fiddle=c61a60074cb8de78da8c51c56a4dc...
[26 Jan 2022 7:27] MySQL Verification Team
Hello Владислав Сокол!

Thank you for the report and feedback.

[27 Jan 2022 2:51] huahua xu
hi, Владислав Сокол

You may find the truth by executing the sql "show create table test"
[27 Jan 2022 3:05] huahua xu
It modifies the first column definition whose SQL type is TIMESTAMP by adding the features DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(Promote first timestamp column), when explicit_defaults_for_timestamp is not set
[27 Jan 2022 4:46] Владислав Сокол
1) The problem is in incorrect operator action, not in default TIMESTAMP column value.

2) Please see my second comment which provides correct fiddle. There is no difference in DDLs (except the length specifying presence and another table charset for 5.x which should not influence).
[27 Jan 2022 13:39] huahua xu
Do you get some rows by executing the sql "select * from test where ts <=> NULL" on the version 8.0.27?
[10 Feb 2022 1:24] Jon Stephens
Documented fix as follows in the MySQL 8.0.29 changelog:

    A null-safe comparison (<=>) did not evaluate correctly for a
    TIMESTAMP column in a trigger.