query_optimizer/tests/resolver/Create.test

#   Copyright 2011-2015 Quickstep Technologies LLC.
#   Copyright 2015 Pivotal Software, Inc.
#
#   Licensed 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 rowstore, TYPE columnstore);
--
ERROR: Properties must be specified at most once. (2 : 17)
(TYPE rowstore, TYPE columnstore);
                ^
==

# Unrecognized BLOCKPROPERTIES should throw.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE rowstore, INVALID_PROP val);
--
ERROR: Unrecognized property name. (2 : 17)
(TYPE 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 rowstore, SORT attr);
--
ERROR: The SORT property does not apply to this block type. (2 : 22)
(TYPE rowstore, SORT attr);
                     ^
==

# Columnstores require a sort attribute.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE columnstore);
--
ERROR: The SORT property must be specified as an attribute name. (2 : 7)
(TYPE columnstore);
      ^
==

# 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 specified as an attribute name. (2 : 7)
(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);
--
ERROR: The COMPRESS property must be specified as ALL or a list of attributes. (2 : 1)
BLOCKPROPERTIES (TYPE compresse...
^
==

# 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 rowstore, BLOCKSIZEMB ten);
--
ERROR: The BLOCKSIZEMB property must be an integer. (2 : 17)
(TYPE rowstore, BLOCKSIZEMB ten);
                ^
==

# BLOCKSIZEMB must not be 0.
CREATE TABLE foo (attr INT) WITH BLOCKPROPERTIES
(TYPE rowstore, BLOCKSIZEMB 0);
--
ERROR: The BLOCKSIZEMB property must be between 2Mb and 1000Mb. (2 : 17)
(TYPE rowstore, BLOCKSIZEMB 0);
                ^