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 9C74AD1B1CE for ; Sat, 3 Jul 2004 05:45:47 -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 98929-04 for ; Sat, 3 Jul 2004 08:45:47 +0000 (GMT) Received: from mail63.csoft.net (leary3.csoft.net [63.111.22.74]) by svr1.postgresql.org (Postfix) with SMTP id C411DD1B379 for ; Sat, 3 Jul 2004 05:45:43 -0300 (ADT) Received: (qmail 18477 invoked by uid 1112); 3 Jul 2004 08:45:45 -0000 Date: Sat, 3 Jul 2004 03:45:45 -0500 (EST) From: Kris Jurka X-X-Sender: books@leary.csoft.net To: software@sistemat.com.ar Cc: pgsql-jdbc@postgresql.org Subject: Re: Error in DatabaseMetaData.getColumns() with Views In-Reply-To: <40E6088E.6030703@yahoo.com.ar> Message-ID: References: <40E6088E.6030703@yahoo.com.ar> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/25 X-Sequence-Number: 10409 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