INET operators

Your name : Tomas Cerha
Your email address : t(dot)cerha(at)sh(dot)cvut(dot)cz

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium MMX

Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.5-15 ELF

PostgreSQL version (example: PostgreSQL-6.5.3): PostgreSQL-6.5.3

Compiler used (example: gcc 2.8.0) : installed from
RPM,
downloaded from your website
installed packages:
postgresql-6.5.3-1.i386.rpm
postgresql-perl-6.5.3-1.i386.rpm
postgresql-server-6.5.3-1.i386.rpm
postgresql-tcl-6.5.3-1.i386.rpm
postgresql-test-6.5.3-1.rpm
----------------------------------------------------------------------

Please enter a FULL description of your problem:
------------------------------------------------

Aplying of NOT operator to << INET operator results always in false.
briefly:
SELECT * FROM table WHERE NOT ip<<'subnet' = always empty set

More exact example:

This is the query without INET operator:

postgres=> SELECT * FROM f00_19991204_0000
postgres-> WHERE stime>944261864 AND etime<944261980;
stime| etime| srcip|srcport|
dstip|dstport|proto|octets
---------+---------+--------------+-------+--------------+-------+-----+------
944261976|944261976| 192.168.18.12| 1051| 192.168.16.2| 53|
17| 62
944261976|944261976| 192.168.16.2| 53| 192.168.18.12| 1051|
17| 336
944261977|944261977|205.156.54.208| 21| 192.168.18.12| 3002|
6| 44
944261977|944261977| 192.168.31.3| 123| 192.168.0.254| 123|
17| 76
944261976|944261978| 192.168.18.12| 3002|205.156.54.208| 21|
6| 382
944261977|944261978|205.156.54.208| 21| 192.168.18.12| 3002|
6| 1787
(6 rows)

Now, I add srcip restriction to subnet '192/8': (still works OK)

postgres=> SELECT * FROM f00_19991204_0000
postgres-> WHERE stime>944261864 AND etime<944261980 AND srcip<<'192/8';
stime| etime| srcip|srcport|
dstip|dstport|proto|octets
---------+---------+-------------+-------+--------------+-------+-----+------
944261976|944261976|192.168.18.12| 1051| 192.168.16.2| 53|
17| 62
944261976|944261976| 192.168.16.2| 53| 192.168.18.12| 1051|
17| 336
944261977|944261977| 192.168.31.3| 123| 192.168.0.254| 123|
17| 76
944261976|944261978|192.168.18.12| 3002|205.156.54.208| 21|
6| 382
(4 rows)

And filally I only add NOT operator before srcip condition:

postgres=> SELECT * FROM f00_19991204_0000
postgres-> WHERE stime>944261864 AND etime<944261980 AND NOT
srcip<<'192/8';
stime|etime|srcip|srcport|dstip|dstport|proto|octets
-----+-----+-----+-------+-----+-------+-----+------
(0 rows)

Is it really a bug? Or is there another way to do this condition? (there
is nothing like !<< ...)

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

CREATE TABLE a (ip inet);

INSERT INTO a VALUES ('10.1.1.1');
INSERT INTO a VALUES ('10.1.1.2');
INSERT INTO a VALUES ('10.2.1.2');
INSERT INTO a VALUES ('10.2.1.1');

SELECT * FROM a;
SELECT * FROM a WHERE ip<<'10.1/16';
SELECT * FROM a WHERE NOT ip<<'10.1/16';

DROP TABLE a;

----------------------------------------------------------------------
Thank you.

Tom Cerha, student, FEE CTU Prague Czech Republic