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: Kris Jurka <books(at)ejurka(dot)com>
Cc: 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-04 20:58:17
Message-ID: 40E86F69.3050100@sistemat.com.ar (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbcpgsql-patchespgsql-sql
kris,
Next you can see how the same test-case is handled by DB2.

-- IBM DB2 V6.1 for Linux

CREATE TABLE userid.t (
 f1 numeric(6,3),
 f2 numeric(6,4),
 f3 numeric(8,2)
);

create view userid.v as
select
(f1+f2+f3) as fsum,
coalesce(f1,f2,f3) as fcoal,
(f1*f2*f3) as fprod,
(f1/f2) as fdiv
from userid.t;

insert into userid.t values ( 123.123, 12.1234, 12345.12);
insert into userid.t values ( null, 12.1234, 12345.12);
insert into userid.t values ( 123.123, null, 12345.12);
insert into userid.t values ( 123.123, 12.1234, null);
insert into userid.t values ( 999.999, 99.9999, 99999.99);

select * from userid.v

 FSUM        | FCOAL    | FPROD                | 
FDIV                        |
------------------------------------------------------------------------------
 12480.3664  | 123.1230 | 18427182.594204384   | 
10.155814375505221307553986 |
------------------------------------------------------------------------------
             | 12.1234  |                      
|                             |
------------------------------------------------------------------------------
             | 123.1230 |                      
|                             |
------------------------------------------------------------------------------
             | 123.1230 |                      | 
10.155814375505221307553986 |
------------------------------------------------------------------------------
 101099.9889 | 999.9990 | 9999979000.011999999 | 
10.000000000000000000000000 |
------------------------------------------------------------------------------



METADATA RETURNED BY DatabaseMetaData.getColumns()

Tabla: USERID.V
#      Campo          Tipo          Anulable ReadOnly Writable AutoInc 
CaseSens Currency
1     FSUM [0]        DECIMAL(11,4)  . . WA . . .
2     FCOAL [0]       DECIMAL(10,4)  . . WA . . .
3     FPROD [0]       DECIMAL(20,9)  . . WA . . .
4     FDIV [0]        DECIMAL(31,24)  . . WA . . .
4 Campos.

------------------------------------------
#  Campo  Tipo  nTy  Nulable  Descripcion 
1 FSUM DECIMAL(11,4) B10 3     
2 FCOAL DECIMAL(10,4) B10 3     
3 FPROD DECIMAL(20,9) B10 3     
4 FDIV DECIMAL(31,24) B10 3     
------------------------------------------

The work-around is posible , if you are working from start and you are 
warned about the problem.
But if you are porting a data model you must to reconstuct all views.

A better solution to return (-1 or 65535) as the length or decs of a 
numeric field could be to do dataType scalation to a DOUBLE data type.
This is not a good solution but better than now.
The optimal solution es make presicion calculation for every column or 
almost the trivial cases like columns formed by  "coalesce", "case 
then", etc.

The overflow or "data truncation" exception , for views, it's a going 
back , but almost put the problem in programmer's hands.

Regards,
Dario.

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.
>>    
>>
>
>It isn't clear that an operation like + should retain the same size
>restrictions as it's arguments.  Consider adding two numeric(6,2) values
>of 9999.99, how do you handle the overflow? Your other arguments about the
>sizing of derived columns may make sense for your application, but it is
>unlikely that they make sense for all users.  Note that you can put a cast
>into your view definition like so:
>
>CREATER VIEW v AS SELECT (a+b)::numeric(6,2) FROM tab;
>
>Kris Jurka
>
>
>
>  
>

-- 

    Dario V. Fassi

SISTEMATICA ingenieria de software  srl
<http://www.sistemat.com.ar>Ituzaingo 1628  (2000)  Rosario, Santa Fe, 
Argentina.
Tel / Fax:  +54 (341) 485.1432 / 485.1353



In response to

pgsql-sql by date

Next:From: Markus BertheauDate: 2004-07-04 21:21:53
Subject: Why no exprs in format string to RAISE
Previous:From: Kris JurkaDate: 2004-07-04 19:16:56
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

pgsql-jdbc by date

Next:From: Stephan SzaboDate: 2004-07-04 22:51:21
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Previous:From: Kris JurkaDate: 2004-07-04 19:16:56
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2004-07-04 22:51:21
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Previous:From: Kris JurkaDate: 2004-07-04 19:16:56
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views

pgsql-patches by date

Next:From: Andrew DunstanDate: 2004-07-04 21:06:06
Subject: Re: [HACKERS] Compile failure in plperl
Previous:From: Bruce MomjianDate: 2004-07-04 20:23:25
Subject: Re: [HACKERS] Compile failure in plperl

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