Re: Help convincing a vendor....

On Tue, 15 Jul 2003, scott.marlowe wrote:

> On Tue, 15 Jul 2003, Andrew Sullivan wrote:
>
> > On Tue, Jul 15, 2003 at 11:28:32AM -0500, Larry Rosenman wrote:
> >
> > > I'm involved in a beta for some extensions for said vendor, and am looking
> > > for COGENT
> > > arguments for them to include BOTH MySQL (they already include MySQL in the
> > > package, and
> > > for the "other OS" may "have" to do PG for stability reasons).
> >
> > Well, what about, "We have to use PostgreSQL, because our accounting
> > department requires stability in numeric data. In MySQL, numeric()
> > gets stored internally as a float, and that leads to potential
> > problems in accounting applications."
> >
> > I didn't know about the numeric/float issue in MySQL, but I know for
> > sure that it would automatically disqualify it for anything we'd use
> > it for.
>
> See my recent correction about this. however, it wouldn't surprise me to
> find out that while they get stored as strings, they quite likely get
> operated on as floats...

OK, I did some testing. This script produces different output on mysql
than on postgresql (testing the mysql 3.23.xx version that comes with
RH7.2, and postgresql 7.3.3 installed from .tar.gz):

create table test (i1 numeric (10,2), i2 numeric(10,2));
insert into test values (123.123,123.123);
insert into test values (123.13,123.12);
select i1*i2 from test;

Postgresql produces this:

?column?
------------
15158.5344
15159.7656

While MySQL produces this:

+----------+
| i1*i2 |
+----------+
| 15158.53 |
| 15159.77 |
+----------+

Note that the mysql result is constrained by the numeric, and it's rounded
off.

Changing the select in postgresql to:

select (i1*i2)::numeric(10,2) from test;

gets the output in numeric format.

This loss of scale is against SQL spec, which states (SQL92 draft):

(QUOTE)

1) If the data type of both operands of a dyadic arithmetic operator is
exact numeric, then the data type of the result is exact numeric, with
precision and scale determined as follows:

a) Let S1 and S2 be the scale of the first and second operands
respectively.

b) The precision of the result of addition and subtraction is
implementation-defined, and the scale is the maximum of S1
and S2.

c) The precision of the result of multiplication is implementation
defined, and the scale is S1 + S2.

(/QUOTE)

Note that the result should therefore have a precion of (x,4)