Re: [BUGS] [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 .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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] [JDBC] Error in DatabaseMetaData.getColumns() with Views
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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] [JDBC] Error in DatabaseMetaData.getColumns() with Views
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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] [JDBC] Error in DatabaseMetaData.getColumns() with Views
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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
