[Gentoo] Numeric Precision in DBs - Oracle/DB2/Firebird/MicrosoftSQLServer/{Sy,Front,Open}base

[robbat2]

Have you got access to DB2/Firebird/{Front,Sy,Open}Base Oracle/DB2/SQLServer
Mind doing a little testcase for me?
You may need to change things slightly for other databases - I'd like to know what as well.

Give me the complete output - including what commands fail, and the exact numeric output.

CREATE TABLE test1 (i INTEGER PRIMARY KEY, n DECIMAL(30,10));
INSERT INTO test1 VALUES (1,12345678901234567890.0123456789);
INSERT INTO test1 VALUES (2,'12345678901234567890.0123456789');
INSERT INTO test1 VALUES (3,0.123456789012345678900123456789E20);
INSERT INTO test1 VALUES (4,'0.123456789012345678900123456789E20');
SELECT * FROM test1;

CREATE TABLE test2 (i INTEGER PRIMARY KEY, n DECIMAL);
INSERT INTO test2 VALUES (3,2.1);
INSERT INTO test2 VALUES (4,2.2);
INSERT INTO test2 VALUES (5,2.3);
INSERT INTO test2 VALUES (6,2.4);
INSERT INTO test2 VALUES (7,2.5);
INSERT INTO test2 VALUES (8,2.6);
INSERT INTO test2 VALUES (9,2.7);
INSERT INTO test2 VALUES (10,2.8);
INSERT INTO test2 VALUES (11,2.9);
INSERT INTO test2 VALUES (12,0.1234567890123456789012345678901234567890123456789012345678901234567890);
INSERT INTO test2 VALUES (13,1234567890123456789012345678901234567890123456789012345678901234567890);
INSERT INTO test2 VALUES (14,'0.1234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO test2 VALUES (15,'1234567890123456789012345678901234567890123456789012345678901234567890');
SELECT * FROM test2;

Followup (2006/06/18 14h58):

So far I have submissions for SQL Server, Oracle 8i, Informix and PostgreSQL. I should have mentioned that I had already tested MySQL/Postgresql/Sqlite2/Sqlite3 myself ;-). These tests however have revealed some very interesting problems in using high precision numbers. What good is a column of DECIMAL(30,10), if Oracle decides to store it as 1.2346E+19? That's only 5 digits of precision. Informix stores it as 1.23456789012e19 - 12 digits of precision. Postgresql gets it right from the start.

The second interesting issue is SQL Server. If it's inserted in an unnormalized form, it works perfectly fine. However try to use a normalized form, and you get capped to 17 digits of precision. Alternatively, try to work around the apparent casting by giving it as a string (which incidently is required for MySQL), and it rejects the normalized form entirely.

Does anybody know how to get more precision out of Oracle? Or use normalized numbers in SQL Server without being truncated? - I don't want to have to store numbers in varchar(255) here - but if it comes to that, I may have to write code that optionally allows people to do that.

Bit of background: I'm trying to write complete decimal support for Ruby on Rail's ActiveRecord ;-).

Comments

SQL Server Output

[(Anonymous)]

Hey there. Here is the output from SQL Server 2000. For the first INSERTS, the 4th refuses to work because it treated the E in the number as a varchar, but here is the output from SELECT * for th first inserts:

1 12345678901234567890.0123456789
2 12345678901234567890.0123456789
3 12345678901234567000.0000000000


Here is the output from the second part.

INSERT INTO test2 VALUES (12,0.1234567890123456789012345678901234567890123456789012345678901234567890);
INSERT INTO test2 VALUES (13,1234567890123456789012345678901234567890123456789012345678901234567890);

These two lines weren't allowed to be inserted as they are out of range outside of the range of SQL decimal.

INSERT INTO test2 VALUES (14,'0.1234567890123456789012345678901234567890123456789012345678901234567890');
INSERT INTO test2 VALUES (15,'1234567890123456789012345678901234567890123456789012345678901234567890');

These two gave the error of:

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.
Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varchar to data type numeric.
The statement has been terminated.

And the output of SELECT:

3 2
4 2
5 2
6 2
7 3
8 3
9 3
10 3
11 3


Hope this helps

Ray Booysen
rjb-blog@rjb.za.net

Oracle 8i (Enterprise Edition Release 8.1.6.0.0 - 64bit Production) results

[(Anonymous)]

All commands succeed and produce the following output

SQL> SELECT * FROM test1;

I N
---------- ----------
1 1.2346E+19
2 1.2346E+19
3 1.2346E+19
4 1.2346E+19


SQL> SELECT * FROM test2;

