Обсуждение: BUG #15812: Select statement of a very big number, with a division operator seems to round up.
BUG #15812: Select statement of a very big number, with a division operator seems to round up.
От
PG Bug reporting form
Дата:
The following bug has been logged on the website:
Bug reference: 15812
Logged by: Kaleb Akalework
Email address: kaleb.akalework@asg.com
PostgreSQL version: 11.3
Operating system: Windows/Linux
Description:
I have a need to divide a big number numeric(20) by 10000000000 to feed it
into a floor function. The division operation rounds up the number which
causes problems. I need the division to just divide the number without
rounding up or down. For my purposes 3691635539999999999/10000000000 should
return 369163553.9999999999 not 369163554. This happens if the data is
retrieved from a column. Below are queries to reproduce the problem
create table test_table
(
REQUEST_UUID varchar(50) not null,
BIG_NUM numeric(20,0) not null
);
INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
3691635539999999999);
INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
3691635530099999999);
INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
3691635530999999999);
SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000 from
test_table;
The following is the result of the above select. you can see that column 2
for first row was rounded up.
"3691635539999999999" "369163554" "369163554.00000000"
"3691635530099999999" "369163553" "369163553.01000000"
"3691635530999999999" "369163553" "369163553.10000000"
On 2019-May-17, PG Bug reporting form wrote:
> create table test_table
> (
> REQUEST_UUID varchar(50) not null,
> BIG_NUM numeric(20,0) not null
> );
>
> INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
> 3691635539999999999);
> INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
> 3691635530099999999);
> INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
> 3691635530999999999);
>
> SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000 from
> test_table;
Well, your column definition has room for zero decimal places, so I'm
not sure this result is all that surprising. Maybe you should cast the
column to one that has a few decimal places, say
select bit_num::numeric(30,10) / 10000000000 from test_table;
and see whether that helps your case.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
RE: BUG #15812: Select statement of a very big number, with adivision operator seems to round up.
От
Kaleb Akalework
Дата:
Hi Alvaro,
Thank you for getting back to me. Well that is not correct the column is defined as numeric(20,0) because we don't
expectdecimal numbers in the column,
but operations on the value should not be dictated by the column definition. My table has millions of rows and cannot
changethe table definition due to number of rows and business purposes. The question is why is the result of the
operationdictated by the column definition?
If you just did select (3691635539999999999/10000000000) you would get the correct result minus all the numbers after
thedecimal point
SELECT (3691635539999999999/10000000000)
"369163553"
This seems to be bug, no? I have data centers with SQL Server and Oracle and they don't exhibit this behavior
Thank you again for getting back to me quickly. Looking forward to hearing from you
Thank you
Kaleb Akalework
-----Original Message-----
From: Alvaro Herrera <alvherre@2ndquadrant.com>
Sent: Friday, May 17, 2019 12:02 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up.
*** External email: Verify sender before opening attachments or links ***
On 2019-May-17, PG Bug reporting form wrote:
> create table test_table
> (
> REQUEST_UUID varchar(50) not null,
> BIG_NUM numeric(20,0) not null
> );
>
> INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
> 3691635539999999999); INSERT INTO test_table (REQUEST_UUID, BIG_NUM)
> values('TEST', 3691635530099999999); INSERT INTO test_table
> (REQUEST_UUID, BIG_NUM) values('TEST', 3691635530999999999);
>
> SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000 from
> test_table;
Well, your column definition has room for zero decimal places, so I'm not sure this result is all that surprising.
Maybeyou should cast the column to one that has a few decimal places, say
select bit_num::numeric(30,10) / 10000000000 from test_table; and see whether that helps your case.
--
Álvaro Herrera
http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiY2MjA1YzY5ZWNiMmRjZTgwOD01Q0RFREIwOF82NDEyOV8yOTEwXzEmJjNkYTNlNmVlYTQ1MDQwMT0xMjMyJiZ1cmw9aHR0cHMlM0ElMkYlMkZ3d3clMkUybmRRdWFkcmFudCUyRWNvbSUyRg==
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi, (on postgresql lists please quote emails nicely, and trip irrelevant pieces) On 2019-05-17 16:10:52 +0000, Kaleb Akalework wrote: > Thank you for getting back to me. Well that is not correct the column is defined as numeric(20,0) because we don't expectdecimal numbers in the column, > but operations on the value should not be dictated by the column > definition. My table has millions of rows and cannot change the table > definition due to number of rows and business purposes. The question > is why is the result of the operation dictated by the column > definition? It doesn't have to be the column division - you could just indicate the desired precision in the divisor. I'd assume that SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000::numeric(21,10)), BIG_NUM/10000000000::numeric(21,10) from test_table; would give you precisely the result you waant? > If you just did select (3691635539999999999/10000000000) you would get the correct result minus all the numbers after thedecimal point > > SELECT (3691635539999999999/10000000000) > > "369163553" > > This seems to be bug, no? That's just because the types here assumed to be bigint (i.e. 64bit integers): postgres[22538][1]=# SELECT pg_typeof(3691635539999999999), pg_typeof(10000000000), pg_typeof(3691635539999999999/10000000000),3691635539999999999/10000000000; ┌───────────┬───────────┬───────────┬───────────┐ │ pg_typeof │ pg_typeof │ pg_typeof │ ?column? │ ├───────────┼───────────┼───────────┼───────────┤ │ bigint │ bigint │ bigint │ 369163553 │ └───────────┴───────────┴───────────┴───────────┘ (1 row) Greetings, Andres Freund
Re: BUG #15812: Select statement of a very big number, with adivision operator seems to round up.
От
"David G. Johnston"
Дата:
On Fri, May 17, 2019 at 9:11 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote:
The question is why is the result of the operation dictated by the column definition?
Because PostgreSQL, and SQL in general, is a typed language and the output of the division operation is defined to be of the exact same type as its inputs. Since you are dividing:
numeric(20,0) / bigint
PostgreSQL converts that to:
numeric(20,0) / numeric(20,0) = numeric(20,0)
Then applies the rules for rounding a scaled value to an unscaled one (i.e., away from half) to the result.
Writing:
numeric / bigint = numeric (same scale/precision as the numeric value)
Basically ends up the same since for these particular values the scale of the input is 0 and so the scale of the output is also 0 (TBH, I'm a bit confused writing this in face of third column's result...)
Thus:
numeric(30,10) / bigint = numeric(30,10)
Also...
SELECT 3691635539999999999/10000000000
is
bigint / bigint = bigint (with fractional truncation instead of rounding)
David J.
Hi,
On 2019-05-17 12:02:11 -0400, Alvaro Herrera wrote:
> On 2019-May-17, PG Bug reporting form wrote:
>
> > create table test_table
> > (
> > REQUEST_UUID varchar(50) not null,
> > BIG_NUM numeric(20,0) not null
> > );
> >
> > INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
> > 3691635539999999999);
> > INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
> > 3691635530099999999);
> > INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values('TEST',
> > 3691635530999999999);
> >
> > SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000 from
> > test_table;
>
> Well, your column definition has room for zero decimal places, so I'm
> not sure this result is all that surprising. Maybe you should cast the
> column to one that has a few decimal places, say
> select bit_num::numeric(30,10) / 10000000000 from test_table;
> and see whether that helps your case.
Arguably it's less the column's and more the divisor's precision that's
the problem. Note that even if big_num were numeric (i.e. without an
implied precision) you'd get the OP's results - the precision is not
"widened" to the appropriate width for the max precision needed for the
division.
Greetings,
Andres Freund
RE: BUG #15812: Select statement of a very big number, with adivision operator seems to round up.
От
Kaleb Akalework
Дата:
Hi,
Thank you Alvaro, Andres and David.
I tried Andres suggestion in his last email and that seems to work as a work around. Please see below.
SELECT BIG_NUM, FLOOR(BIG_NUM /10000000000.0000000000), BIG_NUM/10000000000.0000000000 from test_table
"3691635539999999999" "369163553" "369163553.9999999999"
"3691635530099999999" "369163553" "369163553.0099999999"
"3691635530999999999" "369163553" "369163553.0999999999"
But should this be a bug? Can their a better support of this, instead of having the query writer to know how many
decimalnumbers to put to get the correct type?
It seems a little awkward and error prone to have to type .0000.... etc?
Thank you all again for the fast response.
Kaleb Akalework
-----Original Message-----
From: Andres Freund <andres@anarazel.de>
Sent: Friday, May 17, 2019 12:24 PM
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15812: Select statement of a very big number, with a division operator seems to round up.
*** External email: Verify sender before opening attachments or links ***
Hi,
On 2019-05-17 12:02:11 -0400, Alvaro Herrera wrote:
> On 2019-May-17, PG Bug reporting form wrote:
>
> > create table test_table
> > (
> > REQUEST_UUID varchar(50) not null,
> > BIG_NUM numeric(20,0) not null
> > );
> >
> > INSERT INTO test_table (REQUEST_UUID, BIG_NUM) values ('TEST',
> > 3691635539999999999); INSERT INTO test_table (REQUEST_UUID, BIG_NUM)
> > values('TEST', 3691635530099999999); INSERT INTO test_table
> > (REQUEST_UUID, BIG_NUM) values('TEST', 3691635530999999999);
> >
> > SELECT BIG_NUM, FLOOR(BIG_NUM/10000000000), BIG_NUM/10000000000
> > from test_table;
>
> Well, your column definition has room for zero decimal places, so I'm
> not sure this result is all that surprising. Maybe you should cast
> the column to one that has a few decimal places, say
> select bit_num::numeric(30,10) / 10000000000 from test_table; and
> see whether that helps your case.
Arguably it's less the column's and more the divisor's precision that's the problem. Note that even if big_num were
numeric(i.e. without an implied precision) you'd get the OP's results - the precision is not "widened" to the
appropriatewidth for the max precision needed for the division.
Greetings,
Andres Freund
On Fri, 17 May 2019 at 17:36, Kaleb Akalework <kaleb.akalework@asg.com> wrote: > > I tried Andres suggestion in his last email and that seems to work as a work around. Please see below. > > SELECT BIG_NUM, FLOOR(BIG_NUM /10000000000.0000000000), BIG_NUM/10000000000.0000000000 from test_table > > "3691635539999999999" "369163553" "369163553.9999999999" > "3691635530099999999" "369163553" "369163553.0099999999" > "3691635530999999999" "369163553" "369163553.0999999999" > > But should this be a bug? Can their a better support of this, instead of having the query writer to know how many decimalnumbers to put to get the correct type? > > It seems a little awkward and error prone to have to type .0000.... etc? > I would suggest using the numeric div() function, which divides a pair of numeric values, and returns the truncated integer result. I.e., div(big_num, 10000000000). For example: SELECT div(3691635539999999999::numeric(20, 0), 10000000000); returns 369163553. See https://www.postgresql.org/docs/current/functions-math.html Regards, Dean
>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes: PG> I have a need to divide a big number numeric(20) by 10000000000 to PG> feed it into a floor function. The division operation rounds up the PG> number which causes problems. I need the division to just divide PG> the number without rounding up or down. For my purposes PG> 3691635539999999999/10000000000 should return 369163553.9999999999 PG> not 369163554. This happens if the data is retrieved from a column. There seems to have been a bit of confusion in the prior responses here. The first thing to understand is that numeric/numeric _must_ in general round the result to _some_ precision, since otherwise the output of 1.0/3.0 would be infinitely long. (Whereas numeric addition, subtraction, and multiplication can always give exact results.) The question is how many digits of precision the division function chooses. The documentation seems to be silent on this; the code says: * The result scale of a division isn't specified in any SQL standard. For * PostgreSQL we select a result scale that will give at least * NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a * result no less accurate than float8; but use a scale not less than * either input's display scale. NUMERIC_MIN_SIG_DIGITS is defined to be 16. So here you're dividing 3691635539999999999::numeric, which has a display scale of 0, by 10000000000::numeric, which also has a display scale of 0. 369163553.9999999999 is 19 significant digits; the chosen result scale is 8 because that gives at least the minimum 16 significant digits. (Now, it can be argued that PG's choice of result scale for division is more surprising than it could be. But coming up with a non-surprising rule is not easy.) When a numeric value comes from a table column that has a declared scale, like numeric(20,0), then the value always has the specified scale. You can force the scale to a specific value using round(x,n) (usually more convenient than adding a ::numeric(blah,n) cast). For your example, it might be more convenient to do: select BIG_NUM*(1.0/10000000000) ... which will always give exact results when the divisor is a power of 10. -- Andrew (irc:RhodiumToad)