| Bug #99297 | sql-mode TIME_TRUNCATE_FRACTIONAL unsafe for generated columns | ||
|---|---|---|---|
| Submitted: | 18 Apr 2020 18:11 | Modified: | 18 Apr 2020 19:11 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Apr 2020 19:11]
MySQL Verification Team
Thank you for the bug report.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution BUILT: 2020-APR-07
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 8.0 > CREATE DATABASE h;
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > USE h;
Database changed
mysql 8.0 > SET sql_mode=DEFAULT;
Query OK, 0 rows affected (0.00 sec)
mysql 8.0 > CREATE TABLE fractest(
-> id int not null primary key auto_increment,
-> s varchar(255) not null,
-> ts datetime(2) AS ( CAST(s as datetime(2)) ),
-> INDEX (ts)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql 8.0 > INSERT INTO fractest (s) VALUES ('2018-09-08 17:51:04.777');
Query OK, 1 row affected (0.02 sec)
mysql 8.0 > SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql 8.0 > INSERT INTO fractest (s) VALUES ('2018-09-08 17:51:04.777');
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > SELECT * FROM fractest WHERE ts = '2018-09-08 17:51:04.77';
+----+-------------------------+------------------------+
| id | s | ts |
+----+-------------------------+------------------------+
| 2 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 |
+----+-------------------------+------------------------+
1 row in set (0.00 sec)
mysql 8.0 > SELECT * FROM fractest;
+----+-------------------------+------------------------+
| id | s | ts |
+----+-------------------------+------------------------+
| 1 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 |
| 2 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 |
+----+-------------------------+------------------------+
2 rows in set (0.00 sec)
mysql 8.0 >
Description: The value of the sql-mode impacts how generated columns work, leading to non-deterministic behaviors. This is not a new problem, but I think I found a new case. The docs for TIME_TRUNCATE_FRACTIONAL does not stress it is unsafe to change when using gcols too. How to repeat: DROP TABLE IF EXISTS fractest; SET sql_mode=DEFAULT; CREATE TABLE fractest( id int not null primary key auto_increment, s varchar(255) not null, ts datetime(2) AS ( CAST(s as datetime(2)) ), INDEX (ts) ); INSERT INTO fractest (s) VALUES ('2018-09-08 17:51:04.777'); SET sql_mode='TIME_TRUNCATE_FRACTIONAL'; INSERT INTO fractest (s) VALUES ('2018-09-08 17:51:04.777'); SELECT * FROM fractest WHERE ts = '2018-09-08 17:51:04.77'; SELECT * FROM fractest; Output of last 2 lines: mysql> SELECT * FROM fractest WHERE ts = '2018-09-08 17:51:04.77'; +----+-------------------------+------------------------+ | id | s | ts | +----+-------------------------+------------------------+ | 2 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 | +----+-------------------------+------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM fractest; +----+-------------------------+------------------------+ | id | s | ts | +----+-------------------------+------------------------+ | 1 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 | | 2 | 2018-09-08 17:51:04.777 | 2018-09-08 17:51:04.77 | +----+-------------------------+------------------------+ 2 rows in set (0.00 sec) Suggested fix: Maybe it is worth adding a behavior such that gcol expressions are not influenced by sql-modes to keep it deterministic? There are already some such modes such as HIGH_NOT PRECEDENCE, which don't seem to work inside gcol expressions from what I can tell.