implicitconversions.sql

derby database source code.good for you.

select 1 from all1, all_c1 where b = c_b;
select 1 from all1, all_c1 where b > c_b;
select 1 from all1, all_c1 where b >= c_b;
select 1 from all1, all_c1 where b < c_b;
select 1 from all1, all_c1 where b <= c_b;
select 1 from all1, all_c1 where bv = vc_bv;
select 1 from all1, all_c1 where bv > vc_bv;
select 1 from all1, all_c1 where bv >= vc_bv;
select 1 from all1, all_c1 where bv < vc_bv;
select 1 from all1, all_c1 where bv <= vc_bv;
select 1 from all1, all_c1 where lbv = vc_bv;
select 1 from all1, all_c1 where lbv > vc_bv;
select 1 from all1, all_c1 where lbv >= vc_bv;
select 1 from all1, all_c1 where lbv < vc_bv;
select 1 from all1, all_c1 where lbv <= vc_bv;
select 1 from all1, all_c1 where dt = c_dt;
select 1 from all1, all_c1 where dt > c_dt;
select 1 from all1, all_c1 where dt >= c_dt;
select 1 from all1, all_c1 where dt < c_dt;
select 1 from all1, all_c1 where dt <= c_dt;
select 1 from all1, all_c1 where tm = c_tm;
select 1 from all1, all_c1 where tm > c_tm;
select 1 from all1, all_c1 where tm >= c_tm;
select 1 from all1, all_c1 where tm < c_tm;
select 1 from all1, all_c1 where tm <= c_tm;
select 1 from all1, all_c1 where tms = c_tms;
select 1 from all1, all_c1 where tms > c_tms;
select 1 from all1, all_c1 where tms >= c_tms;
select 1 from all1, all_c1 where tms < c_tms;
select 1 from all1, all_c1 where tms <= c_tms;
select 1 from all1, all_c1 where lvc = c_lvc;
select 1 from all1, all_c1 where lvc > c_lvc;
select 1 from all1, all_c1 where lvc >= c_lvc;
select 1 from all1, all_c1 where lvc < c_lvc;
select 1 from all1, all_c1 where lvc <= c_lvc;

delete from all1;
insert into all1(si) values (null);

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

-- drop the indexes;
rollback;



delete from all_c1;
-- insert with implicit conversions to (var)char
insert into all_c1 select * from all1;
select c_ti, si, c_si, i, c_i from all1, all_c1;
select li, c_li, r, c_r, dp, c_dp from all1, all_c1;
select dc, c_dc, num, c_num, c_bool from all1, all_c1;
select b, c_b, bv, vc_bv, lbv, vc_lbv, dt, c_dt from all1, all_c1;
select tm, c_tm, tms, c_tms, c, c_c from all1, all_c1;
select vc, vc_vc, lvc, c_lvc from all1, all_c1;

-- insert with implicit conversions from (var)char
insert into all1 select c_ti, c_si, c_i, c_li, c_r, c_dp,
					    c_dc, c_num, c_bool, ' ', ' ', ' ',
					    c_dt, c_tm, c_tms, '1', '2' from all_c1;
select c_ti, si, c_si, i, c_i from all1, all_c1;
select li, c_li, r, c_r, dp, c_dp from all1, all_c1;
select dc, c_dc, num, c_num, c_bool from all1, all_c1;
select b, c_b, bv, vc_bv, lbv, vc_lbv, dt, c_dt from all1, all_c1;
select tm, c_tm, tms, c_tms, c, c_c from all1, all_c1;
select vc, vc_vc, lvc, c_lvc from all1, all_c1;

rollback;

-- more insert conversions
create table t5_2(dc decimal(5,2), num numeric(5,2));
-- bug 827, NormalizeResultSet with char->decimal conversions
insert into t5_2 values ('11.95', '95.11');
select * from t5_2;
rollback;

-- update tests
alter table all1 add column c30 char(30) ;
alter table all1 add column vc30 varchar(30) ;
alter table all1 add column lvc2 long varchar ;
select * from all1;
update all1 set si = '11';
update all1 set i = '11';
update all1 set li = '11';
update all1 set r = '11.11';
update all1 set dp = '11.11';
update all1 set dc = '11.11';
update all1 set num = '11.11';
update all1 set b = X'21';
update all1 set bv = X'21';
update all1 set lbv = X'21';
update all1 set dt = '1900-01-01';
update all1 set tm = '08:08:08';
update all1 set tms = '1990-01-01 08:08:08.6';
update all1 set lvc = '44444444';

select * from all1;

select c30, vc30, lvc2 from all1;
update all1 set c30 = si, vc30 = si, lvc2 = si;
update all1 set c30 = i, vc30 = i, lvc2 = i;
update all1 set c30 = li, vc30 = li, lvc2 = li;
update all1 set c30 = r, vc30 = r, lvc2 = r;
select c30, vc30, lvc2 from all1;
update all1 set c30 = dp, vc30 = dp, lvc2 = dp;
update all1 set c30 = dc, vc30 = dc, lvc2 = dc;
update all1 set c30 = num, vc30 = num, lvc2 = num;
select c30, vc30, lvc2 from all1;
update all1 set c30 = b, vc30 = b, lvc2 = b;
select c30, vc30, lvc2 from all1;
update all1 set c30 = bv, vc30 = bv, lvc2 = bv;
update all1 set c30 = lbv, vc30 = lbv, lvc2 = lbv;
update all1 set c30 = dt, vc30 = dt, lvc2 = dt;
update all1 set c30 = tm, vc30 = tm, lvc2 = tm;
update all1 set c30 = tms, vc30 = tms, lvc2 = tms;
select c30, vc30, lvc2 from all1;

rollback;
autocommit off;

-- bug 5838 - arithmetic operators should not be applied to character strings
-- the following arithmetic operations should fail
values 1 + '2';
values 1 - '2';
values 1 * '2';
values 4 / '2';
values 1.1 + '2';
values 1.1 - '2';
values 1.1 * '2';
values 4.4 / '2';
values 1.1 + '2.2';
values 1.1 - '2.2';
values 1.1 * '2.2';
values 4.4 / '2.2';

-- concatentation
values '$' || cast(1 as smallint) || '$';
values '$' || 1 || '$';
values '$' || cast(1 as bigint) || '$';
values '$' || cast(1.1 as real) || '$';
values '$' || cast(1.1 as double precision) || '$';
values '$' || 1.1 || '$';
values '$' || cast(1.1 as decimal(8,3)) || '$';
values '$' || 'abcd' || '$';
values '$' || date('1996-09-09') || '$';
values '$' || time('10:11:12') || '$';
values '$' || timestamp('1996-09-09 10:11:12.4' )|| '$';

-- length functions
values length(cast(1 as smallint));
values length(cast(1 as int));
values length(cast(1 as bigint));
values length(cast(1.1 as real));
values length(cast(1.1 as double precision));
values length(1.1);
values length(cast(1.1 as decimal(8,3)));
values length('four');
values length(date('1996-09-10'));
values length(time('10:11:12'));
values length(timestamp('1996-09-10 10:11:12.4'));

-- extract
values year( '1996-01-10');
values month( '1996-01-10');
values day( '1996-01-10');
values hour( '10:11:12');
values minute( '10:11:12');
values second( '10:11:12');

-- like
select si from all1 where 1 like '%';
-- bug 5845
select 1 from all1 where date('1996-09-10') like '19%';
select si from all1 where '1' like 1;
-- integer 1 gets converted to 1 followed by 0 spaces
-- so for kicks put a single space and make sure it is
-- not the same
select si from all1 where '1 ' like 1;
select si from all1 where '1996-09-10' like date('1996-09-10');

prepare p1 as 'select 1 from all1 where si like ?';
execute p1 using 'values 1';
execute p1 using 'values ''1''';

-- conversions involving non-canonical date, time, and timestamp strings
create table t (d date, t time, ts timestamp);
create index txd on t(d);
create index txt on t(t);
create index txts on t(ts);
insert into t values (CHAR('2000-01-07'), 
		      CHAR('20:06:58'), 
		      CHAR('2000-01-07 20:06:58.9000'));
insert into t values (CHAR('2000-1-06'), 
		      CHAR('20:06:57'), 
		      CHAR('2000-01-7 20:06:58.8000'));
VALUES SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'T');

-- bug 2247, make sure that constant retyping
-- (avoiding unnecessary normalization at execution)
-- does not screw up implicit conversions
create table x(x varchar(10));
insert into x values 123;
select * from x;

-- clean up 
drop table all_c1;
drop table t;
drop table x;
commit;
disconnect;
-- ** insert implicitConversionsNegative.sql
-- negate tests for implicit conversions
-- to/from (var)char

-- union
connect 'jdbc:derby:wombat';
autocommit on;
values cast(1 as smallint), 'a';
values 'a', cast(1 as smallint);
values cast(1 as smallint), '1.1';
values '1.1', cast(1 as smallint);
values 1, 'a';
values 'a', 1;
values 1, '1.1';
values '1.1', a;
values cast(1 as bigint), 'a';
values 'a', cast(1 as bigint);
values cast(1 as bigint), '1.1';