implicitconversions.sql

derby database source code.good for you.

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 cast(null as char) = timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where cast(null as char) > timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where cast(null as char) >= timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where cast(null as char) < timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where cast(null as char) <= timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where cast(null as char) <> timestamp('1996-09-09 12:12:12.4');
select 1 from all1 where timestamp('1996-09-09 12:12:12.4' )= '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )> '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )>= '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )< '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )<= '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )<> '1996-09-09 12:12:12.4';
select 1 from all1 where timestamp('1996-09-09 12:12:12.4' )= cast(null as char);
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )> cast(null as char);
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )>= cast(null as char);
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )< cast(null as char);
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )<= cast(null as char);
select 1 from all1 where timestamp('1996-09-09 12:12:12.5' )<> cast(null as char);
select si from all1 where ' ' = X'0020';
select si from all1 where ' ' > X'001F';
select si from all1 where ' ' >= X'001F';
select si from all1 where ' ' < X'001F';
select si from all1 where ' ' <= X'001F';
select si from all1 where ' ' <> X'001F';
select si from all1 where cast(null as char) = X'0020';
select si from all1 where cast(null as char) > X'001F';
select si from all1 where cast(null as char) >= X'001F';
select si from all1 where cast(null as char) < X'001F';
select si from all1 where cast(null as char) <= X'001F';
select si from all1 where cast(null as char) <> X'001F';
select si from all1 where X'0020' = ' ';
select si from all1 where X'001F' > ' ';
select si from all1 where X'001F' >= ' ';
select si from all1 where X'001F' < ' ';
select si from all1 where X'001F' <= ' ';
select si from all1 where X'001F' <> ' ';
select si from all1 where X'0020' = cast(null as char);
select si from all1 where X'001F' > cast(null as char);
select si from all1 where X'001F' >= cast(null as char);
select si from all1 where X'001F' < cast(null as char);
select si from all1 where X'001F' <= cast(null as char);
select si from all1 where X'001F' <> cast(null as char);

create table all_c1(c_ti char(3), c_si char(10), c_i char(30), c_li char(30), 
					c_r char(50), c_dp char(50), c_dc char(10), 
					c_num char(50), c_bool char(5), c_b char(8), 
					vc_bv varchar(16), vc_lbv varchar(16), c_dt char(10),
					c_tm char(16), c_tms char(21), c_c char(30),
					vc_vc char(30), c_lvc char(30));
insert into all_c1 values ('1', '2', '3', '4', '5.5', '6.6', '7.7', '8.8',
						   'true', ' ', ' ', ' ', '1996-09-09', '12:12:12',
						   '1996-09-09 12:12:12.5', '1', '2', '33333333');

