core/sql/regress/seabase/TEST003

-- @@@ 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 @@@

-- Tests for tinyint, largeint unsigned, binary datatypes


obey TEST003(clean_up);

log LOG003 clear;

cqd traf_tinyint_support 'ON';
cqd traf_tinyint_return_values 'ON';
cqd traf_tinyint_input_params 'ON';

obey TEST003(setup_tiny);
obey TEST003(dml_tiny);
obey TEST003(hive_tiny);
obey TEST003(errors_tiny);

cqd traf_tinyint_support 'ON';
cqd traf_tinyint_return_values 'OFF';
cqd traf_tinyint_input_params 'OFF';

obey TEST003(setup_tiny);
obey TEST003(dml_tiny);
obey TEST003(hive_tiny);
obey TEST003(errors_tiny);

cqd traf_largeint_unsigned_io 'ON';
cqd traf_create_signed_numeric_literal 'ON';
obey TEST003(setup_lu);
obey TEST003(dml_lu);
obey TEST003(errors_lu);

cqd traf_create_signed_numeric_literal 'OFF';
obey TEST003(setup_lu);
obey TEST003(dml_lu);
obey TEST003(errors_lu);

cqd traf_create_signed_numeric_literal 'ON';
cqd traf_largeint_unsigned_io 'OFF';
obey TEST003(setup_lu);
obey TEST003(dml_lu);
obey TEST003(errors_lu);

cqd traf_boolean_io 'ON';
obey TEST003(setup_bool);
obey TEST003(dml_bool);
obey TEST003(errors_bool);
obey TEST003(hive_bool);

cqd traf_boolean_io 'OFF';
obey TEST003(setup_bool);
obey TEST003(dml_bool_short);

log;
exit;

?section setup_tiny
-----------------------------------------------------------
-------------- TINYINT datatype ---------------------------
-----------------------------------------------------------

drop table if exists t003t1 cascade;
drop table if exists t003t1_like;
drop table if exists t003t1_as;

create table t003t1(a tinyint not null primary key, b tinyint, 
                    c tinyint unsigned default 10 not null, d tinyint unsigned);
invoke t003t1;

create table t003t1_like like t003t1;
invoke t003t1_like;

create table t003t1_as primary key (a) as select * from t003t1;
invoke t003t1_as;

create view t003t1_view as select * from t003t1;
invoke t003t1_view;

?section dml_tiny
insert into t003t1 values (1, 2, 3, 4);
insert into t003t1 values (-1, -2, 255, 255);
insert into t003t1 values (-128, 127, 0, 0);

select * from t003t1;

insert into t003t1_as select * from t003t1;
select * from t003t1_as;

select * from t003t1 where a = 1;
select * from t003t1 where a = -1;
select * from t003t1 where a < 1;
select * from t003t1 where a <= -1;
select * from t003t1 where a < 1000;
select * from t003t1 where a < 100000;
select * from t003t1 where a > -1000;
select * from t003t1 where a > -100000;

select * from t003t1 where d = 4;
select * from t003t1 where d < -1;
select * from t003t1 where d < 1000;
select * from t003t1 where d > -1000;

select a+10 from t003t1 where a = 1 or a = -1;

select cast(100 as tinyint unsigned) from (values(1)) x(a);
select cast(-100 as tinyint) from (values(1)) x(a);

select cast(a as char(10)), cast (b as varchar(11)) from t003t1;

prepare s from insert into t003t1 values (?, ?, ?, ?);
execute s using -3, 10, 251, 0;
select * from t003t1;

begin work;
delete from t003t1 where b = -2;
select * from t003t1;
rollback work;

begin work;
update t003t1 set b = b + 1 where b <> 127;
select * from t003t1;
rollback work;

?section hive_tiny
process hive statement 'drop table ttiny';
process hive statement 'create table ttiny(a tinyint, b tinyint)';
sh echo "insert into ttiny values (1, -1);" > TEST003_junk;
sh regrhive.ksh -f TEST003_junk;

invoke hive.hive.ttiny;
select * from hive.hive.ttiny;
insert into hive.hive.ttiny values (127, 10), (-128, -50);
select * from hive.hive.ttiny;
insert overwrite table hive.hive.ttiny select a, b from t003t1;
select * from hive.hive.ttiny;

?section errors_tiny
update t003t1 set b = b + 1;
select cast(1.0 as numeric(1,1) not null) from (values(1)) x(a);

delete from t003t1;

insert into t003t1 values (128, 2, 3, 4);
insert into t003t1 values (2, -129, 3, 4);
insert into t003t1 values (3, 4, 256, 4);
insert into t003t1 values (4, 4, 256, -4);

select cast(-1 as tinyint unsigned) from (values(1)) x(a);
select cast(256 as tinyint unsigned) from (values(1)) x(a);
select cast(-129 as tinyint) from (values(1)) x(a);
select cast(128 as tinyint) from (values(1)) x(a);

prepare s from insert into t003t1 values (?, ?, ?, ?);
execute s using 128, 2, 3, 4;
execute s using 4, 4, 256, -4;


?section setup_lu
drop table if exists t003t2 cascade;
drop table if exists t003t2_like;
drop table if exists t003t2_as;

create table t003t2(a largeint unsigned not null primary key, b largeint unsigned);
invoke t003t2;

create table t003t2_like like t003t2;
invoke t003t2_like;

create table t003t2_as primary key(a) as select * from t003t2;
invoke t003t2_as;

create view t003t2_view as select * from t003t2;
invoke t003t2_view;

?section dml_lu
insert into t003t2 values (1, 2);
insert into t003t2 values (18446744073709551615, 18446744073709551615);

