You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Date: 2014-08-15 17:44:20 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes
Last updated: 2014-10-31 14:13:46 +0100
Comment 20049
Date: 2014-08-15 17:44:20 +0200
From: Martin van Dinther <<martin.van.dinther>>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0
Build Identifier:
for a table such as
CREATE TABLE fract_only (id int, val numeric(4,4));
Note that the precision is equal to the scale, so the allowed number of significant digits before the decimal dot should not exceed 0 (4 - 4 = 0).
the following SQL are NOT accepted but should be accepted:
INSERT INTO fract_only VALUES (1, '-0.9999');
INSERT INTO fract_only VALUES (2, '+0.9999');
the following SQL are accepted but incorrect data is stored:
INSERT INTO fract_only VALUES (3, '+.9999');
SELECT * FROM fract_only;
-- returns value 2.5535 !!
the following SQL are accepted but should error:
INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- both show 1.0000 which out of the allowed value range of numeric(4,4)
INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value 2.5536 for id 6 !!
INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value -0.9999 for id 7 !!
Reproducible: Always
Steps to Reproduce:
start mserver (Jan2014-SP3)
start mclient in sql mode
Run following SQL statements:
CREATE TABLE fract_only (id int, val numeric(4,4));
-- not accepted but should accept it:
INSERT INTO fract_only VALUES (1, '-0.9999');
INSERT INTO fract_only VALUES (2, '+0.9999');
-- accepted but incorrect data is stored
INSERT INTO fract_only VALUES (3, '+.9999');
SELECT * FROM fract_only;
-- returns value 2.5535 !!
-- accepted but should error
INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- both show 1.0000 which out of the allowed value range of numeric(4,4)
INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value 2.5536 for id 6 !!
INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value -0.9999 for id 7 !!
Actual Results:
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE fract_only (id int, val numeric(4,4));
operation successful (9.620ms)
sql>INSERT INTO fract_only VALUES (1, '-0.9999');
decimal (-0.9999) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (2, '+0.9999');
decimal (+0.9999) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (3, '+.9999');
1 affected rows (3.821ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
+------+--------+
1 tuple (3.390ms)
sql>INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
1 affected rows (2.093ms)
sql>INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
1 affected rows (6.997ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
+------+--------+
3 tuples (0.570ms)
sql>INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
decimal (+0.99995) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
1 affected rows (2.074ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
| 6 | 2.5536 |
+------+--------+
4 tuples (0.900ms)
sql>INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
decimal (-0.99995) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
1 affected rows (2.495ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
| 6 | 2.5536 |
| 7 | -0.9...|
+------+--------+
5 tuples (1.747ms) !1 field truncated!
note: to disable dropping columns and/or truncating fields use \w-1
sql>
fixed bug #3543
- properly handle + before decimals
- handle rounding properly (ie if round up, check if we still fit inot
the digits format)
- handle negative rounding properly
Date: 2014-08-15 17:44:20 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes
Last updated: 2014-10-31 14:13:46 +0100
Comment 20049
Date: 2014-08-15 17:44:20 +0200
From: Martin van Dinther <<martin.van.dinther>>
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0
Build Identifier:
for a table such as
CREATE TABLE fract_only (id int, val numeric(4,4));
Note that the precision is equal to the scale, so the allowed number of significant digits before the decimal dot should not exceed 0 (4 - 4 = 0).
the following SQL are NOT accepted but should be accepted:
INSERT INTO fract_only VALUES (1, '-0.9999');
INSERT INTO fract_only VALUES (2, '+0.9999');
the following SQL are accepted but incorrect data is stored:
INSERT INTO fract_only VALUES (3, '+.9999');
SELECT * FROM fract_only;
-- returns value 2.5535 !!
the following SQL are accepted but should error:
INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- both show 1.0000 which out of the allowed value range of numeric(4,4)
INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value 2.5536 for id 6 !!
INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value -0.9999 for id 7 !!
Reproducible: Always
Steps to Reproduce:
CREATE TABLE fract_only (id int, val numeric(4,4));
-- not accepted but should accept it:
INSERT INTO fract_only VALUES (1, '-0.9999');
INSERT INTO fract_only VALUES (2, '+0.9999');
-- accepted but incorrect data is stored
INSERT INTO fract_only VALUES (3, '+.9999');
SELECT * FROM fract_only;
-- returns value 2.5535 !!
-- accepted but should error
INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- both show 1.0000 which out of the allowed value range of numeric(4,4)
INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value 2.5536 for id 6 !!
INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
SELECT * FROM fract_only;
-- returns value -0.9999 for id 7 !!
Actual Results:
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE fract_only (id int, val numeric(4,4));
operation successful (9.620ms)
sql>INSERT INTO fract_only VALUES (1, '-0.9999');
decimal (-0.9999) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (2, '+0.9999');
decimal (+0.9999) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (3, '+.9999');
1 affected rows (3.821ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
+------+--------+
1 tuple (3.390ms)
sql>INSERT INTO fract_only VALUES (4, '0.99995'); -- should fail but is invalidly accepted
1 affected rows (2.093ms)
sql>INSERT INTO fract_only VALUES (5, '0.99999'); -- should fail but is invalidly accepted
1 affected rows (6.997ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
+------+--------+
3 tuples (0.570ms)
sql>INSERT INTO fract_only VALUES (6, '+0.99995'); -- correctly fails
decimal (+0.99995) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (6, '+.99995'); -- should fail but is invalidly accepted
1 affected rows (2.074ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
| 6 | 2.5536 |
+------+--------+
4 tuples (0.900ms)
sql>INSERT INTO fract_only VALUES (7, '-0.99995'); -- correctly fails
decimal (-0.99995) doesn't have format (4.4)
sql>INSERT INTO fract_only VALUES (7, '-.999998'); -- should fail but is invalidly accepted
1 affected rows (2.495ms)
sql>SELECT * FROM fract_only;
+------+--------+
| id | val |
+======+========+
| 3 | 2.5535 |
| 4 | 1.0000 |
| 5 | 1.0000 |
| 6 | 2.5536 |
| 7 | -0.9...|
+------+--------+
5 tuples (1.747ms) !1 field truncated!
note: to disable dropping columns and/or truncating fields use \w-1
sql>
Expected Results:
see comments in the steps
Comment 20067
Date: 2014-08-20 14:10:12 +0200
From: MonetDB Mercurial Repository <>
Changeset 09da452b2f51 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=09da452b2f51
Changeset description:
Comment 20068
Date: 2014-08-20 14:10:15 +0200
From: MonetDB Mercurial Repository <>
Changeset 7dbda0968c84 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=7dbda0968c84
Changeset description:
Comment 20069
Date: 2014-08-20 14:11:21 +0200
From: @njnes
fixed we didn't check rounded values properly and + wasn't skipped.
Comment 20336
Date: 2014-10-31 14:13:46 +0100
From: @sjoerdmullender
Oct2014 has been released.
The text was updated successfully, but these errors were encountered: