Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1991

In CREATE TABLE, allow generated columns (both virtual and stored), CHECK constraints, and DEFAULT clause

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.15.0
    • Component/s: spatial
    • Labels:
      None

      Description

      Extend the CREATE TABLE statement added to the "server" module in CALCITE-707 with generated columns (both VIRTUAL and STORED), CHECK constraints, and DEFAULT clause.

      Details:

      • There are two kinds of generated columns: virtual and stored; we refer to the latter as "stored generated columns".
      • Stored generated columns are calculated from an expression when a row is inserted into the table and stored in that table.
      • Virtual columns are calculated from an expression when a row is read from a table.
      • The DEFAULT clause provides a value when the column is not mentioned in the INSERT.
      • Like columns with a DEFAULT, generated columns have an associated expression; but unlike columns with a DEFAULT, generated columns may not be specified in an INSERT statement (with a small exception involving the DEFAULT expression, described below).
      • A CHECK constraint checks that a given condition is not false before inserting a row.
      • When query involving a table with a stored generated column is planned, the planner sees a constraint as if there were a CHECK constraint on that column.

      Comparison to other databases. Some databases confusingly use "virtual" as an umbrella term for both stored and non-stored:

      • MySQL's "generated columns" may be declared either "stored" or "virtual", using the STORED and VIRTUAL keywords;
      • MariaDB's "virtual (computed)" columns may be tagged VIRTUAL, PERSISTENT, STORED;
      • MSSQL's "computed columns" may be virtual or persisted, with the PERSISTED keyword;
      • Oracle only has "virtual columns", and the VIRTUAL keyword;
      • DB2 has "generated columns" which are always stored, and neither VIRTUAL nor STORED keyword.

      Example:

      CREATE TABLE Foo (
        i INTEGER NOT NULL,
        j INTEGER,
        k INTEGER AS (i + 1), // virtual
        m INTEGER AS (i + 2) STORED, // stored generated
        n INTEGER DEFAULT (i + 4), // has default
        o INTEGER, // constrained
        CHECK (o = i + 4));
      

      If neither VIRTUAL nor STORED is specified, VIRTUAL is the default.

      You can insert into a generated column only if the expression is DEFAULT:

      > INSERT INTO t (i, o) VALUES (1, 5);
      Error: Cannot INSERT into generated column
      > INSERT INTO t (i, o) SELECT empno, deptno FROM emp;
      Error: Cannot INSERT into generated column
      > INSERT INTO t (i, o) VALUES (1, DEFAULT);
      OK.
      > INSERT INTO t (i) VALUES (1);
      OK.
      

      We allow GENERATED ALWAYS before AS, and CONSTRAINT name before CHECK.

      A CHECK constraint is satisfied if it evaluates to TRUE or UNKNOWN. Thus the following statement would insert one row:

      INSERT INTO Foo (i, o) VALUES (1, NULL)
      

      To prevent NULL values, in other words to force o = i + 4 to always be TRUE, write the constraint as follows:

      CREATE TABLE Foo(
        i INTEGER,
        o INTEGER,
        CHECK ((o = i + 4) IS NOT FALSE))
      

      Not part of this proposal:

      • CHECK as part of a column definition;
      • Invisible columns. Oracle has these, and they solve the problem that INSERT INTO t SELECT * FROM u fails if t has any generated columns;
      • A variant of the DEFAULT clause that provides values when the incoming value is NULL.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: