Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-36806

Can't create persistent generated datetime column with constant date in IF statement

    XMLWordPrintable

Details

    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

          Activity

            People

              serg Sergei Golubchik
              j-waters James Waters
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.