From dariovfassi@yahoo.com.ar Sat Jul  3 01:14:58 2004
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 30D2DD1B1CD
	for <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	Fri,  2 Jul 2004 22:14:58 -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 63532-03
	for <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	Sat,  3 Jul 2004 01:14:57 +0000 (GMT)
Received: from smtp107.mail.sc5.yahoo.com (smtp107.mail.sc5.yahoo.com
	[66.163.169.227])
	by svr1.postgresql.org (Postfix) with SMTP id 10EA2D1B174
	for <pgsql-jdbc@postgresql.org>; Fri,  2 Jul 2004 22:14:53 -0300 (ADT)
Received: from unknown (HELO yahoo.com.ar) (dariovfassi@200.80.239.18 with
	plain)
	by smtp107.mail.sc5.yahoo.com with SMTP; 3 Jul 2004 01:14:56 -0000
Message-ID: <40E6088E.6030703@yahoo.com.ar>
Date: Fri, 02 Jul 2004 22:14:54 -0300
From: Dario Fassi <dariovfassi@yahoo.com.ar>
Reply-To: software@sistemat.com.ar
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.0.2) Gecko/20030208 Netscape/7.02
X-Accept-Language: es-ar, es, en
MIME-Version: 1.0
To: pgsql-jdbc@postgresql.org
Subject: Error in DatabaseMetaData.getColumns() with Views
Content-Type: multipart/alternative;
	boundary="------------010207020205090608060300"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30,
	HTML_MESSAGE, HTML_TITLE_EMPTY
X-Spam-Level: *
X-Archive-Number: 200407/22
X-Sequence-Number: 10406

--------------010207020205090608060300
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 )

This error its very molest because if you are trying to do 
CodeGeneration from DatabaseMetaData.

Any suggestion will be appreciated.
Best regards,
Dario Fassi

--------------010207020205090608060300
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Hi, I wish to report a erroneous information returned by&nbsp;&nbsp; <b>DatabaseMetaData.getColumns()</b>&nbsp; 
method.<br>
 <br>
 This happens with ResultSet's column (7) COLUMN_SIZE&nbsp; and&nbsp; (9) DECIMAL_DIGITS
, <br>
 when DatabaseMetaData.getColumns() inform about a VIEW with columns formed
with :<br>
 coalesce , case or numeric operations over DECIMAL fields.<br>
 <br>
 Suppose <br>
 <br>
 CREATE TABLE A (&nbsp; f1 DEC(6,3), f2 dec(6,3) );<br>
 CREATE VIEW B as ( select ( f1 + f2 ) as f from a;<br>
 <br>
 Then DatabaseMetaData.getColumns() returns:<br>
 <br>
 VIEW B <br>
 &nbsp;&nbsp;&nbsp; F&nbsp;&nbsp; NUMERIC(&nbsp; 65535 ,&nbsp; -65531 )<br>
 <br>
 This error its very molest because if you are trying to do CodeGeneration
from DatabaseMetaData.<br>
 <br>
 Any suggestion will be appreciated.<br>
  Best regards,<br>
  <i>Dario Fassi</i><small><small><font
 face="Helvetica, Arial, sans-serif"><big><br>
 </big></font></small></small>
</body>
</html>

--------------010207020205090608060300--




From books@ejurka.com Sat Jul  3 08:45:47 2004
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 <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-jdbc@postgresql.org>; 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 <books@ejurka.com>
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: <Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
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




From software@sistemat.com.ar Sat Jul  3 16:58:32 2004
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 <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-jdbc-postgresql.org@localhost.postgresql.org>;
	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 <pgsql-jdbc@postgresql.org>; 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 <pgsql-jdbc@postgresql.org>; 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" <software@sistemat.com.ar>
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>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
In-Reply-To: <Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<br>
Kris Jurka wrote:<br>
<blockquote type="cite"
 cite="midPine.BSO.4.56.0407030343090.19721@leary.csoft.net">
  <pre wrap="">On Fri, 2 Jul 2004, Dario Fassi wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">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 )

    </pre>
  </blockquote>
  <pre wrap=""><!---->
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
  </pre>
</blockquote>
Yes, a few.<br>
<br>
In the tool named PgManage (come with the commercial version of
Mammoth) ,&nbsp; the information is accurate values for this MetaData, and I
believe, they get that values from pqsql catalog tables and not
from DatabaseMetaData interface.<br>
<br>
More even, the engine resolve the View properly and return data values
properly typed&nbsp; (engine return 999.999&nbsp; for f1+f2&nbsp; and&nbsp; 999.999999 for
f1*f2&nbsp; , like is expected).<br>
So, the information if know or derived en some way for the engine
, and/or is contained some where in catalog's tables.<br>
<br>
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.<br>
<br>
DB2 for example do, data type escalation based on "Error propagation
Theory "&nbsp; , that has rules (I'm not and expert in the field) like :<br>
<br>
[dec(6,a) + dec(6,b) ] -&gt; [ dec( 6, max(a,b) ) ]<br>
[ coalesce( dec(6,a) , dec(6,b) ] -&gt; [ dec[ 6, max(a,b)] ]<br>
[ case( dec(6,a) , dec(6,b), dec(6,c), dec(6,d) ] -&gt; [ dec[ 6,
max(a,b,c,d)] ]<br>
[dec(6,a) * dec(6,b ) -&gt; [ dec( 6,a+b ) ]<br>
etc.<br>
<br>
This rules are taken into account in the engine data formation process,
but there are stored some where ???<br>
<br>
Thanks for your answer.<br>
D<i>ario V. Fassi</i>
<div class="moz-signature"><br>
</div>
</body>
</html>

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




From software@sistemat.com.ar Sat Jul  3 18:31:42 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A6D8AD1B4FC;
	Sat,  3 Jul 2004 15:31:42 -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 46399-08; Sat,  3 Jul 2004 18:31:37 +0000 (GMT)
Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22])
	by svr1.postgresql.org (Postfix) with ESMTP id 6457AD1B493;
	Sat,  3 Jul 2004 15:31:32 -0300 (ADT)
Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40])
	by mail.svhost.com.ar (Postfix) with ESMTP
	id 3CA0A116F5; Sat,  3 Jul 2004 15:30:25 -0300 (ART)
