implicitconversions.sql

derby database source code.good for you.

-- ** insert implicitConversionsPositive.sql
-- tests for implicit conversions between string and non-string types
-- and vice versa

-- create an all types table
create table all1(si smallint, i int, li bigint, r real, 
				  dp double precision, dc decimal(5,1), num numeric(5,1),
				  b char(2) for bit data, bv varchar(2) for bit data,
				  lbv long varchar FOR bit data,
				  dt date, tm time, tms timestamp,
				  c char(1), vc varchar(1), lvc long varchar);

-- populate table
insert into all1 values (2, 3, 4, 5.5, 6.6, 7.7, 8.8,
				  		 X'0020', X'0020', X'0020',
				  		 date('1996-09-09'), time('12:12:12'), 
				  		 timestamp('1996-09-09 12:12:12.5'),
				  		 '1', '2', '33333333');

-- unions between string and non-string types
values cast(1 as smallint), cast('2' as char(1));
values cast(1 as smallint), cast('2' as varchar(1));
values cast(1 as smallint), cast('2' as long varchar);
values cast('2' as char(1)), cast(1 as smallint);
values cast('2' as varchar(1)), cast(1 as smallint);
values cast('2' as long varchar), cast(1 as smallint);
values cast(1 as int), cast('2' as char(1));
values cast(1 as int), cast('2' as varchar(1));
values cast(1 as int), cast('2' as long varchar);
values cast('2' as char(1)), cast(1 as int);
values cast('2' as varchar(1)), cast(1 as int);
values cast('2' as long varchar), cast(1 as int);
values cast(1 as bigint), cast('2' as char(1));
values cast(1 as bigint), cast('2' as varchar(1));
values cast(1 as bigint), cast('2' as long varchar);
values cast('2' as char(1)), cast(1 as bigint);
values cast('2' as varchar(1)), cast(1 as bigint);
values cast('2' as long varchar), cast(1 as bigint);
values cast(1.1 as real), cast('2' as char(1));
values cast(1.1 as real), cast('2' as varchar(1));
values cast(1.1 as real), cast('2' as long varchar);
values cast('2' as char(1)), cast(1.1 as real);
values cast('2' as varchar(1)), cast(1.1 as real);
values cast('2' as long varchar), cast(1.1 as real);
values cast(1.1 as double precision), cast('2' as char(1));
values cast(1.1 as double precision), cast('2' as varchar(1));
values cast(1.1 as double precision), cast('2' as long varchar);
values cast('2' as char(1)), cast(1.1 as double precision);
values cast('2' as varchar(1)), cast(1.1 as double precision);
values cast('2' as long varchar), cast(1.1 as double precision);
values cast(1.1 as decimal(5,1)), cast('2' as char(1));
values cast(1.1 as decimal(5,1)), cast('2' as varchar(1));
values cast(1.1 as decimal(5,1)), cast('2' as long varchar);
values cast('2' as char(1)), cast(1.1 as decimal(5,1));
values cast('2' as varchar(1)), cast(1.1 as decimal(5,1));
values cast('2' as long varchar), cast(1.1 as decimal(5,1));
values cast(1.1 as decimal(5,1)), '0.002';
values cast(1.1 as decimal(5,1)), '0.002';
values '0.002', cast(1.1 as decimal(5,1));
values '0.002', cast(1.1 as decimal(5,1));
values 'abcde', 'fghij';
values 'abcde', cast('fghij' as varchar(5));
values 'abcde', cast('fghij' as long varchar);
values cast('abcde' as varchar(5)), 'fghij';
values cast('abcde' as long varchar), 'fghij';
-- DB2 UDB allows comparisons between hex constants and character constants
-- DB2 CS does not allow comparisons between hex constants and character constants
values X'01', '3';
values X'01', cast('3' as varchar(5));
values X'01', cast('3' as long varchar);
values '3', X'01';
values cast('3' as varchar(5)), X'01';
values cast('3' as long varchar), X'01';
values date('1996-09-09'), '1995-08-08';
values date('1996-09-09'), cast('1995-08-08' as varchar(10));
values date('1996-09-09'), cast('1995-08-08' as long varchar);
values '1995-08-08', date('1996-09-09');
values cast('1995-08-08' as varchar(10)), date('1996-09-09');
values cast('1995-08-08' as long varchar), date('1996-09-09');
values time('12:12:12'), '11:11:11';
values time('12:12:12'), cast('11:11:11' as varchar(8));
values time('12:12:12'), cast('11:11:11' as long varchar);
values '11:11:11', time('12:12:12');
values cast('11:11:11' as varchar(8)), time('12:12:12');
values cast('11:11:11' as long varchar), time('12:12:12');
values timestamp('1996-09-09 12:12:12.5'), '1996-08-08 11:11:11.1';
values timestamp('1996-09-09 12:12:12.5'), cast('1996-08-08 11:11:11.1' as varchar(30));
values timestamp('1996-09-09 12:12:12.5'), cast('1996-08-08 11:11:11.1' as long varchar);
values '1996-08-08 11:11:11.1', timestamp('1996-09-09 12:12:12.5');
values cast('1996-08-08 11:11:11.1' as varchar(30)), timestamp('1996-09-09 12:12:12.5');
values cast('1996-08-08 11:11:11.1' as long varchar), timestamp('1996-09-09 12:12:12.5');