-- test qualifiers with conversion from (var)char
select 1 from all_c1, all1 where si = c_si;
select 1 from all_c1, all1 where si > c_si;
select 1 from all_c1, all1 where si >= c_si;
select 1 from all_c1, all1 where si < c_si;
select 1 from all_c1, all1 where si <= c_si;
select 1 from all_c1, all1 where si <> c_si;
select 1 from all_c1, all1 where i = c_i;
select 1 from all_c1, all1 where i > c_i;
select 1 from all_c1, all1 where i >= c_i;
select 1 from all_c1, all1 where i < c_i;
select 1 from all_c1, all1 where i <= c_i;
select 1 from all_c1, all1 where i <> c_i;
select 1 from all_c1, all1 where li = c_li;
select 1 from all_c1, all1 where li > c_li;
select 1 from all_c1, all1 where li >= c_li;
select 1 from all_c1, all1 where li < c_li;
select 1 from all_c1, all1 where li <= c_li;
select 1 from all_c1, all1 where li <> c_li;
select 1 from all_c1, all1 where r = c_r;
select 1 from all_c1, all1 where r > c_r;
select 1 from all_c1, all1 where r >= c_r;
select 1 from all_c1, all1 where r < c_r;
select 1 from all_c1, all1 where r <= c_r;
select 1 from all_c1, all1 where r <> c_r;
select 1 from all_c1, all1 where dp = c_dp;
select 1 from all_c1, all1 where dp > c_dp;
select 1 from all_c1, all1 where dp >= c_dp;
select 1 from all_c1, all1 where dp < c_dp;
select 1 from all_c1, all1 where dp <= c_dp;
select 1 from all_c1, all1 where dp <> c_dp;
select 1 from all_c1, all1 where dc = c_dc;
select 1 from all_c1, all1 where dc > c_dc;
select 1 from all_c1, all1 where dc >= c_dc;
select 1 from all_c1, all1 where dc < c_dc;
select 1 from all_c1, all1 where dc <= c_dc;
select 1 from all_c1, all1 where dc <> c_dc;
select 1 from all_c1, all1 where b = c_b;
select 1 from all_c1, all1 where b > c_b;
select 1 from all_c1, all1 where b >= c_b;
select 1 from all_c1, all1 where b < c_b;
select 1 from all_c1, all1 where b <= c_b;
select 1 from all_c1, all1 where b <> c_b;
select 1 from all_c1, all1 where bv = vc_bv;
select 1 from all_c1, all1 where bv > vc_bv;
select 1 from all_c1, all1 where bv >= vc_bv;
select 1 from all_c1, all1 where bv < vc_bv;
select 1 from all_c1, all1 where bv <= vc_bv;
select 1 from all_c1, all1 where bv <> vc_bv;
select 1 from all_c1, all1 where lbv = vc_bv;
select 1 from all_c1, all1 where lbv > vc_bv;
select 1 from all_c1, all1 where lbv >= vc_bv;
select 1 from all_c1, all1 where lbv < vc_bv;
select 1 from all_c1, all1 where lbv <= vc_bv;
select 1 from all_c1, all1 where lbv <> vc_bv;
select 1 from all_c1, all1 where dt = c_dt;
select 1 from all_c1, all1 where dt > c_dt;
select 1 from all_c1, all1 where dt >= c_dt;
select 1 from all_c1, all1 where dt < c_dt;
select 1 from all_c1, all1 where dt <= c_dt;
select 1 from all_c1, all1 where dt <> c_dt;
select 1 from all_c1, all1 where tm = c_tm;
select 1 from all_c1, all1 where tm > c_tm;
select 1 from all_c1, all1 where tm >= c_tm;
select 1 from all_c1, all1 where tm < c_tm;
select 1 from all_c1, all1 where tm <= c_tm;
select 1 from all_c1, all1 where tm <> c_tm;
select 1 from all_c1, all1 where tms = c_tms;
select 1 from all_c1, all1 where tms > c_tms;
select 1 from all_c1, all1 where tms >= c_tms;
select 1 from all_c1, all1 where tms < c_tms;
select 1 from all_c1, all1 where tms <= c_tms;
select 1 from all_c1, all1 where tms <> c_tms;
select 1 from all_c1, all1 where lvc = c_lvc;
select 1 from all_c1, all1 where lvc > c_lvc;
select 1 from all_c1, all1 where lvc >= c_lvc;
select 1 from all_c1, all1 where lvc < c_lvc;
select 1 from all_c1, all1 where lvc <= c_lvc;
select 1 from all_c1, all1 where lvc <> c_lvc;

autocommit off;
delete from all_c1;
insert into all_c1 (c_ti) values (null);