Message-ID: <40E6FD7F.50801@sistemat.com.ar>
Date: Sat, 03 Jul 2004 15:39:59 -0300
From: "Dario V. Fassi" <software@sistemat.com.ar>
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
To: pgsql-jdbc@postgresql.org, pgsql-bugs@postgresql.org,
	pgsql-sql@postgresql.org, pgsql-patches@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar>
In-Reply-To: <40E6E7AF.5020306@sistemat.com.ar>
Content-Type: multipart/mixed; boundary="------------000605020806010102070803"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE,
	HTML_TITLE_EMPTY, LINES_OF_YELLING
X-Spam-Level: 
X-Archive-Number: 200407/59
X-Sequence-Number: 8641

This is a multi-part message in MIME format.
--------------000605020806010102070803
Content-Type: multipart/alternative;
	boundary="------------060505010609050200000302"

--------------060505010609050200000302
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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
>


--------------060505010609050200000302
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
I have found that the problem is worst.<br>
<br>
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.<br>
Then generic NUMERIC type is forced for every calculated column without
regard or precision.<br>
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.<br>
<br>
Precision calculation&nbsp; in Numeric fields it's not so difficult. <br>
This problem *must* be corrected in the CREATE VIEW sentence , <br>
and I offer my collaboration for that.<br>
<br>
Regards all,<br>
Dario Fassi.<br>
<br>
Dario V. Fassi wrote:<br>
<blockquote type="cite" cite="mid40E6E7AF.5020306@sistemat.com.ar">
  <meta http-equiv="Content-Type" content="text/html;">
  <title></title>
  <br>
Kris Jurka wrote:<br>
  <blockquote type="cite"
 cite="midPine.BSO.4.56.0407030343090.19721@leary.csoft.net">
    <pre wrap="">On Fri, 2 Jul 2004, Dario Fassi wrote:

  </pre>
    <blockquote type="cite">
      <pre wrap="">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 )

    </pre>
    </blockquote>
    <pre wrap=""><!---->
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
  </pre>
  </blockquote>
Yes, a few.<br>
  <br>
In the tool named PgManage (come with the commercial version of
Mammoth) ,&nbsp; the information is accurate values for this MetaData, and I
believe, they get that values from pqsql catalog tables and not
from DatabaseMetaData interface.<br>
  <br>
