cast.out

derby database source code.good for you.

ij> -- 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.
-- once we have warnings we'll expect
-- a warning when shrinking non space/zeros
-- shrink
values (cast ('hell' as char(2)));
1   
----
he  
ij> values (cast ('hell' as varchar(2)));
1   
----
he  
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  
ij> -- shrink, zero only
values (cast (X'1100' as char(1) for bit data));
1   
----
11  
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  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00011111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00011111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00001111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00011111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00001111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00011111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00001111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000011' as char(1) for bit data);
1   
----
00  
ij> values cast (X'11111111' as char(1) for bit data);
1   
----
11  
ij> values cast (X'01111111' as char(1) for bit data);
1   
----
01  
ij> values cast (X'00111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00011111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00001111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000011' as char(1) for bit data);
1   
----
00  
ij> values cast (X'00000001' as char(1) for bit data);
1   
----
00  
ij> values cast (X'0011111111111111' as char(1) for bit data);
1   
----
00  
ij> values cast (X'1111111100111111' as char(2) for bit data);
1   
----
1111
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)),