BUG #13488: Wrong netmask calculation

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

-----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-----