Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

From: "Dario V(dot) Fassi" <software(at)sistemat(dot)com(dot)ar>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Kris Jurka <books(at)ejurka(dot)com>, pgsql-jdbc(at)postgresql(dot)org, pgsql-bugs(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Date: 2004-07-05 01:10:50
Message-ID: 40E8AA9A.7020806@sistemat.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-jdbc pgsql-patches pgsql-sql

Stephan, look at the samples I send in previous posts , from PgSql and Db2.

I know it's a no ease task to change all that behavior , but you must
agree that a Numeric column with the fractional part varing from row to
row are different data types and break relational rules.
If this is the case the returning data type must be a floating point
data type like a double, but never 2 decimals for a row and 3, 4 or
whatever for others rows.

Stephan Szabo wrote:

>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.
>
>

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2004-07-05 01:41:29 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Previous Message Stephan Szabo 2004-07-05 00:46:26 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

Browse pgsql-jdbc by date

  From Date Subject
Next Message Stephan Szabo 2004-07-05 01:41:29 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Previous Message Stephan Szabo 2004-07-05 00:46:26 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

Browse pgsql-patches by date

  From Date Subject
Next Message Michael Glaesemann 2004-07-05 01:19:44 Type typo in parameter of pgsql function
Previous Message Stephan Szabo 2004-07-05 00:46:26 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2004-07-05 01:41:29 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Previous Message Stephan Szabo 2004-07-05 00:46:26 Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views