cast.sql

derby database source code.good for you.

values (cast (TIMESTAMP('1999-09-09 11:11:11' ) as char(13) for bit data));
values (cast (DATE('1999-09-09') as char(13) for bit data));
values (cast (TIME('11:11:11') as char(13) for bit data));

------------
--bit ->char
------------
values (cast (X'00680065006c006c006f' as char(10)));
--small bit
values (cast (X'11' as char(10)));
values (cast (X'11' as varchar(10)));
values (cast (X'11' as long varchar));
--values (cast (X'00' as char(10)));

--odd length won't work anymore
values (cast (X'123' as char(20)));

--truncate, (should be warning in future)
values (cast ('1234' as char(1) for bit data));
--truncate, ok
values (cast ('1200' as char(1) for bit data));

------------------------------------------------
-- Casting
-----------------------------------------------
create table tab1 (
				i integer, 
				s integer, 
				b integer, 
				l bigint,
				c char(10),
				v varchar(10),
				d double precision,
				r real,
				dt date,
				t time,
				ts timestamp,
				dc decimal);
insert into tab1 values(1, 
				cast(1 as smallint), 
				cast(1 as int), 
				cast(1 as bigint), 
				'char', 
				'varchar', 
				cast(1.1 as double precision), 
				cast(1.1 as real), 
				DATE('1990-10-10'),
				TIME('11:11:11'), 
				TIMESTAMP('1990-11-11 11:11:11'),
				1.1);

insert into tab1 values (null,
				null,
				null,	
				null,	
				null,	
				null,	
				null,	
				null,	
				null,	
				null,	
				null,	
				null);

-- tab1 type -> its tab1 type
select cast(i as integer) from tab1;
select cast(s as smallint) from tab1;
select cast(l as bigint) from tab1; 
select cast(c as char(10)) from tab1; 
select cast(v as char varying(10)) from tab1;
select cast(d as double precision) from tab1;
select cast(r as float) from tab1;
select cast(dt as date) from tab1;
select cast(t as time) from tab1;
select cast(ts as timestamp) from tab1;
select cast(dc as dec) from tab1;

-- try a few others where we try all conversions
select cast(i as integer) from tab1;
select cast(i as smallint) from tab1;
select cast(i as bigint) from tab1; 
select cast(i as char(10)) from tab1; 
select cast(i as char varying(10)) from tab1;
select cast(i as double precision) from tab1;
select cast(i as float) from tab1;
select cast(i as date) from tab1;
select cast(i as time) from tab1;
select cast(i as timestamp) from tab1;
select cast(i as dec) from tab1;

-- try a few others
select cast(c as integer) from tab1;
select cast(c as smallint) from tab1;
select cast(c as bigint) from tab1; 
select cast(c as char(10)) from tab1; 
select cast(c as char varying(10)) from tab1;
select cast(c as double precision) from tab1;
select cast(c as float) from tab1;
select cast(c as date) from tab1;
select cast(c as time) from tab1;
select cast(c as timestamp) from tab1;
select cast(c as dec) from tab1;

select cast(t as integer) from tab1;
select cast(t as smallint) from tab1;
select cast(t as bigint) from tab1; 
select cast(t as char(10)) from tab1; 
select cast(t as char varying(10)) from tab1;
select cast(t as double precision) from tab1;
select cast(t as float) from tab1;
select cast(t as date) from tab1;
select cast(t as time) from tab1;
select cast(t as timestamp) from tab1;
select cast(t as dec) from tab1;

drop table tab1;

---------------------------------------------------------------
-- Other Tests
---------------------------------------------------------------
autocommit off;

-- create tables
create table t1 (bt char(1) for bit data, btv varchar(1) for bit data,
				 c char(30), d double precision, i int, r real, 
				 s smallint, dc decimal(18), num numeric(18),
				 dt date, t time, ts timestamp, v varchar(30), 
				 lvc long varchar);

create table strings(c30 char(30));

