numeric values (as strings) are incorrectly parsed/converted and invalid strings are accepted without error

[monetdb-team]

Date: 2014-08-29 18:15:05 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2014-10-31 14:13:52 +0100

Comment 20109

Date: 2014-08-29 18:15:05 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

numeric values (as strings) are incorrectly parsed/converted
and invalid strings (which do not represent a valid number) are accepted without error but produce strange numbers internally

Reproducible: Always

Steps to Reproduce:

1. start mserver5 from development (default branch of friday morning 29 aug 2014)
2. start mclient
3. enter following SQL statements:

CREATE TABLE num_test (i int, s varchar(50), n numeric);
INSERT INTO num_test(i,s,n) VALUES (1, ' 123', ' 123');
INSERT INTO num_test(i,s,n) VALUES (2, ' 3245874 ', ' 3245874 ');
INSERT INTO num_test(i,s,n) VALUES (3, ' -93853', ' -93853');
INSERT INTO num_test(i,s,n) VALUES (4, '555.50', '555.50');
INSERT INTO num_test(i,s,n) VALUES (5, '-555.50', '-555.50');
SELECT num_test.*, cast(n as VARCHAR(50)) as n2s FROM num_test

INSERT INTO num_test(i,s,n) VALUES (6, 'NaN ', 'NaN ');
INSERT INTO num_test(i,s,n) VALUES (7, ' nan', ' nan');
INSERT INTO num_test(i,s,n) VALUES (8, ' ', ' ');
INSERT INTO num_test(i,s,n) VALUES (9, ' 1234 %', ' 1234 %');
INSERT INTO num_test(i,s,n) VALUES (10,'xyz', 'xyz');
INSERT INTO num_test(i,s,n) VALUES (11,'- 1234', '- 1234');
INSERT INTO num_test(i,s,n) VALUES (12,'5 . 0', '5 . 0');
INSERT INTO num_test(i,s,n) VALUES (13,'5. 0 ', '5. 0 ');
INSERT INTO num_test(i,s,n) VALUES (14,'', '');
INSERT INTO num_test(i,s,n) VALUES (15,' N aN ', ' N aN ');
SELECT num_test.*, cast(n as VARCHAR(50)) as n2s FROM num_test

Actual Results:

start mserver5 output:
builtin opt gdk_dbpath = /ufs/dinther/INSTALL/var/monetdb5/dbfarm/demo
builtin opt gdk_debug = 0
builtin opt gdk_vmtrim = no
builtin opt monet_prompt = >
builtin opt monet_daemon = no
builtin opt mapi_port = 50000
builtin opt mapi_open = false
builtin opt mapi_autosense = false
builtin opt sql_optimizer = default_pipe
builtin opt sql_debug = 0
cmdline opt gdk_debug = 10
MonetDB 5 server v11.20.0
This is an unreleased version
Serving database 'demo', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked
Found 15.356 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:50000/
MonetDB/GIS module loaded
MonetDB/SQL module loaded

bash-4.2$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE num_test (i int, s varchar(50), n numeric);
operation successful (97.022ms)
sql>INSERT INTO num_test(i,s,n) VALUES (1, ' 123', ' 123');
1 affected rows (2.480ms)
sql>INSERT INTO num_test(i,s,n) VALUES (2, ' 3245874 ', ' 3245874 ');
1 affected rows (2.117ms)
sql>INSERT INTO num_test(i,s,n) VALUES (3, ' -93853', ' -93853');
1 affected rows (2.181ms)
sql>INSERT INTO num_test(i,s,n) VALUES (4, '555.50', '555.50');
1 affected rows (1.865ms)
sql>INSERT INTO num_test(i,s,n) VALUES (5, '-555.50', '-555.50');
1 affected rows (1.869ms)
sql>SELECT num_test., cast(n as VARCHAR(50)) as n2s FROM num_test;
+------+----------------+----------------------+-----------------+
| i | s | n | n2s |
+======+================+======================+=================+
| 1 | 123 | 123.000 | 123.000 |
| 2 | 3245874 | 32458722224.000 | 32458722224.000 |
| 3 | -93853 | -93853.000 | -93853.000 |
| 4 | 555.50 | 555.500 | 555.500 |
| 5 | -555.50 | -555.500 | -555.500 |
+------+----------------+----------------------+-----------------+
5 tuples (4.181ms)
sql>INSERT INTO num_test(i,s,n) VALUES (6, 'NaN ', 'NaN ');
1 affected rows (1.727ms)
sql>INSERT INTO num_test(i,s,n) VALUES (7, ' nan', ' nan');
1 affected rows (12.787ms)
sql>INSERT INTO num_test(i,s,n) VALUES (8, ' ', ' ');
1 affected rows (1.448ms)
sql>INSERT INTO num_test(i,s,n) VALUES (9, ' 1234 %', ' 1234 %');
1 affected rows (1.903ms)
sql>INSERT INTO num_test(i,s,n) VALUES (10,'xyz', 'xyz');
1 affected rows (1.826ms)
sql>INSERT INTO num_test(i,s,n) VALUES (11,'- 1234', '- 1234');
1 affected rows (6.500ms)
sql>INSERT INTO num_test(i,s,n) VALUES (12,'5 . 0', '5 . 0');
1 affected rows (1.743ms)
sql>INSERT INTO num_test(i,s,n) VALUES (13,'5. 0 ', '5. 0 ');
1 affected rows (2.047ms)
sql>INSERT INTO num_test(i,s,n) VALUES (14,'', '');
1 affected rows (2.044ms)
sql>INSERT INTO num_test(i,s,n) VALUES (15,' N aN ', ' N aN ');
1 affected rows (1.720ms)
sql>SELECT num_test., cast(n as VARCHAR(50)) as n2s FROM num_test;
+------+----------------+----------------------+-----------------+
| i | s | n | n2s |
+======+================+======================+=================+
| 1 | 123 | 123.000 | 123.000 |
| 2 | 3245874 | 32458722224.000 | 32458722224.000 |
| 3 | -93853 | -93853.000 | -93853.000 |
| 4 | 555.50 | 555.500 | 555.500 |
| 5 | -555.50 | -555.500 | -555.500 |
| 6 | NaN | 35184.000 | 35184.000 |
| 7 | nan | 6752.000 | 6752.000 |
| 8 | | 0.000 | 0.000 |
| 9 | 1234 % | 12322229.000 | 12322229.000 |
| 10 | xyz | 8004.000 | 8004.000 |
| 11 | - 1234 | 158766.000 | 158766.000 |
| 12 | 5 . 0 | 32.400 | 32.400 |
| 13 | 5. 0 | 3.382 | 3.382 |
| 14 | | 0.000 | 0.000 |
| 15 | N aN | 289184.000 | 289184.000 |
+------+----------------+----------------------+-----------------+
15 tuples (2.769ms)
sql>

Expected Results:

The last 10 inserts should fail.
The conversion and numeric value 3245874 of insert 2 is wrong (becomes: 32458722224.000)

Comment 20189

Date: 2014-09-21 22:19:28 +0200
From: MonetDB Mercurial Repository <>

Changeset a14d4678c148 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=a14d4678c148

Changeset description:

more string 2 decimal fixes. fixes bug #3558

Comment 20190

Date: 2014-09-21 22:19:54 +0200
From: @njnes

more testing for invalid data is added

Comment 20238

Date: 2014-10-03 17:17:21 +0200
From: Martin van Dinther <<martin.van.dinther>>

It works now for columns of datatype numeric but not when a precision and scale are specified such as numeric(38,20) or decimal(38,20)

use
CREATE TABLE num_test (i int, s varchar(50), n numeric(38,20));
instead of
CREATE TABLE num_test (i int, s varchar(50), n numeric);
and try to repeat the inserts.
They will give errors:
Error: decimal ( 123) doesn't have format (38.20)
Error: decimal ( 3245874 ) doesn't have format (38.20)
etc.

Idem when using
CREATE TABLE num_test (i int, s varchar(50), n decimal(38,20));
instead of
CREATE TABLE num_test (i int, s varchar(50), n numeric);

Comment 20261

Date: 2014-10-08 09:48:24 +0200
From: @njnes

added missing scale for [18 digits]

Comment 20342

Date: 2014-10-31 14:13:52 +0100
From: @sjoerdmullender

Oct2014 has been released.