List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:October 11 2011 1:11pm
Subject:Inconsistent query result.
View as plain text  
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?

Thanks.

-- 
Paul Halliday
http://www.squertproject.org/
Thread
Inconsistent query result.Paul Halliday11 Oct
  • Re: Inconsistent query result.Peter Brawley11 Oct
    • Re: Inconsistent query result.Paul Halliday11 Oct
      • Re: Inconsistent query result.Johan De Meersman11 Oct
      • Re: Inconsistent query result.Peter Brawley11 Oct