query_optimizer/tests/resolver/Create.test

# 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.

[default initial_logical_plan]
create table test (col int)
--
ERROR: Relation test already exists (1 : 14)
create table test (col int)
             ^
==

create table foo (col1 int not null,
                  col2 long,
                  col3 double,
                  col4 float,
                  col5 char(5),
                  col6 varchar(5),
                  col7 int null,
                  col8 long null,
                  col9 double null,
                  col10 float null,
                  col11 char(5) null,
                  col12 varchar(5) null)
--
TopLevelPlan
+-plan=CreateTable[relation=foo]
| +-attributes=
|   +-AttributeReference[id=0,name=col1,relation=foo,type=Int]
|   +-AttributeReference[id=1,name=col2,relation=foo,type=Long]
|   +-AttributeReference[id=2,name=col3,relation=foo,type=Double]
|   +-AttributeReference[id=3,name=col4,relation=foo,type=Float]
|   +-AttributeReference[id=4,name=col5,relation=foo,type=Char(5)]
|   +-AttributeReference[id=5,name=col6,relation=foo,type=VarChar(5)]
|   +-AttributeReference[id=6,name=col7,relation=foo,type=Int NULL]
|   +-AttributeReference[id=7,name=col8,relation=foo,type=Long NULL]
|   +-AttributeReference[id=8,name=col9,relation=foo,type=Double NULL]
|   +-AttributeReference[id=9,name=col10,relation=foo,type=Float NULL]
|   +-AttributeReference[id=10,name=col11,relation=foo,type=Char(5) NULL]
|   +-AttributeReference[id=11,name=col12,relation=foo,type=VarChar(5) NULL]
+-output_attributes=
  +-AttributeReference[id=0,name=col1,relation=foo,type=Int]
  +-AttributeReference[id=1,name=col2,relation=foo,type=Long]
  +-AttributeReference[id=2,name=col3,relation=foo,type=Double]
  +-AttributeReference[id=3,name=col4,relation=foo,type=Float]
  +-AttributeReference[id=4,name=col5,relation=foo,type=Char(5)]
  +-AttributeReference[id=5,name=col6,relation=foo,type=VarChar(5)]
  +-AttributeReference[id=6,name=col7,relation=foo,type=Int NULL]
  +-AttributeReference[id=7,name=col8,relation=foo,type=Long NULL]
  +-AttributeReference[id=8,name=col9,relation=foo,type=Double NULL]
  +-AttributeReference[id=9,name=col10,relation=foo,type=Float NULL]
  +-AttributeReference[id=10,name=col11,relation=foo,type=Char(5) NULL]
  +-AttributeReference[id=11,name=col12,relation=foo,type=VarChar(5) NULL]
==

create table foo (col1 int, col1 int)
--
ERROR: Column col1 is specified more than once (1 : 29)
create table foo (col1 int, col1 int)
                            ^
==

create table foo (col1 int, CoL1 int)
--
ERROR: Column CoL1 is specified more than once (1 : 29)
create table foo (col1 int, CoL1 int)
                            ^
==

# "_qstemp_result_" is preserved for internal use.
create table "_qstemp_result_s" (col int)
--
ERROR: Relation name cannot start with _qstemp_result_ (1 : 14)
create table "_qstemp_result_s" (col int)
             ^
==

# Cannot repeat Block properties.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE columnstore, TYPE columnstore);
--
ERROR: Properties must be specified at most once. (2 : 20)
(TYPE columnstore, TYPE columnstore);
                   ^
==

# Unrecognized BLOCKPROPERTIES should throw.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, INVALID_PROP val);
--
ERROR: Unrecognized property name. (2 : 23)
(TYPE split_rowstore, INVALID_PROP val);
                      ^
==

# TYPE property only accepts names of storage blocks.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE not_a_type);
--
ERROR: Unrecognized storage type. (2 : 7)
(TYPE not_a_type);
      ^
==

