cast.sql

derby database source code.good for you.

-- 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)));
values (cast ('hell' as varchar(2)));
-- shrink, whitespace only
values (cast ('he  ' as char(2)));
-- expand, check lengths
values (cast ('hell' as char(20))); 
values (cast ('hell' as varchar(20))); 
values length(cast ('hell' as char(20)));
values length(cast ('hell' as varchar(20)));

----------------
--char->bit data
----------------
-- shrink
values (cast (X'1111' as char(1) for bit data));
-- shrink, zero only
values (cast (X'1100' as char(1) for bit data));
-- expand
values (cast (X'1111' as char(2) for bit data));
-- w/o format
-- DB2 UDB PASS
-- DB2 CS FAIL
values (cast ('1234' as char(2) for bit data));

-- extra tests for shrinking parts of bits
values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);
values cast (X'00000011' as char(1) for bit data);

values cast (X'11111111' as char(1) for bit data);
values cast (X'01111111' as char(1) for bit data);
values cast (X'00111111' as char(1) for bit data);
values cast (X'00011111' as char(1) for bit data);
values cast (X'00001111' as char(1) for bit data);
values cast (X'00000111' as char(1) for bit data);
values cast (X'00000011' as char(1) for bit data);
values cast (X'00000001' as char(1) for bit data);
values cast (X'0011111111111111' as char(1) for bit data);
values cast (X'1111111100111111' as char(2) for bit data);

---------
--numbers
---------
values (cast (1.1 as int));
values (cast (1.1 as smallint));
values (cast (1.1 as bigint));
values (cast (1.1 as double precision));
values (cast (1.1 as numeric(2,1)));
values (cast (1.1 as decimal(2,1)));
values (cast (1.1 as numeric(2,0)));
values (cast (1.1 as decimal(2,0)));
values (cast (1.1 as float));
values (cast (1.1 as real));

values (cast (1.9 as int));
values (cast (1.9 as smallint));
values (cast (1.9 as bigint));
values (cast (1.9 as double precision));
values (cast (1.9 as numeric(2,1)));
values (cast (1.9 as decimal(2,1)));
values (cast (1.9 as numeric(2,0)));
values (cast (1.9 as decimal(2,0)));
values (cast (1.9 as float));
values (cast (1.9 as real));

-- bug 4352,4358 loss of precision on casts
--   9223372036854775807 is Long::MAX_VALUE
values (
  9223372036854775807,
  cast (9223372036854775807 as DECIMAL(24,1)),
  cast (
  cast (9223372036854775807 as DECIMAL(24,1)) as BIGINT)
  );

values (
  cast ('9223372036854775807' as DECIMAL(24,1)),
  cast (cast ('9223372036854775807' as DECIMAL(24,1)) as BIGINT)
  );
values (
  cast ('9223372036854775806' as DECIMAL(24,1)),
  cast (cast ('9223372036854775806' as DECIMAL(24,1)) as BIGINT)
  );
-- only this should fail
values (
  cast ('9223372036854775808' as DECIMAL(24,1)),
  cast (cast ('9223372036854775808' as DECIMAL(24,1)) as BIGINT)
  );
values (
  cast ('9223372036854775807.9' as DECIMAL(24,1)),
  cast (cast ('9223372036854775807.9' as DECIMAL(24,1)) as BIGINT)
  );


--   -9223372036854775808 is Long::MIN_VALUE

values (
  cast ('-9223372036854775808' as DECIMAL(24,1)),
  cast (cast ('-9223372036854775808' as DECIMAL(24,1)) as BIGINT)
  );
values (
  cast ('-9223372036854775807' as DECIMAL(24,1)),
  cast (cast ('-9223372036854775807' as DECIMAL(24,1)) as BIGINT)
  );
-- only this should fail
values (
  cast ('-9223372036854775809' as DECIMAL(24,1)),
  cast (cast ('-9223372036854775809' as DECIMAL(24,1)) as BIGINT)
  );
values (
  cast ('-9223372036854775808.9' as DECIMAL(24,1)),
  cast (cast ('-9223372036854775808.9' as DECIMAL(24,1)) as BIGINT)
  );

values (
  cast ('32767' as DECIMAL(24,1)),
  cast (cast ('32767' as DECIMAL(24,1)) as SMALLINT)
  );
values (
  cast ('32766' as DECIMAL(24,1)),
  cast (cast ('32766' as DECIMAL(24,1)) as SMALLINT)
  );
values (
  cast ('32768' as DECIMAL(24,1)),
  cast (cast ('32768' as DECIMAL(24,1)) as SMALLINT)
  );
-- only this should fail
values (
  cast ('32767.9' as DECIMAL(24,1)),
  cast (cast ('32767.9' as DECIMAL(24,1)) as SMALLINT)
  );

values (
  cast ('-32768' as DECIMAL(24,1)),
  cast (cast ('-32768' as DECIMAL(24,1)) as SMALLINT)
  );
values (
  cast ('-32767' as DECIMAL(24,1)),
  cast (cast ('-32767' as DECIMAL(24,1)) as SMALLINT)
  );
-- only this should fail
values (
  cast ('-32769' as DECIMAL(24,1)),
  cast (cast ('-32769' as DECIMAL(24,1)) as SMALLINT)
  );
values (
  cast ('-32768.9' as DECIMAL(24,1)),
  cast (cast ('-32768.9' as DECIMAL(24,1)) as SMALLINT)
  );

values (
  cast ('2147483647' as DECIMAL(24,1)),
  cast (cast ('2147483647' as DECIMAL(24,1)) as INTEGER)
  );
