floattypes.sql

derby database source code.good for you.

select * from s;

-- these are far enough from the infinities to work
insert into s values(3.402e+38, - 3.402e+38);
insert into s values(3.3e+38, - 3.3e+38);

-- these show that math is promoted to double because of the double
-- literals. If it was real math, it would fail
select d - 3.3e+38 - 3.3e+38, p * 3.3e+38, p * -3.3e+38 from s;

-- see two more rows
select -d from s;

-- to do the math as reals, we have to keep it in the columns
delete from s;
insert into s values (1,3.3e+38);
-- these will fail, because the math is done as reals
select d - p - p from s;
select p * p from s;
select p * -p from s;

delete from s;

-- select values between 0 and 1
insert into s values (.111, 1e-1);
insert into s values (0.222, 0.222);
select * from s;
delete from s;

insert into s values (10, 1e-10);

-- underflow calculation doesn't round off, gives error.
update s set d=d*1.4e-55, p=p*1.4e-45;
select d, p from s;

update s set d=d + 1.4e-46;
select d from s;

drop table s;

-- test the arithmetic operators on a type we know they don't work on
create table w (x real, y char);

select x + y from w;

select x - y from w;

select x * y from w;

select x / y from w;

-- clean up after ourselves
drop table w;

--
-- comparisons
--

create table c (i int, s smallint, d double precision, r real, l real);

-- insert some values
insert into c values (0, 0, 0e0, 0e0, 0e0);
insert into c values (null, null, 5e0, null, null);
insert into c values (1, 1, 1e0, 2e0, 3e0);
insert into c values (1956475, 1956, 1956475e0, 1956475e0, 1956475e0);


-- select each one in turn
select r from c where r = 0e0;
select r from c where r = 1e0;
select r from c where r = 1956475e0;

-- now look for a value that isn't in the table
select r from c where l = 2e0;

-- now test null = null semantics
select r from c where r = r;

-- now test <>, <, >, <=, >=
select r from c where r <> 0e0;
select r from c where r <> 1e0;
select r from c where r < 1956475e0;
select r from c where r < 2e0;
select r from c where r > d;
select r from c where r <= l;
select r from c where r >= r;

-- test comparisons with int and smallint and double
select r from c where r <= i;
select r from c where r < s;
select r from c where r > i;
select r from c where r >= s;
select r from c where r <> i;
select r from c where r = s;
select r from c where r = d;
select r from c where r >= d;

-- show that real is comparable to decimal

create table o (c char(10), v varchar(30), dc decimal);

select r from c,o where r <> dc;

-- clean up
drop table c;
drop table o;


--
-- test a variety of inserts and updates
--
create table source (i int, s smallint, c char(10), v varchar(50),
	d double precision, r real);
create table target (t real not null);

-- we have already tested inserting integer and double literals.

insert into source values (1, 2, '3', '4', 5, 6);

-- these will all work:
insert into target select i from source;
insert into target select s from source;
insert into target select d from source;
insert into target select r from source;

delete from source;
insert into source values (null, null, null, null, null, null);
insert into source values (1, 2, '3', '4', 5, 6);
-- these fail because the target won't take a null -- of any type
insert into target values(null);
insert into target select i from source;
insert into target select s from source;
insert into target select d from source;
insert into target select r from source;

-- expect 5 rows in target: 1, 2, 5, 6, and 1:
select * from target;

update target set t = t + 1;
select * from target;

update target set t = t - 1;
select * from target;

update target set t = t / 10;
select * from target;

update target set t = t * 10;
select * from target;

-- these should work
update source set r = 1.4e4;
update source set i = r, s=r, d=r;

select i, s, d from source where i=1.4e4 or s=1.4e4 or d=1.4e4;

-- just curious, do columns see the before or after values, and
-- does it matter if they are before or after the changed value?
update source set i = r, r = 0, s = r;
select i, r, s from source where r = 0;

-- these should get overflow
update source set r = 1.4e12;
update source set i = r;
update source set s = r;

