X-Original-To: pgsql-jdbc-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8F5CAD1B383 for ; Sat, 3 Jul 2004 13:58:32 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 23594-06 for ; Sat, 3 Jul 2004 16:58:31 +0000 (GMT) Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22]) by svr1.postgresql.org (Postfix) with ESMTP id 870D0D1B38F for ; Sat, 3 Jul 2004 13:58:28 -0300 (ADT) Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40]) by mail.svhost.com.ar (Postfix) with ESMTP id 368E8117EF for ; Sat, 3 Jul 2004 13:57:21 -0300 (ART) Message-ID: <40E6E7AF.5020306@sistemat.com.ar> Date: Sat, 03 Jul 2004 14:06:55 -0300 From: "Dario V. Fassi" Organization: SISTEMATICA ingenieria de software srl User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: es, es-ar, en-us, en MIME-Version: 1.0 Cc: pgsql-jdbc@postgresql.org Subject: Re: Error in DatabaseMetaData.getColumns() with Views References: <40E6088E.6030703@yahoo.com.ar> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000604030001010303090101" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200407/28 X-Sequence-Number: 10412 This is a multi-part message in MIME format. --------------000604030001010303090101 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Kris Jurka wrote: >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" data type. 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 > > Yes, a few. In the tool named PgManage (come with the commercial version of Mammoth) , the information is accurate values for this MetaData, and I believe, they get that values from pqsql catalog tables and not from DatabaseMetaData interface. More even, the engine resolve the View properly and return data values properly typed (engine return 999.999 for f1+f2 and 999.999999 for f1*f2 , like is expected). So, the information if know or derived en some way for the engine , and/or is contained some where in catalog's tables. I'm working in CodeGeneration tools for many DB engines (DB2, Oracle, MS-Sql, PostgreSql, etc) , and it's impossible not to use generic DatabaseMetaData interface to obtain metadata information. So this is a very important problem for me. DB2 for example do, data type escalation based on "Error propagation Theory " , that has rules (I'm not and expert in the field) like : [dec(6,a) + dec(6,b) ] -> [ dec( 6, max(a,b) ) ] [ coalesce( dec(6,a) , dec(6,b) ] -> [ dec[ 6, max(a,b)] ] [ case( dec(6,a) , dec(6,b), dec(6,c), dec(6,d) ] -> [ dec[ 6, max(a,b,c,d)] ] [dec(6,a) * dec(6,b ) -> [ dec( 6,a+b ) ] etc. This rules are taken into account in the engine data formation process, but there are stored some where ??? Thanks for your answer. Dario V. Fassi --------------000604030001010303090101 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
Kris Jurka wrote:
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" data type.  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
Yes, a few.

In the tool named PgManage (come with the commercial version of Mammoth) ,  the information is accurate values for this MetaData, and I believe, they get that values from pqsql catalog tables and not from DatabaseMetaData interface.

More even, the engine resolve the View properly and return data values properly typed  (engine return 999.999  for f1+f2  and  999.999999 for f1*f2  , like is expected).
So, the information if know or derived en some way for the engine , and/or is contained some where in catalog's tables.

I'm working in CodeGeneration tools for many DB engines (DB2, Oracle, MS-Sql, PostgreSql, etc) , and it's impossible not to use generic DatabaseMetaData interface to obtain metadata information. So this is a very important problem for me.

DB2 for example do, data type escalation based on "Error propagation Theory "  , that has rules (I'm not and expert in the field) like :

[dec(6,a) + dec(6,b) ] -> [ dec( 6, max(a,b) ) ]
[ coalesce( dec(6,a) , dec(6,b) ] -> [ dec[ 6, max(a,b)] ]
[ case( dec(6,a) , dec(6,b), dec(6,c), dec(6,d) ] -> [ dec[ 6, max(a,b,c,d)] ]
[dec(6,a) * dec(6,b ) -> [ dec( 6,a+b ) ]
etc.

This rules are taken into account in the engine data formation process, but there are stored some where ???

Thanks for your answer.
Dario V. Fassi

--------------000604030001010303090101--