Thread: 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...@ejurka.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@gmail.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@ejurka.com> writes: > On Fri, 9 May 2008, dombrd@gmail.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.