Обсуждение: INET operators and NOT
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 RPMs
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
----------------------------------------------------------------------
I've already posted this bug, but nobody replied yet ... I was not
subscribed, but now I am. If it is not significant, I'm sorry to
overload this
mailing list... Now to the problem:
----------------------------------------------------------------------
Please enter a FULL description of your problem:
----------------------------------------------------------------------
Aplying the NOT operator with << INET operator results always in false.
See the example below:
This is the contents of table a:
accounting=> SELECT * FROM a;
ip
--------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)
Now, let's select only those hosts from subnet '10.1/16': (works fine)
accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
ip
--------
10.1.1.1
10.1.1.2
(2 rows)
And now, I only apply NOT to prewious statement ....
accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
--
(0 rows)
But that is not true! I tryed this also with other versions of postgress
on other machines and the result was always the same. But this makes all
about INET operators quite unusable, when I am not able to exclude some
address space (I can only include them). Or is there another way to do
It?
----------------------------------------------------------------------
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 for any idea which could help to solve this problem ...
Tom Cerha, student, FEE CTU Prague Czech Republic
Tomas Cerha <t.cerha@sh.cvut.cz> writes:
> Aplying the NOT operator with << INET operator results always in false.
> accounting=> SELECT * FROM a;
> ip
> --------
> 10.1.1.1
> 10.1.1.2
> 10.2.1.2
> 10.2.1.1
> (4 rows)
> accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
> ip
> --------
> 10.1.1.1
> 10.1.1.2
> (2 rows)
> accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
> ip
> --
> (0 rows)
What's going on here is that the optimizer is simplifying "NOT x<<y"
(network_sub) into "x>>=y" (network_supeq), because the pg_operator
entry for << claims that >>= is its negator. This example demonstrates
that that ain't so.
Can anyone comment on whether any of the inet operators are actually the
correct negator of << ? For that matter, are inet's other commutator
and negator declarations just as broken?
regards, tom lane
I still see this problem in 7.0.
[ Charset ISO-8859-2 unsupported, converting... ]
> 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 RPMs
> 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
> ----------------------------------------------------------------------
> I've already posted this bug, but nobody replied yet ... I was not
> subscribed, but now I am. If it is not significant, I'm sorry to
> overload this
> mailing list... Now to the problem:
>
> ----------------------------------------------------------------------
> Please enter a FULL description of your problem:
> ----------------------------------------------------------------------
>
> Aplying the NOT operator with << INET operator results always in false.
> See the example below:
>
> This is the contents of table a:
>
> accounting=> SELECT * FROM a;
> ip
> --------
> 10.1.1.1
> 10.1.1.2
> 10.2.1.2
> 10.2.1.1
> (4 rows)
>
> Now, let's select only those hosts from subnet '10.1/16': (works fine)
>
> accounting=> SELECT * FROM a WHERE ip<<'10.1/16';
> ip
> --------
> 10.1.1.1
> 10.1.1.2
> (2 rows)
>
> And now, I only apply NOT to prewious statement ....
>
> accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16';
> ip
> --
> (0 rows)
>
>
> But that is not true! I tryed this also with other versions of postgress
> on other machines and the result was always the same. But this makes all
> about INET operators quite unusable, when I am not able to exclude some
> address space (I can only include them). Or is there another way to do
> It?
>
>
> ----------------------------------------------------------------------
> 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 for any idea which could help to solve this problem ...
>
> Tom Cerha, student, FEE CTU Prague Czech Republic
>
> ************
>
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Here is Tom Lane's comment. > Tomas Cerha <t.cerha@sh.cvut.cz> writes: > > Aplying the NOT operator with << INET operator results always in false. > > > accounting=> SELECT * FROM a; > > ip > > -------- > > 10.1.1.1 > > 10.1.1.2 > > 10.2.1.2 > > 10.2.1.1 > > (4 rows) > > > accounting=> SELECT * FROM a WHERE ip<<'10.1/16'; > > ip > > -------- > > 10.1.1.1 > > 10.1.1.2 > > (2 rows) > > > accounting=> SELECT * FROM a WHERE NOT ip<<'10.1/16'; > > ip > > -- > > (0 rows) > > What's going on here is that the optimizer is simplifying "NOT x<<y" > (network_sub) into "x>>=y" (network_supeq), because the pg_operator > entry for << claims that >>= is its negator. This example demonstrates > that that ain't so. > > Can anyone comment on whether any of the inet operators are actually the > correct negator of << ? For that matter, are inet's other commutator > and negator declarations just as broken? > > regards, tom lane > > ************ > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I still see this problem in 7.0.
You do? I don't:
regression=# SELECT * FROM a;
ip
----------
10.1.1.1
10.1.1.2
10.2.1.2
10.2.1.1
(4 rows)
regression=# SELECT * FROM a WHERE ip<<'10.1/16';
ip
----------
10.1.1.1
10.1.1.2
(2 rows)
regression=# SELECT * FROM a WHERE NOT ip<<'10.1/16';
ip
----------
10.2.1.2
10.2.1.1
(2 rows)
regression=#
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> What's going on here is that the optimizer is simplifying "NOT x<<y"
>> (network_sub) into "x>>=y" (network_supeq), because the pg_operator
>> entry for << claims that >>= is its negator. This example demonstrates
>> that that ain't so.
>>
>> Can anyone comment on whether any of the inet operators are actually the
>> correct negator of << ? For that matter, are inet's other commutator
>> and negator declarations just as broken?
I did take out the demonstrably incorrect negator links for 7.0.
We still have those other issues about CIDR/INET types though...
regards, tom lane
Sorry, I got the 2's and 1's mixed up. Yes, fixed. > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I still see this problem in 7.0. > > You do? I don't: > > regression=# SELECT * FROM a; > ip > ---------- > 10.1.1.1 > 10.1.1.2 > 10.2.1.2 > 10.2.1.1 > (4 rows) > > regression=# SELECT * FROM a WHERE ip<<'10.1/16'; > ip > ---------- > 10.1.1.1 > 10.1.1.2 > (2 rows) > > regression=# SELECT * FROM a WHERE NOT ip<<'10.1/16'; > ip > ---------- > 10.2.1.2 > 10.2.1.1 > (2 rows) > > regression=# > > regards, tom lane > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026