INET operators

Your name        :    Tomas Cerha
Your email address    :    t.cerha@sh.cvut.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