Thread: Re: [JDBC] Error in DatabaseMetaData.getColumns() with Views
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
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.
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.
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.
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 ;-)
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:
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.
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:
Dario V. Fassi
SISTEMATICA ingenieria de software srl
Ituzaingo 1628 (2000) Rosario, Santa Fe, Argentina.
Tel / Fax: +54 (341) 485.1432 / 485.1353
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