Обсуждение: BUG #13488: Wrong netmask calculation

The following bug has been logged on the website:

Bug reference:      13488
Logged by:          Taras
Email address:      stn@inbox.ru
PostgreSQL version: 9.4.4
Operating system:   CentOS 6.5
Description:

database=# select inet(routedest), inet(routemask) from iprouteinterface
group by routedest, routemask order by routedest limit 3;
   inet    |    inet
-----------+-------------
 0.0.0.0   | 0.0.0.0
 10.1.0.0  | 255.255.0.0
 10.10.0.0 | 255.255.0.0
(3 rows)

database=# select inet(routedest), cidr(routemask) from iprouteinterface
group by routedest, routemask order by routedest limit 3;
   inet    |      cidr
-----------+----------------
 0.0.0.0   | 0.0.0.0/32
 10.1.0.0  | 255.255.0.0/32
 10.10.0.0 | 255.255.0.0/32
(3 rows)
database=# select routedest, network(inet(routemask)) from iprouteinterface
group by routedest, routemask order by routedest limit 3;
 routedest |    network
-----------+----------------
 0.0.0.0   | 0.0.0.0/32
 10.1.0.0  | 255.255.0.0/32
 10.10.0.0 | 255.255.0.0/32
(3 rows)

stn@inbox.ru writes:
> database=# select inet(routedest), inet(routemask) from iprouteinterface
> group by routedest, routemask order by routedest limit 3;
>    inet    |    inet
> -----------+-------------
>  0.0.0.0   | 0.0.0.0
>  10.1.0.0  | 255.255.0.0
>  10.10.0.0 | 255.255.0.0
> (3 rows)

> database=# select inet(routedest), cidr(routemask) from iprouteinterface
> group by routedest, routemask order by routedest limit 3;
>    inet    |      cidr
> -----------+----------------
>  0.0.0.0   | 0.0.0.0/32
>  10.1.0.0  | 255.255.0.0/32
>  10.10.0.0 | 255.255.0.0/32
> (3 rows)
> database=# select routedest, network(inet(routemask)) from iprouteinterface
> group by routedest, routemask order by routedest limit 3;
>  routedest |    network
> -----------+----------------
>  0.0.0.0   | 0.0.0.0/32
>  10.1.0.0  | 255.255.0.0/32
>  10.10.0.0 | 255.255.0.0/32
> (3 rows)

You did not say what you think is wrong here, but AFAICS all of these
results are probably per the documentation (I say "probably" because,
without having seen the input data, it's not entirely clear what
calculations you're actually doing).

I suspect what you are wishing for is a function that would take the
inputs "10.1.0.0" and "255.255.0.0" and produce the CIDR value 10.1/16.
That would be a reasonable thing to offer, perhaps, but it's not there
now.  In the meantime you could probably build it easily enough as a
SQL function, at least for the set of mask values that actually occur
in your data.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 06.07.2015 um 18:15 schrieb Tom Lane:
> stn@inbox.ru writes:
>> database=# select inet(routedest), inet(routemask) from
>> iprouteinterface group by routedest, routemask order by routedest
>> limit 3; inet    |    inet -----------+------------- 0.0.0.0   |
>> 0.0.0.0 10.1.0.0  | 255.255.0.0 10.10.0.0 | 255.255.0.0 (3 rows)
>
>> database=# select inet(routedest), cidr(routemask) from
>> iprouteinterface group by routedest, routemask order by routedest
>> limit 3; inet    |      cidr -----------+---------------- 0.0.0.0
>> | 0.0.0.0/32 10.1.0.0  | 255.255.0.0/32 10.10.0.0 |
>> 255.255.0.0/32 (3 rows) database=# select routedest,
>> network(inet(routemask)) from iprouteinterface group by
>> routedest, routemask order by routedest limit 3; routedest |
>> network -----------+---------------- 0.0.0.0   | 0.0.0.0/32
>> 10.1.0.0  | 255.255.0.0/32 10.10.0.0 | 255.255.0.0/32 (3 rows)
>
> You did not say what you think is wrong here, but AFAICS all of
> these results are probably per the documentation (I say "probably"
> because, without having seen the input data, it's not entirely
> clear what calculations you're actually doing).
>
> I suspect what you are wishing for is a function that would take
> the inputs "10.1.0.0" and "255.255.0.0" and produce the CIDR value
> 10.1/16. That would be a reasonable thing to offer, perhaps, but
> it's not there now.  In the meantime you could probably build it
> easily enough as a SQL function, at least for the set of mask
> values that actually occur in your data.

That "function" is an operator: ||

Though, inet() only accepts the short netmask format ("/0" and "/16"
in your example), so you'll need to convert that first, like in e.g.:
http://postgresql.nabble.com/dotted-quad-netmask-conversion-td3295932.ht
ml

I'm dealing with data from those DBMS' that don't offer proper
datatypes quite often, and I'm pretty sure what you're looking for is
something like (using the functions in that mailthread, at $WORK I
have something quite similar in place):


SELECT proper.net, network(proper.net), netmask(proper.net) -- [, etc.
pp.]
FROM (
    SELECT
    inet( routedest || '/' ||
         netmask_bits(inet_to_longip(routemask))
    ) AS net
    FROM iprouteinterface
) proper


Best regards,
- --
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)

iQEcBAEBAgAGBQJVoSrGAAoJEBAQrmsyiTOM7xMIAL4Ndd5Rxi4rodoMoytqDnCY
Zh9t9xB9fHBZknXT/wluiLlCj4U/t1ld0MKOLIlRsuRSe6vDxudbKGgQz5yBLBJq
BjZ2RPlhUn62J3EY8Jj5YPPg3s3xZhSEGgLN/g7loihLkfLglTcY1nQKwLUX8ifg
FdK70xGxZj6QXqBvnGHDldJZL28RdUAXcdzJygd1iMPQCHoBtgK7VUGNXq9ZYRGY
js2duWYpXqHaESMRVw2JJJFW/jSEvmYV9WMITxZJe/+wRS6rF+VG5FL1SkKofkdZ
abhoYcKRU1GA7/hCwu28jyT3PreL4ZnqrDJWiArDmD0uKHEpZ2oySkjGk0ENt1I=
=Y2zl
-----END PGP SIGNATURE-----