server/src/test/resources/sql/table.iq - calcite - Git at Google

 # table.iq - Table DDL
 #
 # Licensed to the Apache Software Foundation (ASF) under one or more
 # contributor license agreements.  See the NOTICE file distributed with
 # this work for additional information regarding copyright ownership.
 # The ASF licenses this file to you under the Apache License, Version 2.0
 # (the "License"); you may not use this file except in compliance with
 # the License.  You may obtain a copy of the License at
 #
 # http://www.apache.org/licenses/LICENSE-2.0
 #
 # Unless required by applicable law or agreed to in writing, software
 # distributed under the License is distributed on an "AS IS" BASIS,
 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 # See the License for the specific language governing permissions and
 # limitations under the License.
 #
 !use server
 !set outputformat mysql

 # Create a basic table
 create table t (i int, j int not null);
 (0 rows modified)

 !update

 create table if not exists t (i int, j int not null, k date);
 (0 rows modified)

 !update

 # There is no "K" column, because table was not re-created
 select * from t;
 I INTEGER(10)
 J INTEGER(10) NOT NULL
 !type

 insert into t values (1, 2);
 (1 row modified)

 !update

 select * from t;
 +---+---+
 | I | J |
 +---+---+
 | 1 | 2 |
 +---+---+
 (1 row)

 !ok

 create table t2 like t;
 (0 rows modified)

 !update

 select * from t2;
 I INTEGER(10)
 J INTEGER(10) NOT NULL
 !type

 select * from t2;
 +---+---+
 | I | J |
 +---+---+
 +---+---+
 (0 rows)

 !ok


 truncate table t;
 (0 rows modified)

 !update

 select * from t;
 +---+---+
 | I | J |
 +---+---+
 +---+---+
 (0 rows)

 !ok

 drop table t;
 (0 rows modified)

 !update

 # Create a table with a DEFAULT column
 create table t (i int, j int default i + 2);
 (0 rows modified)

 !update

 insert into t values (1, 2);
 (1 row modified)

 !update

 insert into t (i) values (3);
 (1 row modified)

 !update

 select * from t;
 +---+---+
 | I | J |
 +---+---+
 | 1 | 2 |
 | 3 | 5 |
 +---+---+
 (2 rows)

 !ok

 drop table t2;
 (0 rows modified)

 !update

 create table t2 like t including defaults;
 (0 rows modified)

 !update

 insert into t2 values (1, 2);
 (1 row modified)

 !update

 insert into t2 (i) values (3);
 (1 row modified)

 !update

 select * from t2;
 +---+---+
 | I | J |
 +---+---+
 | 1 | 2 |
 | 3 | 5 |
 +---+---+
 (2 rows)

 !ok

 drop table t2;
 (0 rows modified)

 !update

 drop table t;
 (0 rows modified)

 !update

 # Create a table with a VIRTUAL column

 create table t (i int, j int as (i + k + 2) virtual, k int);
 (0 rows modified)

 !update

 insert into t values (1, 2, 3);
 Cannot INSERT into generated column 'J'
 !error

 insert into t (i, j) values (1, 2);
 Cannot INSERT into generated column 'J'
 !error

 insert into t (i, k) values (1, 3);
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
   EnumerableValues(tuples=[[{ 1, 3 }]])
 !plan

 insert into t (k, i) values (5, 2);
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
   EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
     EnumerableValues(tuples=[[{ 5, 2 }]])
 !plan

 select * from t;
 +---+---+---+
 | I | J | K |
 +---+---+---+
 | 1 | 6 | 3 |
 | 2 | 9 | 5 |
 +---+---+---+
 (2 rows)

 !ok
 EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)], expr#3=[2], expr#4=[+($t2, $t3)], I=[$t0], J=[$t4], K=[$t1])
   EnumerableTableScan(table=[[T]])
 !plan

 # Create a table with a STORED column
 create table t2 like t including all;
 (0 rows modified)

 !update

 insert into t2 values (1, 2, 3);
 Cannot INSERT into generated column 'J'
 !error

 insert into t2 (i, j) values (1, 2);
 Cannot INSERT into generated column 'J'
 !error

 insert into t2 (i, k) values (1, 3);
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
   EnumerableValues(tuples=[[{ 1, 3 }]])
 !plan

 insert into t2 (k, i) values (5, 2);
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
   EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
     EnumerableValues(tuples=[[{ 5, 2 }]])
 !plan

 select * from t2;
 +---+---+---+
 | I | J | K |
 +---+---+---+
 | 1 | 6 | 3 |
 | 2 | 9 | 5 |
 +---+---+---+
 (2 rows)

 !ok
 EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)], expr#3=[2], expr#4=[+($t2, $t3)], I=[$t0], J=[$t4], K=[$t1])
   EnumerableTableScan(table=[[T2]])
 !plan

 drop table t2;
 (0 rows modified)

 !update

 # Create a table excluding virtual columns
 create table t2 like t;
 (0 rows modified)

 !update

 select * from t2;
 I INTEGER(10)
 J INTEGER(10)
 K INTEGER(10)
 !type

 insert into t2 values (1, 2, 3);
 (1 row modified)

 !update

 select * from t2;
 +---+---+---+
 | I | J | K |
 +---+---+---+
 | 1 | 2 | 3 |
 +---+---+---+
 (1 row)

 !ok

 drop table t2;
 (0 rows modified)

 !update

 drop table if exists t;
 (0 rows modified)

 !update

 select * from t;
 Object 'T' not found
 !error

 drop table t;
 Table 'T' not found
 !error

 drop table if exists t;
 (0 rows modified)

 !update

 # Create a table with a VIRTUAL column of builtin function

 create table t (i varchar(5), j int as (char_length(i)) virtual, k varchar(3));
 (0 rows modified)

 !update

 insert into t values ('abcde', 5, 'de ');
 Cannot INSERT into generated column 'J'
 !error

 insert into t (i, j) values ('abcde', 5);
 Cannot INSERT into generated column 'J'
 !error

 insert into t (i, k) values ('abcde', 'de ');
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
   EnumerableValues(tuples=[[{ 'abcde', 'de ' }]])
 !plan

 insert into t (k, i) values ('de ', 'abcde');
 (1 row modified)

 !update
 EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
   EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
     EnumerableValues(tuples=[[{ 'de ', 'abcde' }]])
 !plan

 select * from t;
 +-------+---+-----+
 | I     | J | K   |
 +-------+---+-----+
 | abcde | 5 | de  |
 | abcde | 5 | de  |
 +-------+---+-----+
 (2 rows)

 !ok
 EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CHAR_LENGTH($t0)], I=[$t0], J=[$t2], K=[$t1])
   EnumerableTableScan(table=[[T]])
 !plan

 drop table if exists t;
 (0 rows modified)

 !update

 select * from t;
 Object 'T' not found
 !error

 drop table t;
 Table 'T' not found
 !error

 drop table if exists t;
 (0 rows modified)

 !update

 truncate table t;
 Table 'T' not found
 !error

 # Create a basic table with DEFAULT constraint column
 create table tdef (i int not null, col1 int default 100, col2 int default 200);
 (0 rows modified)

 !update

 insert into tdef(i, col1) values (1, DEFAULT);
 (1 row modified)

 !update

 insert into tdef(col1, i) values (DEFAULT, 2);
 (1 row modified)

 !update

 insert into tdef(i, col2) values (3, DEFAULT);
 (1 row modified)

 !update

 insert into tdef(col2, i) values (DEFAULT, 4);
 (1 row modified)

 !update


 insert into tdef values (5, DEFAULT, DEFAULT), (6, DEFAULT, 6), (7, 7, DEFAULT);
 (3 rows modified)

 !update

 select * from tdef order by i;
 +---+------+------+
 | I | COL1 | COL2 |
 +---+------+------+
 | 1 |  100 |  200 |
 | 2 |  100 |  200 |
 | 3 |  100 |  200 |
 | 4 |  100 |  200 |
 | 5 |  100 |  200 |
 | 6 |  100 |    6 |
 | 7 |    7 |  200 |
 +---+------+------+
 (7 rows)

 !ok

 # Create a basic table with DEFAULT constraint column
 create table tdef1 (i int not null, col1 int default null, col2 varchar default null);
 (0 rows modified)

 !update

 insert into tdef1(i, col1) values (1, DEFAULT);
 (1 row modified)

 !update

 insert into tdef1(i, col1, col2) values (2, DEFAULT, DEFAULT);
 (1 row modified)

 !update

 insert into tdef1(i, col1, col2) values (3, 100, DEFAULT);
 (1 row modified)

 !update

 insert into tdef1(i, col1, col2) values (4, DEFAULT, 100);
 (1 row modified)

 !update

 insert into tdef1(i) values (5);
 (1 row modified)

 !update

 select * from tdef1 order by i;
 +---+------+------+
 | I | COL1 | COL2 |
 +---+------+------+
 | 1 |      |      |
 | 2 |      |      |
 | 3 |  100 |      |
 | 4 |      | 100  |
 | 5 |      |      |
 +---+------+------+
 (5 rows)

 !ok

 # End table.iq
	# table.iq - Table DDL
	#
	# Licensed to the Apache Software Foundation (ASF) under one or more
	# contributor license agreements. See the NOTICE file distributed with
	# this work for additional information regarding copyright ownership.
	# The ASF licenses this file to you under the Apache License, Version 2.0
	# (the "License"); you may not use this file except in compliance with
	# the License. You may obtain a copy of the License at
	#
	# http://www.apache.org/licenses/LICENSE-2.0
	#
	# Unless required by applicable law or agreed to in writing, software
	# distributed under the License is distributed on an "AS IS" BASIS,
	# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	# See the License for the specific language governing permissions and
	# limitations under the License.
	#
	!use server
	!set outputformat mysql

	# Create a basic table
	create table t (i int, j int not null);
	(0 rows modified)

	!update

	create table if not exists t (i int, j int not null, k date);
	(0 rows modified)

	!update

	# There is no "K" column, because table was not re-created
	select * from t;
	I INTEGER(10)
	J INTEGER(10) NOT NULL
	!type

	insert into t values (1, 2);
	(1 row modified)

	!update

	select * from t;
	+---+---+
	\| I \| J \|
	+---+---+
	\| 1 \| 2 \|
	+---+---+
	(1 row)

	!ok

	create table t2 like t;
	(0 rows modified)

	!update

	select * from t2;
	I INTEGER(10)
	J INTEGER(10) NOT NULL
	!type

	select * from t2;
	+---+---+
	\| I \| J \|
	+---+---+
	+---+---+
	(0 rows)

	!ok


	truncate table t;
	(0 rows modified)

	!update

	select * from t;
	+---+---+
	\| I \| J \|
	+---+---+
	+---+---+
	(0 rows)

	!ok

	drop table t;
	(0 rows modified)

	!update

	# Create a table with a DEFAULT column
	create table t (i int, j int default i + 2);
	(0 rows modified)

	!update

	insert into t values (1, 2);
	(1 row modified)

	!update

	insert into t (i) values (3);
	(1 row modified)

	!update

	select * from t;
	+---+---+
	\| I \| J \|
	+---+---+
	\| 1 \| 2 \|
	\| 3 \| 5 \|
	+---+---+
	(2 rows)

	!ok

	drop table t2;
	(0 rows modified)

	!update

	create table t2 like t including defaults;
	(0 rows modified)

	!update

	insert into t2 values (1, 2);
	(1 row modified)

	!update

	insert into t2 (i) values (3);
	(1 row modified)

	!update

	select * from t2;
	+---+---+
	\| I \| J \|
	+---+---+
	\| 1 \| 2 \|
	\| 3 \| 5 \|
	+---+---+
	(2 rows)

	!ok

	drop table t2;
	(0 rows modified)

	!update

	drop table t;
	(0 rows modified)

	!update

	# Create a table with a VIRTUAL column

	create table t (i int, j int as (i + k + 2) virtual, k int);
	(0 rows modified)

	!update

	insert into t values (1, 2, 3);
	Cannot INSERT into generated column 'J'
	!error

	insert into t (i, j) values (1, 2);
	Cannot INSERT into generated column 'J'
	!error

	insert into t (i, k) values (1, 3);
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
	EnumerableValues(tuples=[[{ 1, 3 }]])
	!plan

	insert into t (k, i) values (5, 2);
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
	EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
	EnumerableValues(tuples=[[{ 5, 2 }]])
	!plan

	select * from t;
	+---+---+---+
	\| I \| J \| K \|
	+---+---+---+
	\| 1 \| 6 \| 3 \|
	\| 2 \| 9 \| 5 \|
	+---+---+---+
	(2 rows)

	!ok
	EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)], expr#3=[2], expr#4=[+($t2, $t3)], I=[$t0], J=[$t4], K=[$t1])
	EnumerableTableScan(table=[[T]])
	!plan

	# Create a table with a STORED column
	create table t2 like t including all;
	(0 rows modified)

	!update

	insert into t2 values (1, 2, 3);
	Cannot INSERT into generated column 'J'
	!error

	insert into t2 (i, j) values (1, 2);
	Cannot INSERT into generated column 'J'
	!error

	insert into t2 (i, k) values (1, 3);
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
	EnumerableValues(tuples=[[{ 1, 3 }]])
	!plan

	insert into t2 (k, i) values (5, 2);
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T2]], operation=[INSERT], flattened=[false])
	EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
	EnumerableValues(tuples=[[{ 5, 2 }]])
	!plan

	select * from t2;
	+---+---+---+
	\| I \| J \| K \|
	+---+---+---+
	\| 1 \| 6 \| 3 \|
	\| 2 \| 9 \| 5 \|
	+---+---+---+
	(2 rows)

	!ok
	EnumerableCalc(expr#0..1=[{inputs}], expr#2=[+($t0, $t1)], expr#3=[2], expr#4=[+($t2, $t3)], I=[$t0], J=[$t4], K=[$t1])
	EnumerableTableScan(table=[[T2]])
	!plan

	drop table t2;
	(0 rows modified)

	!update

	# Create a table excluding virtual columns
	create table t2 like t;
	(0 rows modified)

	!update

	select * from t2;
	I INTEGER(10)
	J INTEGER(10)
	K INTEGER(10)
	!type

	insert into t2 values (1, 2, 3);
	(1 row modified)

	!update

	select * from t2;
	+---+---+---+
	\| I \| J \| K \|
	+---+---+---+
	\| 1 \| 2 \| 3 \|
	+---+---+---+
	(1 row)

	!ok

	drop table t2;
	(0 rows modified)

	!update

	drop table if exists t;
	(0 rows modified)

	!update

	select * from t;
	Object 'T' not found
	!error

	drop table t;
	Table 'T' not found
	!error

	drop table if exists t;
	(0 rows modified)

	!update

	# Create a table with a VIRTUAL column of builtin function

	create table t (i varchar(5), j int as (char_length(i)) virtual, k varchar(3));
	(0 rows modified)

	!update

	insert into t values ('abcde', 5, 'de ');
	Cannot INSERT into generated column 'J'
	!error

	insert into t (i, j) values ('abcde', 5);
	Cannot INSERT into generated column 'J'
	!error

	insert into t (i, k) values ('abcde', 'de ');
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
	EnumerableValues(tuples=[[{ 'abcde', 'de ' }]])
	!plan

	insert into t (k, i) values ('de ', 'abcde');
	(1 row modified)

	!update
	EnumerableTableModify(table=[[T]], operation=[INSERT], flattened=[false])
	EnumerableCalc(expr#0..1=[{inputs}], I=[$t1], K=[$t0])
	EnumerableValues(tuples=[[{ 'de ', 'abcde' }]])
	!plan

	select * from t;
	+-------+---+-----+
	\| I \| J \| K \|
	+-------+---+-----+
	\| abcde \| 5 \| de \|
	\| abcde \| 5 \| de \|
	+-------+---+-----+
	(2 rows)

	!ok
	EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CHAR_LENGTH($t0)], I=[$t0], J=[$t2], K=[$t1])
	EnumerableTableScan(table=[[T]])
	!plan

	drop table if exists t;
	(0 rows modified)

	!update

	select * from t;
	Object 'T' not found
	!error

	drop table t;
	Table 'T' not found
	!error

	drop table if exists t;
	(0 rows modified)

	!update

	truncate table t;
	Table 'T' not found
	!error

	# Create a basic table with DEFAULT constraint column
	create table tdef (i int not null, col1 int default 100, col2 int default 200);
	(0 rows modified)

	!update

	insert into tdef(i, col1) values (1, DEFAULT);
	(1 row modified)

	!update

	insert into tdef(col1, i) values (DEFAULT, 2);
	(1 row modified)

	!update

	insert into tdef(i, col2) values (3, DEFAULT);
	(1 row modified)

	!update

	insert into tdef(col2, i) values (DEFAULT, 4);
	(1 row modified)

	!update


	insert into tdef values (5, DEFAULT, DEFAULT), (6, DEFAULT, 6), (7, 7, DEFAULT);
	(3 rows modified)

	!update

	select * from tdef order by i;
	+---+------+------+
	\| I \| COL1 \| COL2 \|
	+---+------+------+
	\| 1 \| 100 \| 200 \|
	\| 2 \| 100 \| 200 \|
	\| 3 \| 100 \| 200 \|
	\| 4 \| 100 \| 200 \|
	\| 5 \| 100 \| 200 \|
	\| 6 \| 100 \| 6 \|
	\| 7 \| 7 \| 200 \|
	+---+------+------+
	(7 rows)

	!ok

	# Create a basic table with DEFAULT constraint column
	create table tdef1 (i int not null, col1 int default null, col2 varchar default null);
	(0 rows modified)

	!update

	insert into tdef1(i, col1) values (1, DEFAULT);
	(1 row modified)

	!update

	insert into tdef1(i, col1, col2) values (2, DEFAULT, DEFAULT);
	(1 row modified)

	!update

	insert into tdef1(i, col1, col2) values (3, 100, DEFAULT);
	(1 row modified)

	!update

	insert into tdef1(i, col1, col2) values (4, DEFAULT, 100);
	(1 row modified)

	!update

	insert into tdef1(i) values (5);
	(1 row modified)

	!update

	select * from tdef1 order by i;
	+---+------+------+
	\| I \| COL1 \| COL2 \|
	+---+------+------+
	\| 1 \| \| \|
	\| 2 \| \| \|
	\| 3 \| 100 \| \|
	\| 4 \| \| 100 \|
	\| 5 \| \| \|
	+---+------+------+
	(5 rows)

	!ok

	# End table.iq