Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

invalid behavior and incorrect data results for SQL data type: numeric(4,4) #3543

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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:

  1. start mserver (Jan2014-SP3)
  2. start mclient in sql mode
  3. 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>

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:

added test for bug #3543

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:

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

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.

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants