File: cast.out

package info (click to toggle)
derby 10.14.2.0-3
links: PTS, VCS
area: main
in suites: forky, sid, trixie
size: 79,056 kB
sloc: java: 691,961; sql: 42,686; xml: 20,512; sh: 3,373; sed: 96; makefile: 60
file content (1736 lines) | stat: -rw-r--r-- 66,065 bytes
parent folder | download | duplicates (4)
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 date/time 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 date/time 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 date/time 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 substr(cast (cast (TIME('11:11:11') as timestamp) as char(50)), 1, 10) = cast (current_date as char(10));
1    
-----
true 
ij> -- now make sure we got the time right
values substr(cast (cast (TIME('11:11:11') as timestamp) as char(30)), 12);
1                             
------------------------------
11:11:11.0                    
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));
1                            
-----------------------------
1999-09-09 00:00:00.0        
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 date/time value is incorrect.
ij> select cast(c as time) from tab1;
1       
--------
ERROR 22007: The syntax of the string representation of a date/time value is incorrect.
ij> select cast(c as timestamp) from tab1;
1                            
-----------------------------
ERROR 22007: The syntax of the string representation of a date/time 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 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 date/time value is incorrect.
ij> values cast('asdf' as time);
ERROR 22007: The syntax of the string representation of a date/time value is incorrect.
ij> values cast('asdf' as timestamp);
ERROR 22007: The syntax of the string representation of a date/time 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>