select 1 from all_c1, all1 where si = c_si;
select 1 from all_c1, all1 where si > c_si;
select 1 from all_c1, all1 where si >= c_si;
select 1 from all_c1, all1 where si < c_si;
select 1 from all_c1, all1 where si <= c_si;
select 1 from all_c1, all1 where si <> c_si;
select 1 from all_c1, all1 where i = c_i;
select 1 from all_c1, all1 where i > c_i;
select 1 from all_c1, all1 where i >= c_i;
select 1 from all_c1, all1 where i < c_i;
select 1 from all_c1, all1 where i <= c_i;
select 1 from all_c1, all1 where i <> c_i;
select 1 from all_c1, all1 where li = c_li;
select 1 from all_c1, all1 where li > c_li;
select 1 from all_c1, all1 where li >= c_li;
select 1 from all_c1, all1 where li < c_li;
select 1 from all_c1, all1 where li <= c_li;
select 1 from all_c1, all1 where li <> c_li;
select 1 from all_c1, all1 where r = c_r;
select 1 from all_c1, all1 where r > c_r;
select 1 from all_c1, all1 where r >= c_r;
select 1 from all_c1, all1 where r < c_r;
select 1 from all_c1, all1 where r <= c_r;
select 1 from all_c1, all1 where r <> c_r;
select 1 from all_c1, all1 where dp = c_dp;
select 1 from all_c1, all1 where dp > c_dp;
select 1 from all_c1, all1 where dp >= c_dp;
select 1 from all_c1, all1 where dp < c_dp;
select 1 from all_c1, all1 where dp <= c_dp;
select 1 from all_c1, all1 where dp <> c_dp;
select 1 from all_c1, all1 where dc = c_dc;
select 1 from all_c1, all1 where dc > c_dc;
select 1 from all_c1, all1 where dc >= c_dc;
select 1 from all_c1, all1 where dc < c_dc;
select 1 from all_c1, all1 where dc <= c_dc;
select 1 from all_c1, all1 where dc <> c_dc;
select 1 from all_c1, all1 where b = c_b;
select 1 from all_c1, all1 where b > c_b;
select 1 from all_c1, all1 where b >= c_b;
select 1 from all_c1, all1 where b < c_b;
select 1 from all_c1, all1 where b <= c_b;
select 1 from all_c1, all1 where b <> c_b;
select 1 from all_c1, all1 where bv = vc_bv;
select 1 from all_c1, all1 where bv > vc_bv;
select 1 from all_c1, all1 where bv >= vc_bv;
select 1 from all_c1, all1 where bv < vc_bv;
select 1 from all_c1, all1 where bv <= vc_bv;
select 1 from all_c1, all1 where bv <> vc_bv;
select 1 from all_c1, all1 where lbv = vc_bv;
select 1 from all_c1, all1 where lbv > vc_bv;
select 1 from all_c1, all1 where lbv >= vc_bv;
select 1 from all_c1, all1 where lbv < vc_bv;
select 1 from all_c1, all1 where lbv <= vc_bv;
select 1 from all_c1, all1 where lbv <> vc_bv;
select 1 from all_c1, all1 where dt = c_dt;
select 1 from all_c1, all1 where dt > c_dt;
select 1 from all_c1, all1 where dt >= c_dt;
select 1 from all_c1, all1 where dt < c_dt;
select 1 from all_c1, all1 where dt <= c_dt;
select 1 from all_c1, all1 where dt <> c_dt;
select 1 from all_c1, all1 where tm = c_tm;
select 1 from all_c1, all1 where tm > c_tm;
select 1 from all_c1, all1 where tm >= c_tm;
select 1 from all_c1, all1 where tm < c_tm;
select 1 from all_c1, all1 where tm <= c_tm;
select 1 from all_c1, all1 where tm <> c_tm;
select 1 from all_c1, all1 where tms = c_tms;
select 1 from all_c1, all1 where tms > c_tms;
select 1 from all_c1, all1 where tms >= c_tms;
select 1 from all_c1, all1 where tms < c_tms;
select 1 from all_c1, all1 where tms <= c_tms;
select 1 from all_c1, all1 where tms <> c_tms;
select 1 from all_c1, all1 where lvc = c_lvc;
select 1 from all_c1, all1 where lvc > c_lvc;
select 1 from all_c1, all1 where lvc >= c_lvc;
select 1 from all_c1, all1 where lvc < c_lvc;
select 1 from all_c1, all1 where lvc <= c_lvc;
select 1 from all_c1, all1 where lvc <> c_lvc;

rollback;

