You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2005/07/06 23:25:24 UTC
svn commit: r209515 [2/3] - in
/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ master/j9_foundation/ tests/jdbcapi/ tests/lang/
Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/floattypes.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/floattypes.out?rev=209515&view=auto
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/floattypes.out (added)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/j9_foundation/floattypes.out Wed Jul 6 14:25:22 2005
@@ -0,0 +1,2530 @@
+ij> -- ** 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;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
+ij> create table tmp (tmpcoldecimal dec(8,4), tmpcolnumeric numeric);
+0 rows inserted/updated/deleted
+ij> select columndatatype
+ from sys.syscolumns
+ where columnname like 'TMPCOL%';
+COLUMNDATATYPE
+---------------
+DECIMAL(8,4)
+NUMERIC(5,0)
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> -- Negative tests, bad precision/scale
+create table bad (d decimal(11,12));
+ERROR 42Y43: Scale '12' is not a valid scale with precision of '11'.
+ij> create table bad (d decimal(0,0));
+ERROR 42X48: Value '0' is not a valid precision for DECIMAL.
+ij> create table bad (d decimal(0));
+ERROR 42X48: Value '0' is not a valid precision for DECIMAL.
+ij> create table bade(d decimal(32));
+ERROR 42X48: Value '32' is not a valid precision for DECIMAL.
+ij> create table bade(d decimal(31,32));
+ERROR 42Y42: Scale '32' is not a valid scale for a DECIMAL.
+ij> create table bade(d decimal(32,32));
+ERROR 42X48: Value '32' is not a valid precision for DECIMAL.
+ij> -- Simple acceptance test
+values cast (1 as dec);
+1
+------
+1
+ij> values cast (1 as decimal);
+1
+------
+1
+ij> values cast (1 as decimal(5));
+1
+------
+1
+ij> values cast (1 as dec(5));
+1
+------
+1
+ij> values cast (1.1 as dec(5,3));
+1
+--------
+1.100
+ij> values cast (1.1 as numeric(5,3));
+1
+--------
+1.100
+ij> -- cast to all valid types
+values cast (1.1 as int);
+1
+-----------
+1
+ij> values cast (1.1 as bigint);
+1
+--------------------
+1
+ij> values cast (1.1 as smallint);
+1
+------
+1
+ij> values cast (1.1 as real);
+1
+-------------
+1.1
+ij> values cast (1.1 as float);
+1
+----------------------
+1.1
+ij> values cast (1.1 as char(10));
+1
+----------
+1.1
+ij> -- cast all valid types to dec
+values cast ((cast (1 as int)) as dec);
+1
+------
+1
+ij> values cast ((cast (1 as bigint)) as dec);
+1
+------
+1
+ij> values cast ((cast (1 as smallint)) as dec);
+1
+------
+1
+ij> values cast ((cast (1 as real)) as dec);
+1
+------
+1
+ij> values cast ((cast (1 as float)) as dec);
+1
+------
+1
+ij> values cast ((cast (1 as char(10))) as dec);
+1
+------
+1
+ij> -- cast overflow,
+-- make a number bigger than everything but
+-- decimal, and then try to cast it
+drop table tmp;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
+ij> create table tmp(d decimal(31 ,0));
+0 rows inserted/updated/deleted
+ij> insert into tmp values (cast (
+'100000000000000000000000000000' as dec(31,0)));
+1 row inserted/updated/deleted
+ij> update tmp set d = d * d;
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
+ij> select cast(d as int) from tmp;
+1
+-----------
+ERROR 22003: The resulting value is outside the range for the data type INTEGER.
+ij> select cast(d as smallint) from tmp;
+1
+------
+ERROR 22003: The resulting value is outside the range for the data type SMALLINT.
+ij> select cast(d as bigint) from tmp;
+1
+--------------------
+ERROR 22003: The resulting value is outside the range for the data type BIGINT.
+ij> select cast(d as float) from tmp;
+1
+----------------------
+1.0E29
+ij> select cast(d as real) from tmp;
+1
+-------------
+1.0E29
+ij> select cast(d as double precision) from tmp;
+1
+----------------------
+1.0E29
+ij> -- test alternative syntax
+select cast(d as double) from tmp;
+1
+----------------------
+1.0E29
+ij> insert into tmp values (+1.79769E+308);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
+ij> select * from tmp;
+D
+--------------------------------
+100000000000000000000000000000
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> -- try inserting various types into decimal.
+-- we expect silent truncation of the fraction
+drop table tmp;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
+ij> create table tmp (d decimal(5,2));
+0 rows inserted/updated/deleted
+ij> insert into tmp values (100);
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (100 as smallint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (100 as bigint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (100 as real));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (100 as double precision));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (100.999 as real));
+1 row inserted/updated/deleted
+ij> insert into tmp values (100.999e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (100.999);
+1 row inserted/updated/deleted
+ij> --too big
+insert into tmp values (1000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (cast (1000 as smallint));
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (cast (1000 as bigint));
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (cast (1000 as real));
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (cast (1000 as double precision));
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (cast (1000.999 as real));
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (1000.999e0);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (1000.999);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> --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);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (10000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (100000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (1000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (10000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (100000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (1000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (10000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (100000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (1000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (10000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (100000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-1000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-10000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-100000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-1000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-10000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-100000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-1000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-10000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-100000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-1000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-10000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> insert into tmp values (-100000000000000);
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(5,2).
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> create table tmp(d dec(1,1));
+0 rows inserted/updated/deleted
+ij> insert into tmp values (0.0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0.0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (0.1);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0.1);
+1 row inserted/updated/deleted
+ij> insert into tmp values (0.1e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0.1e0);
+1 row inserted/updated/deleted
+ij> select * from tmp;
+D
+----
+0.0
+0.0
+0.1
+-0.1
+0.1
+-0.1
+ij> delete from tmp;
+6 rows inserted/updated/deleted
+ij> insert into tmp values (0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (0.0e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (0.0e10);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0.0e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-0.0e10);
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (0 as smallint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (0 as bigint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (0 as real));
+1 row inserted/updated/deleted
+ij> select * from tmp;
+D
+----
+0.0
+0.0
+0.0
+0.0
+0.0
+0.0
+0.0
+0.0
+0.0
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> create table tmp(d dec(1,0));
+0 rows inserted/updated/deleted
+ij> insert into tmp values (1.0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (1);
+1 row inserted/updated/deleted
+ij> insert into tmp values (1.0e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-1.0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-1);
+1 row inserted/updated/deleted
+ij> insert into tmp values (-1.0e0);
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (1 as smallint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (1 as bigint));
+1 row inserted/updated/deleted
+ij> insert into tmp values (cast (1 as real));
+1 row inserted/updated/deleted
+ij> select * from tmp;
+D
+----
+1
+1
+1
+-1
+-1
+-1
+1
+1
+1
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> -- 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);
+0 rows inserted/updated/deleted
+ij> insert into tmp values (1);
+1 row inserted/updated/deleted
+ij> select columnname, columndatatype
+ from sys.syscolumns c, sys.systables t
+ where c.referenceid = t .tableid and t.tablename='TMP';
+COLUMNNAME |COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------
+X |DOUBLE
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> -- cast dec as as a numeric type in a select list
+create table tmp (d decimal);
+0 rows inserted/updated/deleted
+ij> insert into tmp values (1.1);
+1 row inserted/updated/deleted
+ij> --should all pass
+insert into tmp values (1);
+1 row inserted/updated/deleted
+ij> select cast(d as int) from tmp;
+1
+-----------
+1
+1
+ij> select cast(d as smallint) from tmp;
+1
+------
+1
+1
+ij> select cast(d as bigint) from tmp;
+1
+--------------------
+1
+1
+ij> select cast(d as float) from tmp;
+1
+----------------------
+1.0
+1.0
+ij> select cast(d as real) from tmp;
+1
+-------------
+1.0
+1.0
+ij> select cast(d as double precision) from tmp;
+1
+----------------------
+1.0
+1.0
+ij> select cast(d as dec(10,2)) from tmp;
+1
+-------------
+1.00
+1.00
+ij> select cast(d as dec(10,8)) from tmp;
+1
+-------------
+1.00000000
+1.00000000
+ij> drop table tmp;
+0 rows inserted/updated/deleted
+ij> drop table t;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T' because it does not exist.
+ij> create table t (i int,
+ l bigint,
+ s smallint,
+ d double precision,
+ r real,
+ dc decimal(10,2));
+0 rows inserted/updated/deleted
+ij> insert into t values (null, null, null, null, null, null);
+1 row inserted/updated/deleted
+ij> insert into t values (10, -- int
+ 10, -- bigint
+ 10, -- smallint
+ 10, -- double
+ 10, -- real
+ 10 -- decimal(10,2)
+ );
+1 row inserted/updated/deleted
+ij> insert into t values (-10, -- int
+ -10, -- bigint
+ -10, -- smallint
+ -10, -- double
+ -10, -- real
+ -10 -- decimal(10,2)
+ );
+1 row inserted/updated/deleted
+ij> insert into t values (0, -- int
+ 0, -- bigint
+ 0, -- smallint
+ 0, -- double
+ 0, -- real
+ 0 -- decimal(10,2)
+ );
+1 row inserted/updated/deleted
+ij> select dc from t;
+DC
+-------------
+NULL
+10.00
+-10.00
+0.00
+ij> select dc + i, dc + s, dc + r, dc + dc from t;
+1 |2 |3 |4
+------------------------------------------------------------
+NULL |NULL |NULL |NULL
+20.00 |20.00 |20.0 |20.00
+-20.00 |-20.00 |-20.0 |-20.00
+0.00 |0.00 |0.0 |0.00
+ij> select dc - i, dc - s, dc - r, dc - dc from t;
+1 |2 |3 |4
+------------------------------------------------------------
+NULL |NULL |NULL |NULL
+0.00 |0.00 |0.0 |0.00
+0.00 |0.00 |0.0 |0.00
+0.00 |0.00 |0.0 |0.00
+ij> select dc * i, dc * s, dc * r, dc * dc from t;
+1 |2 |3 |4
+--------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+100.00 |100.00 |100.0 |100.0000
+100.00 |100.00 |100.0 |100.0000
+0.00 |0.00 |0.0 |0.0000
+ij> select dc / i, dc / s, dc / r, dc / dc from t;
+1 |2 |3 |4
+----------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+1.00000000000000000000000 |1.00000000000000000000000 |1.0 |1.000000000000000000000
+1.00000000000000000000000 |1.00000000000000000000000 |1.0 |1.000000000000000000000
+ERROR 22012: Attempt to divide by zero.
+ij> -- try unary minus, plus
+select -(dc * 100 / 100e0 ), +(dc * 100e0 / 100 ) from t;
+1 |2
+---------------------------------------------
+NULL |NULL
+-10.0 |10.0
+10.0 |-10.0
+0.0 |0.0
+ij> -- test null/null, constant/null, null/constant
+select dc, i / dc, 10 / dc, dc / 10e0 from t;
+DC |2 |3 |4
+----------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+10.00 |1.0000000000000000000 |1.0000000000000000000 |1.0
+-10.00 |1.0000000000000000000 |-1.0000000000000000000 |-1.0
+ERROR 22012: Attempt to divide by zero.
+ij> -- test for divide by 0
+select dc / i from t;
+1
+----------------------------------
+NULL
+1.00000000000000000000000
+1.00000000000000000000000
+ERROR 22012: Attempt to divide by zero.
+ij> select 20e0 / 5e0 / 4e0, 20e0 / 4e0 / 5 from t;
+1 |2
+---------------------------------------------
+1.0 |1.0
+1.0 |1.0
+1.0 |1.0
+1.0 |1.0
+ij> -- test positive/negative, negative/positive and negative/negative
+select dc, dc / -dc, (-dc) / dc, (-dc) / -dc from t;
+DC |2 |3 |4
+----------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+10.00 |-1.000000000000000000000 |-1.000000000000000000000 |1.000000000000000000000
+-10.00 |-1.000000000000000000000 |-1.000000000000000000000 |1.000000000000000000000
+ERROR 22012: Attempt to divide by zero.
+ij> -- test some "more complex" expressions
+select dc, dc + 10e0, dc - (10 - 20e0), dc - 10, dc - (20 - 10) from t;
+DC |2 |3 |4 |5
+---------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL
+10.00 |20.0 |20.0 |0.00 |0.00
+-10.00 |0.0 |0.0 |-20.00 |-20.00
+0.00 |10.0 |10.0 |-10.00 |-10.00
+ij> -- make sure we get the right scale/precision during arithmetic
+values (9.0 + 9.0);
+1
+------
+18.0
+ij> values (9.9 + 9.9);
+1
+------
+19.8
+ij> values (-9.0 - 9.0);
+1
+------
+-18.0
+ij> values (-9.9 - 9.9);
+1
+------
+-19.8
+ij> values (9.0 * 9.0);
+1
+-------
+81.00
+ij> values (9.9 * 9.9);
+1
+-------
+98.01
+ij> values (0.9 * 0.9);
+1
+-----
+0.81
+ij> values (0.9999 * 0.9);
+1
+--------
+0.89991
+ij> values (0.9 * 0.9999);
+1
+--------
+0.89991
+ij> values (0.9999 * 0.9999);
+1
+-----------
+0.99980001
+ij> values (1.0 / 3.0);
+1
+----------------------------------
+0.33333333333333333333333333333
+ij> values (1.0 / 0.3);
+1
+----------------------------------
+3.33333333333333333333333333333
+ij> values (1.0 / 0.03);
+1
+----------------------------------
+33.3333333333333333333333333333
+ij> values (1.0 / 0.000003);
+1
+----------------------------------
+333333.333333333333333333333333
+ij> values (10000.0 / 0.000003);
+1
+----------------------------------
+3333333333.33333333333333333333
+ij> values (0.0001 / 0.0003);
+1
+----------------------------------
+0.333333333333333333333333333
+ij> values (0.1 / 3.0);
+1
+----------------------------------
+0.033333333333333333333333333333
+ij> -- 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)));
+1
+--------------------------------
+69757574410000000000000000
+ij> values cast(1.7e30 as dec(31));
+1
+--------------------------------
+1700000000000000000000000000000
+ij> --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));
+1
+----------------------------------
+1.00000000000000000000
+ij> -- test the arithmetic operators on a type we know they don't work on
+create table w (x dec, y long varchar);
+0 rows inserted/updated/deleted
+ij> select x + y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.
+ij> select x - y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.
+ij> select x * y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.
+ij> select x / y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DECIMAL'.
+ij> -- clean up after ourselves
+drop table w;
+0 rows inserted/updated/deleted
+ij> --
+-- comparisons
+--
+insert into t values (123, -- int
+ 123, -- bigint
+ 123, -- smallint
+ 1234.56, -- double
+ 1234.56, -- real
+ 1234.56 -- decimal(10,2)
+ );
+1 row inserted/updated/deleted
+ij> -- test =
+select dc from t where dc is null;
+DC
+-------------
+NULL
+ij> select dc from t where dc = 10;
+DC
+-------------
+10.00
+ij> select dc from t where dc = -10;
+DC
+-------------
+-10.00
+ij> select dc from t where dc = 0;
+DC
+-------------
+0.00
+ij> select dc from t where dc = 1234.45;
+DC
+-------------
+ij> select dc from t where dc = i;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc = l;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc = s;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc = r;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc = d;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc = dc;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> -- test >
+select dc from t where dc > 10;
+DC
+-------------
+1234.56
+ij> select dc from t where dc > -10;
+DC
+-------------
+10.00
+0.00
+1234.56
+ij> select dc from t where dc > 0;
+DC
+-------------
+10.00
+1234.56
+ij> select dc from t where dc > 1234.45;
+DC
+-------------
+1234.56
+ij> select dc from t where dc > i;
+DC
+-------------
+1234.56
+ij> select dc from t where dc > l;
+DC
+-------------
+1234.56
+ij> select dc from t where dc > s;
+DC
+-------------
+1234.56
+ij> select dc from t where dc > r;
+DC
+-------------
+ij> select dc from t where dc > d;
+DC
+-------------
+ij> select dc from t where dc > dc;
+DC
+-------------
+ij> -- test >=
+select dc from t where dc >= 10;
+DC
+-------------
+10.00
+1234.56
+ij> select dc from t where dc >= -10;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= 0;
+DC
+-------------
+10.00
+0.00
+1234.56
+ij> select dc from t where dc >= 1234.45;
+DC
+-------------
+1234.56
+ij> select dc from t where dc >= i;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= l;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= s;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= r;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= d;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc >= dc;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> -- test <
+select dc from t where dc < 10;
+DC
+-------------
+-10.00
+0.00
+ij> select dc from t where dc < -10;
+DC
+-------------
+ij> select dc from t where dc < 0;
+DC
+-------------
+-10.00
+ij> select dc from t where dc < 1234.45;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc < i;
+DC
+-------------
+ij> select dc from t where dc < l;
+DC
+-------------
+ij> select dc from t where dc < s;
+DC
+-------------
+ij> select dc from t where dc < r;
+DC
+-------------
+ij> select dc from t where dc < d;
+DC
+-------------
+ij> select dc from t where dc < dc;
+DC
+-------------
+ij> -- test <=
+select dc from t where dc <= 10;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc <= -10;
+DC
+-------------
+-10.00
+ij> select dc from t where dc <= 0;
+DC
+-------------
+-10.00
+0.00
+ij> select dc from t where dc <= 1234.45;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc <= i;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc <= l;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc <= s;
+DC
+-------------
+10.00
+-10.00
+0.00
+ij> select dc from t where dc <= r;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc <= d;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc <= dc;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> -- test <>
+select dc from t where dc <> 10;
+DC
+-------------
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc <> -10;
+DC
+-------------
+10.00
+0.00
+1234.56
+ij> select dc from t where dc <> 0;
+DC
+-------------
+10.00
+-10.00
+1234.56
+ij> select dc from t where dc <> 1234.45;
+DC
+-------------
+10.00
+-10.00
+0.00
+1234.56
+ij> select dc from t where dc <> i;
+DC
+-------------
+1234.56
+ij> select dc from t where dc <> l;
+DC
+-------------
+1234.56
+ij> select dc from t where dc <> s;
+DC
+-------------
+1234.56
+ij> select dc from t where dc <> r;
+DC
+-------------
+ij> select dc from t where dc <> d;
+DC
+-------------
+ij> select dc from t where dc <> dc;
+DC
+-------------
+ij> --
+-- test a variety of inserts and updates
+--
+drop table t2;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
+ij> create table t2 (i int,
+ l bigint,
+ s smallint,
+ d double precision,
+ r real,
+ dc decimal(10,2));
+0 rows inserted/updated/deleted
+ij> insert into t2 select * from t;
+5 rows inserted/updated/deleted
+ij> -- add a few indexes
+create index dcindex on t2(dc);
+0 rows inserted/updated/deleted
+ij> create unique index dcuniqueindex on t2(dc);
+0 rows inserted/updated/deleted
+ij> -- now do updates and confirm they are ok
+update t2 set dc = dc + 1.1;
+5 rows inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-8.90
+1.10
+11.10
+1235.66
+NULL
+ij> update t2 set dc = dc - 1.1;
+5 rows inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-10.00
+0.00
+10.00
+1234.56
+NULL
+ij> update t2 set dc = dc / 1.1;
+5 rows inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-9.09
+0.00
+9.09
+1122.32
+NULL
+ij> update t2 set dc = dc * 1.1;
+5 rows inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-9.99
+0.00
+9.99
+1234.55
+NULL
+ij> -- try some deletes
+delete from t2 where dc > 0;
+2 rows inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-9.99
+0.00
+NULL
+ij> delete from t2 where dc = 0;
+1 row inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+-9.99
+NULL
+ij> delete from t2 where dc < 0;
+1 row inserted/updated/deleted
+ij> select dc from t2;
+DC
+-------------
+NULL
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> -- 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));
+0 rows inserted/updated/deleted
+ij> create unique index tu on t(d);
+0 rows inserted/updated/deleted
+ij> insert into t values ('a', 1.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 11111.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 11111111.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 6.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 666.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', .6, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 0, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 666666.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 99999999999999.123, 'Z');
+1 row inserted/updated/deleted
+ij> insert into t values ('a', 9.123, 'Z');
+1 row inserted/updated/deleted
+ij> select * from t;
+C1 |D |C2
+---------------------------------
+a |1.1230 |Z
+a |11111.1230 |Z
+a |11111111.1230 |Z
+a |6.1230 |Z
+a |666.1230 |Z
+a |0.6000 |Z
+a |0.0000 |Z
+a |666666.1230 |Z
+a |99999999999999.1230 |Z
+a |9.1230 |Z
+ij> update t set d = d + .0007;
+10 rows inserted/updated/deleted
+ij> select * from t;
+C1 |D |C2
+---------------------------------
+a |1.1237 |Z
+a |11111.1237 |Z
+a |11111111.1237 |Z
+a |6.1237 |Z
+a |666.1237 |Z
+a |0.6007 |Z
+a |0.0007 |Z
+a |666666.1237 |Z
+a |99999999999999.1237 |Z
+a |9.1237 |Z
+ij> drop table tmp;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
+ij> drop table bad;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'BAD' because it does not exist.
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> -- ** 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);
+0 rows inserted/updated/deleted
+ij> insert into t values (null, null, null, null, null);
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0);
+1 row inserted/updated/deleted
+ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0);
+1 row inserted/updated/deleted
+ij> select d + d, i + d, s + d from t;
+1 |2 |3
+--------------------------------------------------------------------
+NULL |NULL |NULL
+400.0 |200.0 |300.0
+-400.0 |-201.0 |-300.0
+ij> select d + d + d, d + 100 + 432e0 from t;
+1 |2
+---------------------------------------------
+NULL |NULL
+600.0 |732.0
+-600.0 |332.0
+ij> select d - i, i - d, d - s, s - d from t;
+1 |2 |3 |4
+-------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+200.0 |-200.0 |100.0 |-100.0
+-199.0 |199.0 |-100.0 |100.0
+ij> select d - d - d, d - 100 - 432e0 from t;
+1 |2
+---------------------------------------------
+NULL |NULL
+-200.0 |-332.0
+200.0 |-732.0
+ij> select i, d, i * d, d * i, d * d, d * 2, d * 2.0e0 from t;
+I |D |3 |4 |5 |6 |7
+-----------------------------------------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL |NULL |NULL
+0 |200.0 |0.0 |0.0 |40000.0 |400.0 |400.0
+-1 |-200.0 |200.0 |200.0 |40000.0 |-400.0 |-400.0
+ij> -- try unary minus, plus
+select -(d * 100 / 100e0 ), +(d * 100e0 / 100 ) from t;
+1 |2
+---------------------------------------------
+NULL |NULL
+-200.0 |200.0
+200.0 |-200.0
+ij> -- test null/null, constant/null, null/constant
+select i, d, i / d, 10 / d, d / 10e0 from t;
+I |D |3 |4 |5
+-------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL
+0 |200.0 |0.0 |0.05 |20.0
+-1 |-200.0 |0.005 |-0.05 |-20.0
+ij> -- test for divide by 0
+select d / i from t;
+1
+----------------------
+NULL
+ERROR 22012: Attempt to divide by zero.
+ij> select 20e0 / 5e0 / 4e0, 20e0 / 4e0 / 5 from t;
+1 |2
+---------------------------------------------
+1.0 |1.0
+1.0 |1.0
+1.0 |1.0
+ij> -- test positive/negative, negative/positive and negative/negative
+select d, d / -d, (-d) / d, (-d) / -d from t;
+D |2 |3 |4
+-------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL
+200.0 |-1.0 |-1.0 |1.0
+-200.0 |-1.0 |-1.0 |1.0
+ij> -- test some "more complex" expressions
+select d, d + 10e0, d - (10 - 20e0), d - 10, d - (20 - 10) from t;
+D |2 |3 |4 |5
+------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL
+200.0 |210.0 |210.0 |190.0 |190.0
+-200.0 |-190.0 |-190.0 |-210.0 |-210.0
+ij> -- show that decimals will go into doubles:
+select d+1.1 from t;
+1
+----------------------
+NULL
+201.1
+-198.9
+ij> insert into t (d) values(1.1);
+1 row inserted/updated/deleted
+ij> select d from t where d=1.1;
+D
+----------------------
+1.1
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> -- test overflow
+create table s (d double precision, p double);
+0 rows inserted/updated/deleted
+ij> insert into s values (null, null);
+1 row inserted/updated/deleted
+ij> insert into s values (0, 100);
+1 row inserted/updated/deleted
+ij> insert into s values (1, 101);
+1 row inserted/updated/deleted
+ij> select d + 1.7e+308 from s;
+1
+----------------------
+NULL
+1.7E308
+1.7E308
+ij> -- 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;
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> select 1.8e+1000, - 1.8e+1000, 'This query should not work' from s;
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- these are far enough from the infinities to work
+select 1.797e+308, - 1.797e+308, 'This query should work' from s;
+1 |2 |3
+--------------------------------------------------------------------
+1.797E308 |-1.797E308 |This query should work
+1.797E308 |-1.797E308 |This query should work
+1.797E308 |-1.797E308 |This query should work
+ij> select 1.6e+308, - 1.6e+308, 'This query should work' from s;
+1 |2 |3
+--------------------------------------------------------------------
+1.6E308 |-1.6E308 |This query should work
+1.6E308 |-1.6E308 |This query should work
+1.6E308 |-1.6E308 |This query should work
+ij> -- the null row will still get returned
+select d - 1.6e+308 - 0, 'This query should work' from s;
+1 |2
+---------------------------------------------
+NULL |This query should work
+-1.6E308 |This query should work
+-1.6E308 |This query should work
+ij> select d - 1.6e+308 - 1.6e+308, 'This query should fail' from s;
+1 |2
+---------------------------------------------
+NULL |This query should fail
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- these should fail
+select p * 1.6e+308 from s;
+1
+----------------------
+NULL
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> select p * -1.6e+308 from s;
+1
+----------------------
+NULL
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- these work
+insert into s values (-1.6e+308, 0);
+1 row inserted/updated/deleted
+ij> insert into s values (-1.797e+308, 0);
+1 row inserted/updated/deleted
+ij> -- these don't work
+insert into s values (-1.798e+308, 0);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> insert into s values (-1.8e+308, 0);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- see two more rows
+select -d from s;
+1
+----------------------
+NULL
+0.0
+-1.0
+1.6E308
+1.797E308
+ij> drop table s;
+0 rows inserted/updated/deleted
+ij> -- test the arithmetic operators on a type we know they don't work on
+create table w (x double precision, y long varchar);
+0 rows inserted/updated/deleted
+ij> select x + y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.
+ij> select x - y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.
+ij> select x * y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.
+ij> select x / y from w;
+ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DOUBLE'.
+ij> -- clean up after ourselves
+drop table w;
+0 rows inserted/updated/deleted
+ij> --
+-- comparisons
+--
+create table c (i int, s smallint, d double precision, p double precision);
+0 rows inserted/updated/deleted
+ij> -- insert some values
+insert into c values (0, 0, 0e0, 0e0);
+1 row inserted/updated/deleted
+ij> insert into c values (null, null, 5e0, null);
+1 row inserted/updated/deleted
+ij> insert into c values (1, 1, 1e0, 2e0);
+1 row inserted/updated/deleted
+ij> insert into c values (1956475, 1956, 1956475e0, 1956475e0);
+1 row inserted/updated/deleted
+ij> -- select each one in turn
+select d from c where d = 0e0;
+D
+----------------------
+0.0
+ij> select d from c where d = 1e0;
+D
+----------------------
+1.0
+ij> select d from c where d = 1956475e0;
+D
+----------------------
+1956475.0
+ij> -- now look for a value that isn't in the table
+select d from c where p = 2e0;
+D
+----------------------
+1.0
+ij> -- now test null = null semantics
+select d from c where d = d;
+D
+----------------------
+0.0
+5.0
+1.0
+1956475.0
+ij> -- now test <>, <, >
+select d from c where d <> 0e0;
+D
+----------------------
+5.0
+1.0
+1956475.0
+ij> select d from c where d <> 1e0;
+D
+----------------------
+0.0
+5.0
+1956475.0
+ij> select d from c where d < 1956475e0;
+D
+----------------------
+0.0
+5.0
+1.0
+ij> select d from c where d < 2e0;
+D
+----------------------
+0.0
+1.0
+ij> select d from c where d > d;
+D
+----------------------
+ij> select d from c where d > p;
+D
+----------------------
+ij> -- now test <=, >=
+select d from c where d <= 0e0;
+D
+----------------------
+0.0
+ij> select d from c where d <= 1e0;
+D
+----------------------
+0.0
+1.0
+ij> select d from c where d <= 2e0;
+D
+----------------------
+0.0
+1.0
+ij> select d from c where d >= 1956475e0;
+D
+----------------------
+1956475.0
+ij> select d from c where d >= d;
+D
+----------------------
+0.0
+5.0
+1.0
+1956475.0
+ij> select d from c where d >= p;
+D
+----------------------
+0.0
+1956475.0
+ij> -- test comparisons with int and smallint
+select d from c where d <= i;
+D
+----------------------
+0.0
+1.0
+1956475.0
+ij> select d from c where d < s;
+D
+----------------------
+ij> select d from c where d > i;
+D
+----------------------
+ij> select d from c where d >= s;
+D
+----------------------
+0.0
+1.0
+1956475.0
+ij> select d from c where d <> i;
+D
+----------------------
+ij> select d from c where d = s;
+D
+----------------------
+0.0
+1.0
+ij> -- 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;
+D
+----------------------
+ij> -- test =SQ
+-- this gets cardinality error
+select d from c where d = (select d from c);
+ERROR 21000: Scalar subquery is only allowed to return a single row.
+ij> -- this works
+select d from c where d = (select d from c where d=5);
+D
+----------------------
+5.0
+ij> -- show that double is comparable to real
+create table o (c char(10), v varchar(30), dc decimal);
+0 rows inserted/updated/deleted
+ij> select d from c,o where d <> dc;
+D
+----------------------
+ij> -- clean up
+drop table c;
+0 rows inserted/updated/deleted
+ij> drop table o;
+0 rows inserted/updated/deleted
+ij> --
+-- test alternate syntax: just double will work for DB2 compatibility
+--
+create table db2version (d double);
+0 rows inserted/updated/deleted
+ij> drop table db2version;
+0 rows inserted/updated/deleted
+ij> --
+-- test a variety of inserts and updates
+--
+create table source (i int, s smallint, c char(10), v varchar(50),
+ d double precision);
+0 rows inserted/updated/deleted
+ij> create table target (p double precision not null);
+0 rows inserted/updated/deleted
+ij> -- we have already tested inserting integer and double literals.
+insert into source values (1, 2, '3', '4', 5);
+1 row inserted/updated/deleted
+ij> -- these will all work:
+insert into target select i from source;
+1 row inserted/updated/deleted
+ij> insert into target select s from source;
+1 row inserted/updated/deleted
+ij> insert into target select d from source;
+1 row inserted/updated/deleted
+ij> -- these will all fail:
+delete from source;
+1 row inserted/updated/deleted
+ij> insert into source values (null, null, null, null, null);
+1 row inserted/updated/deleted
+ij> -- these fail because the target won't take a null -- of any type
+insert into target values(null);
+ERROR 23502: Column 'P' cannot accept a NULL value.
+ij> insert into target select i from source;
+ERROR 23502: Column 'P' cannot accept a NULL value.
+ij> insert into target select s from source;
+ERROR 23502: Column 'P' cannot accept a NULL value.
+ij> insert into target select d from source;
+ERROR 23502: Column 'P' cannot accept a NULL value.
+ij> -- expect 4 rows in target: 1, 2, 5, and 1:
+select * from target;
+P
+----------------------
+1.0
+2.0
+5.0
+ij> update target set p = p + 1;
+3 rows inserted/updated/deleted
+ij> select * from target;
+P
+----------------------
+2.0
+3.0
+6.0
+ij> update target set p = p - 1;
+3 rows inserted/updated/deleted
+ij> select * from target;
+P
+----------------------
+1.0
+2.0
+5.0
+ij> update target set p = p / 10;
+3 rows inserted/updated/deleted
+ij> select * from target;
+P
+----------------------
+0.1
+0.2
+0.5
+ij> update target set p = p * 10;
+3 rows inserted/updated/deleted
+ij> select * from target;
+P
+----------------------
+1.0
+2.0
+5.0
+ij> -- these should work
+update source set i = 1.4e8;
+1 row inserted/updated/deleted
+ij> update source set s = 1.4e4;
+1 row inserted/updated/deleted
+ij> select i, s from source where i=1.4e8 or s=1.4e4;
+I |S
+------------------
+140000000 |14000
+ij> -- these should get overflow
+update source set i = 1.4e12;
+ERROR 22003: The resulting value is outside the range for the data type INTEGER.
+ij> update source set s = 1.4e12;
+ERROR 22003: The resulting value is outside the range for the data type SMALLINT.
+ij> drop table source;
+0 rows inserted/updated/deleted
+ij> drop table target;
+0 rows inserted/updated/deleted
+ij> create table abcfloat (numtest float(20));
+0 rows inserted/updated/deleted
+ij> insert into abcfloat values (1.23456789);
+1 row inserted/updated/deleted
+ij> insert into abcfloat values (.123456789);
+1 row inserted/updated/deleted
+ij> insert into abcfloat values (-.123456789);
+1 row inserted/updated/deleted
+ij> insert into abcfloat values (0.223456789);
+1 row inserted/updated/deleted
+ij> insert into abcfloat values (-0.223456789);
+1 row inserted/updated/deleted
+ij> insert into abcfloat values (12345678.9);
+1 row inserted/updated/deleted
+ij> select * from abcfloat;
+NUMTEST
+-------------
+1.2345679
+0.12345679
+-0.12345679
+0.22345679
+-0.22345679
+1.2345679E7
+ij> drop table abcfloat;
+0 rows inserted/updated/deleted
+ij> -- ** 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));
+0 rows inserted/updated/deleted
+ij> select columnname, columndatatype
+from sys.syscolumns c, sys.systables t
+where c.referenceid = t.tableid and t.tablename='T';
+COLUMNNAME |COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------
+D |DOUBLE
+F |DOUBLE
+F1 |REAL
+F23 |REAL
+F24 |DOUBLE
+F53 |DOUBLE
+R |REAL
+ij> -- invalid float values
+insert into t(r) values 'NaN';
+ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'.
+ij> insert into t(r) values +3.4021E+38;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into t(r) values -3.4021E+38;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> create table tt(c char(254));
+0 rows inserted/updated/deleted
+ij> insert into tt values -3.402E+38;
+ERROR 42821: Columns of type 'CHAR' cannot hold values of type 'DOUBLE'.
+ij> insert into t(r) select * from tt;
+ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'.
+ij> insert into t(r) values '1.0';
+ERROR 42821: Columns of type 'REAL' cannot hold values of type 'CHAR'.
+ij> update t set r = NaN;
+ERROR 42X04: Column 'NAN' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'NAN' is not a column in the target table.
+ij> update t set r = +3.4021E+38;
+0 rows inserted/updated/deleted
+ij> update t set r = -3.4021E+38;
+0 rows inserted/updated/deleted
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> drop table tt;
+0 rows inserted/updated/deleted
+ij> -- these get errors for invalid precision values:
+create table t1 (d double precision, r real, f float(-10));
+ERROR 42X01: Syntax error: Encountered "-" at line 2, column 54.
+ij> --
+create table t2 (d double precision, r real, f float(-1));
+ERROR 42X01: Syntax error: Encountered "-" at line 2, column 54.
+ij> create table t3 (d double precision, r real, f float(0));
+ERROR 42X48: Value '0' is not a valid precision for FLOAT.
+ij> create table t4 (d double precision, r real, f float(100));
+ERROR 42X48: Value '100' is not a valid precision for FLOAT.
+ij> create table t5 (d double precision, r real, f float(53));
+ERROR 42X48: Value '53' is not a valid precision for FLOAT.
+ij> create table t6 (d double precision, r real, f float(12.3));
+ERROR 42X49: Value '12.3' is not a valid integer literal.
+ij> -- ** 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);
+0 rows inserted/updated/deleted
+ij> insert into t values (null, null, null, null, null, null);
+1 row inserted/updated/deleted
+ij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 200.0e0);
+1 row inserted/updated/deleted
+ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -200.0e0);
+1 row inserted/updated/deleted
+ij> select r + r, d + r, i + r, s + r, r + i from t;
+1 |2 |3 |4 |5
+------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL
+400.0 |400.0 |200.0 |300.0 |200.0
+-400.0 |-400.0 |-201.0 |-300.0 |-201.0
+ij> select r + r + r, r + 100 + 432e0 from t;
+1 |2
+------------------------------------
+NULL |NULL
+600.0 |732.0
+-600.0 |332.0
+ij> select r - r, r - d, d - r, r - i, i - r, r - s, s - r from t;
+1 |2 |3 |4 |5 |6 |7
+-------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL |NULL |NULL
+0.0 |0.0 |0.0 |200.0 |-200.0 |100.0 |-100.0
+0.0 |0.0 |0.0 |-199.0 |199.0 |-100.0 |100.0
+ij> select r - r - r, r - 100 - 432e0 from t;
+1 |2
+------------------------------------
+NULL |NULL
+-200.0 |-332.0
+200.0 |-732.0
+ij> select i, d, s, r, i * r, r * i, s * r, d * r, r * r, r * 2, r * 2.0e0 from t;
+I |D |S |R |5 |6 |7 |8 |9 |10 |11
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL
+0 |200.0 |100 |200.0 |0.0 |0.0 |20000.0 |40000.0 |40000.0 |400.0 |400.0
+-1 |-200.0 |-100 |-200.0 |200.0 |200.0 |20000.0 |40000.0 |40000.0 |-400.0 |-400.0
+ij> -- try unary minus, plus
+select -(r * 100 / 100e0 ), +(r * 100e0 / 100 ) from t;
+1 |2
+---------------------------------------------
+NULL |NULL
+-200.0 |200.0
+200.0 |-200.0
+ij> -- test null/null, constant/null, null/constant
+select i, d, r, d / r, i / r, 10 / r, r / d, r / 10e0 from t;
+I |D |R |4 |5 |6 |7 |8
+-------------------------------------------------------------------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL |NULL |NULL |NULL
+0 |200.0 |200.0 |1.0 |0.0 |0.05 |1.0 |20.0
+-1 |-200.0 |-200.0 |1.0 |0.005 |-0.05 |1.0 |-20.0
+ij> -- test for divide by 0
+select r / i from t;
+1
+-------------
+NULL
+ERROR 22012: Attempt to divide by zero.
+ij> -- test positive/negative, negative/positive and negative/negative
+select r, r / -r, (-r) / r, (-r) / -r from t;
+R |2 |3 |4
+-------------------------------------------------------
+NULL |NULL |NULL |NULL
+200.0 |-1.0 |-1.0 |1.0
+-200.0 |-1.0 |-1.0 |1.0
+ij> -- test some "more complex" expressions
+select r, r + 10e0, r - (10 - 20e0), r - 10, r - (20 - 10) from t;
+R |2 |3 |4 |5
+---------------------------------------------------------------------------------------
+NULL |NULL |NULL |NULL |NULL
+200.0 |210.0 |210.0 |190.0 |190.0
+-200.0 |-190.0 |-190.0 |-210.0 |-210.0
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> -- test overflow
+create table s (d real, p real);
+0 rows inserted/updated/deleted
+ij> insert into s values (null, null);
+1 row inserted/updated/deleted
+ij> insert into s values (0, 100);
+1 row inserted/updated/deleted
+ij> insert into s values (1, 101);
+1 row inserted/updated/deleted
+ij> select d + 3.4e+38 from s;
+1
+----------------------
+NULL
+3.4E38
+3.4E38
+ij> -- 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);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into s values(- 3.403e+38, - 3.403e+38);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into s values(1.8e+100, 1.8e+100);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into s values(- 1.8e+100, - 1.8e+100);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select * from s;
+D |P
+---------------------------
+NULL |NULL
+0.0 |100.0
+1.0 |101.0
+ij> -- these are far enough from the infinities to work
+insert into s values(3.402e+38, - 3.402e+38);
+1 row inserted/updated/deleted
+ij> insert into s values(3.3e+38, - 3.3e+38);
+1 row inserted/updated/deleted
+ij> -- 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;
+1 |2 |3
+--------------------------------------------------------------------
+NULL |NULL |NULL
+-6.6E38 |3.3E40 |-3.3E40
+-6.6E38 |3.333E40 |-3.333E40
+-3.1979999994446195E38|-1.1226600001832754E77|1.1226600001832754E77
+-3.300000034517288E38 |-1.088999988609295E77 |1.088999988609295E77
+ij> -- see two more rows
+select -d from s;
+1
+-------------
+NULL
+0.0
+-1.0
+-3.402E38
+-3.3E38
+ij> -- to do the math as reals, we have to keep it in the columns
+delete from s;
+5 rows inserted/updated/deleted
+ij> insert into s values (1,3.3e+38);
+1 row inserted/updated/deleted
+ij> -- these will fail, because the math is done as reals
+select d - p - p from s;
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select p * p from s;
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select p * -p from s;
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> delete from s;
+1 row inserted/updated/deleted
+ij> -- select values between 0 and 1
+insert into s values (.111, 1e-1);
+1 row inserted/updated/deleted
+ij> insert into s values (0.222, 0.222);
+1 row inserted/updated/deleted
+ij> select * from s;
+D |P
+---------------------------
+0.111 |0.1
+0.222 |0.222
+ij> delete from s;
+2 rows inserted/updated/deleted
+ij> insert into s values (10, 1e-10);
+1 row inserted/updated/deleted
+ij> -- underflow calculation doesn't round off, gives error.
+update s set d=d*1.4e-55, p=p*1.4e-45;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select d, p from s;
+D |P
+---------------------------
+10.0 |1.0E-10
+ij> update s set d=d + 1.4e-46;
+1 row inserted/updated/deleted
+ij> select d from s;
+D
+-------------
+10.0
+ij> drop table s;
+0 rows inserted/updated/deleted
+ij> -- test the arithmetic operators on a type we know they don't work on
+create table w (x real, y char);
+0 rows inserted/updated/deleted
+ij> select x + y from w;
+ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.
+ij> select x - y from w;
+ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.
+ij> select x * y from w;
+ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.
+ij> select x / y from w;
+ERROR 42846: Cannot convert types 'CHAR' to 'REAL'.
+ij> -- clean up after ourselves
+drop table w;
+0 rows inserted/updated/deleted
+ij> --
+-- comparisons
+--
+create table c (i int, s smallint, d double precision, r real, l real);
+0 rows inserted/updated/deleted
+ij> -- insert some values
+insert into c values (0, 0, 0e0, 0e0, 0e0);
+1 row inserted/updated/deleted
+ij> insert into c values (null, null, 5e0, null, null);
+1 row inserted/updated/deleted
+ij> insert into c values (1, 1, 1e0, 2e0, 3e0);
+1 row inserted/updated/deleted
+ij> insert into c values (1956475, 1956, 1956475e0, 1956475e0, 1956475e0);
+1 row inserted/updated/deleted
+ij> -- select each one in turn
+select r from c where r = 0e0;
+R
+-------------
+0.0
+ij> select r from c where r = 1e0;
+R
+-------------
+ij> select r from c where r = 1956475e0;
+R
+-------------
+1956475.0
+ij> -- now look for a value that isn't in the table
+select r from c where l = 2e0;
+R
+-------------
+ij> -- now test null = null semantics
+select r from c where r = r;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> -- now test <>, <, >, <=, >=
+select r from c where r <> 0e0;
+R
+-------------
+2.0
+1956475.0
+ij> select r from c where r <> 1e0;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> select r from c where r < 1956475e0;
+R
+-------------
+0.0
+2.0
+ij> select r from c where r < 2e0;
+R
+-------------
+0.0
+ij> select r from c where r > d;
+R
+-------------
+2.0
+ij> select r from c where r <= l;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> select r from c where r >= r;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> -- test comparisons with int and smallint and double
+select r from c where r <= i;
+R
+-------------
+0.0
+1956475.0
+ij> select r from c where r < s;
+R
+-------------
+ij> select r from c where r > i;
+R
+-------------
+2.0
+ij> select r from c where r >= s;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> select r from c where r <> i;
+R
+-------------
+2.0
+ij> select r from c where r = s;
+R
+-------------
+0.0
+ij> select r from c where r = d;
+R
+-------------
+0.0
+1956475.0
+ij> select r from c where r >= d;
+R
+-------------
+0.0
+2.0
+1956475.0
+ij> -- show that real is comparable to decimal
+create table o (c char(10), v varchar(30), dc decimal);
+0 rows inserted/updated/deleted
+ij> select r from c,o where r <> dc;
+R
+-------------
+ij> -- clean up
+drop table c;
+0 rows inserted/updated/deleted
+ij> drop table o;
+0 rows inserted/updated/deleted
+ij> --
+-- 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);
+0 rows inserted/updated/deleted
+ij> create table target (t real not null);
+0 rows inserted/updated/deleted
+ij> -- we have already tested inserting integer and double literals.
+insert into source values (1, 2, '3', '4', 5, 6);
+1 row inserted/updated/deleted
+ij> -- these will all work:
+insert into target select i from source;
+1 row inserted/updated/deleted
+ij> insert into target select s from source;
+1 row inserted/updated/deleted
+ij> insert into target select d from source;
+1 row inserted/updated/deleted
+ij> insert into target select r from source;
+1 row inserted/updated/deleted
+ij> delete from source;
+1 row inserted/updated/deleted
+ij> insert into source values (null, null, null, null, null, null);
+1 row inserted/updated/deleted
+ij> insert into source values (1, 2, '3', '4', 5, 6);
+1 row inserted/updated/deleted
+ij> -- these fail because the target won't take a null -- of any type
+insert into target values(null);
+ERROR 23502: Column 'T' cannot accept a NULL value.
+ij> insert into target select i from source;
+ERROR 23502: Column 'T' cannot accept a NULL value.
+ij> insert into target select s from source;
+ERROR 23502: Column 'T' cannot accept a NULL value.
+ij> insert into target select d from source;
+ERROR 23502: Column 'T' cannot accept a NULL value.
+ij> insert into target select r from source;
+ERROR 23502: Column 'T' cannot accept a NULL value.
+ij> -- expect 5 rows in target: 1, 2, 5, 6, and 1:
+select * from target;
+T
+-------------
+1.0
+2.0
+5.0
+6.0
+ij> update target set t = t + 1;
+4 rows inserted/updated/deleted
+ij> select * from target;
+T
+-------------
+2.0
+3.0
+6.0
+7.0
+ij> update target set t = t - 1;
+4 rows inserted/updated/deleted
+ij> select * from target;
+T
+-------------
+1.0
+2.0
+5.0
+6.0
+ij> update target set t = t / 10;
+4 rows inserted/updated/deleted
+ij> select * from target;
+T
+-------------
+0.1
+0.2
+0.5
+0.6
+ij> update target set t = t * 10;
+4 rows inserted/updated/deleted
+ij> select * from target;
+T
+-------------
+1.0
+2.0
+5.0
+6.0
+ij> -- these should work
+update source set r = 1.4e4;
+2 rows inserted/updated/deleted
+ij> update source set i = r, s=r, d=r;
+2 rows inserted/updated/deleted
+ij> select i, s, d from source where i=1.4e4 or s=1.4e4 or d=1.4e4;
+I |S |D
+-----------------------------------------
+14000 |14000 |14000.0
+14000 |14000 |14000.0
+ij> -- 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;
+2 rows inserted/updated/deleted
+ij> select i, r, s from source where r = 0;
+I |R |S
+--------------------------------
+14000 |0.0 |14000
+14000 |0.0 |14000
+ij> -- these should get overflow
+update source set r = 1.4e12;
+2 rows inserted/updated/deleted
+ij> update source set i = r;
+ERROR 22003: The resulting value is outside the range for the data type INTEGER.
+ij> update source set s = r;
+ERROR 22003: The resulting value is outside the range for the data type SMALLINT.
+ij> drop table source;
+0 rows inserted/updated/deleted
+ij> drop table target;
+0 rows inserted/updated/deleted
+ij> -- ============================================================
+-- TESTS FOR DB2 FLOAT/DOUBLEs LIMITS
+-- ============================================================
+create table fake(r real);
+0 rows inserted/updated/deleted
+ij> -- ============================================================
+-- different errmsg for DB2: "value of of range", CS: "NumberFormatException"
+values 5e-325;
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values 5e-324;
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- --- TEST SPECIAL VALUES
+-- DB2 (should succed)
+insert into fake values( -3.402E+38 );
+1 row inserted/updated/deleted
+ij> insert into fake values( +3.402E+38 );
+1 row inserted/updated/deleted
+ij> insert into fake values -1;
+1 row inserted/updated/deleted
+ij> insert into fake values( -1.175E-37 );
+1 row inserted/updated/deleted
+ij> insert into fake values( +1.175E-37 );
+1 row inserted/updated/deleted
+ij> insert into fake values -2;
+1 row inserted/updated/deleted
+ij> -- CS (should fail)
+insert into fake values( -3.4028235E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +3.4028235E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -3;
+1 row inserted/updated/deleted
+ij> insert into fake values( -1.4E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +1.4E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -4;
+1 row inserted/updated/deleted
+ij> -- ============================================================
+-- variants of ZERO
+insert into fake values (+0);
+1 row inserted/updated/deleted
+ij> insert into fake values (+0.0);
+1 row inserted/updated/deleted
+ij> insert into fake values (+0.0E-37);
+1 row inserted/updated/deleted
+ij> insert into fake values (+0.0E-38);
+1 row inserted/updated/deleted
+ij> insert into fake values (+0.0E-500);
+1 row inserted/updated/deleted
+ij> values (+0.0E-500);
+1
+----------------------
+0.0
+ij> values (+1.0E-300);
+1
+----------------------
+1.0E-300
+ij> -- approx ZERO (java rounds to zero, but not DB2)
+insert into fake values (+1.0E-300);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values (+1.0E-900);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> insert into fake values (cast(+1.0E-900 as real));
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values (cast(+1.0E-300 as real));
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values (+1.0E-900);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values (cast(+1.0E-900 as real));
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> insert into fake values -11;
+1 row inserted/updated/deleted
+ij> -- ============================================================
+-- DB2 MAX_VALUES (first succeed, second fail)
+insert into fake values( -3.4019E+38 );
+1 row inserted/updated/deleted
+ij> insert into fake values( -3.4021E+38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -21;
+1 row inserted/updated/deleted
+ij> insert into fake values( +3.4019E+38 );
+1 row inserted/updated/deleted
+ij> insert into fake values( +3.4021E+38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -22;
+1 row inserted/updated/deleted
+ij> -- DB2 MIN_VALUES (first fail, second succeed)
+insert into fake values( -1.1749E-37 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -1.1751E-37 );
+1 row inserted/updated/deleted
+ij> insert into fake values -23;
+1 row inserted/updated/deleted
+ij> insert into fake values( +1.1749E-37 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +1.1751E-37 );
+1 row inserted/updated/deleted
+ij> insert into fake values -24;
+1 row inserted/updated/deleted
+ij> -- CS (fail)
+insert into fake values( -3.4028234E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -3.40282349E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -3.40282351E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -3.4028236E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -25;
+1 row inserted/updated/deleted
+ij> insert into fake values( +3.4028234E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +3.40282349E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +3.40282351E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +3.4028236E38 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -26;
+1 row inserted/updated/deleted
+ij> insert into fake values( -1.39E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -1.399E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -1.401E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( -1.41E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -27;
+1 row inserted/updated/deleted
+ij> insert into fake values( +1.39E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +1.399E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +1.401E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values( +1.41E-45 );
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values -28;
+1 row inserted/updated/deleted
+ij> -- checkpoint
+select * from fake;
+R
+-------------
+-3.402E38
+3.402E38
+-1.0
+-1.175E-37
+1.175E-37
+-2.0
+-3.0
+-4.0
+0.0
+0.0
+0.0
+0.0
+0.0
+-11.0
+-3.4019E38
+-21.0
+3.4019E38
+-22.0
+-1.1751E-37
+-23.0
+1.1751E-37
+-24.0
+-25.0
+-26.0
+-27.0
+-28.0
+ij> drop table fake;
+0 rows inserted/updated/deleted
+ij> create table fake(r real);
+0 rows inserted/updated/deleted
+ij> -- ============================================================
+-- ---underflow aritmetic
+-- underflow to small real but / makes double!=0, so we catch
+-- ok
+values cast(5e-37/1e0 as real);
+1
+-------------
+5.0E-37
+ij> -- fail
+values cast(5e-37/1e1 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(5e-37/1e300 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values cast(5e-37 as real)/cast(1e10 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> -- ok
+insert into fake values 5e-37/1e0;
+1 row inserted/updated/deleted
+ij> -- fail
+insert into fake values 5e-37/1e1;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into fake values 5e-37/1e300;
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> insert into fake values cast(5e-37 as real)/cast(1e10 as real);
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> drop table fake;
+0 rows inserted/updated/deleted
+ij> -- makes double to small, so java double rounds to 0. need to catch (fail)
+values 5e-37 / 1e300;
+1
+----------------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values cast(5e-37 / 1e300 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- ok, zero result (succeed)
+values cast(cast(0.0e0 as real) - cast(0.0e0 as real) as real);
+1
+-------------
+0.0
+ij> values cast(cast(1.0e-30 as real) - cast(1.0e-30 as real) as real);
+1
+-------------
+0.0
+ij> -- 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);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(5e-37 - 4e-37 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(5e-37 - 4.99e-37 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(5e-308 - 4e-308 as real);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values cast(5e-37 + -4e-37 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(5e-324 - 4e-324 as real);
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values cast(5e-37 * 4e-37 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> values cast(cast(5e-37 as real) * cast(4e-37 as real) as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> -- double trouble, underflow detection (fail)
+values cast(5e-300 * 4e-300 as real);
+1
+-------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- underflow aritmetic DOUBLE (fail)
+values -3e-305/1e100;
+1
+----------------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> values -3e-305/1e100;
+1
+----------------------
+ERROR 22003: The resulting value is outside the range for the data type DOUBLE.
+ij> -- negative zeros not allowed (succeed)
+values 0.0e5/-1;
+1
+----------------------
+0.0
+ij> -- 30 characters limit to be enforced ) (first fail, second ok)
+values 01234567890123456789012345678e1;
+ERROR 42820: The floating point literal '01234567890123456789012345678e1' contains more than 30 characters.
+ij> values 0123456789012345678901234567e1;
+1
+----------------------
+1.2345678901234569E27
+ij> -- ============================================================
+--- 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);
+0 rows inserted/updated/deleted
+ij> insert into t1 values -1.40129846432481700e-46;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into t1 values +1.40129846432481700e-46;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select * from t1;
+C1
+-------------
+ij> -- 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;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into t1 values -3.40282346638528860e+38;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select * from t1;
+C1
+-------------
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> -- 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);
+0 rows inserted/updated/deleted
+ij> insert into t1 values -1.40129846432481700e-46;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into t1 values +1.40129846432481700e-46;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select * from t1;
+C1
+-------------
+ij> -- these 2 insert statements raise ERROR 22003 because
+-- the values are greater db2's limits (fail)
+insert into t1 values 3.40282346638528860e+38;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> insert into t1 values -3.40282346638528860e+38;
+ERROR 22003: The resulting value is outside the range for the data type REAL.
+ij> select * from t1;
+C1
+-------------
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> -- ============================================================
+-- bug 5704 - make sure we catch the overflow correctly for multiplication operator
+values cast(1e30 as decimal(31))*cast(1e30 as decimal(31));
+1
+--------------------------------
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
+ij> values cast('1e30' as decimal(31))*cast('1e30' as decimal(31));
+1
+--------------------------------
+ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
+ij> create table tiger(d decimal(12,11));
+0 rows inserted/updated/deleted
+ij> insert into tiger values (1.234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.1234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.01234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.0001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.00001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.000001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.0000001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.00000001234);
+1 row inserted/updated/deleted
+ij> insert into tiger values (0.00000001234);
+1 row inserted/updated/deleted
+ij> select d from tiger order by 1;
+D
+---------------
+0.00000001234
+0.00000001234
+0.00000012340
+0.00000123400
+0.00001234000
+0.00012340000
+0.00123400000
+0.00123400000
+0.01234000000
+0.12340000000
+1.23400000000
+ij> -- =====================================================
+-- some formatting tests
+values cast ('1e+0' as DECIMAL(6,2));
[... 69 lines stripped ...]