Google Git
Sign in
apache / derby / f16c46cbdd5be8dd9bdcee935ec1f68970146478 / . / java / org.apache.derby.tests / org / apache / derbyTesting / functionTests / master / j9_foundation / floattypes.out
blob: 8c15ed696e9f31896c63bb518c20be17ef67308e [file] [log] [blame]
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- ** 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.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> --
create table t2 (d double precision, r real, f float(-1));
ERROR 42X01: Syntax error: Encountered "-" at line 2, column 54.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
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.3000000345172877E38|-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));
1
--------
1.00
ij> values cast ('+-1e+1' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('-1e+1' as DECIMAL(6,2));
1
--------
-10.00
ij> values cast ('-1e-1' as DECIMAL(6,2));
1
--------
-0.10
ij> values cast ('-1e-+1' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('-1e--1' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('-1e+-1' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('-1e+-1' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('1.0e' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('1.0e+' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('1.0e-' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('1.0ee' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('123.' as DECIMAL(6,2));
1
--------
123.00
ij> values cast ('1e' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('1e1.0' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij> values cast ('.3' as DECIMAL(6,2));
1
--------
0.30
ij> values cast ('' as DECIMAL(6,2));
1
--------
ERROR 22018: Invalid character string format for type DECIMAL.
ij>