Google Git
Sign in
apache / trafodion / 927b9384b9e1957e95487792fcbed6cac09ac22a / . / core / sql / regress / executor / EXPECTED101
blob: 81c888cbbcb6403d253aead11a761f61b4afb384 [file] [log] [blame]
>>
>>obey TEST101(round_function);
>>----------------------------------------------------------------------------
>>-- General exact numeric tests
>>select round(123.45678) from (values(1))t;
(EXPR)
---------------------
123.00000
--- 1 row(s) selected.
>>select round(123.45000, 0)from (values(1))t;
(EXPR)
---------------------
123.00000
--- 1 row(s) selected.
>>select round(123.45000,-0)from (values(1))t;
(EXPR)
------------
123.00000
--- 1 row(s) selected.
>>
>>select round(-123.45678) from (values(1))t;
(EXPR)
---------------------
-123.00000
--- 1 row(s) selected.
>>select round(-123.45000, 0)from (values(1))t;
(EXPR)
---------------------
-123.00000
--- 1 row(s) selected.
>>select round(-123.45000,-0)from (values(1))t;
(EXPR)
---------------------
-123.00000
--- 1 row(s) selected.
>>
>>select round(123.45678, 1) from (values(1))t;
(EXPR)
---------------------
123.50000
--- 1 row(s) selected.
>>select round(123.45000, 1)from (values(1))t;
(EXPR)
---------------------
123.50000
--- 1 row(s) selected.
>>select round(123.45004, 1) from (values(1))t;
(EXPR)
---------------------
123.50000
--- 1 row(s) selected.
>>select round(123.35678,1) from (values(1))t;
(EXPR)
---------------------
123.40000
--- 1 row(s) selected.
>>select round(123.35000, 1)from (values(1))t;
(EXPR)
---------------------
123.40000
--- 1 row(s) selected.
>>select round(123.35004, 1) from (values(1))t;
(EXPR)
---------------------
123.40000
--- 1 row(s) selected.
>>
>>select round(-123.45678, 1) from (values(1))t;
(EXPR)
---------------------
-123.50000
--- 1 row(s) selected.
>>select round(-123.45000, 1)from (values(1))t;
(EXPR)
---------------------
-123.50000
--- 1 row(s) selected.
>>select round(-123.45004, 1) from (values(1))t;
(EXPR)
---------------------
-123.50000
--- 1 row(s) selected.
>>select round(-123.35678,1) from (values(1))t;
(EXPR)
---------------------
-123.40000
--- 1 row(s) selected.
>>select round(-123.35000, 1)from (values(1))t;
(EXPR)
---------------------
-123.40000
--- 1 row(s) selected.
>>select round(-123.35004, 1) from (values(1))t;
(EXPR)
---------------------
-123.40000
--- 1 row(s) selected.
>>
>>select round(12345.678, -1) from (values(1))t;
(EXPR)
------------
12350.000
--- 1 row(s) selected.
>>select round(12345.000, -1)from (values(1))t;
(EXPR)
------------
12350.000
--- 1 row(s) selected.
>>select round(12345.004, -1) from (values(1))t;
(EXPR)
------------
12350.000
--- 1 row(s) selected.
>>select round(12335.678, -1) from (values(1))t;
(EXPR)
------------
12340.000
--- 1 row(s) selected.
>>select round(12335.000, -1)from (values(1))t;
(EXPR)
------------
12340.000
--- 1 row(s) selected.
>>select round(12335.004, -1) from (values(1))t;
(EXPR)
------------
12340.000
--- 1 row(s) selected.
>>
>>select round(-12345.678, -1) from (values(1))t;
(EXPR)
---------------------
-12350.000
--- 1 row(s) selected.
>>select round(-12345.000, -1)from (values(1))t;
(EXPR)
---------------------
-12350.000
--- 1 row(s) selected.
>>select round(-12345.004, -1) from (values(1))t;
(EXPR)
---------------------
-12350.000
--- 1 row(s) selected.
>>select round(-12335.678, -1) from (values(1))t;
(EXPR)
---------------------
-12340.000
--- 1 row(s) selected.
>>select round(-12335.000, -1)from (values(1))t;
(EXPR)
---------------------
-12340.000
--- 1 row(s) selected.
>>select round(-12335.004, -1) from (values(1))t;
(EXPR)
---------------------
-12340.000
--- 1 row(s) selected.
>>
>>select round(999.0, -1) from (values(1))t;
(EXPR)
------------
1000.0
--- 1 row(s) selected.
>>select round(999,-1) from (values(1))t;
(EXPR)
--------------------
1000
--- 1 row(s) selected.
>>select round(999.9, 0) from (values(1))t;
(EXPR)
---------------------
1000.0
--- 1 row(s) selected.
>>select round(999.9, 1) from (values(1))t;
(EXPR)
-------
999.9
--- 1 row(s) selected.
>>select round(-999.0, -1) from (values(1))t;
(EXPR)
------------
-1000.0
--- 1 row(s) selected.
>>select round(-999,-1) from (values(1))t;
(EXPR)
--------------------
-1000
--- 1 row(s) selected.
>>select round(-999.9, 0) from (values(1))t;
(EXPR)
---------------------
-1000.0
--- 1 row(s) selected.
>>select round(-999.9, 1) from (values(1))t;
(EXPR)
------------
-999.9
--- 1 row(s) selected.
>>
>>-- Generic approximate numeric rounding.
>>-- Even rounding tests
>>select round(cast(4.5678 as double precision)) from (values(1))t;
(EXPR)
-------------------------
5.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(4.5000 as double precision)) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(4.5004 as double precision)) from (values(1))t;
(EXPR)
-------------------------
5.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(3.5678 as double precision)) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(3.5000 as double precision)) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(3.5004 as double precision)) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+000
--- 1 row(s) selected.
>>
>>select round(cast(1.45678 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.50000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(1.45000 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(1.45004 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.50000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(1.35678 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(1.35000 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(1.35004 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
1.40000000000000016E+000
--- 1 row(s) selected.
>>
>>select round(cast(45.678 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
5.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(45.000 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(45.004 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
5.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(35.678 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(35.000 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(35.004 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
4.00000000000000000E+001
--- 1 row(s) selected.
>>
>>select round(cast(-4.5678 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-5.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-4.5000 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-4.5004 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-5.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-3.5678 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-3.5000 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-3.5004 as double precision)) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+000
--- 1 row(s) selected.
>>
>>select round(cast(-1.45678 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.50000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-1.45000 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(-1.45004 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.50000000000000000E+000
--- 1 row(s) selected.
>>select round(cast(-1.35678 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(-1.35000 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.40000000000000016E+000
--- 1 row(s) selected.
>>select round(cast(-1.35004 as double precision),1) from (values(1))t;
(EXPR)
-------------------------
-1.40000000000000016E+000
--- 1 row(s) selected.
>>
>>select round(cast(-45.678 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-5.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(-45.000 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(-45.004 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-5.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(-35.678 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(-35.000 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+001
--- 1 row(s) selected.
>>select round(cast(-35.004 as double precision),-1) from (values(1))t;
(EXPR)
-------------------------
-4.00000000000000000E+001
--- 1 row(s) selected.
>>
>>----------------------------------------------------------------------------
>>obey TEST101(round_function_BIGNUM);
>>----------------------------------------------------------------------------
>>-- scale 0, +ve numbers
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901) from (values(1))t;
(EXPR)
--------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,0) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,2) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-1) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-2) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-3) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345679000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-7) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312350000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-8) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312300000000
--- 1 row(s) selected.
>>select round(9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999,-1) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
--- 1 row(s) selected.
>>
>>-- scale 0, -ve numbers
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,0) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,2) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-1) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-2) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-3) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345679000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-7) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312350000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901,-8) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312300000000
--- 1 row(s) selected.
>>select round(-9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999,-1) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
--- 1 row(s) selected.
>>
>>-- scale > 0, +ve numbers
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,0) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,2) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.990000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992500,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993500,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.994000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,4) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992600
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-0) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-2) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-4) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345680000.000000
--- 1 row(s) selected.
>>select round(1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-7) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312350000000.000000
--- 1 row(s) selected.
>>select round(9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.999999,1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000
--- 1 row(s) selected.
>>
>>-- scale > 0, -ve numbers
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578) from (values(1))t;
(EXPR)
---------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,0) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,2) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.990000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992500,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.993500,3) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.994000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,4) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992600
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-0) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678902.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-2) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678900.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-4) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345680000.000000
--- 1 row(s) selected.
>>select round(-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312345678901.992578,-7) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-1234567897974774274024240354580583520842842830858835383853853853854545454545454545453853853853853853853853535312350000000.000000
--- 1 row(s) selected.
>>select round(-9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.999999,1) from (values(1))t;
(EXPR)
----------------------------------------------------------------------------------------------------------------------------------
-10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.000000
--- 1 row(s) selected.
>>
>>-- tests that involve internal representation of result as bignum but
>>-- requires the result to be non-Bignum. Tests SQLBigNum.isARealBigNum().
>>select round(round(748,0),1) from (values(1))t;
(EXPR)
------
748
--- 1 row(s) selected.
>>
>>create table t101( i int)no partition;
--- SQL operation complete.
>>insert into t101 values(1),(2);
--- 2 row(s) inserted.
>>select round(avg(i),0) from t101;
(EXPR)
--------------------
2
--- 1 row(s) selected.
>>drop table t101;
--- SQL operation complete.
>>
>>CREATE TABLE t101
+> (PG_BUDGET NUMERIC(18, 6),
+> PG_RESULT NUMERIC(18, 6)) NO PARTITION;
--- SQL operation complete.
>>insert into t101 values
+>(8394000.000000 , 7945000.000000),
+>(37784000.000000, 68082000.000000),
+>(339236000.000000, 357539000.000000),
+>(0.000000, 0.000000),
+>(231992000.000000, 257631000.000000),
+>(219187000.000000, 218909000.000000),
+>(1166648000.000000, 1131836000.000000);
--- 7 row(s) inserted.
>>select ROUND(( SUM(PG_RESULT)/SUM(PG_BUDGET) )*100,2) from t101;
(EXPR)
--------------------
101
--- 1 row(s) selected.
>>control query default BIGNUM_IO 'ON';
--- SQL operation complete.
>>select ROUND(( SUM(PG_RESULT)/SUM(PG_BUDGET) )*100,2) from t101;
(EXPR)
------------------------------------------
101.930000000000000000
--- 1 row(s) selected.
>>control query default BIGNUM_IO 'SYSTEM';
--- SQL operation complete.
>>
>>----------------------------------------------------------------------------
>>obey TEST101(round_mode_special_1);
>>----------------------------------------------------------------------------
>>-- Tests rounding mode 2 in mode_special_1 which
>>-- requires consideration of precision >19 when attempting to
>>-- apply the rounding algorithm.
>>control query default comp_bool_58 'on';
--- SQL operation complete.
>>control query default mode_special_1 'on';
--- SQL operation complete.
>>control query default rounding_mode '2';
--- SQL operation complete.
>>select (21237.87 * 70787) / 77076.97 from (values(1)) x(a);
(EXPR)
---------------------
19504.73
--- 1 row(s) selected.
>>
>>-- Test the same with pcode off.
>>Control Query Default PCODE_OPT_LEVEL 'OFF';
--- SQL operation complete.
>>select (21237.87 * 70787) / 77076.97 from (values(1)) x(a);
(EXPR)
---------------------
19504.73
--- 1 row(s) selected.
>>
>>log;