# TYPE property must be specified.
CREATE TABLE foo (attr INT) WITH
BLOCKPROPERTIES (BLOCKSIZEMB 10);
--
ERROR: TYPE property must be specified and be a string. (2 : 1)
BLOCKPROPERTIES (BLOCKSIZEMB 1...
^
==

# Rowstores cannot have a sorted attribute.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, SORT attr);
--
ERROR: The SORT property does not apply to this block type. (2 : 28)
(TYPE split_rowstore, SORT attr);
                           ^
==

# Columnstores do not require a sort attribute.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE columnstore);
--
TopLevelPlan
+-plan=CreateTable[relation=foo]
| +-block_properties=ProtoDescription
| | +-Property=ProtoProperty[Property=blocktype,Value=columnstore]
| | +-Property=ProtoProperty[Property=slots,Value=1]
| +-attributes=
|   +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
==

# Columnstores have a optional sort attribute.
CREATE TABLE foo (attr INT, attr2 INT) WITH BLOCKPROPERTIES
(TYPE columnstore, SORT attr2);
--
TopLevelPlan
+-plan=CreateTable[relation=foo]
| +-block_properties=ProtoDescription
| | +-Property=ProtoProperty[Property=blocktype,Value=columnstore]
| | +-Property=ProtoProperty[Property=sort,Value=1]
| | +-Property=ProtoProperty[Property=slots,Value=1]
| +-attributes=
|   +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
|   +-AttributeReference[id=1,name=attr2,relation=foo,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
  +-AttributeReference[id=1,name=attr2,relation=foo,type=Int]
==

# Non-existant columns should be caught.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE columnstore, SORT not_attr);
--
ERROR: The SORT property did not match any attribute name. (2 : 25)
(TYPE columnstore, SORT not_attr);
                        ^
==

# Sort property must be a string type, not a list.
CREATE TABLE foo (attr INT, attr2 INT) WITH BLOCKPROPERTIES
(TYPE columnstore, SORT (attr, attr2));
--
ERROR: The SORT property must be a string, not a string list. (2 : 20)
(TYPE columnstore, SORT (attr, attr2));
                   ^
==
# Compress should only be specified on compressed blocks.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE columnstore, SORT attr, COMPRESS ALL);
--
ERROR: The COMPRESS property does not apply to this block type. (2 : 7)
(TYPE columnstore, SORT attr, COMPRES...
      ^
==

# Compress property is required for compressed blocks.
CREATE TABLE foo (attr INT) WITH
BLOCKPROPERTIES (TYPE compressed_rowstore);
--
ERROR: The COMPRESS property must be specified as ALL or a list of attributes. (2 : 1)
BLOCKPROPERTIES (TYPE compresse...
^
==

# Compress cannot have an integer value
CREATE TABLE foo (attr INT, attr2 INT) WITH
BLOCKPROPERTIES (TYPE compressed_rowstore, COMPRESS 1);
--
[same as above]
==

# All specified COMPRESS columns must exist.
CREATE TABLE foo (attr INT, attr2 INT) WITH BLOCKPROPERTIES
(TYPE compressed_rowstore, COMPRESS (attr, attr3));
--
ERROR: The given attribute was not found. (2 : 44)
...TYPE compressed_rowstore, COMPRESS (attr, attr3));
                                             ^
==

# BLOCKSIZEMB property must be an integer.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, BLOCKSIZEMB ten);
--
ERROR: The BLOCKSIZEMB property must be an integer. (2 : 23)
(TYPE split_rowstore, BLOCKSIZEMB ten);
                      ^
==

# BLOCKSIZEMB property must be multiple times of the slot size.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, BLOCKSIZEMB 25);
--
ERROR: The BLOCKSIZEMB property must be multiple times of 2MB. (2 : 23)
(TYPE split_rowstore, BLOCKSIZEMB 25);
                      ^
==

# BLOCKSIZEMB must be greater than the minimum (defined in StorageConstants.hpp).
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, BLOCKSIZEMB 0);
--
ERROR: The BLOCKSIZEMB property must be between 2MB and 1024MB. (2 : 23)
(TYPE split_rowstore, BLOCKSIZEMB 0);
                      ^
==

# BLOCKSIZEMB must be less than the maximum (defined in StorageConstants.hpp).
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE split_rowstore, BLOCKSIZEMB 2000);
--
ERROR: The BLOCKSIZEMB property must be between 2MB and 1024MB. (2 : 23)
(TYPE split_rowstore, BLOCKSIZEMB 2000);
                      ^
==

CREATE TABLE foo (attr INT) PARTITION BY HASH(attr) PARTITIONS 4;
--
TopLevelPlan
+-plan=CreateTable[relation=foo]
| +-partition_scheme_header=ProtoDescription
| | +-Property=ProtoProperty[Property=partition_type,Value=hash]
| | +-Property=ProtoProperty[Property=num_partitions,Value=4]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=0]
| +-attributes=
|   +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
+-output_attributes=
  +-AttributeReference[id=0,name=attr,relation=foo,type=Int]
==

CREATE TABLE foo (attr1 INT, attr2 LONG, attr3 FLOAT, attr4 DOUBLE, attr5 CHAR(5), attr6 VARCHAR(4))
PARTITION BY HASH(attr1, attr2, attr3, attr4, attr5, attr6) PARTITIONS 4;
--
TopLevelPlan
+-plan=CreateTable[relation=foo]
| +-partition_scheme_header=ProtoDescription
| | +-Property=ProtoProperty[Property=partition_type,Value=hash]
| | +-Property=ProtoProperty[Property=num_partitions,Value=4]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=0]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=1]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=2]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=3]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=4]
| | +-Property=ProtoProperty[Property=partition_attr_id,Value=5]
| +-attributes=
|   +-AttributeReference[id=0,name=attr1,relation=foo,type=Int]
|   +-AttributeReference[id=1,name=attr2,relation=foo,type=Long]
|   +-AttributeReference[id=2,name=attr3,relation=foo,type=Float]
|   +-AttributeReference[id=3,name=attr4,relation=foo,type=Double]
|   +-AttributeReference[id=4,name=attr5,relation=foo,type=Char(5)]
|   +-AttributeReference[id=5,name=attr6,relation=foo,type=VarChar(4)]
+-output_attributes=
  +-AttributeReference[id=0,name=attr1,relation=foo,type=Int]
  +-AttributeReference[id=1,name=attr2,relation=foo,type=Long]
  +-AttributeReference[id=2,name=attr3,relation=foo,type=Float]
  +-AttributeReference[id=3,name=attr4,relation=foo,type=Double]
  +-AttributeReference[id=4,name=attr5,relation=foo,type=Char(5)]
  +-AttributeReference[id=5,name=attr6,relation=foo,type=VarChar(4)]