-- we need a 1 row table with date/time columns because of problems
-- with single quotes in using 'values DATE('')'
create table temporal_values (dt date, t time, ts timestamp);
insert into temporal_values values(DATE('9876-5-4'), TIME('1:02:34'),
								   TIMESTAMP('9876-5-4 1:02:34'));

-- negative
-- pass wrong type for parameter
prepare a1 as 'values cast(? as smallint)';
execute a1 using 'values 1';

-- uninitialized parameter
values cast(? as int);

-- positive

-- test casting null to all builtin types
insert into t1 (bt) values cast(null as char(1) for bit data);
insert into t1 (btv) values cast(null as varchar(1) for bit data);
insert into t1 (c) values cast(null as char(30));
insert into t1 (d) values cast(null as double precision);
insert into t1 (i) values cast(null as int);
insert into t1 (r) values cast(null as real);
insert into t1 (s) values cast(null as smallint);
insert into t1 (dc) values cast(null as decimal);
insert into t1 (num) values cast(null as numeric);
insert into t1 (dt) values cast(null as date);
insert into t1 (t) values cast(null as time);
insert into t1 (ts) values cast(null as timestamp);
insert into t1 (v) values cast(null as varchar(30));
insert into t1 (lvc) values cast(null as long varchar);

-- expect 10 rows of nulls
select * from t1;

-- make sure casting works correctly on nulls
select cast (bt as char(1) for bit data) from t1;
select cast (btv as varchar(1) for bit data) from t1;
select cast (c as char(30)) from t1;
select cast (d as double precision) from t1;
select cast (r as real) from t1;
select cast (s as smallint) from t1;
select cast (num as numeric) from t1;
select cast (dc as decimal) from t1;
select cast (dt as date) from t1;
select cast (t as time) from t1;
select cast (ts as timestamp) from t1;
select cast (v as varchar(30)) from t1;
select cast (lvc as long varchar) from t1;

-- clean up t1
delete from t1;

-- test casting ? to all builtin types
prepare q1 as 'insert into t1 (bt) values cast(? as char(1) for bit data)';
prepare q2 as 'insert into t1 (btv) values cast(? as varchar(1) for bit data)';
prepare q4 as 'insert into t1 (c) values cast(? as char(30))';
prepare q5 as 'insert into t1 (d) values cast(? as double precision)';
prepare q6 as 'insert into t1 (i) values cast(? as int)';
prepare q7 as 'insert into t1 (r) values cast(? as real)';
prepare q8 as 'insert into t1 (s) values cast(? as smallint)';
prepare q10 as 'insert into t1 (num) values cast(? as numeric(18))';
prepare q11 as 'insert into t1 (dc) values cast(? as decimal(18))';
prepare q12 as 'insert into t1 (dt) values cast(? as date)';
prepare q13 as 'insert into t1 (t) values cast(? as time)';
prepare q14 as 'insert into t1 (ts) values cast(? as timestamp)';
prepare q15 as 'insert into t1 (v) values cast(? as varchar(30))';
prepare q16 as 'insert into t1 (lvc) values cast(? as long varchar)';

execute q1 using 'values X''aa''';
execute q2 using 'values X''aa''';
execute q4 using 'values char(123456)';
execute q5 using 'values 123456.78e0';
execute q6 using 'values 4321';
-- bug 5421 - support db2 udb compatible built-in functions
execute q7 using 'values REAL(4321.01234)';
execute q8 using 'values SMALLINT(12321)';
execute q10 using 'values 123456.78';
execute q11 using 'values 123456.78';
execute q12 using 'select dt from temporal_values';
execute q13 using 'select t from temporal_values';
execute q14 using 'select ts from temporal_values';
execute q15 using 'values char(654321)';
execute q16 using 'values char(987654)';

select * from t1;

-- clean up t1
delete from t1;

