From: Dario V. Fassi <software@sistemat.com.ar>
To: pgsql-jdbc@postgresql.org
To: pgsql-bugs@postgresql.org
To: pgsql-sql@postgresql.org
To: pgsql-patches@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
Date: Sat, 03 Jul 2004 15:39:59 -0300
Message-ID: <40E6FD7F.50801@sistemat.com.ar> (raw)
In-Reply-To: <40E6E7AF.5020306@sistemat.com.ar>
References: <40E6088E.6030703@yahoo.com.ar>
<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
<40E6E7AF.5020306@sistemat.com.ar>
I have found that the problem is worst.
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.
Precision calculation in Numeric fields it's not so difficult.
This problem *must* be corrected in the CREATE VIEW sentence ,
and I offer my collaboration for that.
Regards all,
Dario Fassi.
Dario V. Fassi wrote:
>
> 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
>
Attachments:samp.txt (text/plain, 1.9K) download
CREATE TABLE public.t (
f1 numeric(6,3),
f2 numeric(6,4),
f3 numeric(8,2)
) WITHOUT OIDS;
insert into public.t values ( 123.123, 12.1234, 12345.12);
insert into public.t values ( null, 12.1234, 12345.12);
insert into public.t values ( 123.123, null, 12345.12);
insert into public.t values ( 123.123, 12.1234, null);
--
-- Create sql text
--
create view public v as
select
(f1+f2+f3) as fsum,
coalesce(f1,f2,f3) as fcoal,
(f1*f2*f3) as fprod
from public.t;
--
-- Parsed sql view from catalog
--
CREATE OR REPLACE VIEW public.v AS
SELECT ((t.f1 + t.f2 ) + t.f3 ) AS fsum,
CASE WHEN (t.f1 IS NOT NULL ) THEN t.f1
WHEN (t.f2 IS NOT NULL ) THEN t.f2
WHEN (t.f3 IS NOT NULL ) THEN t.f3
ELSE NULL::numeric
END AS fcoal,
((t.f1 * t.f2 ) * t.f3 ) AS fprod FROM t;
--
-- Returned Data
--
select * from public.v;
fsum fcoal fprod
12480.3664 123.123 18427182.594204384
null 12.1234 null << !!! No look good !!!
null 123.123 null
null 123.123 null
--
--
-- WORKAROUND WORKAROUND WORKAROUND
--
--
--
-- Create sql text with forced datatype
--
create view public v2 as
select
cast( (f1+f2+f3) as dec(9,4) ) as fsum,
cast( coalesce(f1,f2,f3) as dec(7,4) ) as fcoal,
cast( (f1*f2*f3) as dec(20,9) ) as fprod
from public.t;
--
-- Parsed sql view from catalog
--
CREATE OR REPLACE VIEW public.v2 AS
SELECT
(((t.f1 + t.f2 ) + t.f3))::numeric(9,4 ) AS fsum,
( CASE
WHEN (t.f1 IS NOT NULL ) THEN t.f1
WHEN (t.f2 IS NOT NULL ) THEN t.f2
WHEN (t.f3 IS NOT NULL ) THEN t.f3
ELSE NULL::numeric END)::numeric(7,4 ) AS fcoal,
(((t.f1 * t.f2 ) * t.f3))::numeric(20,9 ) AS fprod
FROM t;
--
-- Returned Data
--
select * from public.v2;
fsum fcoal fprod
12480.3664 123.1230 18427182.594204384
null 12.1234 null << It's ok now
null 123.1230 null
null 123.1230 null
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: software@sistemat.com.ar, pgsql-jdbc@postgresql.org, pgsql-bugs@postgresql.org, pgsql-patches@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
In-Reply-To: <40E6FD7F.50801@sistemat.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox