Let's way the output of Query 1, without the rownumber column, would be like this:
1.2.3.4
100.200.100.200
4.6.8.10
100.200.100.202
4.6.9.255
Using the ORDER BY and the @rownumber variable, we get an output that is like this:
1 1.2.3.4
2 4.6.8.10
3 4.6.9.255
4 100.200.100.200
5 100.200.100.202
My query creates two copies of this (called ipb and lead); then it joins them in such a way that lead.rownumber = ipb.rownumber + 1, the output of that should be like this:
1 1.2.3.4 4.6.8.10
2 4.6.8.10 4.6.9.255
3 4.6.9.255 100.200.100.200
4 100.200.100.200 100.200.100.202
5 100.200.100.202 NULL
Given this output, I can quickly tell that on row 2, the first and the second IPs are within the same /16 rage, and same with row 4. (I can explain how this can be done easily and with just SQL, but to keep this email short, I will skip that). So in a later query, I can then restrict my results to only the ones in which the IP pairs are within the same /16 (or /22 or whatever range I like to use). Which means we would end with an output like this:
2 4.6.8.10 4.6.9.255
4 100.200.100.200 100.200.100.202
And then I can quickly run proxy checks on some the other IPs within that range and substitute the individual IP blocks with a range block if that is more appropriate.
In a world in which we had already upgraded to MariaDB 10.2 (which, by the way, was released in 2016) I wouldn't need to do any of these ridiculous stuff! I could use a CTE or the LEAD() function conveniently. But because we are stuck with MariaDB 10.1, I need to use this approach to mimic the LEAD() function. With other SQL engines (MySQL included) this works fine, but somehow in our use case, it just never finishes. It doesn't return an error, it doesn't seem to timeout, it just ... hangs. That is surprising to me, so I thought I could pick your collective brains! :)