Greetings,


I am writing a perl-script to deal with IP-Addresses and Subnets.


I store IP-Addresses as unsigned ints using inet_aton and want to leave
all calculations to the SQL-Query.

my query is

Select inet_ntoa(stored_ip),... from table
where
inet_aton("$IP") > ( stored_ip & inet_aton("$SUBNET_MASK"))
and
inet_aton("$IP") < ( stored_ip | ~inet_aton("$SUBNET_MASK"));

(i.e is $IP in the same subnet as any previously stored IP-Address?)


This does not work because inet_aton($SUBNET_MASK) returns a 64bit value
and therefore the bit-negation (~) does not work as expected. (i.e.
(~inet_aton("255.255.255.0")) comes to 18446744069414584575 insted of 255.

Just to be clear: $IP and $SUBNET_MASK are two strings given by the
script itself, they are not stored in the table anywhere.


We use a MySQL5 Server and the only Mysql-book I have says inet_aton
returns either a 32bit or 64bit value without further explanation.


How do I change the return type from bigint to int in order to make this
work? The only solution I came up with, is subtracting
inet_aton("255.255.255.255.0.0.0.0") from
~inet_aton("$SUBNET_MASK"), but that is very ugly.



Thanks in Advance

Sue

"Susan Barnes" <susans-spamtrap@uni-koeln.de> wrote in message
news:48aiamFj66ouU1@individual.net...
> This does not work because inet_aton($SUBNET_MASK) returns a 64bit value

http://dev.mysql.com/doc/refman/5.0/...functions.html says:
" MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these
operators have a maximum range of 64 bits."

I suspect inet_aton is returning a correct value, but the expression is
promoted to a 64-bit expression once you use the ~ operator.

Try one of these expressions to get the correct bitwise complement:

select ~inet_aton('255.255.255.0') & inet_aton('255.255.255.255')

select ~(inet_aton('255.255.255.0') |
inet_aton('255.255.255.255.0.0.0.0'))

I was thinking one also could use CAST to force it to be a 32-bit integer,
but alas, CAST always returns a 64-bit BIGINT, ignoring the request for a
32-bit INTEGER (http://bugs.mysql.com/bug.php?id=4734). It seems that the
only use for CAST is to make the expression signed or unsigned.

Regards,
Bill K.