Google Git
Sign in
apache/derby/a8e109a98394159f3decd0a7b305ea6c7e5c8eea/./java/testing/org/apache/derbyTesting/functionTests/tests/lang/cast.sql
blob: f9809ca6fd591ed038a0ecc2b1cdb45f083ea983 [file]
--
-- 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.
--
-- tests for cast expressions
-- refer to casting.java for a complete analysis on casting
--==================================
--
-- simple test cases
--
--==================================
-- shrink/grow bit and char
-- no exceptions should be raised.
-- expect a warning when shrinking non-space
-- shrink
values (cast ('hell' as char(2)));
values (cast ('hell' as varchar(2)));
-- shrink, whitespace only
values (cast ('he ' as char(2)));
-- expand, check lengths
values (cast ('hell' as char(20)));
values (cast ('hell' as varchar(20)));
values length(cast ('hell' as char(20)));
values length(cast ('hell' as varchar(20)));
----------------
--char->bit data
----------------
-- shrink
values (cast (X'1111' as char(1) for bit data));
-- shrink, zero only
values (cast (X'1100' as char(1) for bit data));
-- expand
values (cast (X'1111' as char(2) for bit data));
-- w/o format
-- DB2 UDB PASS
-- DB2 CS FAIL
values (cast ('1234' as char(2) for bit data));
-- extra tests for shrinking parts of bits
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);
values cast (X'00000011' as char(1) for bit data);
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);
values cast (X'00000011' as char(1) for bit data);
values cast (X'00000001' as char(1) for bit data);
values cast (X'0011111111111111' as char(1) for bit data);
values cast (X'1111111100111111' as char(2) for bit data);
---------
--numbers
---------
values (cast (1.1 as int));
values (cast (1.1 as smallint));
values (cast (1.1 as bigint));
values (cast (1.1 as double precision));
values (cast (1.1 as numeric(2,1)));
values (cast (1.1 as decimal(2,1)));
values (cast (1.1 as numeric(2,0)));
values (cast (1.1 as decimal(2,0)));
values (cast (1.1 as float));
values (cast (1.1 as real));
values (cast (1.9 as int));
values (cast (1.9 as smallint));
values (cast (1.9 as bigint));
values (cast (1.9 as double precision));
values (cast (1.9 as numeric(2,1)));
values (cast (1.9 as decimal(2,1)));
values (cast (1.9 as numeric(2,0)));
values (cast (1.9 as decimal(2,0)));
values (cast (1.9 as float));
values (cast (1.9 as real));
-- bug 4352,4358 loss of precision on casts
-- 9223372036854775807 is Long::MAX_VALUE
values (
9223372036854775807,
cast (9223372036854775807 as DECIMAL(24,1)),
cast (
cast (9223372036854775807 as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('9223372036854775807' as DECIMAL(24,1)),
cast (cast ('9223372036854775807' as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('9223372036854775806' as DECIMAL(24,1)),
cast (cast ('9223372036854775806' as DECIMAL(24,1)) as BIGINT)
);
-- only this should fail
values (
cast ('9223372036854775808' as DECIMAL(24,1)),
cast (cast ('9223372036854775808' as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('9223372036854775807.9' as DECIMAL(24,1)),
cast (cast ('9223372036854775807.9' as DECIMAL(24,1)) as BIGINT)
);
-- -9223372036854775808 is Long::MIN_VALUE
values (
cast ('-9223372036854775808' as DECIMAL(24,1)),
cast (cast ('-9223372036854775808' as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('-9223372036854775807' as DECIMAL(24,1)),
cast (cast ('-9223372036854775807' as DECIMAL(24,1)) as BIGINT)
);
-- only this should fail
values (
cast ('-9223372036854775809' as DECIMAL(24,1)),
cast (cast ('-9223372036854775809' as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('-9223372036854775808.9' as DECIMAL(24,1)),
cast (cast ('-9223372036854775808.9' as DECIMAL(24,1)) as BIGINT)
);
values (
cast ('32767' as DECIMAL(24,1)),
cast (cast ('32767' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('32766' as DECIMAL(24,1)),
cast (cast ('32766' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('32768' as DECIMAL(24,1)),
cast (cast ('32768' as DECIMAL(24,1)) as SMALLINT)
);
-- only this should fail
values (
cast ('32767.9' as DECIMAL(24,1)),
cast (cast ('32767.9' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('-32768' as DECIMAL(24,1)),
cast (cast ('-32768' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('-32767' as DECIMAL(24,1)),
cast (cast ('-32767' as DECIMAL(24,1)) as SMALLINT)
);
-- only this should fail
values (
cast ('-32769' as DECIMAL(24,1)),
cast (cast ('-32769' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('-32768.9' as DECIMAL(24,1)),
cast (cast ('-32768.9' as DECIMAL(24,1)) as SMALLINT)
);
values (
cast ('2147483647' as DECIMAL(24,1)),
cast (cast ('2147483647' as DECIMAL(24,1)) as INTEGER)
);
values (
cast ('2147483646' as DECIMAL(24,1)),
cast (cast ('2147483646' as DECIMAL(24,1)) as INTEGER)
);
-- only this should fail
values (
cast ('2147483648' as DECIMAL(24,1)),
cast (cast ('2147483648' as DECIMAL(24,1)) as INTEGER)
);
values (
cast ('2147483647.9' as DECIMAL(24,1)),
cast (cast ('2147483647.9' as DECIMAL(24,1)) as INTEGER)
);
values (
cast ('-2147483647' as DECIMAL(24,1)),
cast (cast ('-2147483647' as DECIMAL(24,1)) as INTEGER)
);
values (
cast ('-2147483646' as DECIMAL(24,1)),
cast (cast ('-2147483646' as DECIMAL(24,1)) as INTEGER)
);
-- only this should fail
values (
cast ('-2147483649' as DECIMAL(24,1)),
cast (cast ('-2147483649' as DECIMAL(24,1)) as INTEGER)
);
values (
cast ('-2147483648.9' as DECIMAL(24,1)),
cast (cast ('-2147483648.9' as DECIMAL(24,1)) as INTEGER)
);
--numbers to char
values (cast (1.1 as char(10)));
values (cast (1.1 as varchar(10)));
values (cast (1e1 as varchar(10)));
values (cast (1e1 as char(10)));
values (cast (1 as char(10)));
values (cast (1 as varchar(10)));
values (cast (1e200 as char(10)));
values (cast (1e200 as varchar(10)));
values (cast (1 as long varchar));
values (cast (1.1 as long varchar));
values (cast (1e1 as long varchar));
--char to numbers
values (cast ('123' as smallint));
values (cast ('123' as int));
values (cast ('123' as bigint));
values (cast ('123' as double precision));
values (cast ('123' as float));
values (cast ('123' as real));
values (cast ('123' as numeric(3,0)));
values (cast ('123' as decimal(3,0)));
-- char (with decimal) to numbers (truncates where needed Track #3756)
-- bug 5568
values (cast ('123.45' as smallint));
values (cast ('123.45' as int));
values (cast ('123.45' as bigint));
values (cast ('123.45' as double precision));
values (cast ('123.45' as float));
values (cast ('123.45' as real));
values (cast ('123.45' as numeric(5,1)));
values (cast ('123.45' as decimal(5,1)));
values (cast ('123.99' as smallint));
values (cast ('123.99' as int));
values (cast ('123.99' as bigint));
values (cast ('123.99' as double precision));
values (cast ('123.99' as float));
values (cast ('123.99' as real));
values (cast ('123.99' as numeric(5,1)));
values (cast ('123.99' as decimal(5,1)));
--bad
values (cast (1 as char(2) for bit data));
values (cast (1 as date));
values (cast (1 as time));
values (cast (1 as timestamp));
-------------------
--char -> date/time
-------------------
values (cast ('TIME''11:11:11''' as time));
values (cast ('11:11:11' as time));
values (cast ('DATE''1999-09-09''' as date));
values (cast ('1999-09-09' as date));
values (cast ('TIMESTAMP''1999-09-09 11:11:11''' as timestamp));
values (cast ('1999-09-09 11:11:11' as timestamp));
------------------
--date/time ->other
------------------
values (cast (TIME('11:11:11') as char(20)));
values (cast (DATE('1999-09-09') as char(20)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(40)));
values (cast (TIME('11:11:11') as varchar(20)));
values (cast (DATE('1999-09-09') as varchar(20)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as varchar(40)));
values (cast (TIME('11:11:11') as long varchar));
values (cast (DATE('1999-09-09') as long varchar));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as long varchar));
-- truncation errors
values (cast (TIME('11:11:11') as char(2)));
values (cast (DATE('1999-09-09') as char(2)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(2)));
-- to date/time
values (cast (TIME('11:11:11') as time));
values (cast (TIME('11:11:11') as date));
-- this piece of convoluted logic is to ensure that we
-- get the current date for a conversion of time to timestamp
values substr(cast (cast (TIME('11:11:11') as timestamp) as char(50)), 1, 10) = cast (current_date as char(10));
-- now make sure we got the time right
values substr(cast (cast (TIME('11:11:11') as timestamp) as char(30)), 12);
values (cast (DATE('1999-09-09') as date));
values (cast (DATE('1999-09-09') as time));
values (cast (DATE('1999-09-09') as timestamp));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as date));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as time));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as timestamp));
--bad
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as int));
values (cast (DATE('1999-09-09') as int));
values (cast (TIME('11:11:11') as int));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as smallint));
values (cast (DATE('1999-09-09') as smallint));
values (cast (TIME('11:11:11') as smallint));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as bigint));
values (cast (DATE('1999-09-09') as bigint));
values (cast (TIME('11:11:11') as bigint));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as numeric));
values (cast (DATE('1999-09-09') as numeric));
values (cast (TIME('11:11:11') as numeric));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as decimal));
values (cast (DATE('1999-09-09') as decimal));
values (cast (TIME('11:11:11') as decimal));
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 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;
-- test some casting from a java type to standard SQL types.
-- should all fail at runtime
select cast (aliasinfo as BOOLEAN) from sys.sysaliases;
select cast (aliasinfo as SMALLINT) from sys.sysaliases;
select cast (aliasinfo as INTEGER) from sys.sysaliases;
select cast (aliasinfo as BIGINT) from sys.sysaliases;
select cast (aliasinfo as REAL) from sys.sysaliases;
select cast (aliasinfo as DOUBLE) from sys.sysaliases;
select cast (aliasinfo as DECIMAL(5,4)) from sys.sysaliases;
select cast (aliasinfo as CHAR(30) FOR BIT DATA) from sys.sysaliases;
select cast (aliasinfo as VARCHAR(30) FOR BIT DATA) from sys.sysaliases;
select cast (aliasinfo as LONG VARCHAR FOR BIT DATA) from sys.sysaliases;
select cast (aliasinfo as BLOB) from sys.sysaliases;
select cast (aliasinfo as CLOB) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR';
-- Java casts to character types excluding CLOB are supported using Object.toString
select cast (aliasinfo as CHAR(240)) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR';
select cast (aliasinfo as VARCHAR(240)) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR';
select cast (aliasinfo as LONG VARCHAR) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR';