DevelumPHP DevelumPHP - 17 days ago 4
MySQL Question

Translating a PostgreSQL operator to a MySQL equivalent?

Hi I have the following PostgreSQL statement:

SELECT sub1.date_evt AS event_date, SUM(sub1.UC_IP) as UCIP, SUM(sub1.NON_UC_IP) as OUTIP
FROM (
SELECT TO_CHAR(event_date, 'yyyy-mm-dd') as date_evt,
case when ip <<= inet('10.0.0.0/8') then 1
when ip <<= inet('128.218.0.0/16') then 1
else 0 END AS UC_IP,
case when ip <= inet('10.0.0.0/8') then 0
when ip <<= inet('128.218.0.0/16') then 0
else 1
END AS NON_UC_IP
FROM link_history
WHERE url_link = (SELECT `id` FROM links WHERE base_url = 'qt4gt8')
) AS sub1
GROUP BY sub1.date_evt
ORDER BY sub1.date_evt;


I am trying to translate it into something identical in MySQL, I have converted the data already and imported it into MySQL, the IP values have been saved using the INET_NTOA() function into an unsigned INT column in the MySQL database.

I have attempted to convert this but cannot find the answer to the operator <<= this operator I don't know what it does and cannot find documentation on the PostgreSQL website.

I thought maybe it was a bitwise equals operator or something, but I can't seem to make it work in MySQL, here is my MySQL statement.

SELECT sub1.date_evt AS event_date, SUM(sub1.UC_IP) as UCIP, SUM(sub1.NON_UC_IP) as OUTIP
FROM (
SELECT event_date as date_evt,
case
when ip << INET_ATON('10.0.0.8') then 1
when ip << INET_ATON('128.218.0.16') then 1
else 0 END AS UC_IP,
case
when ip << INET_ATON('10.0.0.8') then 0
when ip << INET_ATON('128.218.0.16') then 0
else 1
END AS NON_UC_IP FROM link_history
WHERE url_link = (SELECT `id` FROM links WHERE base_url = 'qt4gt8')
) AS sub1
GROUP BY sub1.date_evt
ORDER BY sub1.date_evt;


When running this I get no results, which is incorrect, I should get 16 rows from my dataset.

Also MySQL doesn't seem to like the IP range value such as '169.230.0.0/16' so I have just placed it in the highest range for the given IP by deleting the 0\ and keeping it either 8/16 for the given IP, I am not sure if this will work the same. But without knowing how to resolve for <<= I cannot fix the rest of the query.

UPDATE:

Ok with the latest information I was given I have the following query:

SELECT sub1.date_evt AS event_date, SUM(sub1.UC_IP) as UCIP, SUM(sub1.NON_UC_IP) as OUTIP
FROM (
SELECT event_date as date_evt,
case
WHEN INET_ATON(ip) >= INET_ATON('10.0.0.0') AND INET_ATON(ip) <= (INET_ATON('10.0.0.0') + (1 << (32-8) - 1)) THEN 1 #8
WHEN INET_ATON(ip) >= INET_ATON('128.218.0.0') AND INET_ATON(ip) <= (INET_ATON('128.218.0.0') + (1 << (32-16) - 1)) then 1 #128.218.0.16
WHEN INET_ATON(ip) >= INET_ATON('64.54.0.0') AND INET_ATON(ip) <= (INET_ATON('64.54.0.0') + (1 << (32-16) - 1)) then 1 #64.54.0.16
WHEN INET_ATON(ip) >= INET_ATON('169.230.0.0') AND INET_ATON(ip) <= (INET_ATON('169.230.0.0') + (1 << (32-16) - 1)) then 1 #169.230.0.16
else 0 END AS UC_IP,
case
WHEN INET_ATON(ip) >= INET_ATON('10.0.0.0') AND INET_ATON(ip) <= (INET_ATON('10.0.0.0') + (1 << (32-8) - 1)) THEN 0 #8
WHEN INET_ATON(ip) >= INET_ATON('128.218.0.0') AND INET_ATON(ip) <= (INET_ATON('128.218.0.0') + (1 << (32-16) - 1)) THEN 0 #128.218.0.16
WHEN INET_ATON(ip) >= INET_ATON('64.54.0.0') AND INET_ATON(ip) <= (INET_ATON('64.54.0.0') + (1 << (32-16) -1)) THEN 0 #64.54.0.16
WHEN INET_ATON(ip) >= INET_ATON('169.230.0.0') AND INET_ATON(ip) <= (INET_ATON('169.230.0.0') + (1 << (32-16) - 1)) THEN 0 #169.230.0.16
else 1
END AS NON_UC_IP FROM link_history
WHERE url_link = (SELECT `id` FROM links WHERE base_url = 'qt4gt8')
) AS sub1
GROUP BY sub1.date_evt
ORDER BY sub1.date_evt


AND my database table now looks like this:

CREATE TABLE `link_history` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`url_link` int(11) DEFAULT NULL,
`event_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip` varchar(15) DEFAULT NULL,
`cidr` varchar(15) DEFAULT NULL,
`ref_url` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=930100 DEFAULT CHARSET=latin1;



id url_link event_date ip cidr ref_url
3204 70 2011-02-05 07:20:00 67.180.61.248 32 http://ucsfmeded.blogspot.com/2010/11/calvin-chou-md-phd-appointed-academy.html


However I am finding that the results from the MYSQL table do not exactly match the results from the Postgres table. In PostgreSQL I get 16 results for the data, in the MySQL I am getting 29 rows. The SUM columns are also off. It looks to me like the GROUP BY is not working because of the time allocation. Do I have to break the DATE-TIME up into separate columns or is there a way to do a GROUP BY where it is only counting the DATE? How can I be sure the IP RANGE is working properly?

Answer

That operator is known 'Is contained within or or equals'

when ip <<= inet('128.218.0.0/16') then 1

so what you are doing here is trying to find out if your ip belongs to the 128.218.0.0/16 network. That is whether the IP address is within 128.218.0.0 to 128.218.0.255

So you can do this with a numeric comparision. But the question is why are you moving from a feature rich, more powerfull, faster database to a one that does not have so many features, slower and in some cases down right broken?

The above is roughly equivalent to

when ip >= inet_aton('128.218.0.0') AND ip <= INET_ATON('128.218.0.255) then 1

Similarly

when ip >= inet_aton('10.0.0.0') AND ip <= INET_ATON('10.0.255.255) then 1