values (
  cast ('2147483646' as DECIMAL(24,1)),
  cast (cast ('2147483646' as DECIMAL(24,1)) as INTEGER)
  );
-- only this should fail
values (
  cast ('2147483648' as DECIMAL(24,1)),
  cast (cast ('2147483648' as DECIMAL(24,1)) as INTEGER)
  );
values (
  cast ('2147483647.9' as DECIMAL(24,1)),
  cast (cast ('2147483647.9' as DECIMAL(24,1)) as INTEGER)
  );

values (
  cast ('-2147483647' as DECIMAL(24,1)),
  cast (cast ('-2147483647' as DECIMAL(24,1)) as INTEGER)
  );
values (
  cast ('-2147483646' as DECIMAL(24,1)),
  cast (cast ('-2147483646' as DECIMAL(24,1)) as INTEGER)
  );
-- only this should fail
values (
  cast ('-2147483649' as DECIMAL(24,1)),
  cast (cast ('-2147483649' as DECIMAL(24,1)) as INTEGER)
  );
values (
  cast ('-2147483648.9' as DECIMAL(24,1)),
  cast (cast ('-2147483648.9' as DECIMAL(24,1)) as INTEGER)
  );
--numbers to char
values (cast (1.1 as char(10)));
values (cast (1.1 as varchar(10)));
values (cast (1e1 as varchar(10)));
values (cast (1e1 as char(10)));
values (cast (1 as char(10))); 
values (cast (1 as varchar(10))); 
values (cast (1e200 as char(10)));
values (cast (1e200 as varchar(10)));
values (cast (1 as long varchar));
values (cast (1.1 as long varchar));
values (cast (1e1 as long varchar));

--char to numbers
values (cast ('123' as smallint));
values (cast ('123' as int));
values (cast ('123' as bigint));
values (cast ('123' as double precision));
values (cast ('123' as float));
values (cast ('123' as real));
values (cast ('123' as numeric(3,0)));
values (cast ('123' as decimal(3,0)));

-- char (with decimal) to numbers  (truncates where needed Track #3756)
-- bug 5568
values (cast ('123.45' as smallint));
values (cast ('123.45' as int));
values (cast ('123.45' as bigint));
values (cast ('123.45' as double precision));
values (cast ('123.45' as float));
values (cast ('123.45' as real));
values (cast ('123.45' as numeric(5,1)));
values (cast ('123.45' as decimal(5,1)));

values (cast ('123.99' as smallint));
values (cast ('123.99' as int));
values (cast ('123.99' as bigint));
values (cast ('123.99' as double precision));
values (cast ('123.99' as float));
values (cast ('123.99' as real));
values (cast ('123.99' as numeric(5,1)));
values (cast ('123.99' as decimal(5,1)));


--bad
values (cast (1 as char(2) for bit data));
values (cast (1 as date)); 
values (cast (1 as time)); 
values (cast (1 as timestamp)); 

-------------------
--char -> date/time
-------------------
values (cast ('TIME''11:11:11''' as time));
values (cast ('11:11:11' as time));

values (cast ('DATE''1999-09-09''' as date));
values (cast ('1999-09-09' as date));

values (cast ('TIMESTAMP''1999-09-09 11:11:11''' as timestamp));
values (cast ('1999-09-09 11:11:11' as timestamp));

------------------
--date/time ->other
------------------
values (cast (TIME('11:11:11') as char(20)));
values (cast (DATE('1999-09-09') as char(20)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(40)));
values (cast (TIME('11:11:11') as varchar(20)));
values (cast (DATE('1999-09-09') as varchar(20)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as varchar(40)));
values (cast (TIME('11:11:11') as long varchar));
values (cast (DATE('1999-09-09') as long varchar));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as long varchar));

-- truncation errors
values (cast (TIME('11:11:11') as char(2)));
values (cast (DATE('1999-09-09') as char(2)));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as char(2)));

-- to date/time
values (cast (TIME('11:11:11') as time));
values (cast (TIME('11:11:11') as date));

-- this piece of convoluted logic is to ensure that we
-- get the current date for a conversion of time to timestamp
values cast (cast (TIME('11:11:11') as timestamp) as char(50)).substring(0, 10).equals(cast (current_date as char(10)));
-- now make sure we got the time right
values cast (cast (TIME('11:11:11') as timestamp) as char(30)).substring(11,21);


values (cast (DATE('1999-09-09') as date));
values (cast (DATE('1999-09-09') as time));
values (cast (DATE('1999-09-09') as timestamp));

values (cast (TIMESTAMP('1999-09-09 11:11:11' )as date));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as time));
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as timestamp));

--bad
values (cast (TIMESTAMP('1999-09-09 11:11:11' )as int));
values (cast (DATE('1999-09-09') as int));
values (cast (TIME('11:11:11') as int));

values (cast (TIMESTAMP('1999-09-09 11:11:11' )as smallint));
values (cast (DATE('1999-09-09') as smallint));
values (cast (TIME('11:11:11') as smallint));

values (cast (TIMESTAMP('1999-09-09 11:11:11' )as bigint));
values (cast (DATE('1999-09-09') as bigint));
values (cast (TIME('11:11:11') as bigint));

values (cast (TIMESTAMP('1999-09-09 11:11:11' )as numeric));
values (cast (DATE('1999-09-09') as numeric));
values (cast (TIME('11:11:11') as numeric));

values (cast (TIMESTAMP('1999-09-09 11:11:11' )as decimal));
values (cast (DATE('1999-09-09') as decimal));
values (cast (TIME('11:11:11') as decimal));