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

No value resolution for non-deterministic defaults

    XMLWordPrintable

Details

    Description

      Values like nextval and current_timestamp are non-deterministic. For those values, default call chains are not properly resolved.

      create sequence s;
      create table t(x int default (nextval(s)), y int default(x));
      insert t(x) values (default);
      insert t(y) values (default);
      

      We would expect the value of 2 inserted to y on the second insert, but instead we'll have NULL:

      select * from t;
      x       y
      1       1
      2       NULL
      

      Example with current_timestamp:

      create table t(x timestamp default current_timestamp, y timestamp default(x));
      insert t(x) values (default);
      insert t(y) values (default(y));
      select * from t;
      x       y
      2025-06-27 14:40:43     2025-06-27 14:40:43
      2025-06-27 14:40:43     NULL
      

      For non-deterministic expressions, we'd have to make defaults a part an expression resolution tree, like it's done for vcols. Here instead, a value for y is fetched before x is calculated.

      For deterministic expressions, the value is stored in the default_values record and is restored before each insert. For non-deterministic defaults, the value of NULL is stored there.

      Attachments

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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