Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbcpgsql-patchespgsql-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

pgsql-sql by date

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

pgsql-jdbc by date

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

pgsql-bugs by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group