Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.7.2, 10.11.12
-
None
Description
You cannot crate a persistent generated column that combines both an IF statement and a constant datetime. The following statement produces an error:
CREATE TABLE `test` (
|
`date` datetime(3),
|
`generated` datetime(3) AS (
|
IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, '2000-01-01 00:00:00')
|
) STORED
|
);
|
ERROR 1901 (HY000): Function or expression 'if(`date` is not null,`date` - interval 1 second,'2000-01-01 00:00:00')' cannot be used in the GENERATED ALWAYS AS clause of `generated`
|
However, every part of the generated column expression can be used independently. Creating a generated column with some datetime math works:
CREATE TABLE `test1` (
|
`date` datetime(3),
|
`generated` datetime(3) AS (
|
`date` - INTERVAL 1 SECOND
|
) STORED
|
);
|
As does a generated column with an IF statement that returns a datetime:
CREATE TABLE `test2` (
|
`date` datetime(3),
|
`generated` datetime(3) AS (
|
IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, `date`)
|
) STORED
|
);
|
As does returning a constant datetime:
CREATE TABLE `test3` (
|
`date` datetime(3),
|
`generated` datetime(3) AS (
|
'2000-01-01 00:00:00'
|
) STORED
|
);
|
However an IF statement that returns a constant datetime doesn't.
Of course using `VIRTUAL` rather than `STORED` works.
The docs say:
Non-deterministic built-in functions are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.
But if every part of the expression is deterministic, surely it should also be deterministic when it is put together.
Attachments
Issue Links
- links to