From: Peter Brawley
Date: October 11 2011 1:37pm
Subject: Re: Inconsistent query result.
List-Archive: http://lists.mysql.com/mysql/226013
Message-Id: <4E9446AD.50406@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/11/2011 8:11 AM, Paul Halliday wrote:
> I have the following query:
>
> SELECT COUNT(signature) AS count, MAX(timestamp) AS maxTime,
> INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as
> dst_cc, signature, signature_id, ip_proto FROM event LEFT JOIN
> mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2
> ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-10-11
> 03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc != 'US' OR map2.cc !=
> 'US') AND (map1.c_long != 'US' OR map2.c_long != 'US') GROUP BY
> src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
> ORDER BY maxTime DESC LIMIT 5000
>
> The part that is causing the strange result is probably this:
>
> AND (map1.cc != 'US' OR map2.cc != 'US') AND (map1.c_long != 'US' OR
> map2.c_long != 'US')
>
> the value could be 'US' or 'UNITED STATES' depending on user input so
> I check both table fields against their input.
>
> On my system (fbsd 5.1.48) 'US' is not shown, on another system (linux
> 5.1.54), 'US' appears in the results.
>
> Is there a better way to write this?
Is this what you mean?

SELECT
   COUNT(signature) AS count, MAX(timestamp) AS maxTime, 
INET_NTOA(src_ip), map1.
   cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc,
   signature, signature_id, ip_proto
FROM event
LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip AND map1.cc != 'US' 
AND map1.c_long != 'United States'
LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip AND map2.cc != 'US' 
AND map2.c_long != 'United States'
WHERE timestamp BETWEEN '2011-10-11 03:00:00' AND '2011-10-12 02:59:59'
GROUP BY src_ip, src_cc, dst_ip, dst_cc, signature, signature_id, ip_proto
ORDER BY maxTime DESC LIMIT 5000

PB

-----

>
> Thanks.
>