floattypes.sql

derby database source code.good for you.

				r real,
				dc decimal(10,2));

insert into t2 select * from t;

-- add a few indexes
create index dcindex on t2(dc);
create unique index dcuniqueindex on t2(dc);

-- now do updates and confirm they are ok
update t2 set dc = dc + 1.1;
select dc from t2;
update t2 set dc = dc - 1.1;
select dc from t2;
update t2 set dc = dc / 1.1;
select dc from t2;
update t2 set dc = dc * 1.1;
select dc from t2;

-- try some deletes
delete from t2 where dc > 0;
select dc from t2;
delete from t2 where dc = 0;
select dc from t2;
delete from t2 where dc < 0;
select dc from t2;

drop table t2;
drop table t;

-- test that we recycle values correctly
-- when reading from a decimal table with
-- variable length byte arrays stored
-- via write external
create table t (c1 char(1), d dec(20,4), c2 char(1));
create unique index tu on t(d);
insert into t values ('a', 1.123, 'Z');
insert into t values ('a', 11111.123, 'Z');
insert into t values ('a', 11111111.123, 'Z');
insert into t values ('a', 6.123, 'Z');
insert into t values ('a', 666.123, 'Z');
insert into t values ('a', .6, 'Z');
insert into t values ('a', 0, 'Z');
insert into t values ('a', 666666.123, 'Z');
insert into t values ('a', 99999999999999.123, 'Z');
insert into t values ('a', 9.123, 'Z');

select * from t;

update t set d = d + .0007;
select * from t;
drop table tmp;
drop table bad;
drop table t;

-- ** insert double.sql

--
-- Test the builtin type 'double precision'
-- assumes these builtin types exist:
--	int, smallint, char, varchar
--
-- other things we might test:
-- show how doubles lose precision on computations

--
-- Test the arithmetic operators
--

create table t (i int, s smallint, c char(10), v varchar(50),
	d double precision);

insert into t values (null, null, null, null, null);
insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0);
insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0);

select d + d, i + d, s + d from t;

select d + d + d, d + 100 + 432e0 from t;

select d - i, i - d, d - s, s - d from t;

select d - d - d, d - 100 - 432e0 from t;

select i, d, i * d, d * i, d * d, d * 2, d * 2.0e0 from t;

-- try unary minus, plus
select -(d * 100 / 100e0 ), +(d * 100e0 / 100 ) from t;

-- test null/null, constant/null, null/constant
select i, d, i / d, 10 / d, d / 10e0 from t;

-- test for divide by 0
select d / i from t;

select 20e0 / 5e0 / 4e0, 20e0 / 4e0 / 5 from t;

-- test positive/negative, negative/positive and negative/negative
select d, d / -d, (-d) / d, (-d) / -d from t;

-- test some "more complex" expressions
select d, d + 10e0, d - (10 - 20e0), d - 10, d - (20 - 10) from t;

-- show that decimals will go into doubles:
select d+1.1 from t;
insert into t (d) values(1.1);
select d from t where d=1.1;

drop table t;

-- test overflow

create table s (d double precision, p double);

insert into s values (null, null);
insert into s values (0, 100);
insert into s values (1, 101);

select d + 1.7e+308 from s;

-- these are close enough to the infinities to overflow
-- the null row will still get returned
select 1.798e+308, - 1.798e+308, 'This query should not work' from s;
select 1.8e+1000, - 1.8e+1000, 'This query should not work' from s;

-- these are far enough from the infinities to work
select 1.797e+308, - 1.797e+308, 'This query should work' from s;
select 1.6e+308, - 1.6e+308, 'This query should work' from s;

-- the null row will still get returned
select d - 1.6e+308 - 0, 'This query should work' from s;
select d - 1.6e+308 - 1.6e+308, 'This query should fail' from s;

-- these should fail
select p * 1.6e+308 from s;
select p * -1.6e+308 from s;

-- these work
insert into s values (-1.6e+308, 0);
insert into s values (-1.797e+308, 0);
-- these don't work
insert into s values (-1.798e+308, 0);
insert into s values (-1.8e+308, 0);

-- see two more rows
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 double precision, y long varchar);

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, p double precision);

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


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

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

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

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

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

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

-- test that the smallint gets promoted to double, and not vice versa.  65537
-- when converted to short becomes 1
select d from c where s = 65537e0;

