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/