-- test start and stop positions for conversions to (var)char
create index all_c1_ti on all_c1(c_ti);
create index all_c1_si on all_c1(c_si);
create index all_c1_i on all_c1(c_i);
create index all_c1_li on all_c1(c_li);
create index all_c1_r on all_c1(c_r);
create index all_c1_dp on all_c1(c_dp);
create index all_c1_dc on all_c1(c_dc);
create index all_c1_num on all_c1(c_num);
create index all_c1_bool on all_c1(c_bool);
create index all_c1_b on all_c1(c_b);
create index all_c1_bv on all_c1(vc_bv);
create index all_c1_dt on all_c1(c_dt);
create index all_c1_tm on all_c1(c_tm);
create index all_c1_tms on all_c1(c_tms);
create index all_c1_lvc on all_c1(c_lvc);

select 1 from all_c1, all1 where si = c_si;
select 1 from all_c1, all1 where si > c_si;
select 1 from all_c1, all1 where si >= c_si;
select 1 from all_c1, all1 where si < c_si;
select 1 from all_c1, all1 where si <= c_si;
select 1 from all_c1, all1 where i = c_i;
select 1 from all_c1, all1 where i > c_i;
select 1 from all_c1, all1 where i >= c_i;
select 1 from all_c1, all1 where i < c_i;
select 1 from all_c1, all1 where i <= c_i;
select 1 from all_c1, all1 where li = c_li;
select 1 from all_c1, all1 where li > c_li;
select 1 from all_c1, all1 where li >= c_li;
select 1 from all_c1, all1 where li < c_li;
select 1 from all_c1, all1 where li <= c_li;
select 1 from all_c1, all1 where r = c_r;
select 1 from all_c1, all1 where r > c_r;
select 1 from all_c1, all1 where r >= c_r;
select 1 from all_c1, all1 where r < c_r;
select 1 from all_c1, all1 where r <= c_r;
select 1 from all_c1, all1 where dp = c_dp;
select 1 from all_c1, all1 where dp > c_dp;
select 1 from all_c1, all1 where dp >= c_dp;
select 1 from all_c1, all1 where dp < c_dp;
select 1 from all_c1, all1 where dp <= c_dp;
select 1 from all_c1, all1 where dc = c_dc;
select 1 from all_c1, all1 where dc > c_dc;
select 1 from all_c1, all1 where dc >= c_dc;
select 1 from all_c1, all1 where dc < c_dc;
select 1 from all_c1, all1 where dc <= c_dc;
select 1 from all_c1, all1 where b = c_b;
select 1 from all_c1, all1 where b > c_b;
select 1 from all_c1, all1 where b >= c_b;
select 1 from all_c1, all1 where b < c_b;
select 1 from all_c1, all1 where b <= c_b;
select 1 from all_c1, all1 where bv = vc_bv;
select 1 from all_c1, all1 where bv > vc_bv;
select 1 from all_c1, all1 where bv >= vc_bv;
select 1 from all_c1, all1 where bv < vc_bv;
select 1 from all_c1, all1 where bv <= vc_bv;
select 1 from all_c1, all1 where lbv = vc_bv;
select 1 from all_c1, all1 where lbv > vc_bv;
select 1 from all_c1, all1 where lbv >= vc_bv;
select 1 from all_c1, all1 where lbv < vc_bv;
select 1 from all_c1, all1 where lbv <= vc_bv;
select 1 from all_c1, all1 where dt = c_dt;
select 1 from all_c1, all1 where dt > c_dt;
select 1 from all_c1, all1 where dt >= c_dt;
select 1 from all_c1, all1 where dt < c_dt;
select 1 from all_c1, all1 where dt <= c_dt;
select 1 from all_c1, all1 where tm = c_tm;
select 1 from all_c1, all1 where tm > c_tm;
select 1 from all_c1, all1 where tm >= c_tm;
select 1 from all_c1, all1 where tm < c_tm;
select 1 from all_c1, all1 where tm <= c_tm;
select 1 from all_c1, all1 where tms = c_tms;