Bug #106286 Null-safe compare works incorrectly with TIMESTAMP in a trigger
Submitted: 26 Jan 6:16 Modified: 10 Feb 1:24
Reporter: Владислав Сокол Email Updates:
Status: Closed Impact on me:
None 
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 6:16] Владислав Сокол
Description:
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
CREATE TABLE test (id INT, 
                   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
BEFORE INSERT
ON test
FOR EACH ROW
BEGIN
    IF EXISTS ( SELECT NULL
                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;
END

-- 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 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 7:27] MySQL Verification Team
Hello Владислав Сокол!

Thank you for the report and feedback.

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

You may find the truth by executing the sql "show create table test"
[27 Jan 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 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 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 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.

Closed.