Horton

Thread

  1. INET operators and NOT

    Tomas Cerha <t.cerha@sh.cvut.cz> — 1999-12-28T20:14:46Z

    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

  2. Re: [BUGS] INET operators and NOT

    Tom Lane <tgl@sss.pgh.pa.us> — 1999-12-28T23:35:44Z

    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

  3. Re: INET operators and NOT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-01T22:22:10Z

    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

  4. Re: [HACKERS] Re: INET operators and NOT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-01T22:23:18Z

    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, Pennsylvania 19026

  5. Re: [HACKERS] Re: INET operators and NOT

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-01T22:42:28Z

    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

  6. Re: INET operators and NOT

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-01T22:46:36Z

    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

  7. Re: INET operators and NOT

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-01T23:36:01Z

    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