Thread: BUG #11734: The "<<=" operator on "inet" values does not return the expected result.

The following bug has been logged on the website:

Bug reference:      11734
Logged by:          Michel Albert
Email address:      michel@albert.lu
PostgreSQL version: 9.1.14
Operating system:   Ubuntu 12.04.5 LTS
Description:

Just now I ran into a peculiarity with the '<<=' operator. I have a table
which contains both "network" and "host" entries. Their addresses are stored
as type "inet".

Sidenote:
    I am aware that it is conceptually bad to have both hosts and networks
in the same table, and it is planned to be changed in the future. But for
now, that's how it is.

The best way to explain this is with a quick example script:

    CREATE TABLE inettest (
        value inet UNIQUE
    );

    INSERT INTO inettest VALUES
        ('1.2.1.0/24'),
        ('1.2.1.1/24'),
        ('1.2.1.2/24'),
        ('1.2.2.0/24'),
        ('1.2.3.0/24'),
        ('1.2.3.1/24'),
        ('1.2.3.2/24'),
        ('1.2.3.3/24'),
        ('1.2.3.4/24')
        ;

    SELECT * FROM inettest WHERE value <<= '1.2.3.1/24'::inet;


I would expect that the above select would only return *one* row. The one
with the host '1.2.3.1/24'. Instead, it returns *all* rows of the
'1.2.3.0/24' network.

I am not sure if this result is correct (by design), or if it is indeed a
bug. In any case, I have solved the issue on my end in the client code, but
thought I would let you know nonetheless :)

michel@albert.lu writes:
> The best way to explain this is with a quick example script:

>     CREATE TABLE inettest (
>         value inet UNIQUE
>     );

>     INSERT INTO inettest VALUES
>         ('1.2.1.0/24'),
>         ('1.2.1.1/24'),
>         ('1.2.1.2/24'),
>         ('1.2.2.0/24'),
>         ('1.2.3.0/24'),
>         ('1.2.3.1/24'),
>         ('1.2.3.2/24'),
>         ('1.2.3.3/24'),
>         ('1.2.3.4/24')
>         ;

>     SELECT * FROM inettest WHERE value <<= '1.2.3.1/24'::inet;

> I would expect that the above select would only return *one* row. The one
> with the host '1.2.3.1/24'. Instead, it returns *all* rows of the
> '1.2.3.0/24' network.

The PG documentation is pretty clear on this point:

  The operators <<, <<=, >>, >>=, and && test for subnet inclusion. They
  consider only the network parts of the two addresses (ignoring any host
  part) and determine whether one network is identical to or a subnet of
  the other.
  [ first para in "Network Address Functions and Operators" ]

So <<= should return true for any two of the addresses you cite here.

Personally I'd use CIDR, not INET, for anything I was thinking of as a
network ID rather than a single host's ID.

            regards, tom lane