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.
- 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.
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.
- depends upon
CALCITE-707 Built-in support for simple DDL statements
CALCITE-1861 Spatial index, based on Hilbert space-filling curve