xref: /dports/databases/cego/cego-2.47.11/dbcheck/check050.sql

--
-- ### Cast handling for default and insert values
--

drop if exists table tstr;
drop if exists table tint;
drop if exists table ttiny;
drop if exists table tsmall;
drop if exists table tlong;
drop if exists table tdecimal;
drop if exists table tfloat;
drop if exists table tdouble;
drop if exists table tbigint;
drop if exists table tdatetime;
drop if exists table tfixed;
drop if exists table tbool;

drop if exists table tdef1;
drop if exists table tdef2;
drop if exists table tdef3;

create table tstr ( a string(30));
insert into tstr values ( 1 );
insert into tstr values ( (long)1 );
insert into tstr values ( '1' );
insert into tstr values ( true );
insert into tstr values ( sysdate );
insert into tstr values ( (bigint)1 );
insert into tstr values ( 1.0 );
insert into tstr values ( (double)1.0 );
insert into tstr values ( (decimal)1.0 );
insert into tstr values ( (fixed)1.0 );
insert into tstr values ( (smallint)1 );
insert into tstr values ( (tinyint)1 );

select * from tstr;

create table tint ( a int);
insert into tint values ( 1 );
insert into tint values ( (long)1 );
insert into tint values ( '1' );
insert into tint values ( true );
insert into tint values ( sysdate );
insert into tint values ( (bigint)1 );
insert into tint values ( 1.0 );
insert into tint values ( (double)1.0 );
insert into tint values ( (decimal)1.0 );
insert into tint values ( (fixed)1.0 );
insert into tint values ( (smallint)1 );
insert into tint values ( (tinyint)1 );

select * from tint;

create table tlong ( a long);
insert into tlong values ( 1 );
insert into tlong values ( (long)1 );
insert into tlong values ( '1' );
insert into tlong values ( true );
insert into tlong values ( sysdate );
insert into tlong values ( (bigint)1 );
insert into tlong values ( 1.0 );
insert into tlong values ( (double)1.0 );
insert into tlong values ( (decimal)1.0 );
insert into tlong values ( (fixed)1.0 );
insert into tlong values ( (smallint)1 );
insert into tlong values ( (tinyint)1 );

select * from tlong;

create table tdecimal ( a decimal(15));
insert into tdecimal values ( 1 );
insert into tdecimal values ( (long)1 );
insert into tdecimal values ( '1' );
insert into tdecimal values ( true );
insert into tdecimal values ( sysdate );
insert into tdecimal values ( (bigint)1 );
insert into tdecimal values ( 1.0 );
insert into tdecimal values ( (double)1.0 );
insert into tdecimal values ( (decimal)1.0 );
insert into tdecimal values ( (fixed)1.0 );
insert into tdecimal values ( (smallint)1 );
insert into tdecimal values ( (tinyint)1 );

select * from tdecimal;

create table tsmall ( a smallint);
insert into tsmall values ( 1 );
insert into tsmall values ( (long)1 );
insert into tsmall values ( '1' );
insert into tsmall values ( true );
insert into tsmall values ( (bigint)1 );
insert into tsmall values ( 1.0 );
insert into tsmall values ( (double)1.0 );
insert into tsmall values ( (decimal)1.0 );
insert into tsmall values ( (fixed)1.0 );
insert into tsmall values ( (tinyint)1 );

select * from tsmall;

create table ttiny ( a tinyint);
insert into ttiny values ( 1 );
insert into ttiny values ( (long)1 );
insert into ttiny values ( '1' );
insert into ttiny values ( true );
insert into ttiny values ( (bigint)1 );
insert into ttiny values ( 1.0 );
insert into ttiny values ( (double)1.0 );
insert into ttiny values ( (decimal)1.0 );
insert into ttiny values ( (fixed)1.0 );
insert into ttiny values ( (smallint)1 );

select * from ttiny;

create table tdatetime ( a datetime);
insert into tdatetime values ( 1476634567 );
insert into tdatetime values ( (long)1476634567 );
insert into tdatetime values ( '03.07.2016 15:59:00' );
insert into tdatetime values ( (bigint)1476634567 );
insert into tdatetime values ( 1476634567.0 );
insert into tdatetime values ( (double)1476634567.0 );
insert into tdatetime values ( (decimal)1476634567.0 );

select * from tdatetime;

create table tbigint ( a bigint(10));
insert into tbigint values ( 1 );
insert into tbigint values ( (long)1 );
insert into tbigint values ( '1' );
insert into tbigint values ( true );
insert into tbigint values ( (bigint)1 );
insert into tbigint values ( 1.0 );
insert into tbigint values ( (double)1.0 );
insert into tbigint values ( (decimal)1.0 );
insert into tbigint values ( (fixed)1.0 );
insert into tbigint values ( (smallint)1 );

create table tfixed ( a fixed(7));
insert into tfixed values ( 1 );
insert into tfixed values ( (long)1 );
insert into tfixed values ( '1.0756' );
insert into tfixed values ( true );
insert into tfixed values ( 1.8976 );
insert into tfixed values ( (double)1.0 );
insert into tfixed values ( (decimal)1.0345 );
insert into tfixed values ( (fixed)1.0000000 );
insert into tfixed values ( (smallint)1 );

select * from tfixed;

create table tbool ( a bool);
insert into tbool values ( 1 );
insert into tbool values ( (long)1 );
insert into tbool values ( 'true' );
insert into tbool values ( true );
insert into tbool values ( 1.8976 );
insert into tbool values ( (double)1.0 );
insert into tbool values ( (decimal)1.0345 );
insert into tbool values ( (fixed)1.00000 );
insert into tbool values ( (smallint)1 );

select * from tbool;

create table tfloat ( a double);
insert into tfloat values ( 1 );
insert into tfloat values ( (long)1 );
insert into tfloat values ( '1.0' );
insert into tfloat values ( true );
insert into tfloat values ( (bigint)1 );
insert into tfloat values ( 1.0 );
insert into tfloat values ( (double)1.0 );
insert into tfloat values ( (decimal)1.0 );
insert into tfloat values ( (fixed)1.0 );
insert into tfloat values ( (smallint)1 );
insert into tfloat values ( (tinyint)1 );

select * from tfloat;

create table tdouble ( a double);
insert into tdouble values ( 1 );
insert into tdouble values ( (long)1 );
insert into tdouble values ( '1.0' );
insert into tdouble values ( true );
insert into tdouble values ( (bigint)1 );
insert into tdouble values ( 1.0 );
insert into tdouble values ( (double)1.0 );
insert into tdouble values ( (decimal)1.0 );
insert into tdouble values ( (fixed)1.0 );
insert into tdouble values ( (smallint)1 );
insert into tdouble values ( (tinyint)1 );

select * from tdouble;


-- default checks

create table tdef1 ( a int, b fixed(2) default 3.257867 not null);
desc table tdef1;
insert into tdef1 ( a ) values ( 3 );
select * from tdef1;

create table tdef2 ( a int, b fixed(2) default 56 not null);
desc table tdef2;

insert into tdef2 ( a ) values ( 1 );
insert into tdef2 ( a, b ) values ( 2, (fixed)44.98 );
insert into tdef2 ( a, b ) values ( 3, 44 );

create table tdef3 ( a int, b float default 7.99 not null);
desc table tdef3;

insert into tdef3 ( a ) values ( 1 );
insert into tdef3 ( a, b ) values ( 2, 44.98 );
insert into tdef3 ( a, b ) values ( 3, 44 );