Description:
The manual (CREATE TABLE and Generated Columns) states the determinism
requirement as:
"A function is deterministic if, given the same data in tables, multiple
invocations produce the same result, independently of the connected user."
Expressions such as CAST(ts AS DATE) where ts is a TIMESTAMP column, or
MONTHNAME(d), do not meet this definition: two connections that differ only in
their session time_zone (respectively lc_time_names) obtain different results
from identical table data. They are nevertheless accepted in STORED generated
columns, in VIRTUAL generated columns, and in functional key parts.
Two distinct failures follow.
(1) Durable violation of a UNIQUE key, and an unrestorable table.
The value is materialized using the writing session's time_zone. Two rows holding
the *same* TIMESTAMP value can therefore be stored with *different* values in the
generated column, and a UNIQUE key on that column does not detect them. The
stored value then contradicts the column's own definition, no rebuild repairs it,
CHECK TABLE reports OK, and mysqldump/restore fails because the restore
recomputes the column and both rows collapse onto one key.
(2) Wrong results under the default plan.
Generated column substitution rewrites "WHERE <expr> = ?" into a lookup on the
generated column or functional index holding the materialized value. A query that
never mentions the generated column therefore returns rows computed under a
different session's context: rows that satisfy the predicate are missing, and
rows that do not satisfy it are returned.
Neither failure requires an ALTER or a configuration change. It is enough that
the reading (or second writing) session's time_zone differs from the first, which
is the normal situation when the server runs in UTC and clients set their local
zone.
The server already performs exactly this check elsewhere. Partitioning rejects
the same expressions:
CREATE TABLE p(ts TIMESTAMP NOT NULL)
PARTITION BY RANGE(TO_DAYS(ts)) (PARTITION p0 VALUES LESS THAN (999999));
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in
(sub)partitioning function are not allowed
TO_DAYS(ts), HOUR(ts) and YEAR(ts) on a TIMESTAMP column are refused there for
the same reason that makes them unsafe here, but are accepted in a generated
column or a functional key part, where the materialized value is additionally
used to answer queries and to enforce constraints.
The manual documents the analogous SQL-mode caveat ("Expression evaluation uses
the SQL mode in effect at evaluation time. If any component of the expression
depends on the SQL mode, different results may occur for different uses of the
table..."), but says nothing about time_zone or lc_time_names, and in neither
case does it state that a UNIQUE key over such a column stops holding, or that
the optimizer will answer a query written against the raw expression from the
stale materialization.
Affected expressions confirmed on 9.7.1, in all three materialization forms
(STORED generated column, VIRTUAL generated column, functional key part):
driven by session time_zone, on a TIMESTAMP column:
CAST(ts AS DATE), DATE(ts), HOUR(ts), WEEKDAY(ts), TO_DAYS(ts),
EXTRACT(DAY FROM ts), DAYOFYEAR(ts)
driven by session lc_time_names:
MONTHNAME(d), DAYNAME(d), DATE_FORMAT(d,'%M'), DATE_FORMAT(d,'%W')
CONVERT_TZ(ts,'UTC','Asia/Tokyo'), which names both zones explicitly, is not
affected.
How to repeat:
--------------------------------------------------------------------------------
How to repeat, part 1: UNIQUE key violated in storage, table not restorable
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS b1u; CREATE DATABASE b1u; USE b1u;
SET time_zone='+00:00';
CREATE TABLE daily (
id INT PRIMARY KEY,
ts TIMESTAMP NOT NULL,
day DATE AS (CAST(ts AS DATE)) STORED,
UNIQUE KEY u(day)
) ENGINE=InnoDB;
INSERT INTO daily(id,ts) VALUES (1,'2024-03-01 23:30:00');
SET time_zone='+09:00';
INSERT INTO daily(id,ts) VALUES (2,'2024-03-02 08:30:00'); -- the same instant
SET time_zone='+00:00';
SELECT id, ts, day, CAST(ts AS DATE) AS recomputed FROM daily ORDER BY id;
+----+---------------------+------------+------------+
| id | ts | day | recomputed |
+----+---------------------+------------+------------+
| 1 | 2024-03-01 23:30:00 | 2024-03-01 | 2024-03-01 |
| 2 | 2024-03-01 23:30:00 | 2024-03-02 | 2024-03-01 |
+----+---------------------+------------+------------+
Both rows hold the identical ts. Row 2 stores day = '2024-03-02' although
CAST(ts AS DATE) for that row is '2024-03-01' — in this session and in every
session, since ts is the same for both rows. The stored value contradicts the
column's own definition, and the table holds two rows for one instant under a
UNIQUE key meant to permit one:
SELECT ts, COUNT(*) FROM daily GROUP BY ts;
+---------------------+----------+
| ts | COUNT(*) |
+---------------------+----------+
| 2024-03-01 23:30:00 | 2 |
+---------------------+----------+
Nothing reports or repairs it:
CHECK TABLE daily;
+-----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| b1u.daily | check | status | OK |
+-----------+-------+----------+----------+
ALTER TABLE daily ENGINE=InnoDB; -- succeeds
SELECT id, ts, day FROM daily ORDER BY id;
+----+---------------------+------------+
| id | ts | day |
+----+---------------------+------------+
| 1 | 2024-03-01 23:30:00 | 2024-03-01 |
| 2 | 2024-03-01 23:30:00 | 2024-03-02 | -- both values survive the rebuild
+----+---------------------+------------+
The table cannot be restored from its own logical backup. mysqldump correctly
omits the generated column, so the restore recomputes it and both rows map to the
same key. Note that the dump already disables UNIQUE_CHECKS and the restore still
fails, because the failure occurs when the index is built, not on the inserts:
$ mysqldump --set-gtid-purged=OFF --skip-comments b1u daily
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
`day` date GENERATED ALWAYS AS (cast(`ts` as date)) STORED,
UNIQUE KEY `u` (`day`)
INSERT INTO `daily` (`id`, `ts`) VALUES (1,'2024-03-01 23:30:00'),
(2,'2024-03-01 23:30:00');
$ mysqldump ... b1u daily | mysql ... b1r
ERROR 1062 (23000) at line 26: Duplicate entry '2024-03-01' for key 'daily.u'
mysql> SELECT COUNT(*) FROM b1r.daily;
0
The restore aborts and the target table is left empty. This occurs under every
session time zone tried (+00:00 and +09:00): because the two rows hold the same
ts, there is no time zone in which the dump can be replayed. A table that is
online and queryable in production is therefore not restorable by any logical
backup, and the condition is invisible to CHECK TABLE.
--------------------------------------------------------------------------------
How to repeat, part 2: wrong results under the default plan
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS b1; CREATE DATABASE b1; USE b1;
SET time_zone='+00:00'; -- writing session (server in UTC)
CREATE TABLE events (
id INT PRIMARY KEY,
ts TIMESTAMP NOT NULL,
amount INT NOT NULL,
day DATE AS (CAST(ts AS DATE)) STORED,
KEY k(day)
) ENGINE=InnoDB;
INSERT INTO events(id,ts,amount) VALUES
(1,'2024-03-01 23:30:00',100), (2,'2024-03-02 10:00:00',200),
(3,'2024-03-02 22:30:00',300), (4,'2024-03-03 05:00:00',400),
(5,'2024-03-03 23:00:00',500);
SET time_zone='+09:00'; -- reading session (client in Tokyo)
In the +09:00 session the local dates of rows 1..3 are 2024-03-02, 2024-03-02 and
2024-03-03, so the correct answer for the predicate below is {1,2}:
SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02';
+----+
| id |
+----+
| 2 |
| 3 |
+----+
SELECT id FROM events IGNORE INDEX(k) WHERE CAST(ts AS DATE)='2024-03-02';
+----+
| id |
+----+
| 1 |
| 2 |
+----+
Row 1 is missing and row 3 is returned although it does not satisfy the predicate
in this session. The query never mentions the column "day", yet the plan shows
the substitution:
EXPLAIN FORMAT=TREE SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02';
-> Index lookup on events using k (day = '2024-03-02') (cost=0.7 rows=2)
Aggregates and DML follow the wrong row set:
SELECT SUM(amount) FROM events WHERE CAST(ts AS DATE)='2024-03-02'; -- 500
SELECT SUM(amount) FROM events IGNORE INDEX(k) WHERE CAST(ts AS DATE)='2024-03-02'; -- 300
CREATE TABLE arch AS SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02';
SELECT GROUP_CONCAT(id ORDER BY id) FROM arch; -- 2,3
-- row 1 is not archived; row 3 does not belong to that day
UPDATE events SET amount=amount+1000 WHERE CAST(ts AS DATE)='2024-03-02';
SELECT GROUP_CONCAT(id ORDER BY id) FROM events IGNORE INDEX(k) WHERE amount>=1000;
-- 2,3
-- row 1 is skipped; row 3 is wrongly modified
The same result is produced with day declared VIRTUAL, and with no generated
column at all using a functional key part:
CREATE TABLE events (id INT PRIMARY KEY, ts TIMESTAMP NOT NULL,
INDEX k((CAST(ts AS DATE)))) ENGINE=InnoDB;
Locale family:
SET lc_time_names='en_US';
CREATE TABLE t2 (id INT PRIMARY KEY, d DATE,
g VARCHAR(32) AS (MONTHNAME(d)) STORED, KEY k(g)) ENGINE=InnoDB;
INSERT INTO t2(id,d) VALUES (1,'2024-01-15'),(2,'2024-06-15'),(3,'2024-01-20');
SET lc_time_names='fr_FR';
SELECT id FROM t2 WHERE MONTHNAME(d)='janvier'; -- empty (wrong)
SELECT id FROM t2 IGNORE INDEX(k) WHERE MONTHNAME(d)='janvier'; -- 1,3 (correct)
Asymmetry with partitioning, on the same server:
CREATE TABLE p(ts TIMESTAMP NOT NULL)
PARTITION BY RANGE(TO_DAYS(ts)) (PARTITION p0 VALUES LESS THAN (999999));
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in
(sub)partitioning function are not allowed
CREATE TABLE q(ts TIMESTAMP NOT NULL,
g INT AS (TO_DAYS(ts)) STORED, KEY k(g)); -- accepted
Suggested fix:
Apply the check that already exists for partitioning functions (the one raising
ER_PARTITION_FUNC_NOT_ALLOWED_ERROR, 1486) to generated column expressions and
functional key part expressions, so that timezone-dependent expressions over
TIMESTAMP columns and lc_time_names-dependent expressions are rejected at DDL
time. This matches the manual's stated determinism requirement, since these
expressions do not produce the same result independently of the connecting
session.
A narrower change — excluding such expressions from generated column
substitution, so that "WHERE <expr> = ?" is always evaluated live — would address
the wrong results in part 2, but not part 1: the UNIQUE key would still fail to
hold and the table would still be unrestorable. Only rejection at DDL time
addresses both.
Description: The manual (CREATE TABLE and Generated Columns) states the determinism requirement as: "A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user." Expressions such as CAST(ts AS DATE) where ts is a TIMESTAMP column, or MONTHNAME(d), do not meet this definition: two connections that differ only in their session time_zone (respectively lc_time_names) obtain different results from identical table data. They are nevertheless accepted in STORED generated columns, in VIRTUAL generated columns, and in functional key parts. Two distinct failures follow. (1) Durable violation of a UNIQUE key, and an unrestorable table. The value is materialized using the writing session's time_zone. Two rows holding the *same* TIMESTAMP value can therefore be stored with *different* values in the generated column, and a UNIQUE key on that column does not detect them. The stored value then contradicts the column's own definition, no rebuild repairs it, CHECK TABLE reports OK, and mysqldump/restore fails because the restore recomputes the column and both rows collapse onto one key. (2) Wrong results under the default plan. Generated column substitution rewrites "WHERE <expr> = ?" into a lookup on the generated column or functional index holding the materialized value. A query that never mentions the generated column therefore returns rows computed under a different session's context: rows that satisfy the predicate are missing, and rows that do not satisfy it are returned. Neither failure requires an ALTER or a configuration change. It is enough that the reading (or second writing) session's time_zone differs from the first, which is the normal situation when the server runs in UTC and clients set their local zone. The server already performs exactly this check elsewhere. Partitioning rejects the same expressions: CREATE TABLE p(ts TIMESTAMP NOT NULL) PARTITION BY RANGE(TO_DAYS(ts)) (PARTITION p0 VALUES LESS THAN (999999)); ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed TO_DAYS(ts), HOUR(ts) and YEAR(ts) on a TIMESTAMP column are refused there for the same reason that makes them unsafe here, but are accepted in a generated column or a functional key part, where the materialized value is additionally used to answer queries and to enforce constraints. The manual documents the analogous SQL-mode caveat ("Expression evaluation uses the SQL mode in effect at evaluation time. If any component of the expression depends on the SQL mode, different results may occur for different uses of the table..."), but says nothing about time_zone or lc_time_names, and in neither case does it state that a UNIQUE key over such a column stops holding, or that the optimizer will answer a query written against the raw expression from the stale materialization. Affected expressions confirmed on 9.7.1, in all three materialization forms (STORED generated column, VIRTUAL generated column, functional key part): driven by session time_zone, on a TIMESTAMP column: CAST(ts AS DATE), DATE(ts), HOUR(ts), WEEKDAY(ts), TO_DAYS(ts), EXTRACT(DAY FROM ts), DAYOFYEAR(ts) driven by session lc_time_names: MONTHNAME(d), DAYNAME(d), DATE_FORMAT(d,'%M'), DATE_FORMAT(d,'%W') CONVERT_TZ(ts,'UTC','Asia/Tokyo'), which names both zones explicitly, is not affected. How to repeat: -------------------------------------------------------------------------------- How to repeat, part 1: UNIQUE key violated in storage, table not restorable -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS b1u; CREATE DATABASE b1u; USE b1u; SET time_zone='+00:00'; CREATE TABLE daily ( id INT PRIMARY KEY, ts TIMESTAMP NOT NULL, day DATE AS (CAST(ts AS DATE)) STORED, UNIQUE KEY u(day) ) ENGINE=InnoDB; INSERT INTO daily(id,ts) VALUES (1,'2024-03-01 23:30:00'); SET time_zone='+09:00'; INSERT INTO daily(id,ts) VALUES (2,'2024-03-02 08:30:00'); -- the same instant SET time_zone='+00:00'; SELECT id, ts, day, CAST(ts AS DATE) AS recomputed FROM daily ORDER BY id; +----+---------------------+------------+------------+ | id | ts | day | recomputed | +----+---------------------+------------+------------+ | 1 | 2024-03-01 23:30:00 | 2024-03-01 | 2024-03-01 | | 2 | 2024-03-01 23:30:00 | 2024-03-02 | 2024-03-01 | +----+---------------------+------------+------------+ Both rows hold the identical ts. Row 2 stores day = '2024-03-02' although CAST(ts AS DATE) for that row is '2024-03-01' — in this session and in every session, since ts is the same for both rows. The stored value contradicts the column's own definition, and the table holds two rows for one instant under a UNIQUE key meant to permit one: SELECT ts, COUNT(*) FROM daily GROUP BY ts; +---------------------+----------+ | ts | COUNT(*) | +---------------------+----------+ | 2024-03-01 23:30:00 | 2 | +---------------------+----------+ Nothing reports or repairs it: CHECK TABLE daily; +-----------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+-------+----------+----------+ | b1u.daily | check | status | OK | +-----------+-------+----------+----------+ ALTER TABLE daily ENGINE=InnoDB; -- succeeds SELECT id, ts, day FROM daily ORDER BY id; +----+---------------------+------------+ | id | ts | day | +----+---------------------+------------+ | 1 | 2024-03-01 23:30:00 | 2024-03-01 | | 2 | 2024-03-01 23:30:00 | 2024-03-02 | -- both values survive the rebuild +----+---------------------+------------+ The table cannot be restored from its own logical backup. mysqldump correctly omits the generated column, so the restore recomputes it and both rows map to the same key. Note that the dump already disables UNIQUE_CHECKS and the restore still fails, because the failure occurs when the index is built, not on the inserts: $ mysqldump --set-gtid-purged=OFF --skip-comments b1u daily /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; `day` date GENERATED ALWAYS AS (cast(`ts` as date)) STORED, UNIQUE KEY `u` (`day`) INSERT INTO `daily` (`id`, `ts`) VALUES (1,'2024-03-01 23:30:00'), (2,'2024-03-01 23:30:00'); $ mysqldump ... b1u daily | mysql ... b1r ERROR 1062 (23000) at line 26: Duplicate entry '2024-03-01' for key 'daily.u' mysql> SELECT COUNT(*) FROM b1r.daily; 0 The restore aborts and the target table is left empty. This occurs under every session time zone tried (+00:00 and +09:00): because the two rows hold the same ts, there is no time zone in which the dump can be replayed. A table that is online and queryable in production is therefore not restorable by any logical backup, and the condition is invisible to CHECK TABLE. -------------------------------------------------------------------------------- How to repeat, part 2: wrong results under the default plan -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS b1; CREATE DATABASE b1; USE b1; SET time_zone='+00:00'; -- writing session (server in UTC) CREATE TABLE events ( id INT PRIMARY KEY, ts TIMESTAMP NOT NULL, amount INT NOT NULL, day DATE AS (CAST(ts AS DATE)) STORED, KEY k(day) ) ENGINE=InnoDB; INSERT INTO events(id,ts,amount) VALUES (1,'2024-03-01 23:30:00',100), (2,'2024-03-02 10:00:00',200), (3,'2024-03-02 22:30:00',300), (4,'2024-03-03 05:00:00',400), (5,'2024-03-03 23:00:00',500); SET time_zone='+09:00'; -- reading session (client in Tokyo) In the +09:00 session the local dates of rows 1..3 are 2024-03-02, 2024-03-02 and 2024-03-03, so the correct answer for the predicate below is {1,2}: SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02'; +----+ | id | +----+ | 2 | | 3 | +----+ SELECT id FROM events IGNORE INDEX(k) WHERE CAST(ts AS DATE)='2024-03-02'; +----+ | id | +----+ | 1 | | 2 | +----+ Row 1 is missing and row 3 is returned although it does not satisfy the predicate in this session. The query never mentions the column "day", yet the plan shows the substitution: EXPLAIN FORMAT=TREE SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02'; -> Index lookup on events using k (day = '2024-03-02') (cost=0.7 rows=2) Aggregates and DML follow the wrong row set: SELECT SUM(amount) FROM events WHERE CAST(ts AS DATE)='2024-03-02'; -- 500 SELECT SUM(amount) FROM events IGNORE INDEX(k) WHERE CAST(ts AS DATE)='2024-03-02'; -- 300 CREATE TABLE arch AS SELECT id FROM events WHERE CAST(ts AS DATE)='2024-03-02'; SELECT GROUP_CONCAT(id ORDER BY id) FROM arch; -- 2,3 -- row 1 is not archived; row 3 does not belong to that day UPDATE events SET amount=amount+1000 WHERE CAST(ts AS DATE)='2024-03-02'; SELECT GROUP_CONCAT(id ORDER BY id) FROM events IGNORE INDEX(k) WHERE amount>=1000; -- 2,3 -- row 1 is skipped; row 3 is wrongly modified The same result is produced with day declared VIRTUAL, and with no generated column at all using a functional key part: CREATE TABLE events (id INT PRIMARY KEY, ts TIMESTAMP NOT NULL, INDEX k((CAST(ts AS DATE)))) ENGINE=InnoDB; Locale family: SET lc_time_names='en_US'; CREATE TABLE t2 (id INT PRIMARY KEY, d DATE, g VARCHAR(32) AS (MONTHNAME(d)) STORED, KEY k(g)) ENGINE=InnoDB; INSERT INTO t2(id,d) VALUES (1,'2024-01-15'),(2,'2024-06-15'),(3,'2024-01-20'); SET lc_time_names='fr_FR'; SELECT id FROM t2 WHERE MONTHNAME(d)='janvier'; -- empty (wrong) SELECT id FROM t2 IGNORE INDEX(k) WHERE MONTHNAME(d)='janvier'; -- 1,3 (correct) Asymmetry with partitioning, on the same server: CREATE TABLE p(ts TIMESTAMP NOT NULL) PARTITION BY RANGE(TO_DAYS(ts)) (PARTITION p0 VALUES LESS THAN (999999)); ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed CREATE TABLE q(ts TIMESTAMP NOT NULL, g INT AS (TO_DAYS(ts)) STORED, KEY k(g)); -- accepted Suggested fix: Apply the check that already exists for partitioning functions (the one raising ER_PARTITION_FUNC_NOT_ALLOWED_ERROR, 1486) to generated column expressions and functional key part expressions, so that timezone-dependent expressions over TIMESTAMP columns and lc_time_names-dependent expressions are rejected at DDL time. This matches the manual's stated determinism requirement, since these expressions do not produce the same result independently of the connecting session. A narrower change — excluding such expressions from generated column substitution, so that "WHERE <expr> = ?" is always evaluated live — would address the wrong results in part 2, but not part 1: the UNIQUE key would still fail to hold and the table would still be unrestorable. Only rejection at DDL time addresses both.