Google Git
Sign in
apache / trafodion / 927b9384b9e1957e95487792fcbed6cac09ac22a / . / core / sql / regress / executor / EXPECTED022.SB
blob: 324058a15718869d266acc86f40641b38b6946ae [file] [log] [blame]
>>obey TEST022 (test);
>>-------------------------------------------------------------------------------
>>create table t022t3 (a int unsigned);
--- SQL operation complete.
>>create table t022t4 (a smallint unsigned, b smallint);
--- SQL operation complete.
>>
>>insert into t022t3 values (4294967295), (3000000000);
--- 2 row(s) inserted.
>>insert into t022t4 values (65535, 32767);
--- 1 row(s) inserted.
>>
>>create table t022a7 (c1 float(22), c2 float not null);
--- SQL operation complete.
>>
>>prepare insertIntoT022A7 from insert into t022a7 values (?,?);
--- SQL command prepared.
>>sh sh -c 'rm -f t022xxx > /dev/null 2>&1 ';
>>-- Must use "sh sh -c" if we want the command to work on NT platforms.
>>-- The space between t022xxx and the single quote (') is required by the SQLCI sh command.
>>-- If the space is not specified, the output is going into the file "t022xxx'" - Strange!
>>sh sh -c 'echo "begin work;" > t022xxx ';
>>sh sh -c 'echo "execute insertIntoT022A7 using 0 , 1. ; " >> t022xxx ';
>>sh sh -c 'echo "execute insertIntoT022A7 using 1 , 2.E3 ; " >> t022xxx ';
>>sh sh -c 'echo "execute insertIntoT022A7 using 3 , 123.456E2 ; " >> t022xxx ';
>>sh sh -c 'echo "commit work;" >> t022xxx ';
>>
>>-- Positive cases to test the limits of each data type.
>>
>>select cast (-9 as numeric(1)) from dual;
(EXPR)
------
-9
--- 1 row(s) selected.
>>select cast (9 as numeric(1)) from dual;
(EXPR)
------
9
--- 1 row(s) selected.
>>select cast (-9999 as numeric(4)) from dual;
(EXPR)
------
-9999
--- 1 row(s) selected.
>>select cast (9999 as numeric(4)) from dual;
(EXPR)
------
9999
--- 1 row(s) selected.
>>select cast (-32768 as smallint) from dual;
(EXPR)
------
-32768
--- 1 row(s) selected.
>>select cast (32767 as smallint) from dual;
(EXPR)
------
32767
--- 1 row(s) selected.
>>
>>select cast (0 as numeric(1) unsigned) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast (9 as numeric(1) unsigned) from dual;
(EXPR)
------
9
--- 1 row(s) selected.
>>select cast (0 as numeric(4) unsigned) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast (9999 as numeric(4) unsigned) from dual;
(EXPR)
------
9999
--- 1 row(s) selected.
>>select cast (0 as smallint unsigned) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast (65535 as smallint unsigned) from dual;
(EXPR)
------
65535
--- 1 row(s) selected.
>>
>>select cast (-99999 as numeric(5)) from dual;
(EXPR)
-----------
-99999
--- 1 row(s) selected.
>>select cast (99999 as numeric(5)) from dual;
(EXPR)
-----------
99999
--- 1 row(s) selected.
>>select cast (-999999999 as numeric(9)) from dual;
(EXPR)
-----------
-999999999
--- 1 row(s) selected.
>>select cast (999999999 as numeric(9)) from dual;
(EXPR)
-----------
999999999
--- 1 row(s) selected.
>>select cast (-2147483648 as int) from dual;
(EXPR)
-----------
-2147483648
--- 1 row(s) selected.
>>select cast (2147483647 as int) from dual;
(EXPR)
-----------
2147483647
--- 1 row(s) selected.
>>
>>select cast (0 as numeric(5) unsigned) from dual;
(EXPR)
----------
0
--- 1 row(s) selected.
>>select cast (99999 as numeric(5) unsigned) from dual;
(EXPR)
----------
99999
--- 1 row(s) selected.
>>select cast (0 as numeric(9) unsigned) from dual;
(EXPR)
----------
0
--- 1 row(s) selected.
>>select cast (999999999 as numeric(9) unsigned) from dual;
(EXPR)
----------
999999999
--- 1 row(s) selected.
>>select cast (0 as int unsigned) from dual;
(EXPR)
----------
0
--- 1 row(s) selected.
>>select cast (4294967295 as int unsigned) from dual;
(EXPR)
----------
4294967295
--- 1 row(s) selected.
>>
>>select cast (-9999999999 as numeric(10)) from dual;
(EXPR)
--------------------
-9999999999
--- 1 row(s) selected.
>>select cast (9999999999 as numeric(10)) from dual;
(EXPR)
--------------------
9999999999
--- 1 row(s) selected.
>>select cast (-999999999999999999 as numeric(18)) from dual;
(EXPR)
--------------------
-999999999999999999
--- 1 row(s) selected.
>>select cast (999999999999999999 as numeric(18)) from dual;
(EXPR)
--------------------
999999999999999999
--- 1 row(s) selected.
>>select cast (-9223372036854775808 as largeint) from dual;
(EXPR)
--------------------
-9223372036854775808
--- 1 row(s) selected.
>>select cast (9223372036854775807 as largeint) from dual;
(EXPR)
--------------------
9223372036854775807
--- 1 row(s) selected.
>>
>>select cast (-9 as decimal(1)) from dual;
(EXPR)
------
-9
--- 1 row(s) selected.
>>select cast (9 as decimal(1)) from dual;
(EXPR)
------
9
--- 1 row(s) selected.
>>select cast (-999999999999999999 as decimal(18)) from dual;
(EXPR)
-------------------
-999999999999999999
--- 1 row(s) selected.
>>select cast (999999999999999999 as decimal(18)) from dual;
(EXPR)
-------------------
999999999999999999
--- 1 row(s) selected.
>>
>>select cast (0 as decimal(1) unsigned) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast (9 as decimal(1) unsigned) from dual;
(EXPR)
------
9
--- 1 row(s) selected.
>>select cast (0 as decimal(9) unsigned) from dual;
(EXPR)
---------
0
--- 1 row(s) selected.
>>select cast (999999999 as decimal(9) unsigned) from dual;
(EXPR)
---------
999999999
--- 1 row(s) selected.
>>
>>select cast (10 as tinyint) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (-10 as tinyint) from dual;
(EXPR)
------
-10
--- 1 row(s) selected.
>>select cast (127 as tinyint) from dual;
(EXPR)
------
127
--- 1 row(s) selected.
>>select cast (-128 as tinyint) from dual;
(EXPR)
------
-128
--- 1 row(s) selected.
>>select cast (10 as tinyint unsigned) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (0 as tinyint unsigned) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast (255 as tinyint unsigned) from dual;
(EXPR)
------
255
--- 1 row(s) selected.
>>
>>select cast (date'12/31/9999' as timestamp(3)) from dual;
(EXPR)
-----------------------
9999-12-31 00:00:00.000
--- 1 row(s) selected.
>>select cast (timestamp'12/31/9999 11:59:59.999999 pm' as date) from dual;
(EXPR)
----------
9999-12-31
--- 1 row(s) selected.
>>select cast (timestamp'12/31/9999 11:59:59.999999 pm' as time(3)) from dual;
(EXPR)
------------
23:59:59.999
--- 1 row(s) selected.
>>
>>select interval'0.1'second + timestamp'12/31/9998 11:59:59.9 pm' from dual;
(EXPR)
---------------------
9999-01-01 00:00:00.0
--- 1 row(s) selected.
>>select timestamp'12/31/9998 11:59:59.9 pm' + interval'0.1'second from dual;
(EXPR)
---------------------
9999-01-01 00:00:00.0
--- 1 row(s) selected.
>>select timestamp'01/01/9999 00:00:00.0' + interval-'0.1'second from dual;
(EXPR)
---------------------
9998-12-31 23:59:59.9
--- 1 row(s) selected.
>>select timestamp'01/01/9999 00:00:00.0' - interval'0.1'second from dual;
(EXPR)
---------------------
9998-12-31 23:59:59.9
--- 1 row(s) selected.
>>select (date'12/31/9999' - date'12/31/9998') day(3) from dual;
(EXPR)
------
365
--- 1 row(s) selected.
>>select (date'12/31/9998' - date'12/31/9999') day(3) from dual;
(EXPR)
------
-365
--- 1 row(s) selected.
>>
>>select interval'1'month + timestamp'12/31/9998 11:59:59.999 pm' from dual;
(EXPR)
-----------------------
9999-01-31 23:59:59.999
--- 1 row(s) selected.
>>select timestamp'12/31/9998 11:59:59.999 pm' + interval'1'month from dual;
(EXPR)
-----------------------
9999-01-31 23:59:59.999
--- 1 row(s) selected.
>>select timestamp'01/31/9999 11:59:59.999 pm' + interval-'1'month from dual;
(EXPR)
-----------------------
9998-12-31 23:59:59.999
--- 1 row(s) selected.
>>select timestamp'01/31/9999 11:59:59.999 pm' - interval'1'month from dual;
(EXPR)
-----------------------
9998-12-31 23:59:59.999
--- 1 row(s) selected.
>>select (date'12/31/9999' - date'12/31/9998') month from dual;
*** ERROR[4035] Type INTERVAL DAY(18) cannot be cast to type INTERVAL MONTH(2).
*** ERROR[8822] The statement was not prepared.
>>select (date'12/31/9998' - date'12/31/9999') month from dual;
*** ERROR[4035] Type INTERVAL DAY(18) cannot be cast to type INTERVAL MONTH(2).
*** ERROR[8822] The statement was not prepared.
>>select (date'12/31/9999' - date'12/31/9998') year to month from dual;
*** ERROR[4035] Type INTERVAL DAY(18) cannot be cast to type INTERVAL YEAR(2) TO MONTH.
*** ERROR[8822] The statement was not prepared.
>>select (date'12/31/9998' - date'12/31/9999') year to month from dual;
*** ERROR[4035] Type INTERVAL DAY(18) cannot be cast to type INTERVAL YEAR(2) TO MONTH.
*** ERROR[8822] The statement was not prepared.
>>
>>select interval'1'second + time'11:59:59 pm' from dual;
(EXPR)
--------
00:00:00
--- 1 row(s) selected.
>>select time'11:59:59 pm' + interval'1'second from dual;
(EXPR)
--------
00:00:00
--- 1 row(s) selected.
>>select time'12:00:00 am' + interval-'1'second from dual;
(EXPR)
--------
23:59:59
--- 1 row(s) selected.
>>select time'12:00:00 am' - interval'1'second from dual;
(EXPR)
--------
23:59:59
--- 1 row(s) selected.
>>
>>select timestamp '2015-05-31T10:11:12' from dual;
(EXPR)
-------------------
2015-05-31 10:11:12
--- 1 row(s) selected.
>>select timestamp '2015-05-31T10:11:12Z' from dual;
(EXPR)
-------------------
2015-05-31 10:11:12
--- 1 row(s) selected.
>>select timestamp '2015-05-31T10:11:12-05:00' from dual;
(EXPR)
-------------------
2015-05-31 15:11:12
--- 1 row(s) selected.
>>select timestamp '2015-05-31T10:11:12+05:00' from dual;
(EXPR)
-------------------
2015-05-31 05:11:12
--- 1 row(s) selected.
>>select timestamp '2015-05-31T10:11:12.123-05:00' from dual;
(EXPR)
-----------------------
2015-05-31 15:11:12.123
--- 1 row(s) selected.
>>select timestamp '2015-05-31T10:11:12.123+05:00' from dual;
(EXPR)
-----------------------
2015-05-31 05:11:12.123
--- 1 row(s) selected.
>>
>>select cast('2015-05-31T10:11:12' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 10:11:12.000000
--- 1 row(s) selected.
>>select cast('2015-05-31T10:11:12Z' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 10:11:12.000000
--- 1 row(s) selected.
>>select cast('2015-05-31T10:11:12-05:00' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 15:11:12.000000
--- 1 row(s) selected.
>>select cast('2015-05-31T10:11:12+05:00' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 05:11:12.000000
--- 1 row(s) selected.
>>select cast('2015-05-31T10:11:12.123-05:00' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 15:11:12.123000
--- 1 row(s) selected.
>>select cast('2015-05-31T10:11:12.123+05:00' as timestamp) from dual;
(EXPR)
--------------------------
2015-05-31 05:11:12.123000
--- 1 row(s) selected.
>>
>>select time '10:11:12' from dual;
(EXPR)
--------
10:11:12
--- 1 row(s) selected.
>>select time '10:11:12Z' from dual;
(EXPR)
--------
10:11:12
--- 1 row(s) selected.
>>select time '10:11:12-05:00' from dual;
(EXPR)
--------
15:11:12
--- 1 row(s) selected.
>>select time '10:11:12+05:00' from dual;
(EXPR)
--------
05:11:12
--- 1 row(s) selected.
>>select time '10:11:12.123-05:00' from dual;
(EXPR)
------------
15:11:12.123
--- 1 row(s) selected.
>>select time '10:11:12.123+05:00' from dual;
(EXPR)
------------
05:11:12.123
--- 1 row(s) selected.
>>
>>select cast('10:11:12' as time) from dual;
(EXPR)
--------
10:11:12
--- 1 row(s) selected.
>>select cast('10:11:12Z' as time) from dual;
(EXPR)
--------
10:11:12
--- 1 row(s) selected.
>>select cast('10:11:12-05:00' as time) from dual;
(EXPR)
--------
15:11:12
--- 1 row(s) selected.
>>select cast('10:11:12+05:00' as time) from dual;
(EXPR)
--------
05:11:12
--- 1 row(s) selected.
>>select cast('10:11:12.123-05:00' as time) from dual;
(EXPR)
--------
15:11:12
--- 1 row(s) selected.
>>select cast('10:11:12.123+05:00' as time) from dual;
(EXPR)
--------
05:11:12
--- 1 row(s) selected.
>>
>>select time '23:11:06.123' from dual;
(EXPR)
------------
23:11:06.123
--- 1 row(s) selected.
>>select cast ('23:11:06.123' as time(2)) from dual;
(EXPR)
-----------
23:11:06.12
--- 1 row(s) selected.
>>
>>select cast('2010-01-01' as timestamp(3)) from dual;
(EXPR)
-----------------------
2010-01-01 00:00:00.000
--- 1 row(s) selected.
>>select cast('2010-01-01 10' as timestamp) from dual;
(EXPR)
--------------------------
2010-01-01 10:00:00.000000
--- 1 row(s) selected.
>>
>>create table if not exists t022dtime(a date not null, b time, c timestamp);
--- SQL operation complete.
>>prepare s from upsert into t022dtime values (?, ?, ?);
--- SQL command prepared.
>>execute s using '2010-01-01', '10:10:10', '2010-01-01 10:10:10.123';
--- 1 row(s) inserted.
>>select * from t022dtime;
A B C
---------- -------- --------------------------
2010-01-01 10:10:10 2010-01-01 10:10:10.123000
--- 1 row(s) selected.
>>
>>-- negative cases
>>select cast('2016-01-29Z' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2016-01-29Z
--- 0 row(s) selected.
>>select cast('2016-01-29+05:00' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2016-01-29+05:00
--- 0 row(s) selected.
>>select cast('10:11:12-05:000' as time) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 10:11:12-05:000
--- 0 row(s) selected.
>>select date '2016-01-29Z' from dual;
*** ERROR[3045] The date '2016-01-29Z' is not valid.
*** ERROR[8822] The statement was not prepared.
>>select date '2016-01-29+05:00' from dual;
*** ERROR[3045] The date '2016-01-29+05:00' is not valid.
*** ERROR[8822] The statement was not prepared.
>>select time '25:11:11' from dual;
*** ERROR[3046] The time '25:11:11' is not valid.
*** ERROR[8822] The statement was not prepared.
>>select time '23:11:11.1234567890' from dual;
*** ERROR[3046] The time '23:11:11.1234567890' is not valid.
*** ERROR[8822] The statement was not prepared.
>>select cast ('23:11:61' as time) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 23:11:61
--- 0 row(s) selected.
>>select cast ('23:11:06' as timestamp) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 23:11:06
--- 0 row(s) selected.
>>select cast('2010-01-01' as time) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2010-01-01
--- 0 row(s) selected.
>>select timestamp'2010-01-01 10' from dual;
*** ERROR[3047] The timestamp '2010-01-01 10' is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>-- Prove these cases work since they're used in the succeeding negative tests.
>>
>>select cast (cast (-10 as smallint) as numeric(2)) from dual;
(EXPR)
------
-10
--- 1 row(s) selected.
>>select cast (cast (10 as smallint) as numeric(2)) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (cast (-10 as numeric(2)) as smallint) from dual;
(EXPR)
------
-10
--- 1 row(s) selected.
>>select cast (cast (10 as numeric(2)) as smallint) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (-1 as smallint) from dual;
(EXPR)
------
-1
--- 1 row(s) selected.
>>
>>select cast (32768 as smallint unsigned) from dual;
(EXPR)
------
32768
--- 1 row(s) selected.
>>select cast (10 as smallint unsigned) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>
>>select cast (-32769 as int) from dual;
(EXPR)
-----------
-32769
--- 1 row(s) selected.
>>select cast (32768 as int) from dual;
(EXPR)
-----------
32768
--- 1 row(s) selected.
>>select cast (-10 as int) from dual;
(EXPR)
-----------
-10
--- 1 row(s) selected.
>>select cast (10 as int) from dual;
(EXPR)
-----------
10
--- 1 row(s) selected.
>>select cast (-1 as int) from dual;
(EXPR)
-----------
-1
--- 1 row(s) selected.
>>select cast (65536 as int) from dual;
(EXPR)
-----------
65536
--- 1 row(s) selected.
>>select cast (-100000 as int) from dual;
(EXPR)
-----------
-100000
--- 1 row(s) selected.
>>select cast (100000 as int) from dual;
(EXPR)
-----------
100000
--- 1 row(s) selected.
>>
>>select cast (32768 as int unsigned) from dual;
(EXPR)
----------
32768
--- 1 row(s) selected.
>>select cast (10 as int unsigned) from dual;
(EXPR)
----------
10
--- 1 row(s) selected.
>>select cast (65536 as int unsigned) from dual;
(EXPR)
----------
65536
--- 1 row(s) selected.
>>select cast (2147483648 as int unsigned) from dual;
(EXPR)
----------
2147483648
--- 1 row(s) selected.
>>select cast (100000 as int unsigned) from dual;
(EXPR)
----------
100000
--- 1 row(s) selected.
>>
>>select cast (-32769 as largeint) from dual;
(EXPR)
--------------------
-32769
--- 1 row(s) selected.
>>select cast (32768 as largeint) from dual;
(EXPR)
--------------------
32768
--- 1 row(s) selected.
>>select cast (-10 as largeint) from dual;
(EXPR)
--------------------
-10
--- 1 row(s) selected.
>>select cast (10 as largeint) from dual;
(EXPR)
--------------------
10
--- 1 row(s) selected.
>>select cast (-1 as largeint) from dual;
(EXPR)
--------------------
-1
--- 1 row(s) selected.
>>select cast (65536 as largeint) from dual;
(EXPR)
--------------------
65536
--- 1 row(s) selected.
>>select cast (-2147483649 as largeint) from dual;
(EXPR)
--------------------
-2147483649
--- 1 row(s) selected.
>>select cast (2147483648 as largeint) from dual;
(EXPR)
--------------------
2147483648
--- 1 row(s) selected.
>>select cast (-100000 as largeint) from dual;
(EXPR)
--------------------
-100000
--- 1 row(s) selected.
>>select cast (100000 as largeint) from dual;
(EXPR)
--------------------
100000
--- 1 row(s) selected.
>>select cast (4294967296 as largeint) from dual;
(EXPR)
--------------------
4294967296
--- 1 row(s) selected.
>>select cast (-10000000000 as largeint) from dual;
(EXPR)
--------------------
-10000000000
--- 1 row(s) selected.
>>select cast (10000000000 as largeint) from dual;
(EXPR)
--------------------
10000000000
--- 1 row(s) selected.
>>
>>select cast (-32769 as decimal(5)) from dual;
(EXPR)
------
-32769
--- 1 row(s) selected.
>>select cast (32768 as decimal(5)) from dual;
(EXPR)
------
32768
--- 1 row(s) selected.
>>select cast (-10 as decimal(2)) from dual;
(EXPR)
------
-10
--- 1 row(s) selected.
>>select cast (10 as decimal(2)) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (-1 as decimal(1)) from dual;
(EXPR)
------
-1
--- 1 row(s) selected.
>>select cast (65536 as decimal(5)) from dual;
(EXPR)
------
65536
--- 1 row(s) selected.
>>select cast (-2147483649 as decimal(10)) from dual;
(EXPR)
-----------
-2147483649
--- 1 row(s) selected.
>>select cast (2147483648 as decimal(10)) from dual;
(EXPR)
-----------
2147483648
--- 1 row(s) selected.
>>select cast (-100000 as decimal(6)) from dual;
(EXPR)
-------
-100000
--- 1 row(s) selected.
>>select cast (100000 as decimal(6)) from dual;
(EXPR)
-------
100000
--- 1 row(s) selected.
>>select cast (4294967296 as decimal(10)) from dual;
(EXPR)
-----------
4294967296
--- 1 row(s) selected.
>>select cast (-10000000000 as decimal(11)) from dual;
(EXPR)
------------
-10000000000
--- 1 row(s) selected.
>>select cast (10000000000 as decimal(11)) from dual;
(EXPR)
------------
10000000000
--- 1 row(s) selected.
>>
>>select cast (32768 as decimal(5) unsigned) from dual;
(EXPR)
------
32768
--- 1 row(s) selected.
>>select cast (10 as decimal(2) unsigned) from dual;
(EXPR)
------
10
--- 1 row(s) selected.
>>select cast (65536 as decimal(5) unsigned) from dual;
(EXPR)
------
65536
--- 1 row(s) selected.
>>select cast (100000 as decimal(6) unsigned) from dual;
(EXPR)
------
100000
--- 1 row(s) selected.
>>
>>select cast (-32769 as real) from dual;
(EXPR)
---------------
-3.2769000E+004
--- 1 row(s) selected.
>>select cast (32768 as real) from dual;
(EXPR)
---------------
3.2768000E+004
--- 1 row(s) selected.
>>select cast (-10 as real) from dual;
(EXPR)
---------------
-1.0000000E+001
--- 1 row(s) selected.
>>select cast (10 as real) from dual;
(EXPR)
---------------
1.0000000E+001
--- 1 row(s) selected.
>>select cast (-1 as real) from dual;
(EXPR)
---------------
-1.0000000E+000
--- 1 row(s) selected.
>>select cast (65536 as real) from dual;
(EXPR)
---------------
6.5536000E+004
--- 1 row(s) selected.
>>select cast (-2147483649 as real) from dual;
(EXPR)
---------------
-2.1474836E+009
--- 1 row(s) selected.
>>select cast (9999999999 as real) from dual;
(EXPR)
---------------
1.0000000E+010
--- 1 row(s) selected.
>>select cast (-100000 as real) from dual;
(EXPR)
---------------
-1.0000000E+005
--- 1 row(s) selected.
>>select cast (100000 as real) from dual;
(EXPR)
---------------
1.0000000E+005
--- 1 row(s) selected.
>>select cast (9999999999 as real) from dual;
(EXPR)
---------------
1.0000000E+010
--- 1 row(s) selected.
>>
>>select cast (-32769 as double precision) from dual;
(EXPR)
-------------------------
-3.27690000000000064E+004
--- 1 row(s) selected.
>>select cast (32768 as double precision) from dual;
(EXPR)
-------------------------
3.27680000000000000E+004
--- 1 row(s) selected.
>>select cast (-10 as double precision) from dual;
(EXPR)
-------------------------
-1.00000000000000000E+001
--- 1 row(s) selected.
>>select cast (10 as double precision) from dual;
(EXPR)
-------------------------
1.00000000000000000E+001
--- 1 row(s) selected.
>>select cast (-1 as double precision) from dual;
(EXPR)
-------------------------
-1.00000000000000000E+000
--- 1 row(s) selected.
>>select cast (65536 as double precision) from dual;
(EXPR)
-------------------------
6.55360000000000000E+004
--- 1 row(s) selected.
>>select cast (-2147483649 as double precision) from dual;
(EXPR)
-------------------------
-2.14748364900000032E+009
--- 1 row(s) selected.
>>select cast (2147483648 as double precision) from dual;
(EXPR)
-------------------------
2.14748364800000000E+009
--- 1 row(s) selected.
>>select cast (-100000 as double precision) from dual;
(EXPR)
-------------------------
-1.00000000000000000E+005
--- 1 row(s) selected.
>>select cast (100000 as double precision) from dual;
(EXPR)
-------------------------
1.00000000000000000E+005
--- 1 row(s) selected.
>>select cast (4294967296 as double precision) from dual;
(EXPR)
-------------------------
4.29496729600000000E+009
--- 1 row(s) selected.
>>
>>select cast ('Hello, world!' as char(13)) from dual;
(EXPR)
-------------
Hello, world!
--- 1 row(s) selected.
>>select cast ('Hello, world!' as varchar(13)) from dual;
(EXPR)
-------------
Hello, world!
--- 1 row(s) selected.
>>select cast (cast ('Hello ' as char(13)) as char(5)) from dual;
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello ' as char(13)) as varchar(5)) from dual;
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello, world!' as char(13)) as char(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello, world!' as char(13)) as varchar(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello ' as varchar(13)) as char(5)) from dual;
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello ' as varchar(13)) as varchar(5)) from dual;
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello, world!' as varchar(13)) as char(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast ('Hello, world!' as varchar(13)) as varchar(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
------
Hello
--- 1 row(s) selected.
>>select cast (cast (-2147483649 as double precision) as largeint) from dual;
(EXPR)
--------------------
-2147483649
--- 1 row(s) selected.
>>select cast (cast (2147483648 as double precision) as largeint) from dual;
(EXPR)
--------------------
2147483648
--- 1 row(s) selected.
>>
>>-- Positive cases to test division.
>>
>>select cast (1 as numeric(1)) / cast (1 as numeric(1)) from dual;
(EXPR)
-------
1.0
--- 1 row(s) selected.
>>select cast (1 as numeric(5)) / cast (1 as numeric(1)) from dual;
(EXPR)
------------
1.0
--- 1 row(s) selected.
>>select cast (1 as numeric(1)) / cast (1 as numeric(5)) from dual;
(EXPR)
------------
1.00000
--- 1 row(s) selected.
>>select cast (1 as numeric(5)) / cast (1 as numeric(5)) from dual;
(EXPR)
---------------------
1.00000
--- 1 row(s) selected.
>>select cast (1 as numeric(10)) / cast (1 as numeric(10)) from dual;
(EXPR)
---------------------
1.00000000
--- 1 row(s) selected.
>>
>>select cast (1 as numeric(1) unsigned) /
+> cast (1 as numeric(1) unsigned) from dual;
(EXPR)
------
1.0
--- 1 row(s) selected.
>>select cast (1 as numeric(5) unsigned) /
+> cast (1 as numeric(1) unsigned) from dual;
(EXPR)
-----------
1.0
--- 1 row(s) selected.
>>select cast (1 as numeric(1) unsigned) /
+> cast (1 as numeric(5) unsigned) from dual;
(EXPR)
-----------
1.00000
--- 1 row(s) selected.
>>select cast (1 as numeric(5) unsigned) /
+> cast (1 as numeric(5) unsigned) from dual;
(EXPR)
---------------------
1.00000
--- 1 row(s) selected.
>>select cast (1 as numeric(9) unsigned) /
+> cast (1 as numeric(9) unsigned) from dual;
(EXPR)
---------------------
1.000000000
--- 1 row(s) selected.
>>
>>select cast (1 as double precision) / cast (1 as double precision) from dual;
(EXPR)
-------------------------
1.00000000000000000E+000
--- 1 row(s) selected.
>>
>>-- Negative tests.
>>
>>select cast (cast (-10 as smallint) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-10 Target Type:LARGEINT(IBIN64S) Min Target Value:-9. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-10 as numeric(2)) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:TINYINT SIGNED(MBIN8S) Source Value:-10 Target Type:LARGEINT(IBIN64S) Min Target Value:-9. Instruction:RANGE_LOW_S8S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (10 as numeric(2)) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:TINYINT SIGNED(MBIN8S) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_S8S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-1 as smallint) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-1 as smallint) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (-10 as smallint) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (10 as smallint) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as smallint) as decimal(1) unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (32768 as smallint unsigned) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT UNSIGNED(MBIN16U) Source Value:32768 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_U16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint unsigned) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT UNSIGNED(MBIN16U) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_U16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint unsigned) as numeric(1)unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT UNSIGNED(MBIN16U) Source Value:10 Target Type:LARGEINT(IBIN64S) Max Target Value:9. Instruction:RANGE_HIGH_U16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as smallint unsigned) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (10 as smallint unsigned) as decimal(1)unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (-32769 as int) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:-32769 Target Type:LARGEINT(IBIN64S) Min Target Value:-32768. Instruction:RANGE_LOW_S32S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (32768 as int) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:32768 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-10 as int) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTEGER SIGNED(REC_BIN32_SIGNED) Source Value:-10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as int) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTEGER SIGNED(REC_BIN32_SIGNED) Source Value:10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as int) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:INTEGER SIGNED(MBIN32S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S32S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (65536 as int) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:65536 Target Type:LARGEINT(IBIN64S) Max Target Value:65535. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as int) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTEGER SIGNED(REC_BIN32_SIGNED) Source Value:10 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-100000 as int) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:-100000 Target Type:LARGEINT(IBIN64S) Min Target Value:-99999. Instruction:RANGE_LOW_S32S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (100000 as int) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-1 as int) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:INTEGER SIGNED(MBIN32S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S32S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (100000 as int) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-10 as int) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (10 as int) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as int) as decimal(1) unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (10 as int) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (32768 as int unsigned) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER UNSIGNED(MBIN32U) Source Value:32768 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_U32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as int unsigned) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED) Source Value:10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (65536 as int unsigned) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER UNSIGNED(MBIN32U) Source Value:65536 Target Type:LARGEINT(IBIN64S) Max Target Value:65535. Instruction:RANGE_HIGH_U32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as int unsigned) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED) Source Value:10 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (2147483648 as int unsigned) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER UNSIGNED(MBIN32U) Source Value:2147483648 Target Type:LARGEINT(IBIN64S) Max Target Value:2147483647. Instruction:RANGE_HIGH_U32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (100000 as int unsigned) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER UNSIGNED(MBIN32U) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_U32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (100000 as int unsigned) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER UNSIGNED(MBIN32U) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_U32S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as int unsigned) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (10 as int unsigned) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (-32769 as largeint) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:-32769 Target Type:LARGEINT(IBIN64S) Min Target Value:-32768. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (32768 as largeint) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:32768 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-10 as largeint) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as largeint) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as largeint) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (65536 as largeint) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:65536 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as largeint) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-2147483649 as largeint) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:-2147483649 Target Type:LARGEINT(IBIN64S) Min Target Value:-2147483648. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (2147483648 as largeint) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:2147483648 Target Type:LARGEINT(IBIN64S) Max Target Value:2147483647. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-100000 as largeint) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:-100000 Target Type:LARGEINT(IBIN64S) Min Target Value:-99999. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (100000 as largeint) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-1 as largeint) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:LARGEINT(MBIN64S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (4294967296 as largeint) as int unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:4294967296 Target Type:LARGEINT(IBIN64S) Max Target Value:4294967295. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (100000 as largeint) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:100000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (-10000000000 as largeint) as numeric(10)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:-10000000000 Target Type:LARGEINT(IBIN64S) Min Target Value:-9999999999. Instruction:RANGE_LOW_S64S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast (cast (10000000000 as largeint) as numeric(10)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:10000000000 Target Type:LARGEINT(IBIN64S) Max Target Value:9999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast (cast (10 as largeint) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as largeint) as decimal(1) unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (10 as largeint) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (-32769 as decimal(5)) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-32769 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (32768 as decimal(5)) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:32768 to Target Type:SMALLINT(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-10 as decimal(2)) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2)) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as decimal(1)) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (65536 as decimal(5)) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:65536 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2)) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-2147483649 as decimal(10)) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-2147483649 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (2147483648 as decimal(10)) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:2147483648 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-100000 as decimal(6)) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-100000 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as decimal(6)) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as decimal(1)) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (4294967296 as decimal(10)) as int unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:4294967296 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as decimal(6)) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-10000000000 as decimal(11)) as numeric(10)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10000000000 as decimal(11)) as numeric(10)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2)) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as decimal(1)) as decimal(1) unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2)) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (32768 as decimal(5) unsigned) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:32768 to Target Type:SMALLINT(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2) unsigned) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (65536 as decimal(5) unsigned) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:65536 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2) unsigned) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as decimal(6) unsigned) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as decimal(6) unsigned) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2) unsigned) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (10 as decimal(2) unsigned) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:10 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (-32769 as real) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:-3.2769000E+004 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (32768 as real) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:3.2768000E+004 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-10 as real) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:0.0000000E+000 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as real) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.4012984E-044 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as real) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (65536 as real) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:6.5536000E+004 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as real) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.4012984E-044 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-2147484160 as real) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:-2.1474841E+009 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (9999999999 as real) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.0000000E+010 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-100000 as real) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:0.0000000E+000 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as real) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.4012984E-040 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as real) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (9999999999 as real) as int unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.0000000E+010 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as real) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.4012984E-040 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as real) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.0000000E+001 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as real) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:-1.0000000E+000 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as real) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE FLOAT(REC_IEEE_FLOAT32) Source Value:1.0000000E+001 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>select cast (cast (-32769 as double precision) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:-3.27690000000000064E+004 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (32768 as double precision) as smallint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:3.27680000000000000E+004 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-10 as double precision) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:0.00000000000000000E+000 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as double precision) as numeric(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:4.94065645841246592E-323 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as double precision) as smallint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (65536 as double precision) as smallint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:6.55360000000000000E+004 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as double precision) as numeric(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:4.94065645841246592E-323 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (-2147483649 as double precision) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:-2.14748364900000032E+009 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (2147483648 as double precision) as int) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:2.14748364800000000E+009 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-100000 as double precision) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:0.00000000000000000E+000 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as double precision) as numeric(5)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:4.94065645841246528E-319 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast (cast (-1 as double precision) as int unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype.
--- 0 row(s) selected.
>>select cast (cast (4294967296 as double precision) as int unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:4.29496729600000000E+009 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (100000 as double precision) as numeric(5) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:4.94065645841246528E-319 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as double precision) as decimal(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.00000000000000000E+001 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast (cast (-1 as double precision) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:-1.00000000000000000E+000 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>select cast (cast (10 as double precision) as decimal(1) unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:IEEE DOUBLE PRECISION(REC_IEEE_FLOAT64) Source Value:1.00000000000000000E+001 to Target Type:DECIMAL UNSIGNED(REC_DECIMAL_UNSIGNED).
--- 0 row(s) selected.
>>
>>-- negative tests for tinyint
>>select cast(-129 as tinyint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-129 Target Type:LARGEINT(IBIN64S) Min Target Value:-128. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>select cast(128 as tinyint) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:128 Target Type:LARGEINT(IBIN64S) Max Target Value:127. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast(256 as tinyint unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:256 Target Type:LARGEINT(IBIN64S) Max Target Value:255. Instruction:RANGE_HIGH_S16S64 Operation:RANGE_HIGH.
--- 0 row(s) selected.
>>select cast(-1 as tinyint unsigned) from dual;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-1 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW.
--- 0 row(s) selected.
>>
>>-- tests for DATE, TIME, TIMESTAMP
>>select cast ('12.07.1961 03.04.55.123456' as timestamp) from dual;
(EXPR)
--------------------------
1961-07-12 03:04:55.123456
--- 1 row(s) selected.
>>select cast ('12.07.1961 03.04.55.123456' as timestamp(3)) from dual;
(EXPR)
-----------------------
1961-07-12 03:04:55.123
--- 1 row(s) selected.
>>select cast (cast ('12.07.1961 03.04.55.123456' as timestamp) as DATE) from dual;
(EXPR)
----------
1961-07-12
--- 1 row(s) selected.
>>select cast (cast ('12.07.1961 03.04.55.123456' as timestamp) as char(21)) from dual;
(EXPR)
---------------------
1961-07-12 03:04:55.1
--- 1 row(s) selected.
>>select cast ('12/13/1987' as date) from dual;
(EXPR)
----------
1987-12-13
--- 1 row(s) selected.
>>
>>-- some negative DATE, TIME, TIMESTAMP
>>-- illegal european format
>>select cast ('12.13.1987' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 12.13.1987
--- 0 row(s) selected.
>>
>>-- illegal US format
>>select cast ('13/12/1987' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 13/12/1987
--- 0 row(s) selected.
>>
>>-- string overflow
>>select cast (cast ('12.07.1961 03:04:55.123456' as timestamp) as char(18)) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 12.07.1961 03:04:55.123456
--- 0 row(s) selected.
>>
>>-- Feb. 30th doesn't exist
>>select cast('1997-30-02' as DATE) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1997-30-02
--- 0 row(s) selected.
>>
>>select interval'0.1'second + timestamp'12/31/9999 11:59:59.9 pm' from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select timestamp'12/31/9999 11:59:59.9 pm' + interval'0.1'second from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select timestamp'01/01/0001 00:00:00.0' + interval-'0.1'second from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select timestamp'01/01/0001 00:00:00.0' - interval'0.1'second from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>
>>select interval'1'month + date'12/31/9999' from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'12/31/9999' + interval'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'01/01/0001' + interval-'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'01/01/0001' - interval'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>
>>select interval'1'month + date'01/31/9999' from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'01/31/9999' + interval'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'03/30/9999' + interval-'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>select date'03/30/9999' - interval'1'month from dual;
*** ERROR[8416] A datetime expression evaluated to an invalid datetime value.
--- 0 row(s) selected.
>>
>>-- Negative cases to test division by zero.
>>
>>select cast (1 as numeric(1)) / cast (0 as numeric(1)) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(5)) / cast (0 as numeric(1)) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(1)) / cast (0 as numeric(5)) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(5)) / cast (0 as numeric(5)) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:100000 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>select cast (1 as numeric(10)) / cast (0 as numeric(10)) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>
>>select cast (1 as numeric(1) unsigned) /
+> cast (0 as numeric(1) unsigned) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(5) unsigned) /
+> cast (0 as numeric(1) unsigned) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(1) unsigned) /
+> cast (0 as numeric(5) unsigned) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero.
--- 0 row(s) selected.
>>select cast (1 as numeric(5) unsigned) /
+> cast (0 as numeric(5) unsigned) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:100000 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>select cast (1 as numeric(9) unsigned) /
+> cast (0 as numeric(9) unsigned) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:1000000000 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:0. Instruction:DIV_MBIN64S_MBIN64S_MBIN64S Operation:DIV.
--- 0 row(s) selected.
>>
>>select cast (1 as double precision) / cast (0 as double precision) from dual;
*** ERROR[8419] An arithmetic expression attempted a division by zero. Operand1 Type:IEEE DOUBLE PRECISION(MFLT64) Operand1 Value:1.00000000000000000E+000 Operand2 Type:IEEE DOUBLE PRECISION(MFLT64) Operand2 Value:0.00000000000000000E+000. Instruction:DIV_MFLT64_MFLT64_MFLT64 Operation:DIV.
--- 0 row(s) selected.
>>
>>-- results: 1 row selected
>>select cast('66.2' as float) from dual where cast('66.2' as float) = 66.2;
(EXPR)
-------------------------
6.62000000000000128E+001
--- 1 row(s) selected.
>>
>>-- to test comparisons between signed and unsigned numbers.
>>
>>select * from t022t3 where 2147483647 < a;
A
----------
4294967295
3000000000
--- 2 row(s) selected.
>>select * from t022t3 where 2147483647 <= a;
A
----------
4294967295
3000000000
--- 2 row(s) selected.
>>select * from t022t3 where a < 2147483647;
--- 0 row(s) selected.
>>select * from t022t3 where a <= 2147483647;
--- 0 row(s) selected.
>>select * from t022t3 where a < 0;
--- 0 row(s) selected.
>>select * from t022t3 where a <= 0;
--- 0 row(s) selected.
>>select * from t022t3 where -1 > a;
--- 0 row(s) selected.
>>select * from t022t3 where a > 0;
A
----------
4294967295
3000000000
--- 2 row(s) selected.
>>select * from t022t3 where a >= 2147483647;
A
----------
4294967295
3000000000
--- 2 row(s) selected.
>>select * from t022t3 where 2147483647 >= a;
--- 0 row(s) selected.
>>select * from t022t3 where a >= 32767;
A
----------
4294967295
3000000000
--- 2 row(s) selected.
>>
>>select * from t022t4 where b < 3000000000;
A B
----- ------
65535 32767
--- 1 row(s) selected.
>>select * from t022t4 where b <= 3000000000;
A B
----- ------
65535 32767
--- 1 row(s) selected.
>>select * from t022t4 where b > 3000000000;
--- 0 row(s) selected.
>>select * from t022t4 where b >= 3000000000;
--- 0 row(s) selected.
>>
>>
>>-- negative test cases
>>create table t022t6 (t022t6_c1 decimal( 9) unsigned,
+> t022t6_c2 decimal(10) unsigned,
+> t022t7_c1 dec ( 9) unsigned,
+> t022t7_c2 dec (10) unsigned);
*** ERROR[3008] Precision of DECIMAL UNSIGNED data type, 10, cannot exceed 9.
*** ERROR[8822] The statement was not prepared.
>>
>>select cast(1 as numeric(10) unsigned) from (values(0))x;
(EXPR)
--------------------
1
--- 1 row(s) selected.
>>select cast(1 as decimal(10) unsigned) from (values(0))x;
*** ERROR[3008] Precision of DECIMAL UNSIGNED data type, 10, cannot exceed 9.
*** ERROR[8822] The statement was not prepared.
>>select cast(1 as dec (10) unsigned) from (values(0))x;
*** ERROR[3008] Precision of DECIMAL UNSIGNED data type, 10, cannot exceed 9.
*** ERROR[8822] The statement was not prepared.
>>
>>
>>-- time/timestamp AM/PM tests (test convDoIt with timestamps)
>>create table t022t8 (c1 timestamp, c2 time);
--- SQL operation complete.
>>
>>set param ?p '12/05/1998 03:15:08.123456 pm';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '2015-05-31T14:15:16Z';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '2015-05-31T14:15:16';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '2100-01-01:00:00:47.250000';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '12/05/1998 10:15:08.889 pm';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '06/28/1998 03:15:08 pm';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '02/10/1998 12:15:08.123456 am';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '12/05/1998 03:15:08.123456 am';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '12/05/1998 03:15:08.123456';
>>insert into t022t8 (c1) values ( ?p );
--- 1 row(s) inserted.
>>
>>-- expect error since 15 hours and am don't match
>>set param ?p '11/20/1997 15:15:08.123456 am';
>>insert into t022t8 (c1) values ( ?p );
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 11/20/1997 15:15:08.123456 am
*** ERROR[15015] PARAM ?p (value 11/20/1997 15:15:08.123456 am) cannot be converted to type TIMESTAMP(6).
--- 0 row(s) inserted.
>>
>>-- expect an error
>>set param ?p '2100-01-01:ab:00:47.250000';
>>insert into t022t8 (c1) values ( ?p );
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61623a30303a34372e323530303030
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2100-01-01:ab:00:47.250000
*** ERROR[15015] PARAM ?p (value 2100-01-01:ab:00:47.250000) cannot be converted to type TIMESTAMP(6).
--- 0 row(s) inserted.
>>
>>-- expect error since the date is not in US format
>>set param ?p '1998-03-12 03:15:08.123456 pm';
>>insert into t022t8 (c1) values ( ?p );
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1998-03-12 03:15:08.123456 pm
*** ERROR[15015] PARAM ?p (value 1998-03-12 03:15:08.123456 pm) cannot be converted to type TIMESTAMP(6).
--- 0 row(s) inserted.
>>
>>-- expect error since the date is not in US format
>>set param ?p '1996-05-23 16:15:08.123 am';
>>insert into t022t8 (c1) values ( ?p );
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1996-05-23 16:15:08.123 am
*** ERROR[15015] PARAM ?p (value 1996-05-23 16:15:08.123 am) cannot be converted to type TIMESTAMP(6).
--- 0 row(s) inserted.
>>
>>select c1 from t022t8;
C1
--------------------------
1998-12-05 15:15:08.123456
2015-05-31 14:15:16.000000
2015-05-31 14:15:16.000000
2100-01-01 00:00:47.250000
1998-12-05 22:15:08.889000
1998-06-28 15:15:08.000000
1998-02-10 00:15:08.123456
1998-12-05 03:15:08.123456
1998-12-05 03:15:08.123456
--- 9 row(s) selected.
>>
>>-- test convDoIt with time
>>set param ?p '03:15:08 pm';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '11:15:08 pm';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '10:15:08 am';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '12:59:59 am';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '10:05:59 am';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '09:00:00 am';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '11:00:00 am';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '08:00:00';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>set param ?p '00:01:00';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>-- expect error
>>set param ?p '00:a1:00';
>>insert into t022t8 (c2) values ( ?p );
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61313a3030
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 00:a1:00
*** ERROR[15015] PARAM ?p (value 00:a1:00) cannot be converted to type TIME(0).
--- 0 row(s) inserted.
>>
>>-- expect error since 14 and am don't match
>>set param ?p '14:59:59 am';
>>insert into t022t8 (c2) values ( ?p );
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 14:59:59 am
*** ERROR[15015] PARAM ?p (value 14:59:59 am) cannot be converted to type TIME(0).
--- 0 row(s) inserted.
>>
>>-- expect error
>>set param ?p '11:60:59 am';
>>insert into t022t8 (c2) values ( ?p );
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 11:60:59 am
*** ERROR[15015] PARAM ?p (value 11:60:59 am) cannot be converted to type TIME(0).
--- 0 row(s) inserted.
>>
>>-- expect error
>>set param ?p '11:03:33';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>-- expect an error
>>set param ?p '00:01:00.03';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>-- expect an error
>>set param ?p '10:36:52.0781 pm';
>>insert into t022t8 (c2) values ( ?p );
--- 1 row(s) inserted.
>>
>>select c2 from t022t8;
C2
--------
?
?
?
?
?
?
?
?
?
15:15:08
23:15:08
10:15:08
00:59:59
10:05:59
09:00:00
11:00:00
08:00:00
00:01:00
11:03:33
00:01:00
22:36:52
--- 21 row(s) selected.
>>
>>-- test type casting involving the ordering of digits and other symbols
>>-- like - . +
>>
>>-- positive cases
>>select cast('-.3' as int), cast ('+1.4' as int) , cast('-32.3' as int)
+> from t022t8;
(EXPR) (EXPR) (EXPR)
----------- ----------- -----------
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
0 1 -32
--- 21 row(s) selected.
>>select c1 from t022t8 where cast('-.3' as int) < 1 and cast('+1.5' as int) <2;
C1
--------------------------
1998-12-05 15:15:08.123456
2015-05-31 14:15:16.000000
2015-05-31 14:15:16.000000
2100-01-01 00:00:47.250000
1998-12-05 22:15:08.889000
1998-06-28 15:15:08.000000
1998-02-10 00:15:08.123456
1998-12-05 03:15:08.123456
1998-12-05 03:15:08.123456
?
?
?
?
?
?
?
?
?
?
?
?
--- 21 row(s) selected.
>>
>>select cast ('123.' as real) ,
+> cast ('123.12' as real),
+> cast ('123.E2' as real),
+> cast ('123.12E2' as real)from (values(1)) as T;
(EXPR) (EXPR) (EXPR) (EXPR)
--------------- --------------- --------------- ---------------
1.2300000E+002 1.2312000E+002 1.2300000E+004 1.2312000E+004
--- 1 row(s) selected.
>>
>>obey t022xxx;
>>begin work;
--- SQL operation complete.
>>execute insertIntoT022A7 using 0 , 1. ;
--- 1 row(s) inserted.
>>execute insertIntoT022A7 using 1 , 2.E3 ;
--- 1 row(s) inserted.
>>execute insertIntoT022A7 using 3 , 123.456E2 ;
--- 1 row(s) inserted.
>>commit work;
--- SQL operation complete.
>>select * from $$TEST_SCHEMA$$.t022a7;
C1 C2
------------------------- -------------------------
0.00000000000000000E+000 1.00000000000000000E+000
1.00000000000000000E+000 2.00000000000000000E+003
3.00000000000000000E+000 1.23456000000000016E+004
--- 3 row(s) selected.
>>
>>-- negative cases (expect errors)
>>select cast('.-2' as int) from t022t8;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2d32
--- 0 row(s) selected.
>>select cast('.+3' as int) from t022t8;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2b33
--- 0 row(s) selected.
>>select cast('.4-' as numeric(4)) from t022t8;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e342d
--- 0 row(s) selected.
>>select cast('-. 3' as int) from t022t8;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2033
--- 0 row(s) selected.
>>select cast('-3 . ' as int) from t022t8;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 33202e20
--- 0 row(s) selected.
>>
>>select c1 from t022t8 where cast('.-2' as int) < 1000;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2d32
--- 0 row(s) selected.
>>select c1 from t022t8 where cast('.+3' as int) < 1000;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2b33
--- 0 row(s) selected.
>>select c1 from t022t8 where cast('.4-' as numeric(4)) < 1;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e342d
--- 0 row(s) selected.
>>select c1 from t022t8 where cast('-. 3' as int) < 1;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 2e2033
--- 0 row(s) selected.
>>select c1 from t022t8 where cast('-3 .' as int) < 1;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 33202e
--- 0 row(s) selected.
>>
>>select cast ('123..' as real) from (values(1)) as T;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 3132332e2e
--- 0 row(s) selected.
>>select cast ('123..12' as real)from (values(1)) as T;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 3132332e2e3132
--- 0 row(s) selected.
>>
>>-- Testing solution 10-030707-7648
>>-- Catch overflow while manipulating interval internal type (decimal)
>>create table t022t9 (h10s4 interval hour(10) to second(4));
--- SQL operation complete.
>>insert into t022t9 values (-interval '1111111111:12:00.1234'
+> hour(10) to second(4));
--- 1 row(s) inserted.
>>insert into t022t9 values (interval '1111111111:12:00.1234'
+> hour(10) to second(4));
--- 1 row(s) inserted.
>>
>>-- Should not overflow.
>>update t022t9 set h10s4 = h10s4 * 1;
--- 2 row(s) updated.
>>select * from t022t9;
H10S4
----------------------
-1111111111:12:00.1234
1111111111:12:00.1234
--- 2 row(s) selected.
>>
>>update t022t9 set h10s4 = h10s4 * 1.0;
--- 2 row(s) updated.
>>
>>select * from t022t9;
H10S4
----------------------
-1111111111:12:00.1234
1111111111:12:00.1234
--- 2 row(s) selected.
>>
>>-- Should overflow.
>>update t022t9 set h10s4 = h10s4 * 10;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND) Source Value:0xCC87EF871EEA72FA to Target Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND).
--- 0 row(s) updated.
>>
>>select * from t022t9;
H10S4
----------------------
-1111111111:12:00.1234
1111111111:12:00.1234
--- 2 row(s) selected.
>>
>>-- End of testing solution 10-030707-7648
>>
>>-- Numeric to interval converion overflow checking
>>create table t022ta(a interval second not null primary key);
--- SQL operation complete.
>>insert into t022ta values (interval '12.200' second(2,3));
--- 1 row(s) inserted.
>>insert into t022ta values (interval '12.300' second(2,4));
--- 1 row(s) inserted.
>>
>>select cast (10 + 90 as interval hour(3)) from t022ta;
(EXPR)
------
100
100
--- 2 row(s) selected.
>>select cast (10 + 89 as interval hour) from t022ta;
(EXPR)
------
99
99
--- 2 row(s) selected.
>>select cast (10 + 89 as interval second) from t022ta;
(EXPR)
----------
99.000000
99.000000
--- 2 row(s) selected.
>>-- should overflow
>>select cast (10 + 90 as interval second) from t022ta;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000000 to Target Type:INTERVAL SECOND(REC_INT_SECOND).
--- 0 row(s) selected.
>>-- should overflow
>>select cast (100 as interval second) from t022ta;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000000 to Target Type:INTERVAL SECOND(REC_INT_SECOND).
--- 0 row(s) selected.
>>select cast (99 as interval second) from t022ta where a < interval '12.3' second;
(EXPR)
----------
99.000000
--- 1 row(s) selected.
>>
>>create table t022tb(a integer);
--- SQL operation complete.
>>insert into t022tb values (10);
--- 1 row(s) inserted.
>>
>>select cast (a + 89 as interval second) from t022tb;
(EXPR)
----------
99.000000
--- 1 row(s) selected.
>>-- should overflow
>>select cast (a + 100.0 as interval second) from t022tb;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:11000000.0 to Target Type:INTERVAL SECOND(REC_INT_SECOND).
--- 0 row(s) selected.
>>select cast (a + 100.00 as interval second(3)) from t022tb;
(EXPR)
-----------
110.000000
--- 1 row(s) selected.
>>select cast (a + 100.00 as interval second(3,1)) from t022tb;
(EXPR)
------
110.0
--- 1 row(s) selected.
>>select cast (a + 100.00 as interval second(3,2)) from t022tb;
(EXPR)
-------
110.00
--- 1 row(s) selected.
>>select cast (a + 100.00 as interval second(3,3)) from t022tb;
(EXPR)
--------
110.000
--- 1 row(s) selected.
>>select time '11:58:59.236' + interval '1.12' second from t022tb;
(EXPR)
------------
11:59:00.356
--- 1 row(s) selected.
>>
>>select a*1 from (values (interval '10.0' second)) x(a);
(EXPR)
-----------
10.000000
--- 1 row(s) selected.
>>select cast (a as numeric (2)) from (values(interval '12.00' second)) t(a);
(EXPR)
------
12
--- 1 row(s) selected.
>>select cast (a as numeric (18)) from (values(interval '12.00' second)) t(a);
(EXPR)
--------------------
12
--- 1 row(s) selected.
>>-- should overflow
>>select cast (a as numeric (1)) from (values(interval '12.00' second)) t(a);
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL SECOND(REC_INT_SECOND) Source Value:0x0C000000 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>-- should overflow
>>select cast (a as numeric (19)) from (values(interval '12.00' second)) t(a);
(EXPR)
--------------------
12
--- 1 row(s) selected.
>>
>>-- End of numeric to interval convertion overflow checking
>>
>>-- Testing for ALM bug 4419 (Feb 2012)
>>-- - overflow check with mismatched fractional precision
>>create table t022th(inter interval hour to second not null primary key);
--- SQL operation complete.
>>insert into t022th values(interval '001:23:45' hour(3) to second);
--- 1 row(s) inserted.
>> -- OK
>>insert into t022th values(interval '901:23:45' hour(3) to second);
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND) Source Value:0x40FA8F8AF3020000 to Target Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND).
--- 0 row(s) inserted.
>>insert into t022th values(interval '90123:45' minute(5) to second(3));
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL MINUTE TO SECOND(REC_INT_MINUTE_SECOND) Source Value:0x4072A503EB040000 to Target Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND).
--- 0 row(s) inserted.
>>
>>create table t022ti(inter interval hour(3) to second(3) not null primary key);
--- SQL operation complete.
>>insert into t022ti values(interval '90123:45' minute(5) to second(6));
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL MINUTE TO SECOND(REC_INT_MINUTE_SECOND) Source Value:0xE8C14E4201000000 to Target Type:INTERVAL HOUR TO SECOND(REC_INT_HOUR_SECOND).
--- 0 row(s) inserted.
>>
>>-- End of ALM bug 4419
>>
>>-- Testing for solution 10-040302-3733
>>
>>-- RG: Build FCS040225, Select interval_second_col * 0.02 gets numeric overflow.
>>
>>create table t022tc (ivsc interval second);
--- SQL operation complete.
>>insert into t022tc values (interval '12' second);
--- 1 row(s) inserted.
>>
>>--- This select should get 0.1440 second.
>>
>>select ivsc * 0.02 from t022tc;
(EXPR)
-----------
0.240000
--- 1 row(s) selected.
>>
>>create table t022td (D_to_F INTERVAL DAY TO SECOND(3));
--- SQL operation complete.
>>insert into t022td values (interval '18 15:17:02.123' day to second(3));
--- 1 row(s) inserted.
>>insert into t022td values (interval '27 04:57:58.999' day to second(3));
--- 1 row(s) inserted.
>>select D_to_F / 1 from t022td;
(EXPR)
----------------
18 15:17:02.123
27 04:57:58.999
--- 2 row(s) selected.
>>
>>-- End of testing solution 10-040302-3733
>>
>>-- Testing Solution 10-040226-3566
>>--RG:0218 'select cast from integer to interval second'got numeric overflow
>>
>>create table t022te (inum integer, dnum numeric (8,6));
--- SQL operation complete.
>>insert into t022te values (99, 23.123456);
--- 1 row(s) inserted.
>>
>>-- should not overflow
>>select CAST (dnum as INTERVAL second) from t022te;
(EXPR)
----------
23.123456
--- 1 row(s) selected.
>>
>>create table t022tf (
+> ivyr interval year (4),
+> ivmt interval month,
+> ivdy interval day,
+> ivhr interval hour,
+> ivmn interval minute,
+> ivsc interval second (2,6)
+> );
--- SQL operation complete.
>>insert into t022tf values ( -- normal values
+> interval '2000' year (4),
+> interval '10' month,
+> interval '2' day,
+> interval '15' hour,
+> interval '60' minute,
+> interval '12' second
+> );
--- 1 row(s) inserted.
>>
>>-- should not overflow
>>select ivsc, ivsc * 0.02 from t022tf;
IVSC (EXPR)
---------- -----------
12.000000 0.240000
--- 1 row(s) selected.
>>
>>-- should not overflow
>>update t022tf set ivyr = ivyr * 1.5;
--- 1 row(s) updated.
>>
>>select * from t022tf;
IVYR IVMT IVDY IVHR IVMN IVSC
----- ---- ---- ---- ---- ----------
3000 10 2 15 60 12.000000
--- 1 row(s) selected.
>>
>>delete from t022tf;
--- 1 row(s) deleted.
>>
>>insert into t022tf values ( -- min values
+> interval '0' year,
+> interval '0' month,
+> interval '0' day,
+> interval '0' hour,
+> interval '0' minute,
+> interval '0' second
+> );
--- 1 row(s) inserted.
>>
>>-- should not overflow
>>update t022tf set
+> IVYR = IVYR + interval '25' year,
+> IVMT = IVMT + interval '18' month,
+> IVDY = IVDY + interval '40' day,
+> IVHR = IVHR + interval '24' hour,
+> IVMN = IVMN + interval '60' minute,
+> IVSC = IVSC + interval '3.99' second;
--- 1 row(s) updated.
>>
>>select * from t022tf;
IVYR IVMT IVDY IVHR IVMN IVSC
----- ---- ---- ---- ---- ----------
25 18 40 24 60 3.990000
--- 1 row(s) selected.
>>
>>-- End of testing Solution 10-040226-3566
>>
>>-- negative tests on casts between CHAR/NCHAR
>>select cast (N'Hello, world!' as char(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>select cast (N'Hello, world!' as varchar(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>select cast ('Hello, world!' as nchar(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>select cast ('Hello, world!' as nchar varying(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>
>>-- tests on cast VAR/NCHAR to VAR/NCHAR
>>select cast (N'Hello, world!' as nchar(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>select cast (N'Hello, world!' as nchar varying(13)) from dual;
(EXPR)
--------------------------
Hello, world!
--- 1 row(s) selected.
>>select cast (cast (N'Hello ' as nchar(13)) as nchar(5)) from dual;
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello ' as nchar(13)) as nchar varying(5)) from dual;
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello, world!' as nchar(13)) as nchar(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello, world!' as nchar(13)) as nchar varying(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello ' as nchar varying(13)) as nchar(5)) from dual;
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello ' as nchar varying(13)) as nchar varying(5)) from dual;
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello, world!' as nchar varying(13)) as nchar(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>select cast (cast (N'Hello, world!' as nchar varying(13)) as nchar varying(5)) from dual;
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
(EXPR)
----------
Hello
--- 1 row(s) selected.
>>
>>-- tests on cast VAR/NCHAR to DATETIME
>>select cast (N'12.07.1961 03.04.55.123456' as timestamp) from dual;
(EXPR)
--------------------------
1961-07-12 03:04:55.123456
--- 1 row(s) selected.
>>select cast (N'12.07.1961 03.04.55.123456' as timestamp(3)) from dual;
(EXPR)
-----------------------
1961-07-12 03:04:55.123
--- 1 row(s) selected.
>>select cast (cast (N'12.07.1961 03.04.55.123456' as timestamp) as DATE)
+>from dual;
(EXPR)
----------
1961-07-12
--- 1 row(s) selected.
>>select cast (cast (N'12.07.1961 03.04.55.123456' as timestamp) as char(21)) from dual;
(EXPR)
---------------------
1961-07-12 03:04:55.1
--- 1 row(s) selected.
>>select cast (N'12/13/1987' as date) from dual;
(EXPR)
----------
1987-12-13
--- 1 row(s) selected.
>>
>>-- some negative DATE, TIME, TIMESTAMP
>>-- illegal european format
>>select cast (N'12.13.1987' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 12.13.1987
--- 0 row(s) selected.
>>
>>-- illegal US format
>>select cast (N'13/12/1987' as date) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 13/12/1987
--- 0 row(s) selected.
>>
>>-- string overflow
>>select cast (cast (N'12/07/1961 03:04:55.123456' as timestamp) as char(18)) from dual;
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression.
--- 0 row(s) selected.
>>
>>-- Feb. 32th doesn't exist
>>select cast(N'1997-02-32' as DATE) from dual;
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1997-02-32
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to smallint
>>select cast(N'2' as SMALLINT) from dual;
(EXPR)
------
2
--- 1 row(s) selected.
>>select cast(N'-32768' as SMALLINT) from dual;
(EXPR)
------
-32768
--- 1 row(s) selected.
>>select cast(N'32767' as SMALLINT) from dual;
(EXPR)
------
32767
--- 1 row(s) selected.
>>select cast(N'0' as SMALLINT UNSIGNED) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast(N'65535' as SMALLINT UNSIGNED) from dual;
(EXPR)
------
65535
--- 1 row(s) selected.
>>
>>-- negative test on cast VAR/NCAR to smallint
>>select cast(N'-1' as SMALLINT UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,2 BYTES,ISO88591) Source Value:-1 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'-32769' as SMALLINT) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,6 BYTES,ISO88591) Source Value:-32769 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast(N'50000' as SMALLINT) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,5 BYTES,ISO88591) Source Value:50000 to Target Type:SMALLINT SIGNED(REC_BIN16_SIGNED).
--- 0 row(s) selected.
>>select cast(N'-32769' as SMALLINT UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,6 BYTES,ISO88591) Source Value:-32769 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'70000' as SMALLINT UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,5 BYTES,ISO88591) Source Value:70000 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'70000' as SMALLINT UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,5 BYTES,ISO88591) Source Value:70000 to Target Type:SMALLINT UNSIGNED(REC_BIN16_UNSIGNED).
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to bit precision integer(X) unsigned
>>select cast(N'1000' as bit precision integer(15) UNSIGNED) from dual;
(EXPR)
------
1000
--- 1 row(s) selected.
>>select cast(N'0' as bit precision integer(15) UNSIGNED) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast(N'32767' as bit precision integer(15) UNSIGNED) from dual;
(EXPR)
------
32767
--- 1 row(s) selected.
>>
>>-- negative test on cast VAR/NCAR to bit precision integer(X) unsigned
>>select cast(N'32768' as bit precision integer(15) UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:BIT PRECISION INTEGER(REC_BPINT_UNSIGNED) Source Value:32768 to Target Type:BIT PRECISION INTEGER(REC_BPINT_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'-1' as bit precision integer(15) UNSIGNED) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,2 BYTES,ISO88591) Source Value:-1 to Target Type:BIT PRECISION INTEGER(REC_BPINT_UNSIGNED).
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to integer
>>select cast(N'4294967295' as integer unsigned) from dual;
(EXPR)
----------
4294967295
--- 1 row(s) selected.
>>select cast(N'0' as integer unsigned) from dual;
(EXPR)
----------
0
--- 1 row(s) selected.
>>select cast(N'2147483647' as integer) from dual;
(EXPR)
-----------
2147483647
--- 1 row(s) selected.
>>select cast(N'-2147483648' as integer) from dual;
(EXPR)
-----------
-2147483648
--- 1 row(s) selected.
>>
>>-- negative test on cast VAR/NCAR to integer
>>select cast(N'4294967296' as integer unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,10 BYTES,ISO88591) Source Value:4294967296 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'-1' as integer unsigned) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,2 BYTES,ISO88591) Source Value:-1 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast(N'2147483648' as integer) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,10 BYTES,ISO88591) Source Value:2147483648 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast(N'-2147483649' as integer) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,11 BYTES,ISO88591) Source Value:-2147483649 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to largeint
>>select cast(N'42949672960000' as largeint) from dual;
(EXPR)
--------------------
42949672960000
--- 1 row(s) selected.
>>
>>-- test on cast VAR/NCAR to DEC
>>select cast(N'42949672960000' as DEC) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,14 BYTES,ISO88591) Source Value:42949672960000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast(N'12345.56' as DEC (7,2) unsigned) from dual;
(EXPR)
--------
12345.56
--- 1 row(s) selected.
>>
>>-- negative test on cast VAR/NCAR to DEC
>>select cast(N'1009a' as DEC) from dual;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 3130303961
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to float/real/double
>>select cast(N'-107' as float) from dual;
(EXPR)
-------------------------
-1.07000000000000000E+002
--- 1 row(s) selected.
>>select cast(N'1.2E-001' as real) from dual;
(EXPR)
---------------
1.1999999E-001
--- 1 row(s) selected.
>>select cast(N'12E-001' as double precision) from dual;
(EXPR)
-------------------------
1.20000000000000016E+000
--- 1 row(s) selected.
>>
>>-- negative test on cast VAR/NCAR to float/real/double
>>select cast(N'this is not a number.' as float) from dual;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 74686973206973206e6f742061206e756d6265722e
--- 0 row(s) selected.
>>select cast(N'1023u' as real) from dual;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 3130323375
--- 0 row(s) selected.
>>select cast(N'''\2' as double precision) from dual;
*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 275c32
--- 0 row(s) selected.
>>
>>-- test on cast VAR/NCAR to interval
>>select cast(N'-07' as interval year) from dual;
*** ERROR[8422] The provided INTERVAL is not valid and cannot be converted.
--- 0 row(s) selected.
>>select cast(N'5' as interval year) from dual;
(EXPR)
------
5
--- 1 row(s) selected.
>>
>>-- test on cast smallint to VAR/NCAR
>>select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(10)) from dual;
(EXPR)
--------------------
65535
--- 1 row(s) selected.
>>select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR(10)) from dual;
(EXPR)
--------------------
-5535
--- 1 row(s) selected.
>>select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(10)) from dual;
(EXPR)
--------------------
65535
--- 1 row(s) selected.
>>select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR VARYING(10)) from dual;
(EXPR)
--------------------
65535
--- 1 row(s) selected.
>>select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR VARYING(10)) from dual;
(EXPR)
--------------------
-5535
--- 1 row(s) selected.
>>
>>-- negative test on cast smallint to VAR/NCAR
>>select cast(cast(N'65535' as SMALLINT UNSIGNED) as NCHAR(1)) from dual;
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:65535 to Target Type:CHAR(1) CHARACTER SET UCS2(REC_BYTE_F_ASCII).
--- 0 row(s) selected.
>>select cast(cast(N'-5535' as SMALLINT SIGNED) as NCHAR VARYING(1)) from dual;
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-5535 to Target Type:VARCHAR(1) CHARACTER SET UCS2(REC_BYTE_V_ASCII).
--- 0 row(s) selected.
>>
>>-- test on cast integer to VAR/NCAR
>>select cast(cast(N'4294967295' as integer unsigned) as NCHAR VARYING(10)) from dual;
(EXPR)
--------------------
4294967295
--- 1 row(s) selected.
>>select cast(cast(N'0' as integer unsigned) as NCHAR(2)) from dual;
(EXPR)
------
0
--- 1 row(s) selected.
>>select cast(cast(N'2147483647' as integer) as NCHAR(12)) from dual;
(EXPR)
------------------------
2147483647
--- 1 row(s) selected.
>>select cast(cast(N'-2147483648' as integer) as NCHAR(12)) from dual;
(EXPR)
------------------------
-2147483648
--- 1 row(s) selected.
>>
>>-- negative test on cast integer to VAR/NCAR
>>select cast(cast(N'4294967296' as integer unsigned) as nchar(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,10 BYTES,ISO88591) Source Value:4294967296 to Target Type:INTEGER UNSIGNED(REC_BIN32_UNSIGNED).
--- 0 row(s) selected.
>>select cast(cast(N'-10' as integer) as nchar(1)) from dual;
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:-10 to Target Type:CHAR(1) CHARACTER SET UCS2(REC_BYTE_F_ASCII).
--- 0 row(s) selected.
>>select cast(cast(N'2147483648' as integer) as nchar varying(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,10 BYTES,ISO88591) Source Value:2147483648 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>select cast(cast(N'-2147483649' as integer) as nchar(1)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,11 BYTES,ISO88591) Source Value:-2147483649 to Target Type:INTEGER SIGNED(REC_BIN32_SIGNED).
--- 0 row(s) selected.
>>
>>-- test on cast DEC to VAR/NCAR
>>select cast(cast(N'42949672960000' as DEC) as nchar(10)) from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Intermediate conversion of Source Type:CHAR(REC_BYTE_F_ASCII,14 BYTES,ISO88591) Source Value:42949672960000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).
--- 0 row(s) selected.
>>select cast(cast(N'12345.56' as DEC (7,2) unsigned) as nchar(20)) from dual;
(EXPR)
----------------------------------------
12345.56
--- 1 row(s) selected.
>>
>>-- "timestamp - timestamp" should return result in seconds
>>select timestamp '2018-10-10 10:10:10' - timestamp '2017-10-10 10:10:10' from dual;
(EXPR)
-------------------
31536000
--- 1 row(s) selected.
>>
>>-- negative test on cast DEC to VAR/NCAR
>>select cast(cast(N'12345.56' as DEC (7,2) unsigned) as nchar(2)) from dual;
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:12345.56 to Target Type:CHAR(2) CHARACTER SET UCS2(REC_BYTE_F_ASCII).
--- 0 row(s) selected.
>>
>>-- test on cast date, time and timestamp to VAR/NCAR
>>select cast(date '1998-03-02' as nchar varying(11)) from dual;
(EXPR)
----------------------
1998-03-02
--- 1 row(s) selected.
>>select cast(date '1998-03-02' as nchar(11)) from dual;
(EXPR)
----------------------
1998-03-02
--- 1 row(s) selected.
>>select cast(time '08:03:02' as nchar(11)) from dual;
(EXPR)
----------------------
08:03:02
--- 1 row(s) selected.
>>select cast(time '08:03:02 pm' as nchar(11)) from dual;
(EXPR)
----------------------
20:03:02
--- 1 row(s) selected.
>>select cast(timestamp
+>'1998-06-03 08:03:02.000000' as nchar varying(21)) from dual;
(EXPR)
------------------------------------------
1998-06-03 08:03:02.0
--- 1 row(s) selected.
>>select cast(timestamp '1998-06-03 08:03:02.000000' as nchar(21)) from dual;
(EXPR)
------------------------------------------
1998-06-03 08:03:02.0
--- 1 row(s) selected.
>>-- Genesis case-10-050208-6275
>>-- Test on ascii to time
>>
>>select cast('10:13:14' as time(4)),cast('10:13:14' as time(4)) from (values(1)) as x;
(EXPR) (EXPR)
------------- -------------
10:13:14.0000 10:13:14.0000
--- 1 row(s) selected.
>>select cast('10:13:14' as time(4)),cast('10:13:14' as time(4)),current_timestamp from (values(1)) as x;
(EXPR) (EXPR) (EXPR)
------------- ------------- --------------------------
10:13:14.0000 10:13:14.0000 2018-06-20 20:52:53.000980
--- 1 row(s) selected.
>>--test on precision of time
>>select cast('10:13:14' as time(10)),cast('10:13:14' as time(4)) from dual;
*** ERROR[3134] The specified TIME or TIMESTAMP precision value, 10, cannot exceed 9.
*** ERROR[8822] The statement was not prepared.
>>--
>>-- testing translate. 1998 6/12
>>--
>>-- Syntax: translate(expr using translation)
>>--
>>-- where translation can be
>>-- 1. ISO88591TOUCS2
>>-- 2. UCS2TOISO88591
>>-- For case 1 and 2, the expr has to be [VAR]CHAR(X) CHARACTERSET ISO88591.
>>-- The result is NCHAR VARYING(Y) CHARACTERSET UNICODE, where Y = X for both
>>-- case 1 and 2.
>>--
>>-- IF expr is NULL, the result is NULL.
>>--
>>
>>create table t022u2 (a char(10) default NULL,
+> b nchar(10) default NULL,
+> c int
+> );
--- SQL operation complete.
>>
>>insert into t022u2(c) values(1);
--- 1 row(s) inserted.
>>insert into t022u2(c) values(2);
--- 1 row(s) inserted.
>>insert into t022u2 values('abcdefg', N'abcdefg', 3);
--- 1 row(s) inserted.
>>insert into t022u2 values('hijkl', N'hijkl', 4);
--- 1 row(s) inserted.
>>
>>select translate(a using iso88591ToUcs2) from t022u2;
(EXPR)
--------------------
?
?
abcdefg
hijkl
--- 4 row(s) selected.
>>select translate(a using iso88591ToUCS2) from t022u2;
(EXPR)
--------------------
?
?
abcdefg
hijkl
--- 4 row(s) selected.
>>select translate(a using ISO88591TOUCS2) from t022u2;
(EXPR)
--------------------
?
?
abcdefg
hijkl
--- 4 row(s) selected.
>>
>>select translate(b using ucs2Toiso88591) from t022u2;
(EXPR)
----------
?
?
abcdefg
hijkl
--- 4 row(s) selected.
>>
>>-- some negative tests
>>select translate(b using ucs2Toiso88591) from t022u2;
(EXPR)
----------
?
?
abcdefg
hijkl
--- 4 row(s) selected.
>> -- wrong mapping name
>>select translate(a using ucs2Toiso88591) from t022u2;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be UCS2.
*** ERROR[8822] The statement was not prepared.
>>-- character set mismatch
>>select translate(b using ISO88591TOUCS2) from t022u2;
*** ERROR[4106] The character set for the operand of function TRANSLATE must be ISO88591.
*** ERROR[8822] The statement was not prepared.
>>-- charactet set mismatch
>>select translate(123 using ISO88591TOUCS2) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char data type
>>select translate(12E-01 using ISO88591TOUCS2) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char data type
>>select translate(-123 using ucs2Toiso88591) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char data type
>>select translate(123.3 using ucs2Toiso88591) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char data type
>>select translate(date '1998-06-15' using ucs2Toiso88591) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char
>>select translate(time '08:03:02 pm' using iso88591ToUcs) from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char
>>select translate(timestamp '1998-06-03 08:03:02.000000' using iso88591ToUcs2)
+>from t022u2;
*** ERROR[4043] The operand of function TRANSLATE must be character.
*** ERROR[8822] The statement was not prepared.
>>-- not a char
>>
>>-- New PCode comparison instructions for float32 and float64
>>create table t022fltcmp(c0 char(23), c1 real not null, c2 float not null);
--- SQL operation complete.
>>-- Insert some rows
>>insert into t022fltcmp values ('unaligned float access', 0, 20.12345),
+> ('unaligned', 0, 20.56789);
--- 2 row(s) inserted.
>>insert into t022fltcmp values('unaligned float', 10.12345,
+> 1.7976931348623157e+308);
--- 1 row(s) inserted.
>>insert into t022fltcmp values('unaligned access', 3.40282346e+38, 10.12345);
--- 1 row(s) inserted.
>>insert into t022fltcmp values('float access', 1.17549436e-38,
+> 2.2250738585072014e-308);
--- 1 row(s) inserted.
>>
>>select * from t022fltcmp;
C0 C1 C2
----------------------- --------------- -------------------------
unaligned float access 0.0000000E+000 2.01234499999999968E+001
unaligned 0.0000000E+000 2.05678899999999968E+001
unaligned float 1.0123450E+001 1.79769313486231520E+308
unaligned access 3.4028234E+038 1.01234500000000000E+001
float access 1.1754943E-038 2.22507385850720128E-308
--- 5 row(s) selected.
>>
>>-- Start the basic tests
>>-- New PCode instructions for FLOAT64 and FLOAT32: GT and LE
>>select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 > 10.02 and c2 <= 30.02;
(EXPR) (EXPR)
------------------------- -------------------------
3.40282346638528832E+038 3.01234500000000000E+001
--- 1 row(s) selected.
>>select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 > c1 and c2 <= 30.02;
--- 0 row(s) selected.
>>select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 <= 20.02 and c2 > 30.02;
(EXPR) (EXPR)
------------------------- -------------------------
-9.87654972076416000E+000 1.79769313486231520E+308
--- 1 row(s) selected.
>>select c1 - 20.00, c2 + 20.00 from t022fltcmp where c1 <= c1 and c2 > 30.02;
(EXPR) (EXPR)
------------------------- -------------------------
-9.87654972076416000E+000 1.79769313486231520E+308
--- 1 row(s) selected.
>>
>>-- New PCode instructions for FLOAT64 and FLOAT32: GE and LT
>>select c2 * 20.00 from t022fltcmp where c1 >= 20.12345 and c2 < 1.7976E208;
(EXPR)
-------------------------
2.02469000000000000E+002
--- 1 row(s) selected.
>>select c2 * 20.00 from t022fltcmp where c1 >= c1 and c2 < 1.7976E208;
(EXPR)
-------------------------
4.02468999999999936E+002
4.11357800000000064E+002
2.02469000000000000E+002
4.45014771701440256E-307
--- 4 row(s) selected.
>>select c1 * 20.00 from t022fltcmp where c1 < 20.12345
+> and c2 >= 2.2250738585072014e-308;
(EXPR)
-------------------------
0.00000000000000000E+000
0.00000000000000000E+000
2.02469005584716800E+002
2.35098870164457504E-037
--- 4 row(s) selected.
>>select c1 * 20.00 from t022fltcmp where c1 < c1
+> and c2 >= 2.2250738585072014e-308;
--- 0 row(s) selected.
>>
>>-- New PCode instructions for FLOAT64 and FLOAT32: EQ and NE
>>select c2 from t022fltcmp where c1 = 1.17549436e-38 and
+> c2 <> 1.7976931348623157e+308;
C2
-------------------------
2.22507385850720128E-308
--- 1 row(s) selected.
>>select c2 from t022fltcmp where c1 = c1 and
+> c2 <> 1.7976931348623157e+308;
C2
-------------------------
2.01234499999999968E+001
2.05678899999999968E+001
1.01234500000000000E+001
2.22507385850720128E-308
--- 4 row(s) selected.
>>select c1 from t022fltcmp where c1 <> 10.12345
+> and c2 = 2.2250738585072014e-308;
C1
---------------
1.1754943E-038
--- 1 row(s) selected.
>>select c1 from t022fltcmp where c1 <> c1
+> and c2 = 2.2250738585072014e-308;
--- 0 row(s) selected.
>>
>>-- PCode bug fix validation
>>-- PCode constant and jump table setup for ALM CR5679 (64-bit)
>>create table t022bug (t1 date not null
+> , f2 real not null
+> , z char (8)
+> , vc1 varchar(15)
+> , primary key (t1)) no partition;
--- SQL operation complete.
>>create view vt022bug as select * from t022bug where trim(z) < 'Row05' or
+> vc1 in ('~!#$%^\&', '~', '~');
--- SQL operation complete.
>>insert into t022bug values ( date '1959-12-31', 9, null, '~!#$%^\&');
--- 1 row(s) inserted.
>>
>>-- Expect 1 row selected
>>select f2 from vt022bug where f2 > 8.2399999E-1 and f2 < 9.1234567E01
+> group by vc1, f2 having vc1 in ('~', '~!#$%^\&');
F2
---------------
9.0000000E+000
--- 1 row(s) selected.
>>
>>--to test comparison between shorter signed and longer unsigned
>>--comparisons that involve implicit typecast of both operands
>>--to signed int.
>>create table t022tg (iu int unsigned, ss smallint);
--- SQL operation complete.
>>insert into t022tg values(4294967295, -1),(3000000000,-1),(4294967295,32765);
--- 3 row(s) inserted.
>>
>>--Must return zero rows.
>>select * from t022tg where -1>iu;
--- 0 row(s) selected.
>>
>>--Must return 3 rows.
>>select * from t022tg where iu>ss;
IU SS
---------- ------
4294967295 -1
3000000000 -1
4294967295 32765
--- 3 row(s) selected.
>>
>>--Must return zero rows.
>>select * from t022tg where iu<=ss;
--- 0 row(s) selected.
>>
>>--Must return 3 rows.
>>select * from t022tg where iu>=ss;
IU SS
---------- ------
4294967295 -1
3000000000 -1
4294967295 32765
--- 3 row(s) selected.
>>
>>--Must return 3 rows.
>>select * from t022tg where ss<iu;
IU SS
---------- ------
4294967295 -1
3000000000 -1
4294967295 32765
--- 3 row(s) selected.
>>
>>--Must return zero rows.
>>select * from t022tg where ss>iu;
--- 0 row(s) selected.
>>
>>--Must return 3 rows.
>>select * from t022tg where ss<=iu;
IU SS
---------- ------
4294967295 -1
3000000000 -1
4294967295 32765
--- 3 row(s) selected.
>>
>>--Must return zero rows.
>>select * from t022tg where ss>=iu;
--- 0 row(s) selected.
>>
>>-- check for string truncation, with and without auto truncate
>>delete from t022u2;
--- 4 row(s) deleted.
>>insert into t022u2 (c) values (1);
--- 1 row(s) inserted.
>>
>>-- should return truncation error
>>insert into t022u2 (a) values ('abcdefghijkl');
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:VARCHAR(12) CHARACTER SET ISO88591(REC_BYTE_V_ASCII) Source Value:abcdefghijkl to Target Type:CHAR(10) CHARACTER SET ISO88591(REC_BYTE_F_ASCII).
--- 0 row(s) inserted.
>>update t022u2 set a = 'abcdefghijkl';
*** ERROR[8402] A string overflow occurred during the evaluation of a character expression. Conversion of Source Type:CHAR(12) CHARACTER SET ISO88591(REC_BYTE_F_ASCII) Source Value:abcdefghijkl to Target Type:CHAR(10) CHARACTER SET ISO88591(REC_BYTE_F_ASCII).
--- 0 row(s) updated.
>>
>>-- should not return truncation errors.
>>cqd traf_string_auto_truncate 'ON';
--- SQL operation complete.
>>delete from t022u2;
--- 1 row(s) deleted.
>>insert into t022u2 (a) values ('abcdefghijkl');
--- 1 row(s) inserted.
>>select * from t022u2;
A B C
---------- -------------------- -----------
abcdefghij ? ?
--- 1 row(s) selected.
>>update t022u2 set a = 'lkjihgfedcba';
--- 1 row(s) updated.
>>select * from t022u2;
A B C
---------- -------------------- -----------
lkjihgfedc ? ?
--- 1 row(s) selected.
>>
>>-- return warnings if truncation occurs
>>cqd traf_string_auto_truncate_warning 'ON';
--- SQL operation complete.
>>delete from t022u2;
--- 1 row(s) deleted.
>>insert into t022u2 (a) values ('abcdefghijkl');
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
--- 1 row(s) inserted.
>>select * from t022u2;
A B C
---------- -------------------- -----------
abcdefghij ? ?
--- 1 row(s) selected.
>>update t022u2 set a = 'lkjihgfedcba';
*** WARNING[8402] A string overflow occurred during the evaluation of a character expression.
--- 1 row(s) updated.
>>select * from t022u2;
A B C
---------- -------------------- -----------
lkjihgfedc ? ?
--- 1 row(s) selected.
>>
>>-- tests for nanosecond precision for datetime and interval
>>create table t022nanos(a timestamp(9), b time(9), c interval hour to second(9));
--- SQL operation complete.
>>invoke t022nanos;
-- Definition of Trafodion table TRAFODION.SCH.T022NANOS
-- Definition current Wed Jun 20 20:53:22 2018
(
SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
, A TIMESTAMP(9) DEFAULT NULL
, B TIME(9) DEFAULT NULL
, C INTERVAL HOUR(2) TO SECOND(9) DEFAULT NULL
)
--- SQL operation complete.
>>insert into t022nanos values (timestamp '2018-01-01 10:10:10.123456789',
+> time '10:10:10.123456789',
+> interval '10:10:10.123456789' hour to second(9));
--- 1 row(s) inserted.
>>prepare s from insert into t022nanos values (?, ?, ?);
--- SQL command prepared.
>>execute s using '2019-02-02 11:11:11.987654321', '01:01:01.112233445',
+> '02:02:02.111111111';
--- 1 row(s) inserted.
>>select * from t022nanos;
A B C
----------------------------- ------------------ -------------------
2018-01-01 10:10:10.123456789 10:10:10.123456789 10:10:10.123456789
2019-02-02 11:11:11.987654321 01:01:01.112233445 2:02:02.111111111
--- 2 row(s) selected.
>>select a - a, b - b, c-c, c+c, a+c, a-c from t022nanos;
(EXPR) (EXPR) (EXPR) (EXPR) (EXPR) (EXPR)
-------------------- -------------------- -------------------- -------------------- ----------------------------- -----------------------------
0.000000000 0.000000000 0:00:00.000000000 20:20:20.246913578 2018-01-01 20:20:20.246913578 2018-01-01 00:00:00.000000000
0.000000000 0.000000000 0:00:00.000000000 4:04:04.222222222 2019-02-02 13:13:14.098765432 2019-02-02 09:09:09.876543210
--- 2 row(s) selected.
>>select cast(a as char(30)), cast(b as char(20)), cast(c as char(20)) from t022nanos;
(EXPR) (EXPR) (EXPR)
------------------------------ -------------------- --------------------
2018-01-01 10:10:10.123456789 10:10:10.123456789 10:10:10.123456789
2019-02-02 11:11:11.987654321 01:01:01.112233445 2:02:02.111111111
--- 2 row(s) selected.
>>select * from t022nanos where a = timestamp '2018-01-01 10:10:10.123456789';
A B C
----------------------------- ------------------ -------------------
2018-01-01 10:10:10.123456789 10:10:10.123456789 10:10:10.123456789
--- 1 row(s) selected.
>>select * from t022nanos where a = timestamp '2018-01-01 10:10:10.123456788';
--- 0 row(s) selected.
>>
>>drop table if exists hive.hive.thivets;
--- SQL operation complete.
>>create table hive.hive.thivets (a timestamp);
--- SQL operation complete.
>>insert into hive.hive.thivets values (timestamp '2018-01-01 10:10:10.123456789');
--- 1 row(s) inserted.
>>select * from hive.hive.thivets;
A
-----------------------------
2018-01-01 10:10:10.123456789
--- 1 row(s) selected.
>>select * from hive.hive.thivets where a = timestamp '2018-01-01 10:10:10.123456788' + interval '0.000000001' second (1,9);
A
-----------------------------
2018-01-01 10:10:10.123456789
--- 1 row(s) selected.
>>
>>select extract(second from a), extract(second from b), extract(second from c)
+> from t022nanos;
(EXPR) (EXPR) (EXPR)
--------------------- --------------------- ---------------------
10.123456789 10.123456789 .088598805
11.987654321 1.112233445 .000505799
--- 2 row(s) selected.
>>
>>
>>-- ERROR CASES
>>
>>-- total precision exceeds Int64 max.
>>create table t022interr(a interval second(12, 9));
*** ERROR[3044] The interval SECOND(12,9) is not valid.
*** ERROR[8822] The statement was not prepared.
>>
>>select timestamp '9999-01-01 10:10:10.123456789' - timestamp '2018-01-01 10:10:10.123456' from dual;
*** ERROR[8453] This expression results in an invalid interval value '251856000000000000789'
--- 0 row(s) selected.
>>select timestamp '2100-01-01 10:10:10.123456789' - timestamp '2018-01-01 10:10:10.123456' from dual;
*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:INTERVAL SECOND(REC_INT_SECOND) Source Value:0x150362F83F49E923 to Target Type:INTERVAL SECOND(REC_INT_SECOND).
--- 0 row(s) selected.
>>
>>-- invalid timestamp, time, interval values
>>execute s using '2019-02-02 11:11:11.98765432115', '01:01:01.1122334455',
+> '02:02:02.1111111115';
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2019-02-02 11:11:11.98765432115
*** ERROR[15015] PARAM ?(UNNAMED_1) (value 2019-02-02 11:11:11.98765432115) cannot be converted to type TIMESTAMP(9).
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 01:01:01.1122334455
*** ERROR[15015] PARAM ?(UNNAMED_2) (value 01:01:01.1122334455) cannot be converted to type TIME(9).
*** ERROR[15015] PARAM ?(UNNAMED_3) (value 02:02:02.1111111115) cannot be converted to type INTERVAL HOUR(2) TO SECOND(9).
--- 0 row(s) inserted.
>>
>>-- done
>>log;