-- test =SQ
-- this gets cardinality error
select d from c where d = (select d from c);
-- this works
select d from c where d = (select d from c where d=5);

-- show that double is comparable to real

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

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

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


--
-- test alternate syntax: just double will work for DB2 compatibility
--
create table db2version (d double);
drop table db2version;
--
-- test a variety of inserts and updates
--
create table source (i int, s smallint, c char(10), v varchar(50),
	d double precision);
create table target (p double precision not null);

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

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

-- 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;

-- these will all fail:

delete from source;
insert into source values (null, null, null, null, null);
-- 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;

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

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

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

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

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

-- these should work
update source set i = 1.4e8;
update source set s = 1.4e4;

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

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

drop table source;
drop table target;

create table abcfloat (numtest float(20));
insert into abcfloat values (1.23456789);
insert into abcfloat values (.123456789);
insert into abcfloat values (-.123456789);
insert into abcfloat values (0.223456789);
insert into abcfloat values (-0.223456789);
insert into abcfloat values (12345678.9);
select * from abcfloat;
drop table abcfloat;


-- ** insert float.sql
--
-- Test the builtin type 'float'
-- Float is a synonym for double or real, depending on
-- the precision specified; so all we need to do is
-- show the mapping here; the double and real tests
-- show how well those types behave.
--

-- this shows several working versions of float, the default
-- and all of the boundary values:

create table t (d double precision, r real, f float, f1 float(1),
	f23 float(23), f24 float(24), f53 float(52));

select columnname, columndatatype 
from sys.syscolumns c, sys.systables t
where c.referenceid = t.tableid and t.tablename='T';

-- invalid float values
insert into t(r) values 'NaN';
insert into t(r) values +3.4021E+38;
insert into t(r) values -3.4021E+38;
create table tt(c char(254));
insert into tt values -3.402E+38;
insert into t(r) select * from tt;
insert into t(r) values '1.0';
update t set r = NaN;
update t set r = +3.4021E+38;
update t set r = -3.4021E+38;

drop table t;
drop table tt;

-- these get errors for invalid precision values:
create table t1 (d double precision, r real, f float(-10));
--
create table t2 (d double precision, r real, f float(-1));
create table t3 (d double precision, r real, f float(0));
create table t4 (d double precision, r real, f float(100));
create table t5 (d double precision, r real, f float(53));
create table t6 (d double precision, r real, f float(12.3));

-- ** insert real.sql
--
-- Test the builtin type 'real'
-- assumes these builtin types exist:
--	int, smallint, char, varchar, double precision
--
-- other things we might test:
-- show how reals lose precision on computations

--
-- Test the arithmetic operators
--

create table t (i int, s smallint, c char(10), v varchar(50),
	d double precision, r real);

insert into t values (null, null, null, null, null, null);
insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0);
insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0);

select r + r, d + r, i + r, s + r, r + i from t;

select r + r + r, r + 100 + 432e0 from t;

select r - r, r - d, d - r, r - i, i - r, r - s, s - r from t;

select r - r - r, r - 100 - 432e0 from t;

select i, d, s, r, i * r, r * i, s * r, d * r, r * r, r * 2, r * 2.0e0 from t;

-- try unary minus, plus
select -(r * 100 / 100e0 ), +(r * 100e0 / 100 ) from t;

-- test null/null, constant/null, null/constant
select i, d, r, d / r, i / r, 10 / r, r / d, r / 10e0 from t;

-- test for divide by 0
select r / i from t;

-- test positive/negative, negative/positive and negative/negative
select r, r / -r, (-r) / r, (-r) / -r from t;

-- test some "more complex" expressions
select r, r + 10e0, r - (10 - 20e0), r - 10, r - (20 - 10) from t;

drop table t;

-- test overflow

create table s (d real, p real);

insert into s values (null, null);
insert into s values (0, 100);
insert into s values (1, 101);

select d + 3.4e+38 from s;

-- these are close enough to the infinities to overflow
-- Can't test simple select of literal because literals are doubles
insert into s values(3.403e+38, 3.403e+38);
insert into s values(- 3.403e+38, - 3.403e+38);
insert into s values(1.8e+100, 1.8e+100);
insert into s values(- 1.8e+100, - 1.8e+100);