core/sql/regress/seabase/TEST040

-- @@@ START COPYRIGHT @@@
--
-- 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.
--
-- @@@ END COPYRIGHT @@@
--
-- test for CREATE TABLE LIKE
--

log LOG040 clear;

?section set_up

-- set-up

drop schema if exists t040sch cascade;

create schema t040sch;

set schema t040sch;

create table t040salt(a int not null, b int not null, c int, primary key (a,b))
 salt using 4 partitions;

showddl t040salt;

create table t040nosalt(a int not null, b int not null, c int, primary key (a,b));

showddl t040nosalt;

create table t040bigcols(a int not null,
                         b char(1000) character set iso88591,
                         c varchar(1001) character set iso88591,
                         d char(1002) character set utf8,
                         e varchar(1003) character set utf8,
                         f char(1004) character set ucs2,
                         g varchar(1005) character set ucs2,
                         primary key (a));

showddl t040bigcols;

?section positive_tests

-- positive tests

create table t040salt1 like t040salt without salt;

showddl t040salt1;

create table t040salt5 like t040salt salt using 5 partitions;

showddl t040salt5;

create table t040salt6 like t040salt salt using 6 partitions on (a);

showddl t040salt6;

create table t040salt7 like t040salt salt using 7 partitions on (b);

showddl t040salt7;

create table t040salt8 like t040salt salt using 8 partitions on (a,b);

showddl t040salt8;

create table t040nosalt1 like t040nosalt without salt;

showddl t040nosalt1;

create table t040nosalt4 like t040nosalt salt using 4 partitions;

showddl t040nosalt4;

create table t040saltsame like t040salt;

showddl t040saltsame;

create table t040nosaltsame like t040nosalt;

showddl t040nosaltsame;

create table t040limitedcols like t040bigcols 
limit column length to 256;

showddl t040limitedcols;

create table t040somelimitedcols like t040bigcols 
limit column length to 1002;

showddl t040somelimitedcols;

create table t040fewerlimitedcols like t040bigcols 
limit column length to 2010;

showddl t040fewerlimitedcols;

create table t040nolimitedcols like t040bigcols 
limit column length to 4012;

showddl t040nolimitedcols;

?section negative_tests

-- negative tests

-- non-key column, should fail
create table t040saltbad1 like t040salt salt using 5 partitions on (c);

-- non-existent column, should fail
create table t040saltbad2 like t040salt salt using 5 partitions on (d);

-- should fail with error 3154
create table t040saltbad3 like t040salt without salt salt using 4 partitions;

-- should fail, duplicate clauses
create table t040saltbad4 like t040salt without salt without salt;

-- and now an extravaganza of errors
create table t040saltbad5 like t040salt salt using 4 partitions without salt salt using 7 partitions;

-- syntax error; clause not supported with LIKE
create table t040saltbad6 like t040salt store by (b);

log;

?section clean_up

-- clean up

drop schema if exists t040sch cascade;