insert of incomplete or invalid ip address values in inet column is silently accepted but the values are not stored (they become/show nil)

[monetdb-team]

Date: 2014-11-21 17:01:03 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2015-01-29 14:07:38 +0100

Comment 20452

Date: 2014-11-21 17:01:03 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

insert of incomplete or invalid ip address values in inet column is silently accepted but the values are not stored (they become/show nil)

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (MonetDB 5 server v11.19.3 "Oct2014")
2. start mclient
3. run the SQL queries:
   CREATE TABLE INET_TBL (ip inet);
   INSERT INTO INET_TBL VALUES ('192.168.1');
   SELECT * FROM INET_TBL;
   INSERT INTO INET_TBL VALUES ('192.168.2.');
   SELECT * FROM INET_TBL;
   INSERT INTO INET_TBL VALUES ('193');
   INSERT INTO INET_TBL VALUES ('193.169');
   INSERT INTO INET_TBL VALUES ('193.169.13');
   INSERT INTO INET_TBL VALUES ('193.169.13.4');
   INSERT INTO INET_TBL VALUES ('193.169.13.4/80');
   SELECT * FROM INET_TBL;
   INSERT INTO INET_TBL VALUES ('193.169.13.4/32');
   SELECT * FROM INET_TBL;

Actual Results:

bash-4.2$ mclient -p 41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.19.3 (Oct2014), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE INET_TBL (ip inet);
operation successful (38.052ms)
sql>SELECT * FROM INET_TBL;
+----+
| ip |
+====+
+----+
0 tuples (1.059ms)
sql>NSERT INTO INET_TBL VALUES ('192.168.1');
syntax error, unexpected IDENT in: "nsert"
sql>INSERT INTO INET_TBL VALUES ('192.168.1');
1 affected row (2.850ms)
sql>SELECT * FROM INET_TBL;
+-------------------------------------------------------------------------------------------------+
| ip |
+=================================================================================================+
| (nil) |
+-------------------------------------------------------------------------------------------------+
1 tuple (2.138ms)
sql>INSERT INTO INET_TBL VALUES ('192.168.2.');
1 affected row (13.271ms)
sql>SELECT * FROM INET_TBL;
+-------------------------------------------------------------------------------------------------+
| ip |
+=================================================================================================+
| (nil) |
| 192.168.2.0 |
+-------------------------------------------------------------------------------------------------+
2 tuples (0.802ms)
sql>INSERT INTO INET_TBL VALUES ('193');
1 affected row (3.815ms)
sql>INSERT INTO INET_TBL VALUES ('193.169');
1 affected row (2.872ms)
sql>INSERT INTO INET_TBL VALUES ('193.169.13');
1 affected row (2.020ms)
sql>INSERT INTO INET_TBL VALUES ('193.169.13.4');
1 affected row (3.601ms)
sql>INSERT INTO INET_TBL VALUES ('193.169.13.4/80');
1 affected row (2.466ms)
sql>SELECT * FROM INET_TBL;
+-------------------------------------------------------------------------------------------------+
| ip |
+=================================================================================================+
| (nil) |
| 192.168.2.0 |
| (nil) |
| (nil) |
| (nil) |
| 193.169.13.4 |
| (nil) |
+-------------------------------------------------------------------------------------------------+
7 tuples (0.662ms)
sql>INSERT INTO INET_TBL VALUES ('193.169.13.4/32');
1 affected row (11.396ms)
sql>SELECT * FROM INET_TBL;
+-------------------------------------------------------------------------------------------------+
| ip |
+=================================================================================================+
| (nil) |
| 192.168.2.0 |
| (nil) |
| (nil) |
| (nil) |
| 193.169.13.4 |
| (nil) |
| 193.169.13.4 |
+-------------------------------------------------------------------------------------------------+
8 tuples (0.568ms)
sql>

Expected Results:

sql>INSERT INTO INET_TBL VALUES ('192.168.1');
Error: invalid inet address: 192.168.1
sql>SELECT * FROM INET_TBL;
0 tuple (2.138ms)
sql>INSERT INTO INET_TBL VALUES ('192.168.2.');
1 affected row (13.271ms)
sql>SELECT * FROM INET_TBL;
+-------------------------------------------------------------------------------------------------+
| ip |
+=================================================================================================+
192.168.2.0 |
+-------------------------------------------------------------------------------------------------+
1 tuples (0.802ms)
sql>INSERT INTO INET_TBL VALUES ('193');
Error: invalid inet address: 193
sql>INSERT INTO INET_TBL VALUES ('193.169');
Error: invalid inet address: 193.169
sql>INSERT INTO INET_TBL VALUES ('193.169.13');
Error: invalid inet address: 193.169.13
sql>INSERT INTO INET_TBL VALUES ('193.169.13.4');
1 affected row (3.601ms)
sql>INSERT INTO INET_TBL VALUES ('193.169.13.4/80');
1 affected row (2.466ms)
sql>SELECT * FROM INET_TBL;
ip
192.168.2.0 193.169.13.4
193.169.13.4/80
3 tuples (0.662ms)

Comment 20499

Date: 2014-12-01 10:01:30 +0100
From: @njnes

added parsing errors

Comment 20504

Date: 2014-12-01 19:25:27 +0100
From: MonetDB Mercurial Repository <>

Changeset 7ad1ee2a65a7 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=7ad1ee2a65a7

Changeset description:

added test for inet parsing bug #3624

Comment 20517

Date: 2014-12-05 13:21:21 +0100
From: MonetDB Mercurial Repository <>

Changeset 0a110f07a888 made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Reapproved test output after fix for bug #3624

Comment 20605

Date: 2015-01-29 14:07:38 +0100
From: @sjoerdmullender

Oct2014-SP2 has been released.