-- comparisons at the language level
select si from all1 where cast(1 as smallint) = '1';
select si from all1 where cast(1 as smallint) > '2';
select si from all1 where cast(1 as smallint) >= '2';
select si from all1 where cast(1 as smallint) < '2';
select si from all1 where cast(1 as smallint) <= '2';
select si from all1 where cast(1 as smallint) <> '2';
select si from all1 where cast(1 as smallint) = cast(null as char);
select si from all1 where cast(1 as smallint) > cast(null as char);
select si from all1 where cast(1 as smallint) >= cast(null as char);
select si from all1 where cast(1 as smallint) < cast(null as char);
select si from all1 where cast(1 as smallint) <= cast(null as char);
select si from all1 where cast(1 as smallint) <> cast(null as char);
select si from all1 where '1' = cast(1 as smallint);
select si from all1 where '2' > cast(1 as smallint);
select si from all1 where '2' >= cast(1 as smallint);
select si from all1 where '2' < cast(1 as smallint);
select si from all1 where '2' <= cast(1 as smallint);
select si from all1 where '2' <> cast(1 as smallint);
select si from all1 where cast(null as char) = cast(1 as smallint);
select si from all1 where cast(null as char) > cast(1 as smallint);
select si from all1 where cast(null as char) >= cast(1 as smallint);
select si from all1 where cast(null as char) < cast(1 as smallint);
select si from all1 where cast(null as char) <= cast(1 as smallint);
select si from all1 where cast(null as char) <> cast(1 as smallint);
select si from all1 where cast(1 as int) = '1';
select si from all1 where cast(1 as int) > '2';
select si from all1 where cast(1 as int) >= '2';
select si from all1 where cast(1 as int) < '2';
select si from all1 where cast(1 as int) <= '2';
select si from all1 where cast(1 as int) <> '2';
select si from all1 where cast(1 as int) = cast(null as char);
select si from all1 where cast(1 as int) > cast(null as char);
select si from all1 where cast(1 as int) >= cast(null as char);
select si from all1 where cast(1 as int) < cast(null as char);
select si from all1 where cast(1 as int) <= cast(null as char);
select si from all1 where cast(1 as int) <> cast(null as char);
select si from all1 where '1' = cast(1 as int);
select si from all1 where '2' > cast(1 as int);
select si from all1 where '2' >= cast(1 as int);
select si from all1 where '2' < cast(1 as int);
select si from all1 where '2' <> cast(1 as int);
select si from all1 where '2' <= cast(1 as int);
select si from all1 where cast(null as char) = cast(1 as int);
select si from all1 where cast(null as char) > cast(1 as int);
select si from all1 where cast(null as char) >= cast(1 as int);
select si from all1 where cast(null as char) < cast(1 as int);
select si from all1 where cast(null as char) <> cast(1 as int);
select si from all1 where cast(null as char) <= cast(1 as int);
select si from all1 where cast(1 as bigint) = '1';
select si from all1 where cast(1 as bigint) > '2';
select si from all1 where cast(1 as bigint) >= '2';
select si from all1 where cast(1 as bigint) < '2';
select si from all1 where cast(1 as bigint) <= '2';
select si from all1 where cast(1 as bigint) <> '2';
select si from all1 where cast(1 as bigint) = cast(null as char);
select si from all1 where cast(1 as bigint) > cast(null as char);
select si from all1 where cast(1 as bigint) >= cast(null as char);
select si from all1 where cast(1 as bigint) < cast(null as char);
select si from all1 where cast(1 as bigint) <= cast(null as char);
select si from all1 where cast(1 as bigint) <> cast(null as char);
select si from all1 where '1' = cast(1 as bigint);
select si from all1 where '2' > cast(1 as bigint);
select si from all1 where '2' >= cast(1 as bigint);
select si from all1 where '2' < cast(1 as bigint);
select si from all1 where '2' <= cast(1 as bigint);
select si from all1 where '2' <> cast(1 as bigint);
select si from all1 where cast(null as char) = cast(1 as bigint);
select si from all1 where cast(null as char) > cast(1 as bigint);
select si from all1 where cast(null as char) >= cast(1 as bigint);
select si from all1 where cast(null as char) < cast(1 as bigint);
select si from all1 where cast(null as char) <= cast(1 as bigint);
select si from all1 where cast(null as char) <> cast(1 as bigint);
select si from all1 where cast(1 as real) = '1';
select si from all1 where cast(1 as real) > '2';
select si from all1 where cast(1 as real) >= '2';
select si from all1 where cast(1 as real) < '2';
select si from all1 where cast(1 as real) <> '2';
select si from all1 where cast(1 as real) <= '2';
select si from all1 where cast(1 as real) = cast(null as char);
select si from all1 where cast(1 as real) > cast(null as char);
select si from all1 where cast(1 as real) >= cast(null as char);
select si from all1 where cast(1 as real) < cast(null as char);
select si from all1 where cast(1 as real) <> cast(null as char);
select si from all1 where cast(1 as real) <= cast(null as char);
select si from all1 where '1' = cast(1 as real);
select si from all1 where '2' > cast(1 as real);
select si from all1 where '2' >= cast(1 as real);
select si from all1 where '2' < cast(1 as real);
select si from all1 where '2' <= cast(1 as real);
select si from all1 where '2' <> cast(1 as real);
select si from all1 where cast(null as char) = cast(1 as real);
select si from all1 where cast(null as char) > cast(1 as real);
select si from all1 where cast(null as char) >= cast(1 as real);
select si from all1 where cast(null as char) < cast(1 as real);
select si from all1 where cast(null as char) <= cast(1 as real);
select si from all1 where cast(null as char) <> cast(1 as real);
select si from all1 where cast(1 as double precision) = '1';
select si from all1 where cast(1 as double precision) > '2';
select si from all1 where cast(1 as double precision) >= '2';
select si from all1 where cast(1 as double precision) < '2';
select si from all1 where cast(1 as double precision) <= '2';
select si from all1 where cast(1 as double precision) <> '2';
select si from all1 where cast(1 as double precision) = cast(null as char);
select si from all1 where cast(1 as double precision) > cast(null as char);
select si from all1 where cast(1 as double precision) >= cast(null as char);
select si from all1 where cast(1 as double precision) < cast(null as char);
select si from all1 where cast(1 as double precision) <= cast(null as char);
select si from all1 where cast(1 as double precision) <> cast(null as char);
select si from all1 where '1' = cast(1 as double precision);
select si from all1 where '2' > cast(1 as double precision);
select si from all1 where '2' >= cast(1 as double precision);
select si from all1 where '2' < cast(1 as double precision);
select si from all1 where '2' <= cast(1 as double precision);
select si from all1 where '2' <> cast(1 as double precision);
select si from all1 where cast(null as char) = cast(1 as double precision);
select si from all1 where cast(null as char) > cast(1 as double precision);
select si from all1 where cast(null as char) >= cast(1 as double precision);
select si from all1 where cast(null as char) < cast(1 as double precision);
select si from all1 where cast(null as char) <= cast(1 as double precision);
select si from all1 where cast(null as char) <> cast(1 as double precision);
select si from all1 where cast(1 as numeric) = '1';
select si from all1 where cast(1 as numeric) > '2';
select si from all1 where cast(1 as numeric) >= '2';
select si from all1 where cast(1 as numeric) < '2';
select si from all1 where cast(1 as numeric) <= '2';
select si from all1 where cast(1 as numeric) <> '2';
select si from all1 where cast(1 as numeric) = cast(null as char);
select si from all1 where cast(1 as numeric) > cast(null as char);
select si from all1 where cast(1 as numeric) >= cast(null as char);
select si from all1 where cast(1 as numeric) < cast(null as char);
select si from all1 where cast(1 as numeric) <= cast(null as char);
select si from all1 where cast(1 as numeric) <> cast(null as char);
select si from all1 where '1' = cast(1 as numeric);
select si from all1 where '2' > cast(1 as numeric);
select si from all1 where '2' >= cast(1 as numeric);
select si from all1 where '2' < cast(1 as numeric);
select si from all1 where '2' <= cast(1 as numeric);
select si from all1 where '2' <> cast(1 as numeric);
select si from all1 where cast(null as char) = cast(1 as numeric);
select si from all1 where cast(null as char) > cast(1 as numeric);
select si from all1 where cast(null as char) >= cast(1 as numeric);
select si from all1 where cast(null as char) < cast(1 as numeric);
select si from all1 where cast(null as char) <= cast(1 as numeric);
select si from all1 where cast(null as char) <> cast(1 as numeric);
-- the following queries return 1 if the search condition is satisfied
-- and returns nothing if the search condition is not satisfied
select 1 from all1 where '1996-09-09' = date('1996-09-09');
select 1 from all1 where '1996-9-10' > date('1996-09-09');
select 1 from all1 where '1996-9-10' >= date('1996-09-09');
select 1 from all1 where '1996-9-10' < date('1996-09-09');
select 1 from all1 where '1996-9-10' <= date('1996-09-09');
select 1 from all1 where '1996-9-10' <> date('1996-09-09');
select 1 from all1 where cast(null as char) = date('1996-09-09');
select 1 from all1 where cast(null as char)> date('1996-09-09');
select 1 from all1 where cast(null as char)>= date('1996-09-09');
select 1 from all1 where cast(null as char)< date('1996-09-09');
select 1 from all1 where cast(null as char)<= date('1996-09-09');
select 1 from all1 where cast(null as char)<> date('1996-09-09');
select 1 from all1 where date('1996-09-09') = '1996-09-09';
select 1 from all1 where date('1996-9-10') > '1996-09-09';
select 1 from all1 where date('1996-9-10') >= '1996-09-09';
select 1 from all1 where date('1996-9-10') < '1996-09-09';
select 1 from all1 where date('1996-9-10') <= '1996-09-09';
select 1 from all1 where date('1996-9-10') <> '1996-09-09';
select 1 from all1 where date('1996-09-09') = cast(null as char);
select 1 from all1 where date('1996-9-10') > cast(null as char);
select 1 from all1 where date('1996-9-10') >= cast(null as char);
select 1 from all1 where date('1996-9-10') < cast(null as char);
select 1 from all1 where date('1996-9-10') <= cast(null as char);
select 1 from all1 where date('1996-9-10') <> cast(null as char);
select 1 from all1 where '12:12:12' = time('12:12:12');
select 1 from all1 where '12:13:12' > time('12:12:12');
select 1 from all1 where '12:13:12' >= time('12:12:12');
select 1 from all1 where '12:13:12' < time('12:12:12');
select 1 from all1 where '12:13:12' <= time('12:12:12');
select 1 from all1 where '12:13:12' <> time('12:12:12');
select 1 from all1 where cast(null as char) = time('12:12:12');
select 1 from all1 where cast(null as char) > time('12:12:12');
select 1 from all1 where cast(null as char) >= time('12:12:12');
select 1 from all1 where cast(null as char) < time('12:12:12');
select 1 from all1 where cast(null as char) <= time('12:12:12');
select 1 from all1 where cast(null as char) <> time('12:12:12');
select 1 from all1 where time('12:12:12') = '12:12:12';
select 1 from all1 where time('12:13:12') > '12:12:12';
select 1 from all1 where time('12:13:12') >= '12:12:12';
select 1 from all1 where time('12:13:12') < '12:12:12';
select 1 from all1 where time('12:13:12') <= '12:12:12';
select 1 from all1 where time('12:13:12') <> '12:12:12';
select 1 from all1 where time('12:12:12') = cast(null as char);
select 1 from all1 where time('12:13:12') > cast(null as char);
select 1 from all1 where time('12:13:12') >= cast(null as char);
select 1 from all1 where time('12:13:12') < cast(null as char);
select 1 from all1 where time('12:13:12') <= cast(null as char);
select 1 from all1 where time('12:13:12') <> cast(null as char);
select 1 from all1 where '1996-09-09 12:12:12.4' = timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where '1996-09-09 12:12:12.5' > timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where '1996-09-09 12:12:12.5' >= timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where '1996-09-09 12:12:12.5' < timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where '1996-09-09 12:12:12.5' <= timestamp('1996-09-09 12:12:12.4');