You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2017/09/22 20:46:01 UTC
[jira] [Created] (CALCITE-1991) In CREATE TABLE, allow generated
columns (both virtual and stored), CHECK constraints, and DEFAULT clause
Julian Hyde created CALCITE-1991:
------------------------------------
Summary: 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)