Re: BIG files

On Sun, Jun 19, 2005 at 07:48:08AM -0500, Bruno Wolff III wrote:
>
> From discussions I have seen here, MYSQL implements Numeric using a floating
> point type. Postgres stores it using something like a base 10000 digit
> for each 4 bytes of storage.

Hmmm...that got me wondering about something....

CREATE TABLE foo (n1 decimal(17, 0), n2 decimal(17, 0));
INSERT INTO foo VALUES (10000000000000000, 10000000000000001);
SELECT n1, n2, n1 + n2 FROM foo;

PostgreSQL 8.0.3:

        n1         |        n2         |     ?column?
-------------------+-------------------+-------------------
 10000000000000000 | 10000000000000001 | 20000000000000001

MySQL 4.1.12:

+-------------------+-------------------+-------------------+
| n1                | n2                | n1 + n2           |
+-------------------+-------------------+-------------------+
| 10000000000000000 | 10000000000000001 | 20000000000000000 |
+-------------------+-------------------+-------------------+

Wrong sum in MySQL.  Nice.  Oh wait, even better....

CREATE TABLE foo (n1 decimal(20, 0), n2 decimal(20, 0));
INSERT INTO foo VALUES (10000000000000000000, 10000000000000000001);
SELECT n1, n2, n1 + n2 FROM foo;

PostgreSQL 8.0.3:

          n1          |          n2          |       ?column?
----------------------+----------------------+----------------------
 10000000000000000000 | 10000000000000000001 | 20000000000000000001

MySQL 4.1.12:

+----------------------+----------------------+-----------------------+
| n1                   | n2                   | n1 + n2               |
+----------------------+----------------------+-----------------------+
| -8446744073709551616 | -8446744073709551615 | -16893488147419099136 |
+----------------------+----------------------+-----------------------+

Beauty.  Nice of MySQL not to raise an error if I've exceeded some
implementation-defined limit.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/