select * from t003t2;

insert into t003t2_as select * from t003t2;
select * from t003t2_as;

select * from t003t2 where a = 1;
select * from t003t2 where a = -1;
select * from t003t2 where a < 1;
select * from t003t2 where a <= -1;
select * from t003t2 where a < 1000;
select * from t003t2 where a > -1000;
select * from t003t2 where a = 18446744073709551615;

select * from t003t2 where b = 2;
select * from t003t2 where b < -1;
select * from t003t2 where b < 1000;
select * from t003t2 where b > -1000;
select * from t003t2 where b = 18446744073709551615;

select a, cast(cast(a as varchar(40)) as largeint unsigned) from t003t2;

select cast('-9223372036854775808' as largeint) from (values(1)) x(a);
select cast('-9223372036854775809' as largeint) from (values(1)) x(a);

select a+10 from t003t2 where a = 1 or a = -1;

select cast(100 as largeint unsigned) from (values(1)) x(a);

select cast(a as char(21)), cast (b as varchar(22)) from t003t2;

prepare s from insert into t003t2 values (?, ?);
execute s using 251, 0;
execute s using 11, 18446744073709551615;
select * from t003t2;

begin work;
delete from t003t2 where b = 1;
select * from t003t2;
rollback work;

begin work;
update t003t2 set b = b + 1 where b = 2;
select * from t003t2;
rollback work;

?section errors_lu
update t003t2 set b = b + 1;

delete from t003t2;

insert into t003t2 values (18446744073709551616, 2);
insert into t003t2 values (2, 18446744073709551616);
insert into t003t2 values (-1, 1);

select cast(-1 as largeint unsigned) from (values(1)) x(a);
select cast(18446744073709551616 as largeint unsigned) from (values(1)) x(a);

prepare s from insert into t003t2 values (?, ?);
execute s using 18446744073709551616, 2;


?section setup_bool
-----------------------------------------------------------
-------------- BOOLEAN datatype ---------------------------
-----------------------------------------------------------

drop table if exists t003t3 cascade;
drop table if exists t003t3_like;
drop table if exists t003t3_as;

create table if not exists t003t3(a boolean not null primary key, b boolean)
  attribute aligned format;
invoke t003t3;

create table if not exists t003t3_like like t003t3;
invoke t003t3_like;

create table if not exists t003t3_as primary key (a) as select * from t003t3;
invoke t003t3_as;

create table if not exists t003t3_salt(a boolean not null primary key)
  salt using 2 partitions;
invoke t003t3_salt;

create view t003t3_view as select * from t003t3;
invoke t003t3_view;

?section dml_bool
insert into t003t3 values (true, true);
insert into t003t3 values (false, false);

select * from t003t3;

insert into t003t3_as select * from t003t3;
select * from t003t3_as;

select * from t003t3 where a = true;
select * from t003t3 where a = false;
select * from t003t3 where b = true;
select * from t003t3 where b = false;
select * from t003t3 where a != true;
select * from t003t3 where a != false;
select * from t003t3 where b != true;
select * from t003t3 where b != false;
select * from t003t3 where a is null;
select * from t003t3 where b is not null;

select * from t003t3 where a = true or a = false;
select * from t003t3 where b = true or b = false;

select cast('true' as boolean) from (values(1)) x(a);
select cast(' faLse  ' as boolean) from (values(1)) x(a);

select cast(true as boolean not null) from (values(1)) x(a);
select cast(false as boolean) from (values(1)) x(a);

select cast(a as char(10)), cast (b as varchar(11)) from t003t3;

delete from t003t3;
prepare s from insert into t003t3 values (?, ?);
execute s using true, true;
execute s using 'false', ' falSE  ';
select * from t003t3;

select !a, !b from t003t3;
select * from t003t3 where !a = false;
update t003t3 set b = !b;
select * from t003t3;

begin work;
delete from t003t3 where b = false;
select * from t003t3;
rollback work;
select * from t003t3;

begin work;
update t003t3 set b = false where b != false;
select * from t003t3;
rollback work;
select * from t003t3;

?section hive_bool
process hive statement 'drop table tbool';
process hive statement 'create table tbool(a boolean)';
sh echo "insert into tbool values (true), (false), (NULL);" > TEST003_junk;
sh regrhive.ksh -f TEST003_junk;

invoke hive.hive.tbool;
select * from hive.hive.tbool;
insert into hive.hive.tbool values (false), (true), (null);
select * from hive.hive.tbool;
insert overwrite table hive.hive.tbool select a from t003t3;
select * from hive.hive.tbool;

?section errors_bool
update t003t3 set b = b + 1;

delete from t003t3;

insert into t003t3 values ('true', false);
insert into t003t3 values (1, true);

select cast(' truee' as boolean) from (values(1)) x(a);

prepare s from insert into t003t3 values (?, ?);
execute s using 1, 2;

?section dml_bool_short
insert into t003t3 values (true, true);
insert into t003t3 values (false, false);

select * from t003t3;

delete from t003t3;
prepare s from insert into t003t3 values (cast(? as boolean), cast(? as boolean));
execute s using true, true;
execute s using 'false', 'falSE';
select * from t003t3;

?section clean_up
drop view t003t1_view;
drop table t003t1 cascade;
drop table t003t1_like;
drop table t003t1_as;

drop view t003t2_view;
drop table t003t2 cascade;
drop table t003t2_like;
drop table t003t2_as;

drop view t003t3_view;
drop table t003t3 cascade;
drop table t003t3_like;
drop table t003t3_as;
drop table t003t3_salt;