Thread: Error in DatabaseMetaData.getColumns() with Views

Error in DatabaseMetaData.getColumns() with Views

From

Dario Fassi

Date:

03 July 2004, 01:15:22

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

Kris Jurka

Date:

03 July 2004, 08:45:49

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

Dario Fassi

Date:

03 July 2004, 17:47:16

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

04 July 2004, 18:12:07

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

04 July 2004, 18:12:11

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

04 July 2004, 18:12:20

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

Re: Error in DatabaseMetaData.getColumns() with Views

From

Kris Jurka

Date:

04 July 2004, 21:26:41

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

Re: [BUGS] Error in DatabaseMetaData.getColumns() with Views

From

Stephan Szabo

Date:

04 July 2004, 22:59:46

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.

Re: [BUGS] Error in DatabaseMetaData.getColumns() with Views

From

Stephan Szabo

Date:

05 July 2004, 00:48:00

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.

Re: [BUGS] Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

05 July 2004, 01:18:02

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.

Re: [BUGS] Error in DatabaseMetaData.getColumns() with Views

From

Stephan Szabo

Date:

05 July 2004, 01:52:15

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.

Re: [BUGS] Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

05 July 2004, 03:51:39

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 ;-)

Re: Error in DatabaseMetaData.getColumns() with Views

From

"Dario V. Fassi"

Date:

05 July 2004, 08:46:16

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
Ituzaingo 1628 (2000) Rosario, Santa Fe, Argentina.
Tel / Fax: +54 (341) 485.1432 / 485.1353

ResultSerMetaData.getColumnDisplaySize() with timestamp error

From

"Dario V. Fassi"

Date:

07 July 2004, 06:24:50

Hi all,

I believe that the method ResultSerMetaData.getColumnDisplaySize() with timestamp columns return 0 length
when they would return 19 or 26.

The same way , methods getColumnPresicion() and getColumnScale() .

Re: ResultSerMetaData.getColumnDisplaySize() with timestamp

From

Kris Jurka

Date:

08 July 2004, 06:46:35

On Wed, 7 Jul 2004, Dario V. Fassi wrote:

> I believe that the method   ResultSerMetaData.getColumnDisplaySize()
> with timestamp columns return 0 length
> when they would return 19 or 26.

Yes it appears to only handle timestamp and not timestamp with time zone.
Will fix.

> The same way , methods getColumnPresicion()  and getColumnScale() .

I'm not sure what this comment means.  I'm guessing you'd like to be able
to tell the difference between timestamp(0) and timestamp(6), but that is
not clear.  As I understood it these numbers were really only for numeric
types, but extending that to timestamps does seem useful.

Kris Jurka

Re: ResultSerMetaData.getColumnDisplaySize() with timestamp

From

Kris Jurka

Date:

09 July 2004, 23:55:40

On Wed, 7 Jul 2004, Dario V. Fassi wrote:

> Hi all,
>
> I believe that the method   ResultSerMetaData.getColumnDisplaySize()
> with timestamp columns return 0 length
> when they would return 19 or 26.

I've modified the cvs version of the driver to return a size for
times and timestamps with timezones.

> The same way , methods getColumnPresicion()  and getColumnScale() .
>

I've modified the cvs version of the driver to return the number of
fractional seconds in time/timezone fields in the getScale() method.  I'm
not sure what you are implying about getPrecision here.

Kris Jurka