Обсуждение: Postgresql INET select and default route ?

Hi,

create table test(a inet);
insert into test values ('0.0.0.0/0');
insert into test values ('10.1.2.3');
=> select * from test;
     a
-----------
 0.0.0.0/0
 10.1.2.3
(2 rows)


This works as expected .....
=> select * from test where a <<= '10.1.2.3';
    a
----------
 10.1.2.3
(1 row)


This does not work as expected ....
=> select * from test where a <<= '11.1.2.3';
 a
---
(0 rows)


Surely the second one should return the default route entry (i.e. 0.0.0.0/0)  ?

Tim Smith wrote:
> create table test(a inet);
> insert into test values ('0.0.0.0/0');
> insert into test values ('10.1.2.3');
> => select * from test;
>      a
> -----------
>  0.0.0.0/0
>  10.1.2.3
> (2 rows)
> 
> 
> This works as expected .....
> => select * from test where a <<= '10.1.2.3';
>     a
> ----------
>  10.1.2.3
> (1 row)
> 
> 
> This does not work as expected ....
> => select * from test where a <<= '11.1.2.3';
>  a
> ---
> (0 rows)
> 
> 
> Surely the second one should return the default route entry (i.e. 0.0.0.0/0)  ?

I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
query if you expect it to be returned by the second.
Is that an oversicht?

I guess your problem is a misunderstanding what the operator means:

"<<=" is called "is contained by or equals".
The definition (from the PostgreSQL source) is that x <<= y iff:
- x has at least as many relevant bits as y and
- all relevant bits of y are the same in x.

So the following would produce what you expect:

test=> select * from test where '11.1.2.3' <<= a;
     a
-----------
 0.0.0.0/0
(1 row)

Yours,
Laurenz Albe

Hi Albe,

Apologies for the delayed reply.

Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
query if you expect it to be returned by the second.
Is that an oversicht?

Thanks for the question, but no, it wasn't an oversight, I only am
looking for 0.0.0.0/0 to be returned if there is no more specific
match.

On 9 December 2015 at 12:45, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Tim Smith wrote:
>> create table test(a inet);
>> insert into test values ('0.0.0.0/0');
>> insert into test values ('10.1.2.3');
>> => select * from test;
>>      a
>> -----------
>>  0.0.0.0/0
>>  10.1.2.3
>> (2 rows)
>>
>>
>> This works as expected .....
>> => select * from test where a <<= '10.1.2.3';
>>     a
>> ----------
>>  10.1.2.3
>> (1 row)
>>
>>
>> This does not work as expected ....
>> => select * from test where a <<= '11.1.2.3';
>>  a
>> ---
>> (0 rows)
>>
>>
>> Surely the second one should return the default route entry (i.e. 0.0.0.0/0)  ?
>
> I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
> query if you expect it to be returned by the second.
> Is that an oversicht?
>
> I guess your problem is a misunderstanding what the operator means:
>
> "<<=" is called "is contained by or equals".
> The definition (from the PostgreSQL source) is that x <<= y iff:
> - x has at least as many relevant bits as y and
> - all relevant bits of y are the same in x.
>
> So the following would produce what you expect:
>
> test=> select * from test where '11.1.2.3' <<= a;
>      a
> -----------
>  0.0.0.0/0
> (1 row)
>
> Yours,
> Laurenz Albe

Tim Smith wrote:
> Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
> query if you expect it to be returned by the second.
> Is that an oversicht?
> 
> Thanks for the question, but no, it wasn't an oversight, I only am
> looking for 0.0.0.0/0 to be returned if there is no more specific
> match.

I see, but then you'll have to use a different query:

SELECT a from test where '11.1.2.3' <<= a
   ORDER BY masklen(a) DESC LIMIT 1;

Yours,
Laurenz Albe

Fabuous !  Thank you !

On 14 December 2015 at 07:52, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Tim Smith wrote:
>> Re: I am surprised that you don't expect "0.0.0.0/0" to be returned by the first
>> query if you expect it to be returned by the second.
>> Is that an oversicht?
>>
>> Thanks for the question, but no, it wasn't an oversight, I only am
>> looking for 0.0.0.0/0 to be returned if there is no more specific
>> match.
>
> I see, but then you'll have to use a different query:
>
> SELECT a from test where '11.1.2.3' <<= a
>    ORDER BY masklen(a) DESC LIMIT 1;
>
> Yours,
> Laurenz Albe