drop table source;
drop table target;




-- ============================================================
--          TESTS FOR DB2 FLOAT/DOUBLEs LIMITS
-- ============================================================
create table fake(r real);

-- ============================================================
-- different errmsg for DB2: "value of of range", CS: "NumberFormatException"
values 5e-325;
values 5e-324;

-- --- TEST SPECIAL VALUES

-- DB2 (should succed)
insert into fake values( -3.402E+38 );
insert into fake values( +3.402E+38 ); 
insert into fake values -1;

insert into fake values( -1.175E-37 ); 
insert into fake values( +1.175E-37 );
insert into fake values -2;

-- CS (should fail)
insert into fake values( -3.4028235E38 );
insert into fake values( +3.4028235E38 );
insert into fake values -3;

insert into fake values( -1.4E-45 );
insert into fake values( +1.4E-45 );
insert into fake values -4;

-- ============================================================
-- variants of ZERO
insert into fake values (+0);
insert into fake values (+0.0);
insert into fake values (+0.0E-37);
insert into fake values (+0.0E-38);
insert into fake values (+0.0E-500);
values (+0.0E-500);
values (+1.0E-300);

-- approx ZERO (java rounds to zero, but not DB2)
insert into fake values (+1.0E-300);
insert into fake values (+1.0E-900);
insert into fake values (cast(+1.0E-900 as real));
values (cast(+1.0E-300 as real));
values (+1.0E-900);
values (cast(+1.0E-900 as real));
insert into fake values -11;

-- ============================================================

-- DB2 MAX_VALUES (first succeed, second fail)
insert into fake values( -3.4019E+38 );
insert into fake values( -3.4021E+38 );
insert into fake values -21;

insert into fake values( +3.4019E+38 ); 
insert into fake values( +3.4021E+38 ); 
insert into fake values -22;

-- DB2 MIN_VALUES (first fail, second succeed)
insert into fake values( -1.1749E-37 ); 
insert into fake values( -1.1751E-37 ); 
insert into fake values -23;

insert into fake values( +1.1749E-37 );
insert into fake values( +1.1751E-37 );
insert into fake values -24;

-- CS (fail)
insert into fake values( -3.4028234E38 );
insert into fake values( -3.40282349E38 );
insert into fake values( -3.40282351E38 );
insert into fake values( -3.4028236E38 );
insert into fake values -25;

insert into fake values( +3.4028234E38 );
insert into fake values( +3.40282349E38 );
insert into fake values( +3.40282351E38 );
insert into fake values( +3.4028236E38 );
insert into fake values -26;

insert into fake values( -1.39E-45 );
insert into fake values( -1.399E-45 );
insert into fake values( -1.401E-45 );
insert into fake values( -1.41E-45 );
insert into fake values -27;

insert into fake values( +1.39E-45 );
insert into fake values( +1.399E-45 );
insert into fake values( +1.401E-45 );
insert into fake values( +1.41E-45 );
insert into fake values -28;

-- checkpoint
select * from fake;

drop table fake;
create table fake(r real);

-- ============================================================
-- ---underflow aritmetic
-- underflow to small real but / makes double!=0, so we catch

-- ok
values cast(5e-37/1e0 as real);
-- fail
values cast(5e-37/1e1 as real);
values cast(5e-37/1e300 as real);
values cast(5e-37 as real)/cast(1e10 as real);

-- ok
insert into fake values 5e-37/1e0;
-- fail
insert into fake values 5e-37/1e1;
insert into fake values 5e-37/1e300;
insert into fake values cast(5e-37 as real)/cast(1e10 as real);

drop table fake;

-- makes double to small, so java double rounds to 0. need to catch (fail)
values 5e-37 / 1e300;
values cast(5e-37 / 1e300 as real);

-- ok, zero result (succeed)
values cast(cast(0.0e0 as real) - cast(0.0e0 as real) as real);
values cast(cast(1.0e-30 as real) - cast(1.0e-30 as real) as real);

