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.
>
>