Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views - Mailing list pgsql-bugs

From Stephan Szabo
Subject Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Date
Msg-id 20040704173258.Y55353@megazone.bigpanda.com
Whole thread Raw
In response to Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views  (Kris Jurka <books@ejurka.com>)
Responses Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views  ("Dario V. Fassi" <software@sistemat.com.ar>)
List pgsql-bugs
On Sun, 4 Jul 2004, Dario V. Fassi wrote:

>
>
> Stephan Szabo wrote:
>
> >On Sun, 4 Jul 2004, Kris Jurka wrote:
> >
> >
> >
> >>On Sat, 3 Jul 2004, Dario V. Fassi wrote:
> >>
> >>
> >>
> >>>In the sample adjunct, you can see that error arise at the time when the
> >>>view's sql text is parsed and saved in database catalog.
> >>>Then generic NUMERIC type is forced for every calculated column without
> >>>regard or precision.
> >>>And at execute time the f2 column has varying type decimals (in row 2
> >>>you can see 4 decimals and in other rows has 3 decimals), this is not a
> >>>behavior , this is an ERROR.
> >>>
> >>>
> >
> >[Jumping in, because this was the first message of the thread I've seen]
> >
> >Technically, the correct behavior by spec would be an
> >implementation-defined precision and a particular scale based on the
> >argument scales. So, having numeric(6,2)+numeric(6,2) return
> >numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
> >technically, but I don't think this is an issue in the jdbc metadata
> >getting as much as an issue in the database proper.
> >
>
> I agree with all your spech but a  numeric data type like numeric(65535,
> 2) isn't a very serious return value.

Why not?  If 65535 were the maximal numeric precision (also
implementation-defined IIRC) then it's a reasonable answer, although it
doesn't give a user much information about the expected result range, but
AFAICS that isn't one of the expected properties.

> A value like this could be usefull to eliminate the need of presicion
> specification in numeric data type too.
> Of course it's a dabase proper issue , but impact in my needs in jdbc
> usability.

Since I'm not on -jdbc, I didn't see how it was intended to be used, but I
think it'd be a portability bug to expect it to return only the precision
that the result could take.

Now, the current results are fairly broken because the precision value has
no connection to reality (it's not the maximum precision, and might in
fact be smaller than the actual precision in some absurd cases) and the
scale is wrong if one follows spec.  I'm not sure the jdbc driver can do
much better given the backend right now.

> No matter what's technically or not, a behavior at this point like those
> of db2/oracle would be very nice ;-)

Probably true.  But I don't think it's likely to happen any time soon
unless someone steps up and takes responsibility for making it happen. I
think it'd also be non-trivial for the general case since I think it'd
have to mean that arbitrary functions would have to be able to have some
sort of way of specifying the values for its output.

pgsql-bugs by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Next
From: Stephan Szabo
Date:
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views