floattypes.sql

derby database source code.good for you.

-- ** insert decimal.sql
--
-- Test DECIMAL and NUMERIC.  Note that we
-- know that DECIMAL and NUMERIC are pretty much the
-- same thing, so we don't do much testing with
-- the two types other than to make sure the 
-- syntax is the same.


-- test some of the meta data
drop table tmp;
create table tmp (tmpcoldecimal dec(8,4), tmpcolnumeric numeric);
select columndatatype 
	from sys.syscolumns 
	where columnname like 'TMPCOL%';
drop table tmp;

-- Negative tests, bad precision/scale
create table bad (d decimal(11,12));
create table bad (d decimal(0,0));
create table bad (d decimal(0));
create table bade(d decimal(32));
create table bade(d decimal(31,32));
create table bade(d decimal(32,32));

-- Simple acceptance test
values cast (1 as dec);
values cast (1 as decimal);
values cast (1 as decimal(5));
values cast (1 as dec(5));
values cast (1.1 as dec(5,3));
values cast (1.1 as numeric(5,3));

-- cast to all valid types
values cast (1.1 as int);
values cast (1.1 as bigint);
values cast (1.1 as smallint);
values cast (1.1 as real);
values cast (1.1 as float);
values cast (1.1 as char(10));

-- cast all valid types to dec
values cast ((cast (1 as int)) as dec);
values cast ((cast (1 as bigint)) as dec);
values cast ((cast (1 as smallint)) as dec);
values cast ((cast (1 as real)) as dec);
values cast ((cast (1 as float)) as dec);
values cast ((cast (1 as char(10))) as dec);

-- cast overflow,
-- make a number bigger than everything but 
-- decimal, and then try to cast it
drop table tmp;
create table tmp(d decimal(31 ,0));
insert into tmp values (cast (
'100000000000000000000000000000' as dec(31,0)));
update tmp set d = d * d;
select cast(d as int) from tmp;
select cast(d as smallint) from tmp;
select cast(d as bigint) from tmp;
select cast(d as float) from tmp;
select cast(d as real) from tmp;
select cast(d as double precision) from tmp;
-- test alternative syntax
select cast(d as double) from tmp;
insert into tmp values (+1.79769E+308);
select * from tmp;
drop table tmp;

-- try inserting various types into decimal.
-- we expect silent truncation of the fraction
drop table tmp;
create table tmp (d decimal(5,2));
insert into tmp values (100);
insert into tmp values (cast (100 as smallint));
insert into tmp values (cast (100 as bigint));
insert into tmp values (cast (100 as real));
insert into tmp values (cast (100 as double precision));
insert into tmp values (cast (100.999 as real));
insert into tmp values (100.999e0);
insert into tmp values (100.999);
--too big
insert into tmp values (1000);
insert into tmp values (cast (1000 as smallint));
insert into tmp values (cast (1000 as bigint));
insert into tmp values (cast (1000 as real));
insert into tmp values (cast (1000 as double precision));
insert into tmp values (cast (1000.999 as real));
insert into tmp values (1000.999e0);
insert into tmp values (1000.999);

--try a few values that hit borders in how java.lang.Double work
--(this is really tied to some details in the internals of
-- SQLDecimal)
insert into tmp values (1000);
insert into tmp values (10000);
insert into tmp values (100000);
insert into tmp values (1000000);
insert into tmp values (10000000); 
insert into tmp values (100000000);
insert into tmp values (1000000000);
insert into tmp values (10000000000);
insert into tmp values (100000000000);
insert into tmp values (1000000000000);
insert into tmp values (10000000000000);
insert into tmp values (100000000000000);
insert into tmp values (-1000);
insert into tmp values (-10000);
insert into tmp values (-100000);
insert into tmp values (-1000000);
insert into tmp values (-10000000); 
insert into tmp values (-100000000);
insert into tmp values (-1000000000);
insert into tmp values (-10000000000);
insert into tmp values (-100000000000);
insert into tmp values (-1000000000000);
insert into tmp values (-10000000000000);
insert into tmp values (-100000000000000);
drop table tmp;

create table tmp(d dec(1,1));
insert into tmp values (0.0);
insert into tmp values (-0.0);
insert into tmp values (0.1);
insert into tmp values (-0.1);
insert into tmp values (0.1e0);
insert into tmp values (-0.1e0);
select * from tmp;
delete from tmp;

insert into tmp values (0);
insert into tmp values (0.0e0);
insert into tmp values (0.0e10);
insert into tmp values (-0);
insert into tmp values (-0.0e0);
insert into tmp values (-0.0e10);
insert into tmp values (cast (0 as smallint));
insert into tmp values (cast (0 as bigint));
insert into tmp values (cast (0 as real));
select * from tmp;
drop table tmp;

create table tmp(d dec(1,0));
insert into tmp values (1.0);
insert into tmp values (1);
insert into tmp values (1.0e0);
insert into tmp values (-1.0);
insert into tmp values (-1);
insert into tmp values (-1.0e0);
insert into tmp values (cast (1 as smallint));
insert into tmp values (cast (1 as bigint));
insert into tmp values (cast (1 as real));
select * from tmp;
drop table tmp;