More even, the engine resolve the View properly and return data values
properly typed&nbsp; (engine return 999.999&nbsp; for f1+f2&nbsp; and&nbsp; 999.999999 for
f1*f2&nbsp; , like is expected).<br>
So, the information if know or derived en some way for the engine
, and/or is contained some where in catalog's tables.<br>
  <br>
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.<br>
  <br>
DB2 for example do, data type escalation based on "Error propagation
Theory "&nbsp; , that has rules (I'm not and expert in the field) like :<br>
  <br>
[dec(6,a) + dec(6,b) ] -&gt; [ dec( 6, max(a,b) ) ]<br>
[ coalesce( dec(6,a) , dec(6,b) ] -&gt; [ dec[ 6, max(a,b)] ]<br>
[ case( dec(6,a) , dec(6,b), dec(6,c), dec(6,d) ] -&gt; [ dec[ 6,
max(a,b,c,d)] ]<br>
[dec(6,a) * dec(6,b ) -&gt; [ dec( 6,a+b ) ]<br>
etc.<br>
  <br>
This rules are taken into account in the engine data formation process,
but there are stored some where ???<br>
  <br>
Thanks for your answer.<br>
D<i>ario V. Fassi</i>
  <div class="moz-signature"><br>
  </div>
</blockquote>
<div class="moz-signature"><br>
</div>
</body>
</html>

--------------060505010609050200000302--

--------------000605020806010102070803
Content-Type: text/plain;
 name="samp.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="samp.txt"

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

--------------000605020806010102070803--




From books@ejurka.com Sun Jul  4 19:16:59 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EC4C6D1B198
	for <pgsql-bugs-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Jul 2004 16:16:59 -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 97267-09
	for <pgsql-bugs-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Jul 2004 19:16:57 +0000 (GMT)
Received: from mail63.csoft.net (leary3.csoft.net [63.111.22.74])
	by svr1.postgresql.org (Postfix) with SMTP id 50B62D1B182
	for <pgsql-bugs@postgresql.org>; Sun,  4 Jul 2004 16:16:54 -0300 (ADT)
Received: (qmail 4690 invoked by uid 1112); 4 Jul 2004 19:16:56 -0000
Date: Sun, 4 Jul 2004 14:16:56 -0500 (EST)
From: Kris Jurka <books@ejurka.com>
X-X-Sender: books@leary.csoft.net
To: "Dario V. Fassi" <software@sistemat.com.ar>
Cc: pgsql-jdbc@postgresql.org, pgsql-bugs@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
In-Reply-To: <40E6FD7F.50801@sistemat.com.ar>
Message-ID: <Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.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/46
X-Sequence-Number: 8628



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




From software@sistemat.com.ar Sun Jul  4 20:49:58 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B88CAD1B21B;
	Sun,  4 Jul 2004 17:49:58 -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 22624-06; Sun,  4 Jul 2004 20:49:56 +0000 (GMT)
Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22])
	by svr1.postgresql.org (Postfix) with ESMTP id CE610D1B170;
	Sun,  4 Jul 2004 17:49:51 -0300 (ADT)
Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40])
	by mail.svhost.com.ar (Postfix) with ESMTP
	id D98A31DC1F; Sun,  4 Jul 2004 17:48:42 -0300 (ART)
Message-ID: <40E86F69.3050100@sistemat.com.ar>
Date: Sun, 04 Jul 2004 17:58:17 -0300
From: "Dario V. Fassi" <software@sistemat.com.ar>
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
To: Kris Jurka <books@ejurka.com>
Cc: pgsql-jdbc@postgresql.org, pgsql-bugs@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
In-Reply-To: <Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
Content-Type: multipart/alternative;
	boundary="------------040605080206050507070308"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=HTML_20_30,
	HTML_MESSAGE, HTML_TITLE_EMPTY
X-Spam-Level: *
X-Archive-Number: 200407/64
X-Sequence-Number: 8645

This is a multi-part message in MIME format.
--------------040605080206050507070308
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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




