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/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com
Oct 11
Peter Brawley Re: Inconsistent query result.
Oct 11, 2011; 08:37
Peter Brawley
Re: Inconsistent query result.
Oct 11
Paul Halliday Re: Inconsistent query result.
Oct 11, 2011; 14:26
Paul Halliday
Re: Inconsistent query result.
Oct 11
Johan De Meersman Re: Inconsistent query result.
Oct 11, 2011; 20:13
Johan De Meersman
Re: Inconsistent query result.
Oct 11
Peter Brawley Re: Inconsistent query result.
Oct 11, 2011; 14:07
Peter Brawley
Re: Inconsistent query result.
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!