Cannot add persistent generated datetime column with if statement

Question

I'm confused at the behaviour of persistent generated columns in Mariadb when it comes to IF statements and dates. The following works:

CREATE TABLE `test1` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      `date` - INTERVAL 1 SECOND
  ) STORED
);

As does this:

CREATE TABLE `test2` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, `date`)
  ) STORED
);

As does this:

CREATE TABLE `test3` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      '2000-01-01 00:00:00'
  ) STORED
);

But this doesn't:

CREATE TABLE `test4` (
    `date` datetime(3),
    `generated` datetime(3) AS (
      IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, '2000-01-01 00:00:00')
  ) STORED
);

It throws the following error:

ERROR 1901 (HY000) at line 16: 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`

I've put together these examples here: https://onecompiler.com/mariadb/43hnm9j7r, but have also tested locally with mariadb 10.11, and the behaviour in mysql seems to be the same.

Making the generated column VIRTUAL rather than STORED fixes the problem, as expected, but in my use case I'd like to be able to add an index on the generated column.

The docs say:

Non-deterministic built-in functions are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.

So what part of the final example there is non-deterministic, when all of its component parts work fine?

Since you are using MariaDB, not MySQL, I removed the 'mysql' tag. These two databases are not the same, and they are not compatible. One may have bugs the other does not, and solutions for one are not necessarily relevant to the other. For example, your table test4 works without error in MySQL 8.4. — Bill Karwin
– Bill Karwin, Commented May 13 at 22:42
@BillKarwin They said in the question that the problem also exists in MySQL. Maybe it's version-dependent and has been fixed. — Barmar
– Barmar, Commented May 14 at 15:22
Okay thanks, but I infer from that the query would fail in MariaDB 10.11 and 11.4, as it does in MariaDB 10.5 through 10.9. So this is still a MariaDB problem only, and is not reproduced in MySQL, in spite of the OP's statement. — Bill Karwin
– Bill Karwin, Commented May 14 at 21:45

danblack · Accepted Answer · 2025-05-15 23:48:47Z

Adapted from Sergei Golubchik answer on the JIRA bug report;

The cause is related to sql_mode=TIME_ROUND_FRACTIONAL. In your first example you have

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

An expression;

first argument of IF is datetime(3)
second argument of IF is a string
IF needs to aggregate them to a single type, it'll be temporal, datetime, but with max possible precision (because the string can contain anything, precision isn't fixed), thus IF returns datetime(6).
when storing datetime(6) into generated datetime(3) the server can either do truncation or rounding, it depends on sql_mode, that is the value depends on the environment, thus cannot be stored.

The easiest solution for you would be to use not a string but a temporal literal:

  IF(`date` IS NOT NULL, `date` - INTERVAL 1 SECOND, TIMESTAMP'2000-01-01 00:00:00')

Collectives™ on Stack Overflow

Cannot add persistent generated datetime column with if statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related