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]
Владислав Сокол
[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. regards, Umesh
[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. Closed.