I N
---------- ----------
3 2
4 2
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 0
13 1.2346E+69
14 0
15 1.2346E+69

informix ids 9.4

[(Anonymous)]

Not requested but "why not?"

isql -qr decimal.sql

Database selected.


Table created.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.



i n

1 1.23456789012e19
2 1.23456789012e19
3 1.23456789012e19
4 1.23456789012e19

4 row(s) retrieved.


Table created.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.


1 row(s) inserted.



i n

3 2.10000000000000
4 2.20000000000000
5 2.30000000000000
6 2.40000000000000
7 2.50000000000000
8 2.60000000000000
9 2.70000000000000
10 2.80000000000000
11 2.90000000000000
12 0.12345678901235
13 1.23456789012e69
14 0.12345678901235
15 1.23456789012e69

13 row(s) retrieved.


-- dbschema

{ TABLE "informix".test1 row size = 20 number of columns = 2 index size = 9 }
create table "informix".test1
(
i integer,
n decimal(30,10),
primary key (i)
);
revoke all on "informix".test1 from "public";

{ TABLE "informix".test2 row size = 14 number of columns = 2 index size = 9 }
create table "informix".test2
(
i integer,
n decimal(16),
primary key (i)
);
revoke all on "informix".test2 from "public";

PostgreSQL 8.0.7

[(Anonymous)]

NOTICEs about implicit INDEX creation excluded.

BEGIN
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
i | n
---+---------------------------------
1 | 12345678901234567890.0123456789
2 | 12345678901234567890.0123456789
3 | 12345678901234567890.0123456789
4 | 12345678901234567890.0123456789
(4 rows)

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
i | n
----+--------------------------------------------------------------------------
3 | 2.1
4 | 2.2
5 | 2.3
6 | 2.4
7 | 2.5
8 | 2.6
9 | 2.7
10 | 2.8
11 | 2.9
12 | 0.1234567890123456789012345678901234567890123456789012345678901234567890
13 | 1234567890123456789012345678901234567890123456789012345678901234567890
14 | 0.1234567890123456789012345678901234567890123456789012345678901234567890
15 | 1234567890123456789012345678901234567890123456789012345678901234567890
(13 rows)

DB2 V9 (Viper)

[(Anonymous)]

[db2900_4@tpa10(:) ~]$ db2 "CREATE TABLE test1 (i INTEGER PRIMARY KEY, n DECIMAL(30,10))"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0542N "I" cannot be a column of a primary key or unique key because it can
contain null values. SQLSTATE=42831
[db2900_4@tpa10(:) ~]$ db2 "CREATE TABLE test1 (i INTEGER , n DECIMAL(30,10))"
DB20000I The SQL command completed successfully.
[db2900_4@tpa10(:) ~]$ db2 "INSERT INTO test1 VALUES (1,12345678901234567890.0123456789)"
DB20000I The SQL command completed successfully.
[db2900_4@tpa10(:) ~]$ db2 "INSERT INTO test1 VALUES (2,'12345678901234567890.0123456789')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "N". SQLSTATE=42821
[db2900_4@tpa10(:) ~]$ db2 "INSERT INTO test1 VALUES (3,0.123456789012345678900123456789E20)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0410N The floating point literal "0.123456789012345678900123456789E20"
contains more than 30 characters. SQLSTATE=42820
[db2900_4@tpa10(:) ~]$ db2 "INSERT INTO test1 VALUES (4,'0.123456789012345678900123456789E20')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "N". SQLSTATE=42821
[db2900_4@tpa10(:) ~]$ db2 "SELECT * FROM test1"

I N
----------- --------------------------------
1 12345678901234567890.0123456789

1 record(s) selected.


db2 -c -t -n -f test2
DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB20000I The SQL command completed successfully.

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0405N The numeric literal
"0.12345678901234567890123456789012345678901234567890123456789012345678" is
not valid because its value is out of range. SQLSTATE=42820

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0405N The numeric literal
"1234567890123456789012345678901234567890123456789012345678901234567890" is
not valid because its value is out of range. SQLSTATE=42820

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "N". SQLSTATE=42821

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "N". SQLSTATE=42821


I N
----------- -------
10 2.
11 2.
5 2.
7 2.
6 2.
9 2.
3 2.
4 2.
8 2.

9 record(s) selected.
FYI:from the Db2 doc.
Decimal (DECIMAL or NUMERIC)

A decimal value is a packed decimal number with an implicit decimal point. The position of the decimal point is determined by the precision and the scale of the number. The scale, which is the number of digits in the fractional part of the number, cannot be negative or greater than the precision. The maximum precision is 31 digits.