--------------040605080206050507070308
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
kris,<br>
<tt>Next you can see how the same test-case is handled by DB2.<br>
<br>
-- IBM DB2 V6.1 for Linux <br>
<br>
CREATE TABLE userid.t ( <br>
&nbsp;f1 numeric(6,3),<br>
&nbsp;f2 numeric(6,4),<br>
&nbsp;f3 numeric(8,2)<br>
);<br>
<br>
create view userid.v as <br>
select <br>
(f1+f2+f3) as fsum,<br>
coalesce(f1,f2,f3) as fcoal,<br>
(f1*f2*f3) as fprod,<br>
(f1/f2) as fdiv<br>
from userid.t;<br>
<br>
insert into userid.t values ( 123.123, 12.1234, 12345.12);<br>
insert into userid.t values ( null, 12.1234, 12345.12);<br>
insert into userid.t values ( 123.123, null, 12345.12);<br>
insert into userid.t values ( 123.123, 12.1234, null);<br>
insert into userid.t values ( 999.999, 99.9999, 99999.99);<br>
<br>
select * from userid.v<br>
<br>
&nbsp;FSUM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | FCOAL&nbsp;&nbsp;&nbsp; | FPROD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
FDIV&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
------------------------------------------------------------------------------<br>
&nbsp;12480.3664&nbsp; | 123.1230 | 18427182.594204384&nbsp;&nbsp; |
10.155814375505221307553986 |<br>
------------------------------------------------------------------------------<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 12.1234&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
------------------------------------------------------------------------------<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 123.1230 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
------------------------------------------------------------------------------<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | 123.1230 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |
10.155814375505221307553986 |<br>
------------------------------------------------------------------------------<br>
&nbsp;101099.9889 | 999.9990 | 9999979000.011999999 |
10.000000000000000000000000 |<br>
------------------------------------------------------------------------------<br>
<br>
<br>
<br>
METADATA RETURNED BY DatabaseMetaData.getColumns()<br>
<br>
Tabla: USERID.V <br>
#&nbsp; &nbsp;&nbsp;&nbsp; Campo&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Tipo &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;Anulable ReadOnly Writable AutoInc
CaseSens Currency <br>
1 &nbsp;&nbsp;&nbsp; FSUM [0] &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; DECIMAL(11,4)&nbsp; . . WA . . . <br>
2 &nbsp;&nbsp;&nbsp; FCOAL [0] &nbsp;&nbsp;&nbsp; &nbsp; DECIMAL(10,4)&nbsp; . . WA . . . <br>
3 &nbsp;&nbsp;&nbsp; FPROD [0] &nbsp;&nbsp;&nbsp; &nbsp; DECIMAL(20,9)&nbsp; . . WA . . . <br>
4 &nbsp;&nbsp;&nbsp; FDIV [0] &nbsp;&nbsp;&nbsp; &nbsp;&nbsp; DECIMAL(31,24)&nbsp; . . WA . . . <br>
4 Campos. <br>
<br>
------------------------------------------<br>
#&nbsp; Campo&nbsp; Tipo&nbsp; nTy&nbsp; Nulable&nbsp; Descripcion&nbsp; <br>
1 FSUM DECIMAL(11,4) B10 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
2 FCOAL DECIMAL(10,4) B10 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
3 FPROD DECIMAL(20,9) B10 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
4 FDIV DECIMAL(31,24) B10 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
------------------------------------------<br>
</tt><br>
The work-around is posible , if you are working from start and you are
warned about the problem. <br>
But if you are porting a data model you must to reconstuct all views. <br>
<br>
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.<br>
This is not a good solution but better than now.<br>
The optimal solution es make presicion calculation for every column or
almost the trivial cases like columns formed by&nbsp; "coalesce", "case
then", etc.<br>
<br>
The overflow or "data truncation" exception , for views, it's a going
back , but almost put the problem in programmer's hands.<br>
<br>
Regards,<br>
Dario.<br>
<br>
Kris Jurka wrote:<br>
<blockquote type="cite"
 cite="midPine.BSO.4.56.0407041330180.26773@leary.csoft.net">
  <pre wrap="">
On Sat, 3 Jul 2004, Dario V. Fassi wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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



  </pre>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<title></title>
<i><br>
&nbsp;&nbsp;&nbsp; Dario V. Fassi</i><br>
<br>
<a href="http://www.sistemat.com.ar"><small><font
 face="Helvetica, Arial, sans-serif"><big><big><small>SISTEMATICA
ingenieria
de software &nbsp;srl </small></big></big></font></small><b><font
 face="Helvetica, Arial, sans-serif"><br>
</font></b></a><small><small><font face="Helvetica, Arial, sans-serif"><big>Ituzaingo
1628&nbsp; (2000)&nbsp; Rosario, Santa Fe, Argentina.<br>
Tel / Fax: &nbsp;+54 (341) 485.1432 / 485.1353<br>
<br>
</big></font></small><br>
</small><br>
</div>
</body>
</html>

