Date: 2012-12-04 22:00:36 +0100
From: Percy Wegmann <>
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: @drstmane

Last updated: 2013-01-22 09:29:05 +0100

Comment 18233

Date: 2012-12-04 22:00:36 +0100
From: Percy Wegmann <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.95 Safari/537.11
Build Identifier:

When using parameters of type INET in SQL queries, the results of inserts and queries vary depending on which of the following syntaxes one uses:

inet '127.0.0.1'
cast('127.0.0.1' AS inet)
'127.0.0.1'

Reproducible: Always

Steps to Reproduce:

sql>create table test_table(col inet);
operation successful (27.394ms)

sql>insert into test_table values('127.0.0.1');
1 affected row (20.370ms)

sql>insert into test_table values(inet '127.0.0.1');
1 affected row (19.654ms)

sql>insert into test_table values(cast('127.0.0.1' as inet));
1 affected row (25.038ms)

sql>select count(*) from test_table where col = '127.0.0.1';
+------+
| L1 |
+======+
| 3 |
+------+
1 tuple (1.547ms)

sql>select count(*) from test_table where col = inet '127.0.0.1';
+------+
| L1 |
+======+
| 0 |
+------+
1 tuple (0.946ms)

sql>select count(*) from test_table where col = cast('127.0.0.1' as inet);
+------+
| L1 |
+======+
| 2 |
+------+
1 tuple (0.982ms)

sql>select count(*) from test_table where col = inet '127.0.0.1';
+------+
| L1 |
+======+
| 2 |
+------+
1 tuple (0.937ms)

sql>select count(*) from test_table where col = inet '127.0.0.1';
+------+
| L1 |
+======+
| 0 |
+------+
1 tuple (0.801ms)

Actual Results:

Queries return different counts depending on how INET value was bound to query. Particularly interesting is that the output when using the inet '127.0.0.1' syntax is non-deterministic.

Expected Results:

All queries return a count of 3

Comment 18234

Date: 2012-12-04 22:43:28 +0100
From: @drstmane

With a slightly modified/extended test, I seems to get deterministic, though also incorrect / unexpected results:

create table test_table(pos int, col inet);
operation successful
insert into test_table values(1, '127.0.0.1');
1 affected row
insert into test_table values(2, inet '127.0.0.1');
1 affected row
insert into test_table values(3, cast('127.0.0.1' as inet));
1 affected row
select * from test_table;
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 1 | 127.0.0.1 |
| 2 | 127.0.0.1 |
| 3 | 127.0.0.1 |
+------+------------------------------------------------------+
3 tuples
select * from test_table where col = '127.0.0.1';
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 1 | 127.0.0.1 |
| 2 | 127.0.0.1 |
| 3 | 127.0.0.1 |
+------+------------------------------------------------------+
3 tuples
select * from test_table where col = inet '127.0.0.1';
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 2 | 127.0.0.1 |
+------+------------------------------------------------------+
1 tuple
select * from test_table where col = cast('127.0.0.1' as inet);
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 1 | 127.0.0.1 |
| 3 | 127.0.0.1 |
+------+------------------------------------------------------+
2 tuples
select * from test_table where col = inet '127.0.0.1';
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 2 | 127.0.0.1 |
+------+------------------------------------------------------+
1 tuple
select * from test_table where col = inet '127.0.0.1';
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 2 | 127.0.0.1 |
+------+------------------------------------------------------+
1 tuple
select * from test_table;
+------+------------------------------------------------------+
| pos | col |
+======+======================================================+
| 1 | 127.0.0.1 |
| 2 | 127.0.0.1 |
| 3 | 127.0.0.1 |
+------+------------------------------------------------------+
3 tuples

Comment 18235

Date: 2012-12-05 08:40:06 +0100
From: @grobian

I think the problem here is that the underlying datatype is a 64-bits integer (lng). It uses four bytes for the dotted quad (IPv4 address), and the rest for the subnet mask. If the subnetmask isn't given, it defaults to 32, which is also suppressed in the output if it is 32.

It might be that a difference in the subnetmask is causing this "weird" output.

Comment 18236

Date: 2012-12-05 10:39:19 +0100
From: @sjoerdmullender

Changeset 0103f834b924 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=0103f834b924

Changeset description:

Added test for bug #3205.

Comment 18237

Date: 2012-12-05 10:56:54 +0100
From: @sjoerdmullender

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

Changeset description:

Fully initialize inet structure.
This fixes bug #3205.

Comment 18238

Date: 2012-12-05 10:58:08 +0100
From: @sjoerdmullender

It helps to fully initialize the structure.
The bug seems to be fixed.

Comment 18360

Date: 2013-01-22 09:29:05 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.