-- java (and CS previously) rounded result to zero, but now gives errors like DB2 (fail)
values cast(cast(5e-37 as real) - cast(4e-37 as real) as real);
values cast(5e-37 - 4e-37 as real);
values cast(5e-37 - 4.99e-37 as real);

values cast(5e-308 - 4e-308 as real);

values cast(5e-37 + -4e-37 as real);
values cast(5e-324 - 4e-324 as real);

values cast(5e-37 * 4e-37 as real);
values cast(cast(5e-37 as real) * cast(4e-37 as real) as real);

-- double trouble, underflow detection (fail)
values cast(5e-300 * 4e-300 as real);

-- underflow aritmetic DOUBLE (fail)
values -3e-305/1e100;
values -3e-305/1e100; 

-- negative zeros not allowed (succeed)
values 0.0e5/-1;

-- 30 characters limit to be enforced ) (first fail, second ok)
values 01234567890123456789012345678e1;
values 0123456789012345678901234567e1;

-- ============================================================

--- Marks tests

-- Examples in Cloudscape 5.2:
-- these 2 insert statements should raise error msgs in compat mode because 
-- the values are between the -mpv and +mpv (fail)
create table t1 (c1 real);
insert into t1 values -1.40129846432481700e-46;
insert into t1 values +1.40129846432481700e-46;
select * from t1;

-- these 2 insert statements should raise an error msg in compat mode
-- because the values are greater db2's limits (fail)
insert into t1 values 3.40282346638528860e+38;
insert into t1 values -3.40282346638528860e+38;
select * from t1;

drop table t1;

-- Examples in DB2 UDB for LUW 8.1.4:
-- these 2 insert statements raise ERROR 22003 because
-- the values are between the -mpv and +mpv (fail)
create table t1 (c1 real);
insert into t1 values -1.40129846432481700e-46;
insert into t1 values +1.40129846432481700e-46;
select * from t1;

-- these 2 insert statements raise ERROR 22003 because
-- the values are greater db2's limits (fail)
insert into t1 values 3.40282346638528860e+38;
insert into t1 values -3.40282346638528860e+38;
select * from t1;

drop table t1;

-- ============================================================

-- bug 5704 - make sure we catch the overflow correctly for multiplication operator
values cast(1e30 as decimal(31))*cast(1e30 as decimal(31));
values cast('1e30' as decimal(31))*cast('1e30' as decimal(31));


create table tiger(d decimal(12,11));

insert into tiger values (1.234);
insert into tiger values (0.1234);
insert into tiger values (0.01234);
insert into tiger values (0.001234);
insert into tiger values (0.001234);
insert into tiger values (0.0001234);
insert into tiger values (0.00001234);
insert into tiger values (0.000001234);
insert into tiger values (0.0000001234);
insert into tiger values (0.00000001234);
insert into tiger values (0.00000001234);

select d from tiger order by 1;


-- =====================================================
-- some formatting tests
values cast ('1e+0' as DECIMAL(6,2));
values cast ('+-1e+1' as DECIMAL(6,2));
values cast ('-1e+1' as DECIMAL(6,2));
values cast ('-1e-1' as DECIMAL(6,2));
values cast ('-1e-+1' as DECIMAL(6,2));
values cast ('-1e--1' as DECIMAL(6,2));
values cast ('-1e+-1' as DECIMAL(6,2));
values cast ('-1e+-1' as DECIMAL(6,2));
values cast ('1.0e' as DECIMAL(6,2));
values cast ('1.0e+' as DECIMAL(6,2));
values cast ('1.0e-' as DECIMAL(6,2));
values cast ('1.0ee' as DECIMAL(6,2));
values cast ('123.' as DECIMAL(6,2));
values cast ('1e' as DECIMAL(6,2));
values cast ('1e1.0' as DECIMAL(6,2));
values cast ('.3' as DECIMAL(6,2));
values cast ('' as DECIMAL(6,2));