Re: Correct getScale() behavior?

I am currently trying to move from the 8.1 JDBC driver to the 8.2
version. In my app, I have some code that checks the scale of a
NUMERIC, and if it is 0, then I convert the value into an Integer.
This code works fine on 8.1 and previous driver versions, but breaks
on 8.2 and 8.3.

I have looked into the driver source, and it seems like a change was
made in the 8.2+ drivers. When getting the result of an aggregate sum
(of NUMERIC(7,2) values) in 8.1 it returns -1 as the scale, but in 8.2
it returns 0. It looks like "-1" is meant to be taken as an undefined
value, which seems to be the correct behavior to me.

Is there a reason this was changed in the 8.2+ drivers or is this a
bug? I just need to find out, so I know whether to get rid of my
Integer-casting code.

-Dave

On Mon, 5 May 2008, Dave Dombrosky wrote:

> I have looked into the driver source, and it seems like a change was
> made in the 8.2+ drivers. When getting the result of an aggregate sum
> (of NUMERIC(7,2) values) in 8.1 it returns -1 as the scale, but in 8.2
> it returns 0. It looks like "-1" is meant to be taken as an undefined
> value, which seems to be the correct behavior to me.

This change was made because some ORM tools (some of the rowset stuff I
think) pulled the scale and stored it off regardless of whether it was
used or not. The storing code did "validation" that required it be >= 0.
So everyone using these frameworks was screwed whether they needed the
scale or not. So we changed it to keep them happy at the expense of
people like used based on a size of the camps measurement alone.

Kris Jurka

On May 8, 2:33 am, bo(dot)(dot)(dot)(at)ejurka(dot)com (Kris Jurka) wrote:
> On Mon, 5 May 2008, Dave Dombrosky wrote:
> > I have looked into the driver source, and it seems like a change was
> > made in the 8.2+ drivers.  When getting the result of an aggregate sum
> > (of NUMERIC(7,2) values) in 8.1 it returns -1 as the scale, but in 8.2
> > it returns 0.  It looks like "-1" is meant to be taken as an undefined
> > value, which seems to be the correct behavior to me.
>
> This change was made because some ORM tools (some of the rowset stuff I
> think) pulled the scale and stored it off regardless of whether it was
> used or not.  The storing code did "validation" that required it be >= 0.
> So everyone using these frameworks was screwed whether they needed the
> scale or not.  So we changed it to keep them happy at the expense of
> people like used based on a size of the camps measurement alone.

I believe the relevant thread where you made the change is here:
http://groups.google.com/group/pgsql.interfaces.jdbc/browse_thread/thread/c4e6778c8663584e/920ab518bb972973

I realize I'm not going to get you to change back to returning -1 for
the scale and precision, because a lot of users would be affected by
that. However, I am wondering if there is a way we could get the
actual precision and scale returned for an aggregate sum, and other
aggregate functions as well.

I would think it should be returning a scale and precision if the
argument column has scale and precision specified, such as in the
NUMERIC(7,2) example I previously showed. It seems wrong to return 0
in this case.

I am guessing the postgresql server code would need to be changed for
that to work though. Maybe I should be suggesting this change on a
different list?

-Dave

On Fri, 9 May 2008, dombrd(at)gmail(dot)com wrote:

> I realize I'm not going to get you to change back to returning -1 for
> the scale and precision, because a lot of users would be affected by
> that. However, I am wondering if there is a way we could get the
> actual precision and scale returned for an aggregate sum, and other
> aggregate functions as well.
>
> I would think it should be returning a scale and precision if the
> argument column has scale and precision specified, such as in the
> NUMERIC(7,2) example I previously showed. It seems wrong to return 0
> in this case.
>
> I am guessing the postgresql server code would need to be changed for
> that to work though. Maybe I should be suggesting this change on a
> different list?
>

Right, that change would be nice, but it would require a server change.
The best place to ask about that would be -hackers. Also how do you
determine the precision of sum(numeric(7,2))?

Kris Jurka

Kris Jurka <books(at)ejurka(dot)com> writes:
> On Fri, 9 May 2008, dombrd(at)gmail(dot)com wrote:
>> I would think it should be returning a scale and precision if the
>> argument column has scale and precision specified, such as in the
>> NUMERIC(7,2) example I previously showed. It seems wrong to return 0
>> in this case.
>>
>> I am guessing the postgresql server code would need to be changed for
>> that to work though. Maybe I should be suggesting this change on a
>> different list?

> Right, that change would be nice, but it would require a server change.
> The best place to ask about that would be -hackers. Also how do you
> determine the precision of sum(numeric(7,2))?

The chances of anything being done about that are not measurably
different from zero. Postgres is by and large an abstract-data-type
system that tries hard not to have too much knowledge about specific
data types, let alone specific functions for specific data types,
wired into the core server. So even assuming that you could determine
the correct typmod to return (which, as Kris implies, you really cannot
for this example or most others), any sort of quick-hack patch to
inject the knowledge would probably be rejected. We'd want to see it
formulated as a plugin API available to all datatypes and functions
... and the effort to do that, in comparison to the number of actual
use cases, doesn't look very promising.

regards, tom lane

Kris Jurka написав(ла):
>
> Right, that change would be nice, but it would require a server
> change. The best place to ask about that would be -hackers. Also how
> do you determine the precision of sum(numeric(7,2))?
I suppose the answer is: "As always, by function definition":
9.18. Aggregate Functions

|sum(/expression/)

|bigint for smallint or int arguments, numeric for bigint arguments,
double precision for floating-point arguments, otherwise the same as the
argument data type

So, sum(numeric(7,2)) must be numeric(7,2). If result do not fit, sql
code must be raised.