MySQL Bugs: #106286: Null-safe compare works incorrectly with TIMESTAMP in a trigger

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:	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

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.

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...

Hello Владислав Сокол!

Thank you for the report and feedback.

regards,
Umesh

hi, Владислав Сокол

You may find the truth by executing the sql "show create table test"

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

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).

Do you get some rows by executing the sql "select * from test where ts <=> NULL" on the version 8.0.27?

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.