Incorrect Results for Comparison Operators on inet Datatype

[monetdb-team]

Date: 2015-02-02 00:56:26 +0100
From: K <>
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)

Last updated: 2015-05-07 12:38:09 +0200

Comment 20615

Date: 2015-02-02 00:56:26 +0100
From: K <>

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

A problem remains in the implementation of comparison operators on the inet datatype. Depending on the actual values for the data, the results may not be correct. Perhaps this is related to the same issue as 3646?

Reproducible: Always

Steps to Reproduce:

create table "bug"
(
address inet
);

insert into "bug" values('1.0.0.11');
insert into "bug" values('2.0.0.10');
insert into "bug" values('3.0.0.9');
insert into "bug" values('4.0.0.8');
insert into "bug" values('5.0.0.7');
insert into "bug" values('6.0.0.6');
insert into "bug" values('7.0.0.5');
insert into "bug" values('8.0.0.4');
insert into "bug" values('9.0.0.3');
insert into "bug" values('10.0.0.2');
insert into "bug" values('11.0.0.1');

select count() from bug where address>=inet'4.0.0.0';
select count() from bug where address<inet'8.0.0.0';
select count(*) from bug where address>=inet'4.0.0.0' and address<inet'8.0.0.0';

Actual Results:

8
6 <-- wrong
3 <-- wrong

Expected Results:

8
7
4

Running the same test case on Oct2014-SP1 gave different (but still wrong) results:

11
0
0

Also note that the results do depend on the data. If instead of the values given above, you use:

1.0.0.0
2.0.0.0
3.0.0.0
4.0.0.0
5.0.0.0
6.0.0.0
7.0.0.0
8.0.0.0
9.0.0.0
10.0.0.0
11.0.0.0

the results are correct!

8
7
4

So as with 3646 I suspect that this may be a byte order problem.

I'm marking this "normal" because I'm not sure if it qualifies as "major", but this bug is fatal for anyone working with the 'inet' datatype because it causes monetdb to return incorrect results. It would almost be better if the server crashed, since at least then you'd know something was wrong! It would be great if a patch were made available, since this otherwise no queries (at least using comparisons on this datatype) can be done until the next release.

As requested here's the output from mserver5 --version:

MonetDB 5 server v11.19.9 "Oct2014-SP2" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 31.3GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.33 2013-05-28 (compiled with 8.33)
openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with )
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: mockbuild@ (x86_64-redhat-linux-gnu)
Compilation: gcc -O3 -fomit-frame-pointer -pipe -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_FORTIFY_SOURCE=2
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-z,relro

Comment 20616

Date: 2015-02-02 00:59:08 +0100
From: K <>

Created attachment 316
Test case (same as described in bug report)

Attached file: bug3660.testcase (text/plain, 641 bytes)
Description: Test case (same as described in bug report)

Comment 20617

Date: 2015-02-02 09:34:58 +0100
From: MonetDB Mercurial Repository <>

Changeset 5890ce4c8682 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=5890ce4c8682

Changeset description:

Don't use ATOMstorage if nil or compare function is different.
This fixes bug #3660.
Also added test for the bug.