Date: 2016-10-27 18:07:52 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)

Last updated: 2017-03-03 10:24:48 +0100

Comment 24639

Date: 2016-10-27 18:07:52 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

in sys.types the number of max digits for decimals are:
+------------+---------+--------+-------+-------+--------+
| systemname | sqlname | digits | scale | radix | eclass |
+============+=========+========+=======+=======+========+
| bte | decimal | 2 | 1 | 10 | 10 |
| sht | decimal | 4 | 1 | 10 | 10 |
| int | decimal | 9 | 1 | 10 | 10 |
| lng | decimal | 18 | 1 | 10 | 10 |
| hge | decimal | 39 | 1 | 10 | 10 |
+------------+---------+--------+-------+-------+--------+

For hge the max number of digits should be 38 and not 39.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5
2. Start mclient
3. Run SQL commands:
   SELECT systemname, sqlname, digits, scale, radix, eclass FROM sys.types where sqlname = 'decimal' order by digits;

CREATE TABLE "t_dec39" (val DECIMAL(39)); -- should and will error
CREATE TABLE "t_dec39.5" (val DECIMAL(39,5)); -- should and will error

Actual Results:

bash-4.3$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.26.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>SELECT systemname, sqlname, digits, scale, radix, eclass FROM sys.types where sqlname = 'decimal' order by digits;
+------------+---------+--------+-------+-------+--------+
| systemname | sqlname | digits | scale | radix | eclass |
+============+=========+========+=======+=======+========+
| bte | decimal | 2 | 1 | 10 | 10 |
| sht | decimal | 4 | 1 | 10 | 10 |
| int | decimal | 9 | 1 | 10 | 10 |
| lng | decimal | 18 | 1 | 10 | 10 |
| hge | decimal | 39 | 1 | 10 | 10 |
+------------+---------+--------+-------+-------+--------+
5 tuples (2.524ms)
sql>
sql>CREATE TABLE "t_dec39" (val DECIMAL(39));
decimal of 39 digits are not supported in: "create table "t_dec39" (val decimal(39)"
syntax error, unexpected ')' in: ")"
sql>CREATE TABLE "t_dec39.5" (val DECIMAL(39,5));
decimal(39,5) isn't supported because P=39 > 38 in: "create table "t_dec39.5" (val decimal(39,5)"
syntax error, unexpected ')' in: ")"
sql>

Expected Results:

+------------+---------+--------+-------+-------+--------+
| systemname | sqlname | digits | scale | radix | eclass |
+============+=========+========+=======+=======+========+
| bte | decimal | 2 | 1 | 10 | 10 |
| sht | decimal | 4 | 1 | 10 | 10 |
| int | decimal | 9 | 1 | 10 | 10 |
| lng | decimal | 18 | 1 | 10 | 10 |
| hge | decimal | 38 | 1 | 10 | 10 |
+------------+---------+--------+-------+-------+--------+

Only applies to MonetDB servers which support HUGEINT (so _int128)

Comment 24960

Date: 2017-02-03 13:37:44 +0100
From: MonetDB Mercurial Repository <>

Changeset 84b7fc2a39e7 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Report maximum number of digits correctly for decimals in sys.types.
The SQL parser already only accepted 18/38 digits, now sys.types also
reports those values.
This fixes bug #6111.

Comment 25127

Date: 2017-03-03 10:24:48 +0100
From: @sjoerdmullender

Dec2016-SP2 has been released, incorporating the fix.