--------------040605080206050507070308--




From sszabo@megazone.bigpanda.com Sun Jul  4 22:51:26 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 39C7CD1B1B0;
	Sun,  4 Jul 2004 19:51:26 -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 59089-04; Sun,  4 Jul 2004 22:51:24 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 6FC8ED1B182;
	Sun,  4 Jul 2004 19:51:20 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 35FE435853; Sun,  4 Jul 2004 15:51:21 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 3124135852; Sun,  4 Jul 2004 15:51:21 -0700 (PDT)
Date: Sun, 4 Jul 2004 15:51:21 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Kris Jurka <books@ejurka.com>
Cc: "Dario V. Fassi" <software@sistemat.com.ar>,
	pgsql-jdbc@postgresql.org, pgsql-bugs@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
In-Reply-To: <Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
Message-ID: <20040704153840.Y52747@megazone.bigpanda.com>
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
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/47
X-Sequence-Number: 8629

On Sun, 4 Jul 2004, 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.

[Jumping in, because this was the first message of the thread I've seen]

Technically, the correct behavior by spec would be an
implementation-defined precision and a particular scale based on the
argument scales. So, having numeric(6,2)+numeric(6,2) return
numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
technically, but I don't think this is an issue in the jdbc metadata
getting as much as an issue in the database proper.




From software@sistemat.com.ar Sun Jul  4 23:19:40 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 357C6D1B199;
	Sun,  4 Jul 2004 20:19:40 -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 69378-02; Sun,  4 Jul 2004 23:19:38 +0000 (GMT)
Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22])
	by svr1.postgresql.org (Postfix) with ESMTP id 9AAD2D1B172;
	Sun,  4 Jul 2004 20:19:33 -0300 (ADT)
Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40])
	by mail.svhost.com.ar (Postfix) with ESMTP
	id 08C0423A0F; Sun,  4 Jul 2004 20:18:26 -0300 (ART)
Message-ID: <40E89282.9060306@sistemat.com.ar>
Date: Sun, 04 Jul 2004 20:28:02 -0300
From: "Dario V. Fassi" <software@sistemat.com.ar>
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
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Kris Jurka <books@ejurka.com>, pgsql-jdbc@postgresql.org,
	pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
	<20040704153840.Y52747@megazone.bigpanda.com>
In-Reply-To: <20040704153840.Y52747@megazone.bigpanda.com>
Content-Type: multipart/alternative;
	boundary="------------030901000008080708020206"
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/60
X-Sequence-Number: 8642

This is a multi-part message in MIME format.
--------------030901000008080708020206
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit



Stephan Szabo wrote:

>On Sun, 4 Jul 2004, 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.
>>>      
>>>
>
>[Jumping in, because this was the first message of the thread I've seen]
>
>Technically, the correct behavior by spec would be an
>implementation-defined precision and a particular scale based on the
>argument scales. So, having numeric(6,2)+numeric(6,2) return
>numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
>technically, but I don't think this is an issue in the jdbc metadata
>getting as much as an issue in the database proper.
>

I agree with all your spech but a  numeric data type like numeric(65535, 
2) isn't a very serious return value.
A value like this could be usefull to eliminate the need of presicion 
specification in numeric data type too.
Of course it's a dabase proper issue , but impact in my needs in jdbc 
usability.
No matter what's technically or not, a behavior at this point like those 
of db2/oracle would be very nice ;-)


--------------030901000008080708020206
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
<br>
<br>
Stephan Szabo wrote:<br>
<blockquote type="cite"
 cite="mid20040704153840.Y52747@megazone.bigpanda.com">
  <pre wrap="">On Sun, 4 Jul 2004, Kris Jurka wrote:

  </pre>
  <blockquote type="cite">
    <pre wrap="">On Sat, 3 Jul 2004, Dario V. Fassi wrote:

    </pre>
    <blockquote type="cite">
      <pre wrap="">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.
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->
[Jumping in, because this was the first message of the thread I've seen]

Technically, the correct behavior by spec would be an
implementation-defined precision and a particular scale based on the
argument scales. So, having numeric(6,2)+numeric(6,2) return
numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
technically, but I don't think this is an issue in the jdbc metadata
getting as much as an issue in the database proper.</pre>
</blockquote>
<br>
I agree with all your spech but a&nbsp; numeric data type like
numeric(65535, 2) isn't a very serious return value.<br>
A value like this could be usefull to eliminate the need of presicion
specification in numeric data type too.<br>
Of course it's a dabase proper issue , but impact in my needs in jdbc
usability.<br>
No matter what's technically or not, a behavior at this point like
those of db2/oracle would be very nice ;-)<br>
<br>
</body>
</html>

--------------030901000008080708020206--




From sszabo@megazone.bigpanda.com Mon Jul  5 00:46:28 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9A42CD1B1F8;
	Sun,  4 Jul 2004 21:46:28 -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 89396-10; Mon,  5 Jul 2004 00:46:27 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id A824FD1B1D9;
	Sun,  4 Jul 2004 21:46:22 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 62A3D3572E; Sun,  4 Jul 2004 17:46:26 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 60DE33572B; Sun,  4 Jul 2004 17:46:26 -0700 (PDT)
Date: Sun, 4 Jul 2004 17:46:26 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Dario V. Fassi" <software@sistemat.com.ar>
Cc: Kris Jurka <books@ejurka.com>, pgsql-jdbc@postgresql.org,
	pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
In-Reply-To: <40E89282.9060306@sistemat.com.ar>
Message-ID: <20040704173258.Y55353@megazone.bigpanda.com>
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
	<20040704153840.Y52747@megazone.bigpanda.com>
	<40E89282.9060306@sistemat.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/48
X-Sequence-Number: 8630

On Sun, 4 Jul 2004, Dario V. Fassi wrote:

>
>
> Stephan Szabo wrote:
>
> >On Sun, 4 Jul 2004, 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.
> >>>
> >>>
> >
> >[Jumping in, because this was the first message of the thread I've seen]
> >
> >Technically, the correct behavior by spec would be an
> >implementation-defined precision and a particular scale based on the
> >argument scales. So, having numeric(6,2)+numeric(6,2) return
> >numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
> >technically, but I don't think this is an issue in the jdbc metadata
> >getting as much as an issue in the database proper.
> >
>
> I agree with all your spech but a  numeric data type like numeric(65535,
> 2) isn't a very serious return value.

Why not?  If 65535 were the maximal numeric precision (also
implementation-defined IIRC) then it's a reasonable answer, although it
doesn't give a user much information about the expected result range, but
AFAICS that isn't one of the expected properties.

> A value like this could be usefull to eliminate the need of presicion
> specification in numeric data type too.
> Of course it's a dabase proper issue , but impact in my needs in jdbc
> usability.

Since I'm not on -jdbc, I didn't see how it was intended to be used, but I
think it'd be a portability bug to expect it to return only the precision
that the result could take.

Now, the current results are fairly broken because the precision value has
no connection to reality (it's not the maximum precision, and might in
fact be smaller than the actual precision in some absurd cases) and the
scale is wrong if one follows spec.  I'm not sure the jdbc driver can do
much better given the backend right now.

> No matter what's technically or not, a behavior at this point like those
> of db2/oracle would be very nice ;-)

Probably true.  But I don't think it's likely to happen any time soon
unless someone steps up and takes responsibility for making it happen. I
think it'd also be non-trivial for the general case since I think it'd
have to mean that arbitrary functions would have to be able to have some
sort of way of specifying the values for its output.



From software@sistemat.com.ar Mon Jul  5 01:02:27 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 88FFAD1B20E;
	Sun,  4 Jul 2004 22:02:27 -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 99852-01; Mon,  5 Jul 2004 01:02:26 +0000 (GMT)
Received: from mail.svhost.com.ar (mail.svhost.com.ar [200.80.239.22])
	by svr1.postgresql.org (Postfix) with ESMTP id A9AF3D1B18C;
	Sun,  4 Jul 2004 22:02:20 -0300 (ADT)
Received: from sistemat.com.ar (devel.sistemat.com.ar [192.168.1.40])
	by mail.svhost.com.ar (Postfix) with ESMTP
	id E4F371DE16; Sun,  4 Jul 2004 22:01:13 -0300 (ART)
Message-ID: <40E8AA9A.7020806@sistemat.com.ar>
Date: Sun, 04 Jul 2004 22:10:50 -0300
From: "Dario V. Fassi" <software@sistemat.com.ar>
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
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Kris Jurka <books@ejurka.com>, pgsql-jdbc@postgresql.org,
	pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
	<20040704153840.Y52747@megazone.bigpanda.com>
	<40E89282.9060306@sistemat.com.ar>
	<20040704173258.Y55353@megazone.bigpanda.com>
In-Reply-To: <20040704173258.Y55353@megazone.bigpanda.com>
Content-Type: multipart/alternative;
	boundary="------------020002080102070401080805"
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/61
X-Sequence-Number: 8642

This is a multi-part message in MIME format.
--------------020002080102070401080805
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Stephan, look at the samples I send in previous posts , from PgSql and Db2.

I know it's a no ease task to change all that behavior , but you must 
agree that a Numeric column with the fractional part varing from row to 
row are different data types and break relational rules.
If this is the case the returning data type must be a floating point 
data type like a double, but never 2 decimals for a row and 3, 4 or 
whatever for others rows.

Stephan Szabo wrote:

>On Sun, 4 Jul 2004, Dario V. Fassi wrote:
>  
>
>>Stephan Szabo wrote:
>>    
>>
>>>On Sun, 4 Jul 2004, 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.
>>>>>
>>>>>
>>>>>          
>>>>>
>>>[Jumping in, because this was the first message of the thread I've seen]
>>>
>>>Technically, the correct behavior by spec would be an
>>>implementation-defined precision and a particular scale based on the
>>>argument scales. So, having numeric(6,2)+numeric(6,2) return
>>>numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
>>>technically, but I don't think this is an issue in the jdbc metadata
>>>getting as much as an issue in the database proper.
>>>
>>>      
>>>
>>I agree with all your spech but a  numeric data type like numeric(65535,
>>2) isn't a very serious return value.
>>    
>>
>
>Why not?  If 65535 were the maximal numeric precision (also
>implementation-defined IIRC) then it's a reasonable answer, although it
>doesn't give a user much information about the expected result range, but
>AFAICS that isn't one of the expected properties.
>
>  
>
>>A value like this could be usefull to eliminate the need of presicion
>>specification in numeric data type too.
>>Of course it's a dabase proper issue , but impact in my needs in jdbc
>>usability.
>>    
>>
>
>Since I'm not on -jdbc, I didn't see how it was intended to be used, but I
>think it'd be a portability bug to expect it to return only the precision
>that the result could take.
>
>Now, the current results are fairly broken because the precision value has
>no connection to reality (it's not the maximum precision, and might in
>fact be smaller than the actual precision in some absurd cases) and the
>scale is wrong if one follows spec.  I'm not sure the jdbc driver can do
>much better given the backend right now.
>
>  
>
>>No matter what's technically or not, a behavior at this point like those
>>of db2/oracle would be very nice ;-)
>>    
>>
>
>Probably true.  But I don't think it's likely to happen any time soon
>unless someone steps up and takes responsibility for making it happen. I
>think it'd also be non-trivial for the general case since I think it'd
>have to mean that arbitrary functions would have to be able to have some
>sort of way of specifying the values for its output.
>  
>



--------------020002080102070401080805
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Stephan, look at the samples I send in previous posts , from PgSql and
Db2.<br>
<br>
I know it's a no ease task to change all that behavior , but you must
agree that a Numeric column with the fractional part varing from row to
row are different data types and break relational rules.<br>
If this is the case the returning data type must be a floating point
data type like a double, but never 2 decimals for a row and 3, 4 or
whatever for others rows.<br>
<br>
Stephan Szabo wrote:<br>
<blockquote type="cite"
 cite="mid20040704173258.Y55353@megazone.bigpanda.com">
  <pre wrap="">On Sun, 4 Jul 2004, Dario V. Fassi wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">
