Thread
-
INET operators
Tomas Cerha <t.cerha@sh.cvut.cz> — 1999-12-12T20:16:13Z
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