Re: Error in DatabaseMetaData.getColumns() with Views

On Fri, 2 Jul 2004, Dario Fassi wrote:

> Hi, I wish to report a erroneous information returned by   
> DatabaseMetaData.getColumns()  method.
> 
> This happens with ResultSet's column (7) COLUMN_SIZE  and  (9) 
> DECIMAL_DIGITS ,
> when DatabaseMetaData.getColumns() inform about a VIEW with columns 
> formed with :
> coalesce , case or numeric operations over DECIMAL fields.
> 
> Suppose
> 
> CREATE TABLE A (  f1 DEC(6,3), f2 dec(6,3) );
> CREATE VIEW B as ( select ( f1 + f2 ) as f from a;
> 
> Then DatabaseMetaData.getColumns() returns:
> 
> VIEW B
>     F   NUMERIC(  65535 ,  -65531 )
> 

The problem is that f1+f2 does not retain the numeric(6,3) size 
restriction, but turns into an unbounded plain "numeric" datatype.  So 
when retrieving this data the precision/scale are unavailable and the 
unreasonable values you see are returned.  We could return NULL instead, 
but I'm not sure that would be much more helpful if the client is 
expecting real values.  Any other ideas?

Kris Jurka