| >> |
| >>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.000000 |
| |
| --- 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.000000 |
| |
| --- 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. |
| >> |
| >>-- white box test of new BigNum::round implementation |
| >> |
| >>drop table if exists t101big; |
| |
| --- SQL operation complete. |
| >> |
| >>create table t101big (a int not null, |
| +> b numeric(128,2), |
| +> c numeric(128,2) unsigned, |
| +> d numeric(35,20), |
| +> e int, |
| +> f decimal(6,2), |
| +> g decimal(6,0), |
| +> primary key (a)); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t101big values |
| +>(1,12.34,12.34,1234.01234567890123456789,1,1,1), |
| +>(2,23.45,23.45,999999999999999.90000000000000000000,1,1,1), |
| +>(3,-12.34,12.55,-999999999999999.90000000000000000000,1,1,1), |
| +>(4,-0.12,0.12,0.12,1,1,1), |
| +>(5,999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.99, |
| +> 999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.99, |
| +> 0,1,1,1), |
| +>(6,null,null,null,1,1,1); |
| |
| --- 6 row(s) inserted. |
| >> |
| >>-- round with 1 parameter; includes rounding a negative number to zero, round up and non-round up cases |
| >>select a,round(b) from t101big where a < 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.00 |
| 2 23.00 |
| 3 -12.00 |
| 4 0.00 |
| |
| --- 4 row(s) selected. |
| >>select a,round(c) from t101big where a < 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.00 |
| 2 23.00 |
| 3 13.00 |
| 4 0.00 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- round with 1 parameter, with a round up that increases precision |
| >>select a,round(d) from t101big; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.00000000000000000000 |
| 2 1000000000000000.00000000000000000000 |
| 3 -1000000000000000.00000000000000000000 |
| 4 0.00000000000000000000 |
| 5 0.00000000000000000000 |
| 6 ? |
| |
| --- 6 row(s) selected. |
| >> |
| >>-- round with 1 parameter, with a round up that causes overflow |
| >>select a,round(b) from t101big where a = 5; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. The error occurred when rounding up a value. |
| |
| --- 0 row(s) selected. |
| >>select a,round(c) from t101big where a = 5; |
| |
| *** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. The error occurred when rounding up a value. |
| |
| --- 0 row(s) selected. |
| >> |
| >>-- round with 1 parameter of null value |
| >>select a,round(b),round(c),round(d) from t101big where a = 6; |
| |
| A (EXPR) (EXPR) (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------- -------------------------------------- |
| |
| 6 ? ? ? |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- round with 2 parameters |
| >>select a,round(b,1) from t101big where a < 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 -12.30 |
| 4 -0.10 |
| |
| --- 4 row(s) selected. |
| >>select a,round(c,1) from t101big where a < 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 12.60 |
| 4 0.10 |
| |
| --- 4 row(s) selected. |
| >>select a,round(d,1) from t101big where a < 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.00000000000000000000 |
| 2 999999999999999.90000000000000000000 |
| 3 -999999999999999.90000000000000000000 |
| 4 0.10000000000000000000 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- round with 2 parameters where the rounding value is a no-op |
| >>select a,round(b,2) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.34 |
| 2 23.45 |
| 3 -12.34 |
| 4 -0.12 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,2) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.34 |
| 2 23.45 |
| 3 12.55 |
| 4 0.12 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,20) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.01234567890123456789 |
| 2 999999999999999.90000000000000000000 |
| 3 -999999999999999.90000000000000000000 |
| 4 0.12000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round with 2 parameters where the rounding value is greater than scale; again a no-op |
| >>select a,round(b,3) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.34 |
| 2 23.45 |
| 3 -12.34 |
| 4 -0.12 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,3) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.34 |
| 2 23.45 |
| 3 12.55 |
| 4 0.12 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,40) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.01234567890123456789 |
| 2 999999999999999.90000000000000000000 |
| 3 -999999999999999.90000000000000000000 |
| 4 0.12000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round with 2 parameters with negative rounding value |
| >>select a,round(b,-1) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 10.00 |
| 2 20.00 |
| 3 -10.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,-1) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 10.00 |
| 2 20.00 |
| 3 10.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,-1) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1230.00000000000000000000 |
| 2 1000000000000000.00000000000000000000 |
| 3 -1000000000000000.00000000000000000000 |
| 4 0.00000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(b,-4) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 0.00 |
| 2 0.00 |
| 3 0.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,-4) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 0.00 |
| 2 0.00 |
| 3 0.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,-4) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 0.00000000000000000000 |
| 2 1000000000000000.00000000000000000000 |
| 3 -1000000000000000.00000000000000000000 |
| 4 0.00000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round with 2 parameters using a ridiculous but still sensible rounding value |
| >>select a,round(b,-100) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 0.00 |
| 2 0.00 |
| 3 0.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,-100) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 0.00 |
| 2 0.00 |
| 3 0.00 |
| 4 0.00 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,-100) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 0.00000000000000000000 |
| 2 0.00000000000000000000 |
| 3 0.00000000000000000000 |
| 4 0.00000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round using an integer column for the rounding value |
| >>select a,round(b,e) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 -12.30 |
| 4 -0.10 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,e) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 12.60 |
| 4 0.10 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,e) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.00000000000000000000 |
| 2 999999999999999.90000000000000000000 |
| 3 -999999999999999.90000000000000000000 |
| 4 0.10000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round using an integer decimal column for the rounding value |
| >>select a,round(b,g) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 -12.30 |
| 4 -0.10 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(c,g) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 12.30 |
| 2 23.50 |
| 3 12.60 |
| 4 0.10 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >>select a,round(d,g) from t101big where a <> 5; |
| |
| A (EXPR) |
| ----------- -------------------------------------- |
| |
| 1 1234.00000000000000000000 |
| 2 999999999999999.90000000000000000000 |
| 3 -999999999999999.90000000000000000000 |
| 4 0.10000000000000000000 |
| 6 ? |
| |
| --- 5 row(s) selected. |
| >> |
| >>-- round using a null value for second operand (from a column or a constant) |
| >>select a,round(b,null) from t101big; |
| |
| *** ERROR[4052] The second operand of function ROUND must be numeric. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>-- bind time error |
| >>update t101big set e = null; |
| |
| --- 6 row(s) updated. |
| >>select a,round(b,e) from t101big; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 ? |
| 2 ? |
| 3 ? |
| 4 ? |
| 5 ? |
| 6 ? |
| |
| --- 6 row(s) selected. |
| >> -- returns null values |
| >> |
| >>-- negative test: round using a non-integer decimal column |
| >>select a,round(b,f) from t101big; |
| |
| *** ERROR[4047] The operands of function ROUND must have a scale of 0. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- negative test: round using rounding values > max big num precision |
| >>select a,round(b,129) from t101big; |
| |
| *** ERROR[4054] The second operand of function ROUND may not be larger than 128. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- but -129 works fine |
| >>select a,round(b,-129) from t101big; |
| |
| A (EXPR) |
| ----------- ---------------------------------------------------------------------------------------------------------------------------------- |
| |
| 1 0.00 |
| 2 0.00 |
| 3 0.00 |
| 4 0.00 |
| 5 900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00 |
| 6 ? |
| |
| --- 6 row(s) selected. |
| >> |
| >>drop table t101big; |
| |
| --- 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. |
| >>#ifdef SEABASE_REGRESS |
| >>control query default DISABLE_READ_ONLY 'ON'; |
| |
| --- SQL operation complete. |
| >>#else |
| >>control query default COMP_BOOL_58 'ON'; |
| >>#endif |
| >>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; |