-- more ? tests
-- Truncation exception expected in non-parameter cases
-- RESOLVE, no truncation expected in parameter cases
-- where parameter value is not a string.  This is
-- currently an "extension".
create table x(c1 char(1));
prepare param1 as 'insert into x values cast(? as char(1))';
insert into x values cast('12' as char(1));
execute param1 using 'values ''34''';
select * from x;
delete from x;
insert into x values cast(12 as char(1));
execute param1 using 'values 34';
select * from x;
delete from x;
insert into x values cast(time('12:12:12') as char(1));
execute param1 using 'values time(''21:12:12'')';
select * from x;
delete from x;
drop table x;

-- method resolution tests

-- clean up the prepared statements
remove a1;
remove q1;
remove q2;
remove q4;
remove q5;
remove q6;
remove q7;
remove q8;
remove q10;
remove q11;
remove q12;
remove q13;
remove q14;
remove q15;

-- reset autocomiit
commit;
autocommit on;

-- bind time casting tests

-- negative
values cast('asdf' as smallint);
values cast('asdf' as int);
values cast('asdf' as bigint);
values cast('asdf' as real);
values cast('asdf' as double precision);
values cast('asdf' as decimal(5,4));
values cast('asdf' as date);
values cast('asdf' as time);
values cast('asdf' as timestamp);

values cast('2999999999' as int);
values cast(2999999999 as int);
values cast('99999' as smallint);
values cast(99999 as smallint);

values cast(cast(99 as int) as char);
values cast(cast(-9 as int) as char);
values cast(cast(99 as smallint) as char);
values cast(cast(99 as bigint) as char);
values cast(cast(9.9 as real) as char);
values cast(cast(9.9 as double precision) as char);

-- positive
values cast(1 as int);
values cast(1 as smallint);
values cast(1 as bigint);
values cast(1 as char);
values cast('true' as char(4));


-- drop the tables
drop table t1;
drop table temporal_values;
drop table strings;

-- ISO time/timestamp formats
values (cast ('08.08.08' as TIME));
values (cast ('2001-01-01-08.08.08.123456' as TIMESTAMP));

-- char, varchar
values (char('abcde', 5));
values (char('abcde', 6));
values (char('abcde', 4));
values (varchar('', 20));
create table t1 (c5 date, c6 time, c7 timestamp, c8 char(5), c9 varchar(5));
insert into t1 values ('2003-09-10', '16:44:02', '2003-09-08 12:20:30.123456', 'abc', 'abcde');
insert into t1 values ('2005-09-10', '18.44.02', '2004-09-08-12.20.30.123456', 'cba', 'c');
select char(c5), char(c6), char(c7), char(c8), char(c9) from t1;
select varchar(c5), varchar(c6), varchar(c7), varchar(c8), varchar(c9) from t1;
select char(c8, 10), varchar(c9, 9) from t1;
select { fn concat(c8, char(c8)) } from t1;
select { fn concat(c8, varchar(c9)) } from t1;
select { fn concat(varchar(c9, 20), char(c8, 8)) } from t1;
select { fn concat(char(c9, 20), varchar(c8, 8)) } from t1;

-- clean up
drop table t1;

-- bug 5421 - support db2 udb compatible built-in functions
values CHAR(INT(67890));
values CHAR(INTEGER(12345));
values CHAR(DEC(67.21,4,2));
values CHAR(DECIMAL(67.10,4,2));
values CHAR(DOUBLE(5.55));
values CHAR(DOUBLE_PRECISION(5.555));
values CHAR(BIGINT(1));
values CHAR(BIGINT(-1));

values LENGTH(CAST('hello' AS CHAR(25)));
values LENGTH(CAST('hello' AS VARCHAR(25)));
values LENGTH(CAST('hello' AS LONG VARCHAR));

values CAST (X'03' as CHAR(5) for bit data);
values CAST (X'04' as VARCHAR(5) for bit data);
values CAST (X'05' as LONG VARCHAR for bit data);

-- clean up
drop table t1;