[
https://issues.apache.org/jira/browse/CALCITE-1991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16177094#comment-16177094
]
Julian Hyde commented on CALCITE-1991:
--------------------------------------
This feature is necessary for spatial indexes (see CALCITE-1861), because it
allows you to define a column {{h = hilbert(x, y)}}, either as a stored
generated column or as a regular column with a CHECK constraint.
> In CREATE TABLE, allow generated columns (both virtual and stored), CHECK
> constraints, and DEFAULT clause
> ---------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-1991
> URL: https://issues.apache.org/jira/browse/CALCITE-1991
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
>
> 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|https://en.wikipedia.org/wiki/Virtual_column].
> 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)|https://mariadb.com/kb/en/library/virtual-computed-columns/]";
> 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|https://docs.oracle.com/database/122/SQLRF/CREATE-TABLE.htm#SQLRF01402]";,
> and the VIRTUAL keyword;
> * DB2 has "[generated
> columns|https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html|]"
> which are always stored, and neither VIRTUAL nor STORED keyword.
> Example:
> {code}
> 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));
> {code}
> If neither {{VIRTUAL}} nor {{STORED}} is specified, {{VIRTUAL}} is the
> default.
> You can insert into a generated column only if the expression is DEFAULT:
> {code}
> > 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.
> {code}
> 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:
> {code}
> INSERT INTO Foo (i, o) VALUES (1, NULL)
> {code}
> To prevent NULL values, in other words to force {{o = i + 4}} to always be
> TRUE, write the constraint as follows:
> {code}
> CREATE TABLE Foo(
> i INTEGER,
> o INTEGER,
> CHECK ((o = i + 4) IS NOT FALSE))
> {code}
> 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.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
