ij> -- -- 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))); 1 -- he WARNING 01004: Data truncation ij> values (cast ('hell' as varchar(2))); 1 -- he WARNING 01004: Data truncation ij> -- shrink, whitespace only values (cast ('he ' as char(2))); 1 -- he ij> -- expand, check lengths values (cast ('hell' as char(20))); 1 -------------------- hell ij> values (cast ('hell' as varchar(20))); 1 -------------------- hell ij> values length(cast ('hell' as char(20))); 1 ----------- 20 ij> values length(cast ('hell' as varchar(20))); 1 ----------- 4 ij> ---------------- --char->bit data ---------------- -- shrink values (cast (X'1111' as char(1) for bit data)); 1 -- 11 WARNING 01004: Data truncation ij> -- shrink, zero only values (cast (X'1100' as char(1) for bit data)); 1 -- 11 WARNING 01004: Data truncation ij> -- expand values (cast (X'1111' as char(2) for bit data)); 1 ---- 1111 ij> -- w/o format -- DB2 UDB PASS -- DB2 CS FAIL values (cast ('1234' as char(2) for bit data)); ERROR 42846: Cannot convert types 'CHAR' to 'CHAR () FOR BIT DATA'. ij> -- extra tests for shrinking parts of bits values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00011111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00011111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00001111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00011111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00001111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00011111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00001111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000011' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'11111111' as char(1) for bit data); 1 -- 11 WARNING 01004: Data truncation ij> values cast (X'01111111' as char(1) for bit data); 1 -- 01 WARNING 01004: Data truncation ij> values cast (X'00111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00011111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00001111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000011' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'00000001' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'0011111111111111' as char(1) for bit data); 1 -- 00 WARNING 01004: Data truncation ij> values cast (X'1111111100111111' as char(2) for bit data); 1 ---- 1111 WARNING 01004: Data truncation ij> --------- --numbers --------- values (cast (1.1 as int)); 1 ----------- 1 ij> values (cast (1.1 as smallint)); 1 ------ 1 ij> values (cast (1.1 as bigint)); 1 -------------------- 1 ij> values (cast (1.1 as double precision)); 1 ---------------------- 1.1 ij> values (cast (1.1 as numeric(2,1))); 1 ---- 1.1 ij> values (cast (1.1 as decimal(2,1))); 1 ---- 1.1 ij> values (cast (1.1 as numeric(2,0))); 1 --- 1 ij> values (cast (1.1 as decimal(2,0))); 1 --- 1 ij> values (cast (1.1 as float)); 1 ---------------------- 1.1 ij> values (cast (1.1 as real)); 1 ------------- 1.1 ij> values (cast (1.9 as int)); 1 ----------- 1 ij> values (cast (1.9 as smallint)); 1 ------ 1 ij> values (cast (1.9 as bigint)); 1 -------------------- 1 ij> values (cast (1.9 as double precision)); 1 ---------------------- 1.9 ij> values (cast (1.9 as numeric(2,1))); 1 ---- 1.9 ij> values (cast (1.9 as decimal(2,1))); 1 ---- 1.9 ij> values (cast (1.9 as numeric(2,0))); 1 --- 1 ij> values (cast (1.9 as decimal(2,0))); 1 --- 1 ij> values (cast (1.9 as float)); 1 ---------------------- 1.9 ij> values (cast (1.9 as real)); 1 ------------- 1.9 ij> -- 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) ); 1 |2 |3 -------------------------------------------------------------------- 9223372036854775807 |9223372036854775807.0 |9223372036854775807 ij> values ( cast ('9223372036854775807' as DECIMAL(24,1)), cast (cast ('9223372036854775807' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- 9223372036854775807.0 |9223372036854775807 ij> values ( cast ('9223372036854775806' as DECIMAL(24,1)), cast (cast ('9223372036854775806' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- 9223372036854775806.0 |9223372036854775806 ij> -- only this should fail values ( cast ('9223372036854775808' as DECIMAL(24,1)), cast (cast ('9223372036854775808' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> values ( cast ('9223372036854775807.9' as DECIMAL(24,1)), cast (cast ('9223372036854775807.9' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- 9223372036854775807.9 |9223372036854775807 ij> -- -9223372036854775808 is Long::MIN_VALUE values ( cast ('-9223372036854775808' as DECIMAL(24,1)), cast (cast ('-9223372036854775808' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- -9223372036854775808.0 |-9223372036854775808 ij> values ( cast ('-9223372036854775807' as DECIMAL(24,1)), cast (cast ('-9223372036854775807' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- -9223372036854775807.0 |-9223372036854775807 ij> -- only this should fail values ( cast ('-9223372036854775809' as DECIMAL(24,1)), cast (cast ('-9223372036854775809' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- ERROR 22003: The resulting value is outside the range for the data type BIGINT. ij> values ( cast ('-9223372036854775808.9' as DECIMAL(24,1)), cast (cast ('-9223372036854775808.9' as DECIMAL(24,1)) as BIGINT) ); 1 |2 ----------------------------------------------- -9223372036854775808.9 |-9223372036854775808 ij> values ( cast ('32767' as DECIMAL(24,1)), cast (cast ('32767' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- 32767.0 |32767 ij> values ( cast ('32766' as DECIMAL(24,1)), cast (cast ('32766' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- 32766.0 |32766 ij> values ( cast ('32768' as DECIMAL(24,1)), cast (cast ('32768' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> -- only this should fail values ( cast ('32767.9' as DECIMAL(24,1)), cast (cast ('32767.9' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- 32767.9 |32767 ij> values ( cast ('-32768' as DECIMAL(24,1)), cast (cast ('-32768' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- -32768.0 |-32768 ij> values ( cast ('-32767' as DECIMAL(24,1)), cast (cast ('-32767' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- -32767.0 |-32767 ij> -- only this should fail values ( cast ('-32769' as DECIMAL(24,1)), cast (cast ('-32769' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> values ( cast ('-32768.9' as DECIMAL(24,1)), cast (cast ('-32768.9' as DECIMAL(24,1)) as SMALLINT) ); 1 |2 --------------------------------- -32768.9 |-32768 ij> values ( cast ('2147483647' as DECIMAL(24,1)), cast (cast ('2147483647' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- 2147483647.0 |2147483647 ij> values ( cast ('2147483646' as DECIMAL(24,1)), cast (cast ('2147483646' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- 2147483646.0 |2147483646 ij> -- only this should fail values ( cast ('2147483648' as DECIMAL(24,1)), cast (cast ('2147483648' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> values ( cast ('2147483647.9' as DECIMAL(24,1)), cast (cast ('2147483647.9' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- 2147483647.9 |2147483647 ij> values ( cast ('-2147483647' as DECIMAL(24,1)), cast (cast ('-2147483647' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- -2147483647.0 |-2147483647 ij> values ( cast ('-2147483646' as DECIMAL(24,1)), cast (cast ('-2147483646' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- -2147483646.0 |-2147483646 ij> -- only this should fail values ( cast ('-2147483649' as DECIMAL(24,1)), cast (cast ('-2147483649' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> values ( cast ('-2147483648.9' as DECIMAL(24,1)), cast (cast ('-2147483648.9' as DECIMAL(24,1)) as INTEGER) ); 1 |2 -------------------------------------- -2147483648.9 |-2147483648 ij> --numbers to char values (cast (1.1 as char(10))); 1 ---------- 1.1 ij> values (cast (1.1 as varchar(10))); ERROR 42846: Cannot convert types 'DECIMAL' to 'VARCHAR'. ij> values (cast (1e1 as varchar(10))); ERROR 42846: Cannot convert types 'DOUBLE' to 'VARCHAR'. ij> values (cast (1e1 as char(10))); ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. ij> values (cast (1 as char(10))); 1 ---------- 1 ij> values (cast (1 as varchar(10))); ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. ij> values (cast (1e200 as char(10))); ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. ij> values (cast (1e200 as varchar(10))); ERROR 42846: Cannot convert types 'DOUBLE' to 'VARCHAR'. ij> values (cast (1 as long varchar)); ERROR 42846: Cannot convert types 'INTEGER' to 'LONG VARCHAR'. ij> values (cast (1.1 as long varchar)); ERROR 42846: Cannot convert types 'DECIMAL' to 'LONG VARCHAR'. ij> values (cast (1e1 as long varchar)); ERROR 42846: Cannot convert types 'DOUBLE' to 'LONG VARCHAR'. ij> --char to numbers values (cast ('123' as smallint)); 1 ------ 123 ij> values (cast ('123' as int)); 1 ----------- 123 ij> values (cast ('123' as bigint)); 1 -------------------- 123 ij> values (cast ('123' as double precision)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123' as float)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123' as real)); ERROR 42846: Cannot convert types 'CHAR' to 'REAL'. ij> values (cast ('123' as numeric(3,0))); 1 ---- 123 ij> values (cast ('123' as decimal(3,0))); 1 ---- 123 ij> -- char (with decimal) to numbers (truncates where needed Track #3756) -- bug 5568 values (cast ('123.45' as smallint)); 1 ------ 123 ij> values (cast ('123.45' as int)); 1 ----------- 123 ij> values (cast ('123.45' as bigint)); 1 -------------------- 123 ij> values (cast ('123.45' as double precision)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123.45' as float)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123.45' as real)); ERROR 42846: Cannot convert types 'CHAR' to 'REAL'. ij> values (cast ('123.45' as numeric(5,1))); 1 ------- 123.4 ij> values (cast ('123.45' as decimal(5,1))); 1 ------- 123.4 ij> values (cast ('123.99' as smallint)); 1 ------ 123 ij> values (cast ('123.99' as int)); 1 ----------- 123 ij> values (cast ('123.99' as bigint)); 1 -------------------- 123 ij> values (cast ('123.99' as double precision)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123.99' as float)); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values (cast ('123.99' as real)); ERROR 42846: Cannot convert types 'CHAR' to 'REAL'. ij> values (cast ('123.99' as numeric(5,1))); 1 ------- 123.9 ij> values (cast ('123.99' as decimal(5,1))); 1 ------- 123.9 ij> --bad values (cast (1 as char(2) for bit data)); ERROR 42846: Cannot convert types 'INTEGER' to 'CHAR () FOR BIT DATA'. ij> values (cast (1 as date)); ERROR 42846: Cannot convert types 'INTEGER' to 'DATE'. ij> values (cast (1 as time)); ERROR 42846: Cannot convert types 'INTEGER' to 'TIME'. ij> values (cast (1 as timestamp)); ERROR 42846: Cannot convert types 'INTEGER' to 'TIMESTAMP'. ij> ------------------- --char -> date/time ------------------- values (cast ('TIME''11:11:11''' as time)); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values (cast ('11:11:11' as time)); 1 -------- 11:11:11 ij> values (cast ('DATE''1999-09-09''' as date)); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values (cast ('1999-09-09' as date)); 1 ---------- 1999-09-09 ij> values (cast ('TIMESTAMP''1999-09-09 11:11:11''' as timestamp)); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values (cast ('1999-09-09 11:11:11' as timestamp)); 1 ----------------------------- 1999-09-09 11:11:11.0 ij> ------------------ --date/time ->other ------------------ values (cast (TIME('11:11:11') as char(20))); 1 -------------------- 11:11:11 ij> values (cast (DATE('1999-09-09') as char(20))); 1 -------------------- 1999-09-09 ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(40))); 1 ---------------------------------------- 1999-09-09 11:11:11.0 ij> values (cast (TIME('11:11:11') as varchar(20))); 1 -------------------- 11:11:11 ij> values (cast (DATE('1999-09-09') as varchar(20))); 1 -------------------- 1999-09-09 ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as varchar(40))); 1 ---------------------------------------- 1999-09-09 11:11:11.0 ij> values (cast (TIME('11:11:11') as long varchar)); ERROR 42846: Cannot convert types 'TIME' to 'LONG VARCHAR'. ij> values (cast (DATE('1999-09-09') as long varchar)); ERROR 42846: Cannot convert types 'DATE' to 'LONG VARCHAR'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as long varchar)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'LONG VARCHAR'. ij> -- truncation errors values (cast (TIME('11:11:11') as char(2))); ERROR 22001: A truncation error was encountered trying to shrink CHAR '11:11:11' to length 2. ij> values (cast (DATE('1999-09-09') as char(2))); ERROR 22001: A truncation error was encountered trying to shrink CHAR '1999-09-09' to length 2. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(2))); ERROR 22001: A truncation error was encountered trying to shrink CHAR '1999-09-09 11:11:11.0' to length 2. ij> -- to date/time values (cast (TIME('11:11:11') as time)); 1 -------- 11:11:11 ij> values (cast (TIME('11:11:11') as date)); ERROR 42846: Cannot convert types 'TIME' to 'DATE'. ij> -- this piece of convoluted logic is to ensure that we -- get the current date for a conversion of time to timestamp values cast (cast (TIME('11:11:11') as timestamp) as char(50)).substring(0, 10).equals(cast (current_date as char(10))); ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'. ij> -- now make sure we got the time right values cast (cast (TIME('11:11:11') as timestamp) as char(30)).substring(11,21); ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'. ij> values (cast (DATE('1999-09-09') as date)); 1 ---------- 1999-09-09 ij> values (cast (DATE('1999-09-09') as time)); ERROR 42846: Cannot convert types 'DATE' to 'TIME'. ij> values (cast (DATE('1999-09-09') as timestamp)); ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as date)); 1 ---------- 1999-09-09 ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as time)); 1 -------- 11:11:11 ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as timestamp)); 1 ----------------------------- 1999-09-09 11:11:11.0 ij> --bad values (cast (TIMESTAMP('1999-09-09 11:11:11' )as int)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'INTEGER'. ij> values (cast (DATE('1999-09-09') as int)); ERROR 42846: Cannot convert types 'DATE' to 'INTEGER'. ij> values (cast (TIME('11:11:11') as int)); ERROR 42846: Cannot convert types 'TIME' to 'INTEGER'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as smallint)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'SMALLINT'. ij> values (cast (DATE('1999-09-09') as smallint)); ERROR 42846: Cannot convert types 'DATE' to 'SMALLINT'. ij> values (cast (TIME('11:11:11') as smallint)); ERROR 42846: Cannot convert types 'TIME' to 'SMALLINT'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as bigint)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'BIGINT'. ij> values (cast (DATE('1999-09-09') as bigint)); ERROR 42846: Cannot convert types 'DATE' to 'BIGINT'. ij> values (cast (TIME('11:11:11') as bigint)); ERROR 42846: Cannot convert types 'TIME' to 'BIGINT'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as numeric)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'NUMERIC'. ij> values (cast (DATE('1999-09-09') as numeric)); ERROR 42846: Cannot convert types 'DATE' to 'NUMERIC'. ij> values (cast (TIME('11:11:11') as numeric)); ERROR 42846: Cannot convert types 'TIME' to 'NUMERIC'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' )as decimal)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'DECIMAL'. ij> values (cast (DATE('1999-09-09') as decimal)); ERROR 42846: Cannot convert types 'DATE' to 'DECIMAL'. ij> values (cast (TIME('11:11:11') as decimal)); ERROR 42846: Cannot convert types 'TIME' to 'DECIMAL'. ij> values (cast (TIMESTAMP('1999-09-09 11:11:11' ) as char(13) for bit data)); ERROR 42846: Cannot convert types 'TIMESTAMP' to 'CHAR () FOR BIT DATA'. ij> values (cast (DATE('1999-09-09') as char(13) for bit data)); ERROR 42846: Cannot convert types 'DATE' to 'CHAR () FOR BIT DATA'. ij> values (cast (TIME('11:11:11') as char(13) for bit data)); ERROR 42846: Cannot convert types 'TIME' to 'CHAR () FOR BIT DATA'. ij> ------------ --bit ->char ------------ values (cast (X'00680065006c006c006f' as char(10))); ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'. ij> --small bit values (cast (X'11' as char(10))); ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'CHAR'. ij> values (cast (X'11' as varchar(10))); ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'VARCHAR'. ij> values (cast (X'11' as long varchar)); ERROR 42846: Cannot convert types 'CHAR () FOR BIT DATA' to 'LONG VARCHAR'. ij> --values (cast (X'00' as char(10))); --odd length won't work anymore values (cast (X'123' as char(20))); ERROR 42606: An invalid hexadecimal constant starting with 'X'123'' has been detected. ij> --truncate, (should be warning in future) values (cast ('1234' as char(1) for bit data)); ERROR 42846: Cannot convert types 'CHAR' to 'CHAR () FOR BIT DATA'. ij> --truncate, ok values (cast ('1200' as char(1) for bit data)); ERROR 42846: Cannot convert types 'CHAR' to 'CHAR () FOR BIT DATA'. ij> ------------------------------------------------ -- 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); 0 rows inserted/updated/deleted ij> 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); 1 row inserted/updated/deleted ij> insert into tab1 values (null, null, null, null, null, null, null, null, null, null, null, null); 1 row inserted/updated/deleted ij> -- tab1 type -> its tab1 type select cast(i as integer) from tab1; 1 ----------- 1 NULL ij> select cast(s as smallint) from tab1; 1 ------ 1 NULL ij> select cast(l as bigint) from tab1; 1 -------------------- 1 NULL ij> select cast(c as char(10)) from tab1; 1 ---------- char NULL ij> select cast(v as char varying(10)) from tab1; 1 ---------- varchar NULL ij> select cast(d as double precision) from tab1; 1 ---------------------- 1.1 NULL ij> select cast(r as float) from tab1; 1 ---------------------- 1.100000023841858 NULL ij> select cast(dt as date) from tab1; 1 ---------- 1990-10-10 NULL ij> select cast(t as time) from tab1; 1 -------- 11:11:11 NULL ij> select cast(ts as timestamp) from tab1; 1 ----------------------------- 1990-11-11 11:11:11.0 NULL ij> select cast(dc as dec) from tab1; 1 ------ 1 NULL ij> -- try a few others where we try all conversions select cast(i as integer) from tab1; 1 ----------- 1 NULL ij> select cast(i as smallint) from tab1; 1 ------ 1 NULL ij> select cast(i as bigint) from tab1; 1 -------------------- 1 NULL ij> select cast(i as char(10)) from tab1; 1 ---------- 1 NULL ij> select cast(i as char varying(10)) from tab1; ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'. ij> select cast(i as double precision) from tab1; 1 ---------------------- 1.0 NULL ij> select cast(i as float) from tab1; 1 ---------------------- 1.0 NULL ij> select cast(i as date) from tab1; ERROR 42846: Cannot convert types 'INTEGER' to 'DATE'. ij> select cast(i as time) from tab1; ERROR 42846: Cannot convert types 'INTEGER' to 'TIME'. ij> select cast(i as timestamp) from tab1; ERROR 42846: Cannot convert types 'INTEGER' to 'TIMESTAMP'. ij> select cast(i as dec) from tab1; 1 ------ 1 NULL ij> -- try a few others select cast(c as integer) from tab1; 1 ----------- ERROR 22018: Invalid character string format for type INTEGER. ij> select cast(c as smallint) from tab1; 1 ------ ERROR 22018: Invalid character string format for type SMALLINT. ij> select cast(c as bigint) from tab1; 1 -------------------- ERROR 22018: Invalid character string format for type BIGINT. ij> select cast(c as char(10)) from tab1; 1 ---------- char NULL ij> select cast(c as char varying(10)) from tab1; 1 ---------- char NULL ij> select cast(c as double precision) from tab1; ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> select cast(c as float) from tab1; ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> select cast(c as date) from tab1; 1 ---------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select cast(c as time) from tab1; 1 -------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select cast(c as timestamp) from tab1; 1 ----------------------------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select cast(c as dec) from tab1; 1 ------ ERROR 22018: Invalid character string format for type DECIMAL. ij> select cast(t as integer) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'INTEGER'. ij> select cast(t as smallint) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'SMALLINT'. ij> select cast(t as bigint) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'BIGINT'. ij> select cast(t as char(10)) from tab1; 1 ---------- 11:11:11 NULL ij> select cast(t as char varying(10)) from tab1; 1 ---------- 11:11:11 NULL ij> select cast(t as double precision) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'DOUBLE'. ij> select cast(t as float) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'DOUBLE'. ij> select cast(t as date) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'DATE'. ij> select cast(t as time) from tab1; 1 -------- 11:11:11 NULL ij> select cast(t as timestamp) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'. ij> select cast(t as dec) from tab1; ERROR 42846: Cannot convert types 'TIME' to 'DECIMAL'. ij> drop table tab1; 0 rows inserted/updated/deleted ij> --------------------------------------------------------------- -- Other Tests --------------------------------------------------------------- autocommit off; ij> -- 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); 0 rows inserted/updated/deleted ij> create table strings(c30 char(30)); 0 rows inserted/updated/deleted ij> -- 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); 0 rows inserted/updated/deleted ij> insert into temporal_values values(DATE('9876-5-4'), TIME('1:02:34'), TIMESTAMP('9876-5-4 1:02:34')); 1 row inserted/updated/deleted ij> -- negative -- pass wrong type for parameter prepare a1 as 'values cast(? as smallint)'; ij> execute a1 using 'values 1'; 1 ------ 1 ij> -- uninitialized parameter values cast(? as int); ERROR 07000: At least one parameter to the current statement is uninitialized. ij> -- positive -- test casting null to all builtin types insert into t1 (bt) values cast(null as char(1) for bit data); 1 row inserted/updated/deleted ij> insert into t1 (btv) values cast(null as varchar(1) for bit data); 1 row inserted/updated/deleted ij> insert into t1 (c) values cast(null as char(30)); 1 row inserted/updated/deleted ij> insert into t1 (d) values cast(null as double precision); 1 row inserted/updated/deleted ij> insert into t1 (i) values cast(null as int); 1 row inserted/updated/deleted ij> insert into t1 (r) values cast(null as real); 1 row inserted/updated/deleted ij> insert into t1 (s) values cast(null as smallint); 1 row inserted/updated/deleted ij> insert into t1 (dc) values cast(null as decimal); 1 row inserted/updated/deleted ij> insert into t1 (num) values cast(null as numeric); 1 row inserted/updated/deleted ij> insert into t1 (dt) values cast(null as date); 1 row inserted/updated/deleted ij> insert into t1 (t) values cast(null as time); 1 row inserted/updated/deleted ij> insert into t1 (ts) values cast(null as timestamp); 1 row inserted/updated/deleted ij> insert into t1 (v) values cast(null as varchar(30)); 1 row inserted/updated/deleted ij> insert into t1 (lvc) values cast(null as long varchar); 1 row inserted/updated/deleted ij> -- expect 10 rows of nulls select * from t1; BT |BTV |C |D |I |R |S |DC |NUM |DT |T |TS |V |LVC ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL ij> -- make sure casting works correctly on nulls select cast (bt as char(1) for bit data) from t1; 1 ---- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (btv as varchar(1) for bit data) from t1; 1 ---- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (c as char(30)) from t1; 1 ------------------------------ NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (d as double precision) from t1; 1 ---------------------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (r as real) from t1; 1 ------------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (s as smallint) from t1; 1 ------ NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (num as numeric) from t1; 1 ------ NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (dc as decimal) from t1; 1 ------ NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (dt as date) from t1; 1 ---------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (t as time) from t1; 1 -------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (ts as timestamp) from t1; 1 ----------------------------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (v as varchar(30)) from t1; 1 ------------------------------ NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> select cast (lvc as long varchar) from t1; 1 -------------------------------------------------------------------------------------------------------------------------------- NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> -- clean up t1 delete from t1; 14 rows inserted/updated/deleted ij> -- test casting ? to all builtin types prepare q1 as 'insert into t1 (bt) values cast(? as char(1) for bit data)'; ij> prepare q2 as 'insert into t1 (btv) values cast(? as varchar(1) for bit data)'; ij> prepare q4 as 'insert into t1 (c) values cast(? as char(30))'; ij> prepare q5 as 'insert into t1 (d) values cast(? as double precision)'; ij> prepare q6 as 'insert into t1 (i) values cast(? as int)'; ij> prepare q7 as 'insert into t1 (r) values cast(? as real)'; ij> prepare q8 as 'insert into t1 (s) values cast(? as smallint)'; ij> prepare q10 as 'insert into t1 (num) values cast(? as numeric(18))'; ij> prepare q11 as 'insert into t1 (dc) values cast(? as decimal(18))'; ij> prepare q12 as 'insert into t1 (dt) values cast(? as date)'; ij> prepare q13 as 'insert into t1 (t) values cast(? as time)'; ij> prepare q14 as 'insert into t1 (ts) values cast(? as timestamp)'; ij> prepare q15 as 'insert into t1 (v) values cast(? as varchar(30))'; ij> prepare q16 as 'insert into t1 (lvc) values cast(? as long varchar)'; ij> execute q1 using 'values X''aa'''; 1 row inserted/updated/deleted ij> execute q2 using 'values X''aa'''; 1 row inserted/updated/deleted ij> execute q4 using 'values char(123456)'; 1 row inserted/updated/deleted ij> execute q5 using 'values 123456.78e0'; 1 row inserted/updated/deleted ij> execute q6 using 'values 4321'; 1 row inserted/updated/deleted ij> -- bug 5421 - support db2 udb compatible built-in functions execute q7 using 'values REAL(4321.01234)'; ERROR 42X80: VALUES clause must contain at least one element. Empty elements are not allowed. ij> execute q8 using 'values SMALLINT(12321)'; 1 row inserted/updated/deleted ij> execute q10 using 'values 123456.78'; 1 row inserted/updated/deleted ij> execute q11 using 'values 123456.78'; 1 row inserted/updated/deleted ij> execute q12 using 'select dt from temporal_values'; 1 row inserted/updated/deleted ij> execute q13 using 'select t from temporal_values'; 1 row inserted/updated/deleted ij> execute q14 using 'select ts from temporal_values'; 1 row inserted/updated/deleted ij> execute q15 using 'values char(654321)'; 1 row inserted/updated/deleted ij> execute q16 using 'values char(987654)'; 1 row inserted/updated/deleted ij> select * from t1; BT |BTV |C |D |I |R |S |DC |NUM |DT |T |TS |V |LVC ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- aa |NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|aa |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|123456 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |123456.78 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |4321 |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |12321 |NULL |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |123456 |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |123456 |NULL |NULL |NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |9876-05-04|NULL |NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |01:02:34|NULL |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |9876-05-04 01:02:34.0 |NULL |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |654321 |NULL NULL|NULL|NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |987654 ij> -- clean up t1 delete from t1; 13 rows inserted/updated/deleted ij> -- 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)); 0 rows inserted/updated/deleted ij> prepare param1 as 'insert into x values cast(? as char(1))'; ij> insert into x values cast('12' as char(1)); 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> execute param1 using 'values ''34'''; 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> select * from x; C1 ---- 1 3 ij> delete from x; 2 rows inserted/updated/deleted ij> insert into x values cast(12 as char(1)); ERROR 22001: A truncation error was encountered trying to shrink CHAR '12' to length 1. ij> execute param1 using 'values 34'; 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> select * from x; C1 ---- 3 ij> delete from x; 1 row inserted/updated/deleted ij> insert into x values cast(time('12:12:12') as char(1)); ERROR 22001: A truncation error was encountered trying to shrink CHAR '12:12:12' to length 1. ij> execute param1 using 'values time(''21:12:12'')'; 1 row inserted/updated/deleted WARNING 01004: Data truncation ij> select * from x; C1 ---- 2 ij> delete from x; 1 row inserted/updated/deleted ij> drop table x; 0 rows inserted/updated/deleted ij> -- method resolution tests -- clean up the prepared statements remove a1; ij> remove q1; ij> remove q2; ij> remove q4; ij> remove q5; ij> remove q6; ij> remove q7; ij> remove q8; ij> remove q10; ij> remove q11; ij> remove q12; ij> remove q13; ij> remove q14; ij> remove q15; ij> -- reset autocomiit commit; ij> autocommit on; ij> -- bind time casting tests -- negative values cast('asdf' as smallint); ERROR 22018: Invalid character string format for type SMALLINT. ij> values cast('asdf' as int); ERROR 22018: Invalid character string format for type INTEGER. ij> values cast('asdf' as bigint); ERROR 22018: Invalid character string format for type BIGINT. ij> values cast('asdf' as real); ERROR 42846: Cannot convert types 'CHAR' to 'REAL'. ij> values cast('asdf' as double precision); ERROR 42846: Cannot convert types 'CHAR' to 'DOUBLE'. ij> values cast('asdf' as decimal(5,4)); 1 ------- ERROR 22018: Invalid character string format for type DECIMAL. ij> values cast('asdf' as date); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values cast('asdf' as time); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values cast('asdf' as timestamp); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values cast('2999999999' as int); ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> values cast(2999999999 as int); ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> values cast('99999' as smallint); ERROR 22003: The resulting value is outside the range for the data type SHORT. ij> values cast(99999 as smallint); ERROR 22003: The resulting value is outside the range for the data type SMALLINT. ij> values cast(cast(99 as int) as char); ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1. ij> values cast(cast(-9 as int) as char); ERROR 22001: A truncation error was encountered trying to shrink CHAR '-9' to length 1. ij> values cast(cast(99 as smallint) as char); ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1. ij> values cast(cast(99 as bigint) as char); ERROR 22001: A truncation error was encountered trying to shrink CHAR '99' to length 1. ij> values cast(cast(9.9 as real) as char); ERROR 42846: Cannot convert types 'REAL' to 'CHAR'. ij> values cast(cast(9.9 as double precision) as char); ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. ij> -- positive values cast(1 as int); 1 ----------- 1 ij> values cast(1 as smallint); 1 ------ 1 ij> values cast(1 as bigint); 1 -------------------- 1 ij> values cast(1 as char); 1 - 1 ij> values cast('true' as char(4)); 1 ---- true ij> -- drop the tables drop table t1; 0 rows inserted/updated/deleted ij> drop table temporal_values; 0 rows inserted/updated/deleted ij> drop table strings; 0 rows inserted/updated/deleted ij> -- ISO time/timestamp formats values (cast ('08.08.08' as TIME)); 1 -------- 08:08:08 ij> values (cast ('2001-01-01-08.08.08.123456' as TIMESTAMP)); 1 ----------------------------- 2001-01-01 08:08:08.123456 ij> -- char, varchar values (char('abcde', 5)); 1 ----- abcde ij> values (char('abcde', 6)); 1 ------ abcde ij> values (char('abcde', 4)); 1 ---- abcd WARNING 01004: Data truncation ij> values (varchar('', 20)); 1 -------------------- ij> create table t1 (c5 date, c6 time, c7 timestamp, c8 char(5), c9 varchar(5)); 0 rows inserted/updated/deleted ij> insert into t1 values ('2003-09-10', '16:44:02', '2003-09-08 12:20:30.123456', 'abc', 'abcde'); 1 row inserted/updated/deleted ij> insert into t1 values ('2005-09-10', '18.44.02', '2004-09-08-12.20.30.123456', 'cba', 'c'); 1 row inserted/updated/deleted ij> select char(c5), char(c6), char(c7), char(c8), char(c9) from t1; 1 |2 |3 |4 |5 ------------------------------------------------------------- 2003-09-10|16:44:02|2003-09-08 12:20:30.123456 |abc |abcde 2005-09-10|18:44:02|2004-09-08 12:20:30.123456 |cba |c ij> select varchar(c5), varchar(c6), varchar(c7), varchar(c8), varchar(c9) from t1; 1 |2 |3 |4 |5 ------------------------------------------------------------- 2003-09-10|16:44:02|2003-09-08 12:20:30.123456 |abc |abcde 2005-09-10|18:44:02|2004-09-08 12:20:30.123456 |cba |c ij> select char(c8, 10), varchar(c9, 9) from t1; 1 |2 -------------------- abc |abcde cba |c ij> select { fn concat(c8, char(c8)) } from t1; 1 ---------- abc abc cba cba ij> select { fn concat(c8, varchar(c9)) } from t1; 1 ---------- abc abcde cba c ij> select { fn concat(varchar(c9, 20), char(c8, 8)) } from t1; 1 ---------------------------- abcdeabc ccba ij> select { fn concat(char(c9, 20), varchar(c8, 8)) } from t1; 1 ---------------------------- abcde abc c cba ij> -- clean up drop table t1; 0 rows inserted/updated/deleted ij> -- bug 5421 - support db2 udb compatible built-in functions values CHAR(INT(67890)); 1 ----------- 67890 ij> values CHAR(INTEGER(12345)); 1 ----------- 12345 ij> values CHAR(DEC(67.21,4,2)); ERROR 42X01: Syntax error: Encountered "DEC" at line 1, column 13. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> values CHAR(DECIMAL(67.10,4,2)); ERROR 42X01: Syntax error: Encountered "DECIMAL" at line 1, column 13. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> values CHAR(DOUBLE(5.55)); 1 ----------------------------------------------------- 5.55 ij> values CHAR(DOUBLE_PRECISION(5.555)); ERROR 42Y03: 'DOUBLE_PRECISION' is not recognized as a function or procedure. ij> values CHAR(BIGINT(1)); 1 -------------------- 1 ij> values CHAR(BIGINT(-1)); 1 -------------------- -1 ij> values LENGTH(CAST('hello' AS CHAR(25))); 1 ----------- 25 ij> values LENGTH(CAST('hello' AS VARCHAR(25))); 1 ----------- 5 ij> values LENGTH(CAST('hello' AS LONG VARCHAR)); 1 ----------- 5 ij> values CAST (X'03' as CHAR(5) for bit data); 1 ---------- 0320202020 ij> values CAST (X'04' as VARCHAR(5) for bit data); 1 ---------- 04 ij> values CAST (X'05' as LONG VARCHAR for bit data); 1 -------------------------------------------------------------------------------------------------------------------------------- 05 ij> -- clean up drop table t1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist. ij> -- test some casting from a java type to standard SQL types. -- should all fail at runtime select cast (aliasinfo as BOOLEAN) from sys.sysaliases; 1 ----- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'BOOLEAN'. ij> select cast (aliasinfo as SMALLINT) from sys.sysaliases; 1 ------ ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'SMALLINT'. ij> select cast (aliasinfo as INTEGER) from sys.sysaliases; 1 ----------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'INTEGER'. ij> select cast (aliasinfo as BIGINT) from sys.sysaliases; 1 -------------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'BIGINT'. ij> select cast (aliasinfo as REAL) from sys.sysaliases; 1 ------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'REAL'. ij> select cast (aliasinfo as DOUBLE) from sys.sysaliases; 1 ---------------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'DOUBLE'. ij> select cast (aliasinfo as DECIMAL(5,4)) from sys.sysaliases; 1 ------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'DECIMAL'. ij> select cast (aliasinfo as CHAR(30) FOR BIT DATA) from sys.sysaliases; 1 ------------------------------------------------------------ ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'CHAR () FOR BIT DATA'. ij> select cast (aliasinfo as VARCHAR(30) FOR BIT DATA) from sys.sysaliases; 1 ------------------------------------------------------------ ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'VARCHAR () FOR BIT DATA'. ij> select cast (aliasinfo as LONG VARCHAR FOR BIT DATA) from sys.sysaliases; 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'LONG VARCHAR FOR BIT DATA'. ij> select cast (aliasinfo as BLOB) from sys.sysaliases; 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'BLOB'. ij> select cast (aliasinfo as CLOB) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR'; 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.catalog.types.RoutineAliasInfo' into a data value of type 'CLOB'. ij> -- 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'; 1 -------------------------------------------------------------------------------------------------------------------------------- INSTALL_JAR(IN "URL" VARCHAR(256),IN "JAR" VARCHAR(128),IN "DEPLOY" INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DA& ij> select cast (aliasinfo as VARCHAR(240)) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR'; 1 -------------------------------------------------------------------------------------------------------------------------------- INSTALL_JAR(IN "URL" VARCHAR(256),IN "JAR" VARCHAR(128),IN "DEPLOY" INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DA& ij> select cast (aliasinfo as LONG VARCHAR) from sys.sysaliases where CAST(alias AS VARCHAR(128)) = 'INSTALL_JAR'; 1 -------------------------------------------------------------------------------------------------------------------------------- INSTALL_JAR(IN "URL" VARCHAR(256),IN "JAR" VARCHAR(128),IN "DEPLOY" INTEGER) LANGUAGE JAVA PARAMETER STYLE JAVA MODIFIES SQL DA& ij>