implicitconversions.out

derby database source code.good for you.

ij> -- ** 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);
0 rows inserted/updated/deleted
ij> -- 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('xxxxxxFILTERED-TIMESTAMPxxxxx'),
				  		 '1', '2', '33333333');
1 row inserted/updated/deleted
ij> -- unions between string and non-string types
values cast(1 as smallint), cast('2' as char(1));
ERROR 42X61: Types 'SMALLINT' and 'CHAR' are not UNION compatible.
ij> values cast(1 as smallint), cast('2' as varchar(1));
ERROR 42X61: Types 'SMALLINT' and 'VARCHAR' are not UNION compatible.
ij> values cast(1 as smallint), cast('2' as long varchar);
ERROR 42X61: Types 'SMALLINT' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1 as smallint);
ERROR 42X61: Types 'CHAR' and 'SMALLINT' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1 as smallint);
ERROR 42X61: Types 'VARCHAR' and 'SMALLINT' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1 as smallint);
ERROR 42X61: Types 'LONG VARCHAR' and 'SMALLINT' are not UNION compatible.
ij> values cast(1 as int), cast('2' as char(1));
ERROR 42X61: Types 'INTEGER' and 'CHAR' are not UNION compatible.
ij> values cast(1 as int), cast('2' as varchar(1));
ERROR 42X61: Types 'INTEGER' and 'VARCHAR' are not UNION compatible.
ij> values cast(1 as int), cast('2' as long varchar);
ERROR 42X61: Types 'INTEGER' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1 as int);
ERROR 42X61: Types 'CHAR' and 'INTEGER' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1 as int);
ERROR 42X61: Types 'VARCHAR' and 'INTEGER' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1 as int);
ERROR 42X61: Types 'LONG VARCHAR' and 'INTEGER' are not UNION compatible.
ij> values cast(1 as bigint), cast('2' as char(1));
ERROR 42X61: Types 'BIGINT' and 'CHAR' are not UNION compatible.
ij> values cast(1 as bigint), cast('2' as varchar(1));
ERROR 42X61: Types 'BIGINT' and 'VARCHAR' are not UNION compatible.
ij> values cast(1 as bigint), cast('2' as long varchar);
ERROR 42X61: Types 'BIGINT' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1 as bigint);
ERROR 42X61: Types 'CHAR' and 'BIGINT' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1 as bigint);
ERROR 42X61: Types 'VARCHAR' and 'BIGINT' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1 as bigint);
ERROR 42X61: Types 'LONG VARCHAR' and 'BIGINT' are not UNION compatible.
ij> values cast(1.1 as real), cast('2' as char(1));
ERROR 42X61: Types 'REAL' and 'CHAR' are not UNION compatible.
ij> values cast(1.1 as real), cast('2' as varchar(1));
ERROR 42X61: Types 'REAL' and 'VARCHAR' are not UNION compatible.
ij> values cast(1.1 as real), cast('2' as long varchar);
ERROR 42X61: Types 'REAL' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1.1 as real);
ERROR 42X61: Types 'CHAR' and 'REAL' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1.1 as real);
ERROR 42X61: Types 'VARCHAR' and 'REAL' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1.1 as real);
ERROR 42X61: Types 'LONG VARCHAR' and 'REAL' are not UNION compatible.
ij> values cast(1.1 as double precision), cast('2' as char(1));
ERROR 42X61: Types 'DOUBLE' and 'CHAR' are not UNION compatible.
ij> values cast(1.1 as double precision), cast('2' as varchar(1));
ERROR 42X61: Types 'DOUBLE' and 'VARCHAR' are not UNION compatible.
ij> values cast(1.1 as double precision), cast('2' as long varchar);
ERROR 42X61: Types 'DOUBLE' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1.1 as double precision);
ERROR 42X61: Types 'CHAR' and 'DOUBLE' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1.1 as double precision);
ERROR 42X61: Types 'VARCHAR' and 'DOUBLE' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1.1 as double precision);
ERROR 42X61: Types 'LONG VARCHAR' and 'DOUBLE' are not UNION compatible.
ij> values cast(1.1 as decimal(5,1)), cast('2' as char(1));
ERROR 42X61: Types 'DECIMAL' and 'CHAR' are not UNION compatible.
ij> values cast(1.1 as decimal(5,1)), cast('2' as varchar(1));
ERROR 42X61: Types 'DECIMAL' and 'VARCHAR' are not UNION compatible.
ij> values cast(1.1 as decimal(5,1)), cast('2' as long varchar);
ERROR 42X61: Types 'DECIMAL' and 'LONG VARCHAR' are not UNION compatible.
ij> values cast('2' as char(1)), cast(1.1 as decimal(5,1));
ERROR 42X61: Types 'CHAR' and 'DECIMAL' are not UNION compatible.
ij> values cast('2' as varchar(1)), cast(1.1 as decimal(5,1));
ERROR 42X61: Types 'VARCHAR' and 'DECIMAL' are not UNION compatible.
ij> values cast('2' as long varchar), cast(1.1 as decimal(5,1));
ERROR 42X61: Types 'LONG VARCHAR' and 'DECIMAL' are not UNION compatible.
ij> values cast(1.1 as decimal(5,1)), '0.002';
ERROR 42X61: Types 'DECIMAL' and 'CHAR' are not UNION compatible.
ij> values cast(1.1 as decimal(5,1)), '0.002';
ERROR 42X61: Types 'DECIMAL' and 'CHAR' are not UNION compatible.
ij> values '0.002', cast(1.1 as decimal(5,1));
ERROR 42X61: Types 'CHAR' and 'DECIMAL' are not UNION compatible.
ij> values '0.002', cast(1.1 as decimal(5,1));
ERROR 42X61: Types 'CHAR' and 'DECIMAL' are not UNION compatible.
ij> values 'abcde', 'fghij';
1    
-----
abcde
fghij
ij> values 'abcde', cast('fghij' as varchar(5));
1    
-----
abcde
fghij
ij> values 'abcde', cast('fghij' as long varchar);
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
abcde                                                                                                                           
fghij                                                                                                                           
ij> values cast('abcde' as varchar(5)), 'fghij';
1    
-----
abcde
fghij
ij> values cast('abcde' as long varchar), 'fghij';
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
abcde                                                                                                                           
fghij                                                                                                                           
ij> -- 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';
ERROR 42X61: Types 'CHAR () FOR BIT DATA' and 'CHAR' are not UNION compatible.
ij> values X'01', cast('3' as varchar(5));
ERROR 42X61: Types 'CHAR () FOR BIT DATA' and 'VARCHAR' are not UNION compatible.
ij> values X'01', cast('3' as long varchar);
ERROR 42X61: Types 'CHAR () FOR BIT DATA' and 'LONG VARCHAR' are not UNION compatible.
ij> values '3', X'01';
ERROR 42X61: Types 'CHAR' and 'CHAR () FOR BIT DATA' are not UNION compatible.
ij> values cast('3' as varchar(5)), X'01';
ERROR 42X61: Types 'VARCHAR' and 'CHAR () FOR BIT DATA' are not UNION compatible.
ij> values cast('3' as long varchar), X'01';
ERROR 42X61: Types 'LONG VARCHAR' and 'CHAR () FOR BIT DATA' are not UNION compatible.
ij> values date('1996-09-09'), '1995-08-08';
1         
----------
1996-09-09
1995-08-08
ij> values date('1996-09-09'), cast('1995-08-08' as varchar(10));
1         
----------
1996-09-09
1995-08-08
ij> values date('1996-09-09'), cast('1995-08-08' as long varchar);
ERROR 42X61: Types 'DATE' and 'LONG VARCHAR' are not UNION compatible.
ij> values '1995-08-08', date('1996-09-09');
1         
----------
1995-08-08
1996-09-09
ij> values cast('1995-08-08' as varchar(10)), date('1996-09-09');
1         
----------
1995-08-08
1996-09-09
ij> values cast('1995-08-08' as long varchar), date('1996-09-09');
ERROR 42X61: Types 'LONG VARCHAR' and 'DATE' are not UNION compatible.
ij> values time('12:12:12'), '11:11:11';
1       
--------
12:12:12
11:11:11
ij> values time('12:12:12'), cast('11:11:11' as varchar(8));
1       
--------
12:12:12
11:11:11
ij> values time('12:12:12'), cast('11:11:11' as long varchar);
ERROR 42X61: Types 'TIME' and 'LONG VARCHAR' are not UNION compatible.
ij> values '11:11:11', time('12:12:12');
1       
--------
11:11:11
12:12:12
ij> values cast('11:11:11' as varchar(8)), time('12:12:12');
1       
--------
11:11:11
12:12:12
ij> values cast('11:11:11' as long varchar), time('12:12:12');
ERROR 42X61: Types 'LONG VARCHAR' and 'TIME' are not UNION compatible.
ij> values timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'), 'xxxxxxFILTERED-TIMESTAMPxxxxx';
1                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> values timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'), cast('xxxxxxFILTERED-TIMESTAMPxxxxx' as varchar(30));
1                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> values timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx'), cast('xxxxxxFILTERED-TIMESTAMPxxxxx' as long varchar);
ERROR 42X61: Types 'TIMESTAMP' and 'LONG VARCHAR' are not UNION compatible.
ij> values 'xxxxxxFILTERED-TIMESTAMPxxxxx', timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx');
1                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> values cast('xxxxxxFILTERED-TIMESTAMPxxxxx' as varchar(30)), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx');
1                         
--------------------------
xxxxxxFILTERED-TIMESTAMPxxxxx
xxxxxxFILTERED-TIMESTAMPxxxxx
ij> values cast('xxxxxxFILTERED-TIMESTAMPxxxxx' as long varchar), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx');
ERROR 42X61: Types 'LONG VARCHAR' and 'TIMESTAMP' are not UNION compatible.
ij> -- comparisons at the language level
select si from all1 where cast(1 as smallint) = '1';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) > '2';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) >= '2';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) < '2';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) <= '2';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) <> '2';
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) = cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) > cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) >= cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) < cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) <= cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where cast(1 as smallint) <> cast(null as char);
ERROR 42818: Comparisons between 'SMALLINT' and 'CHAR' are not supported.
ij> select si from all1 where '1' = cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where '2' > cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where '2' >= cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where '2' < cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where '2' <= cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where '2' <> cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where cast(null as char) = cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where cast(null as char) > cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.
ij> select si from all1 where cast(null as char) >= cast(1 as smallint);
ERROR 42818: Comparisons between 'CHAR' and 'SMALLINT' are not supported.