occ

SQL Server 2003 (9.00.1399.06)

[(Anonymous)]

As a point of testing newer versions, SQL Server 9.00.1399.06 performed the same as above. Just including for completeness.

Firebird Output

[(Anonymous)]

Firebird 1.5 Output

I wrote a not null constraint on primary key, Because firebirds demands it.
CREATE TABLE test1 (i INTEGER NOT NULL PRIMARY KEY, n DECIMAL(30,10));

CREATE TABLE test2 (i INTEGER NOT NULL PRIMARY KEY, n DECIMAL);
------
Errors
------

================================================================================
********* Statement:
CREATE TABLE test1 (i INTEGER NOT NULL PRIMARY KEY, n DECIMAL(30,10))

********* Error:
Wrong numeric type
.
Dynamic SQL Error.
SQL error code = -842.
Precision must be from 1 to 18.


================================================================================
********* Statement:
INSERT INTO test1 VALUES (1,12345678901234567890.0123456789)

********* Error:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, char 29.
1.


================================================================================
********* Statement:
INSERT INTO test1 VALUES (2,'12345678901234567890.0123456789')

********* Error:
Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Table unknown.
TEST1.
At line 1, column 13.


================================================================================
********* Statement:
INSERT INTO test1 VALUES (3,0.123456789012345678900123456789E20)

********* Error:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, char 29.
0.


================================================================================
********* Statement:
INSERT INTO test1 VALUES (4,'0.123456789012345678900123456789E20')

********* Error:
Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Table unknown.
TEST1.
At line 1, column 13.


================================================================================
********* Statement:
INSERT INTO test2 VALUES (12,0.1234567890123456789012345678901234567890123456789012345678901234567890)

********* Error:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, char 30.
0.


================================================================================
********* Statement:
INSERT INTO test2 VALUES (13,1234567890123456789012345678901234567890123456789012345678901234567890)

********* Error:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, char 30.
1.


================================================================================
********* Statement:
INSERT INTO test2 VALUES (14,'0.1234567890123456789012345678901234567890123456789012345678901234567890')

********* Error:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.


================================================================================
********* Statement:
INSERT INTO test2 VALUES (15,'1234567890123456789012345678901234567890123456789012345678901234567890')

********* Error:
Arithmetic overflow or division by zero has occurred.
arithmetic exception, numeric overflow, or string truncation.



output of select * from test2
As you can see decimal without decimal places was interpreted as integer

I N

3 2
4 2
5 2
6 2
7 3
8 3
9 3
10 3
11 3

if you need another type of teste with firebird fell free to contact me at
geraldo.ls at gmail dot com

[chrisdanek.livejournal.com]

You would really love the Daily WTF.

[robbat2.livejournal.com]

ahead of you there, a bunch of us have been following it since 2004 ;-).

[chrisdanek.livejournal.com]

Darn, I was checking out your profile page and didn't see the feed listed, thought it might be something you hadn't seen. Very close to one of my top feeds (I was a programmer in a past life). Wish I could help you with your database experiment though!

Maybe I could run it in MS access.

[robbat2.livejournal.com]

I read most feeds with a little hacked up bit of code that does rss->email, since I live on email (via mutt on the commandline) a lot more than the web.

[chrisdanek.livejournal.com]

Cool. I can definitely appreciate the nerd factor there, and might consider going the same route if it weren't for the fact that I'm already inundated with email as it is. I try to keep my email to absolute "must-action" items. I know, I used a corporate monkeyword.

I'm going to hell to file TPS reports.

What I really want to do though is get my PVR to record shows, automatically upload them to my NAS which could back them up remotely to my work computer so I could watch them almost real time (while simultaneously backing them up on my terabyte storage).

I am proud of my photo album, though. I hacked it up in java more or less myself - the implementation requirements don't include looking too fancy, so it doesn't, it gets the job done. Instead, what I'm really proud of is how simple it is to upload new pictures with all the commentary that I need. I can get 50 pictures from my camera to my webspace, with generated html, thumbnails, resized images, and captions, all in a few seconds (plus witty-caption-human-processing time). I love it. :)

[robbat2.livejournal.com]

MythTV can accomplish what you are after PVR-wise. I'd consider something similar, only that I hardly ever watch shows. Sure, there's TV because I use cable internet, but that's about it. Maybe I've watched 30 minutes in the last month.

I go thru some 500 emails/day - mainly due to development mailing lists, excluding spam. I get twice that many in spam, of which less than 3% make it thru my setup to my actual inboxes.