Stephan Szabo wrote:
    </pre>
    <blockquote type="cite">
      <pre wrap="">On Sun, 4 Jul 2004, Kris Jurka wrote:
      </pre>
      <blockquote type="cite">
        <pre wrap="">On Sat, 3 Jul 2004, Dario V. Fassi wrote:
        </pre>
        <blockquote type="cite">
          <pre wrap="">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.


          </pre>
        </blockquote>
      </blockquote>
      <pre wrap="">[Jumping in, because this was the first message of the thread I've seen]

Technically, the correct behavior by spec would be an
implementation-defined precision and a particular scale based on the
argument scales. So, having numeric(6,2)+numeric(6,2) return
numeric(65535, 2) is fine. Returning numeric(65535, 65531) isn't
technically, but I don't think this is an issue in the jdbc metadata
getting as much as an issue in the database proper.

      </pre>
    </blockquote>
    <pre wrap="">I agree with all your spech but a  numeric data type like numeric(65535,
2) isn't a very serious return value.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Why not?  If 65535 were the maximal numeric precision (also
implementation-defined IIRC) then it's a reasonable answer, although it
doesn't give a user much information about the expected result range, but
AFAICS that isn't one of the expected properties.

  </pre>
  <blockquote type="cite">
    <pre wrap="">A value like this could be usefull to eliminate the need of presicion
specification in numeric data type too.
Of course it's a dabase proper issue , but impact in my needs in jdbc
usability.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Since I'm not on -jdbc, I didn't see how it was intended to be used, but I
think it'd be a portability bug to expect it to return only the precision
that the result could take.

Now, the current results are fairly broken because the precision value has
no connection to reality (it's not the maximum precision, and might in
fact be smaller than the actual precision in some absurd cases) and the
scale is wrong if one follows spec.  I'm not sure the jdbc driver can do
much better given the backend right now.

  </pre>
  <blockquote type="cite">
    <pre wrap="">No matter what's technically or not, a behavior at this point like those
of db2/oracle would be very nice ;-)
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Probably true.  But I don't think it's likely to happen any time soon
unless someone steps up and takes responsibility for making it happen. I
think it'd also be non-trivial for the general case since I think it'd
have to mean that arbitrary functions would have to be able to have some
sort of way of specifying the values for its output.
  </pre>
</blockquote>
<div class="moz-signature"><br>
</div>
<div class="moz-signature"><br>
</div>
</body>
</html>

--------------020002080102070401080805--




From sszabo@megazone.bigpanda.com Mon Jul  5 01:41:33 2004
X-Original-To: pgsql-bugs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 0F955D1B16E;
	Sun,  4 Jul 2004 22:41:33 -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 09175-04; Mon,  5 Jul 2004 01:41:32 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id BCE24D1B1B4;
	Sun,  4 Jul 2004 22:41:25 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 3B19D3587F; Sun,  4 Jul 2004 18:41:29 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 3975735701; Sun,  4 Jul 2004 18:41:29 -0700 (PDT)
Date: Sun, 4 Jul 2004 18:41:29 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Dario V. Fassi" <software@sistemat.com.ar>
Cc: Kris Jurka <books@ejurka.com>, pgsql-jdbc@postgresql.org,
	pgsql-bugs@postgresql.org, pgsql-sql@postgresql.org
Subject: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
In-Reply-To: <40E8AA9A.7020806@sistemat.com.ar>
Message-ID: <20040704183143.O56951@megazone.bigpanda.com>
References: <40E6088E.6030703@yahoo.com.ar>
	<Pine.BSO.4.56.0407030343090.19721@leary.csoft.net>
	<40E6E7AF.5020306@sistemat.com.ar> <40E6FD7F.50801@sistemat.com.ar>
	<Pine.BSO.4.56.0407041330180.26773@leary.csoft.net>
	<20040704153840.Y52747@megazone.bigpanda.com>
	<40E89282.9060306@sistemat.com.ar>
	<20040704173258.Y55353@megazone.bigpanda.com>
	<40E8AA9A.7020806@sistemat.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/49
X-Sequence-Number: 8631


On Sun, 4 Jul 2004, Dario V. Fassi wrote:

> Stephan, look at the samples I send in previous posts , from PgSql and Db2.

I don't see any samples apart from the original view descriptions and the
getColumns results.  I see some implication about db2 but no details.  My
guess is that the messages are more recent than the last archives update
and since I'm not on -jdbc I wouldn't have seen them.  That's pretty much
why I asked.

> I know it's a no ease task to change all that behavior , but you must
> agree that a Numeric column with the fractional part varing from row to
> row are different data types and break relational rules.

Maybe, but without seeing the example, it's hard to say.  For example,
your views from the head of what I see for this on archives, consistently
give me output with a scale of 3.

In addition, PostgreSQL provides a numeric with effectively undefined
precision and scale. This is a non-conforming definition, but means that
while the number of digits after the decimal are different, it's the same
type, numeric with undefined precision and scale.