Thread: BUG #7575: "between" does not work properly with inet/cidr addresses

The following bug has been logged on the website:

Bug reference:      7575
Logged by:          Mr-4
Email address:      mr.dash.four@googlemail.com
PostgreSQL version: 9.1.4
Operating system:   Linux (kernel 3.5)
Description:        =


1. select '10.1.1.0/24'::inet between '10.1.1.0'::inet and
'10.1.1.255'::inet gives me FALSE.

2. select '10.1.0.0/24'::inet between '10.1.0.0'::inet and
'10.1.255.255'::inet also produces FALSE.

To my understanding, 10.1.1.0/24 covers the range 10.1.1.0-10.1.1.255, so
the first statement above should produce TRUE, not FALSE. It is similar to
"select 1 between 1 and 1" which, quite rightly, produces TRUE.

Same goes for the second statement above: 10.1.0.0/24 covers the range
10.1.0.0-10.1.0.255, which falls fully within the 10.1.0.0-10.1.255.255
(10.1.0.0/16) range.

Am I missing something obvious?

mr.dash.four@googlemail.com writes:
> 1. select '10.1.1.0/24'::inet between '10.1.1.0'::inet and
> '10.1.1.255'::inet gives me FALSE.

> Am I missing something obvious?

10.1.1.0/24 is different from, and sorts before, 10.1.1.0/32
(which is what '10.1.1.0'::inet is an abbreviation for).

You might find that the net address inclusion operators (<< and friends)
provide the semantics you're looking for.  BETWEEN only knows about
btree sort ordering, which is fundamentally a brick or two shy of a
load when considering two-dimensional quantities such as netmasks.

            regards, tom lane

> 10.1.1.0/24 is different from, and sorts before, 10.1.1.0/32
> (which is what '10.1.1.0'::inet is an abbreviation for).
>
> You might find that the net address inclusion operators (<< and friends)
> provide the semantics you're looking for.
That isn't possible in my case, because on the right side I have IP
ranges (from-to) and currently there isn't a PostgreSQL function which
converts IP ranges to cidr/inet, but even if there was such function
available, this may produce more than one cidr/inet ranges, so it won't
be of much use. Employing << and friends is of no use either, as they
work on inet/cidr on the right side - I can't use something like
"10.1.1.0/24 << (10.1.1.0-10.1.1.255)" for example.

>   BETWEEN only knows about
> btree sort ordering, which is fundamentally a brick or two shy of a
> load when considering two-dimensional quantities such as netmasks.
>
If BETWEEN can't handle operations between cidr/inet and IP ranges
(which is what I highlighted in the initial bug report), it should
either be fixed to produce the correct result or it should return an
error. Leaving things as they are can't be allowed.

Mr Dash Four <mr.dash.four@googlemail.com> writes:
> That isn't possible in my case, because on the right side I have IP ranges
> (from-to) and currently there isn't a PostgreSQL function which converts IP
> ranges to cidr/inet, but even if there was such function available, this may
> produce more than one cidr/inet ranges, so it won't be of much use.

Have you tried using the ip4r extension yet?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

> Have you tried using the ip4r extension yet?
>
First time I am seeing this - well done, exactly what I needed. That
doesn't address the above bug though - the "between" functionality is
still wrong and needs to be corrected.