-- Using the DOUBLE built-in function
-- test that double maps to the double data type
-- all of the following should work if DOUBLE appears in the COLUMNDATATYPE column
create table tmp (x double);
insert into tmp values (1);
select columnname, columndatatype 
       from sys.syscolumns c, sys.systables t 
       where c.referenceid = t .tableid and t.tablename='TMP';
drop table tmp;

-- cast dec as as a numeric type in a select list
create table tmp (d decimal);
insert into tmp values (1.1);
--should all pass
insert into tmp values (1);
select cast(d as int) from tmp;
select cast(d as smallint) from tmp;
select cast(d as bigint) from tmp;
select cast(d as float) from tmp;
select cast(d as real) from tmp;
select cast(d as double precision) from tmp;
select cast(d as dec(10,2)) from tmp;
select cast(d as dec(10,8)) from tmp;
drop table tmp;


drop table t;
create table t (i int, 
				l bigint,
				s smallint, 
				d double precision,
				r real,
				dc decimal(10,2));

insert into t values (null, null, null, null, null, null);

insert into t values (10,		-- int
						10,		-- bigint
						10,		-- smallint	
						10,		-- double
						10,		-- real
						10		-- decimal(10,2)		
						 );
insert into t values (-10,		-- int
						-10,	-- bigint
						-10,	-- smallint	
						-10,	-- double
						-10,	-- real
						-10		-- decimal(10,2)		
						 );


insert into t values (0,		-- int
						0,		-- bigint
						0,		-- smallint	
						0,		-- double
						0,		-- real
						0		-- decimal(10,2)		
						 );

select dc from t;

select dc + i, dc + s, dc + r, dc + dc from t;
select dc - i, dc - s, dc - r, dc - dc from t;
select dc * i, dc * s, dc * r, dc * dc from t;
select dc / i, dc / s, dc / r, dc / dc from t;

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

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

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

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

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

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

-- make sure we get the right scale/precision during arithmetic
values (9.0 + 9.0);
values (9.9 + 9.9);

values (-9.0 - 9.0);
values (-9.9 - 9.9);

values (9.0 * 9.0);
values (9.9 * 9.9);
values (0.9 * 0.9);
values (0.9999 * 0.9);
values (0.9 * 0.9999);
values (0.9999 * 0.9999);

values (1.0 / 3.0);
values (1.0 / 0.3);
values (1.0 / 0.03);
values (1.0 / 0.000003);
values (10000.0 / 0.000003);
values (0.0001 / 0.0003);
values (0.1 / 3.0);

-- huge number
values (
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)) *
		cast(1.7e3 as dec(31)));

values cast(1.7e30 as dec(31));

--try a tiny number 
-- the following seems to be asking a bit
-- too much of poor old biginteger, so try
-- something smaller
--values (cast(1.7e-307 as dec(2147483647,2147483640)) /
--		(cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647)) *
--		 cast(1.7e308 as dec(2147483647))));
--

values cast(1 as dec(31, 20));
-- test the arithmetic operators on a type we know they don't work on
create table w (x dec, 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
--

insert into t values (123,			-- int
						123,		-- bigint
						123,		-- smallint	
						1234.56,	-- double
						1234.56,	-- real
						1234.56		-- decimal(10,2)		
						 );

-- test =
select dc from t where dc is null;
select dc from t where dc = 10;
select dc from t where dc = -10;
select dc from t where dc = 0;
select dc from t where dc = 1234.45;

select dc from t where dc = i;
select dc from t where dc = l;
select dc from t where dc = s;
select dc from t where dc = r;
select dc from t where dc = d;
select dc from t where dc = dc;


-- test >
select dc from t where dc > 10;
select dc from t where dc > -10;
select dc from t where dc > 0;
select dc from t where dc > 1234.45;

select dc from t where dc > i;
select dc from t where dc > l;
select dc from t where dc > s;
select dc from t where dc > r;
select dc from t where dc > d;
select dc from t where dc > dc;


-- test >=
select dc from t where dc >= 10;
select dc from t where dc >= -10;
select dc from t where dc >= 0;
select dc from t where dc >= 1234.45;

select dc from t where dc >= i;
select dc from t where dc >= l;
select dc from t where dc >= s;
select dc from t where dc >= r;
select dc from t where dc >= d;
select dc from t where dc >= dc;



-- test <
select dc from t where dc < 10;
select dc from t where dc < -10;
select dc from t where dc < 0;
select dc from t where dc < 1234.45;

select dc from t where dc < i;
select dc from t where dc < l;
select dc from t where dc < s;
select dc from t where dc < r;
select dc from t where dc < d;
select dc from t where dc < dc;


-- test <=
select dc from t where dc <= 10;
select dc from t where dc <= -10;
select dc from t where dc <= 0;
select dc from t where dc <= 1234.45;

select dc from t where dc <= i;
select dc from t where dc <= l;
select dc from t where dc <= s;
select dc from t where dc <= r;
select dc from t where dc <= d;
select dc from t where dc <= dc;



-- test <>
select dc from t where dc <> 10;
select dc from t where dc <> -10;
select dc from t where dc <> 0;
select dc from t where dc <> 1234.45;

select dc from t where dc <> i;
select dc from t where dc <> l;
select dc from t where dc <> s;
select dc from t where dc <> r;
select dc from t where dc <> d;
select dc from t where dc <> dc;


--
-- test a variety of inserts and updates
--

drop table t2;
create table t2 (i int, 
				l bigint,
				s smallint, 
				d double precision,