From: Paul Halliday Date: October 11 2011 5:26pm Subject: Re: Inconsistent query result. List-Archive: http://lists.mysql.com/mysql/226025 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley wrote: > 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 =3D map1.ip LEFT JOIN mappings AS map2 >> ON event.dst_ip =3D map2.ip WHERE timestamp BETWEEN '2011-10-11 >> 03:00:00' AND '2011-10-12 02:59:59' AND (map1.cc !=3D 'US' OR map2.cc != =3D >> 'US') AND (map1.c_long !=3D 'US' OR map2.c_long !=3D '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 !=3D 'US' OR map2.cc !=3D 'US') AND (map1.c_long !=3D 'US' = OR >> map2.c_long !=3D '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 > =A0COUNT(signature) AS count, MAX(timestamp) AS maxTime, INET_NTOA(src_ip= ), > map1. > =A0cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc, > =A0signature, signature_id, ip_proto > FROM event > LEFT JOIN mappings AS map1 ON event.src_ip =3D map1.ip AND map1.cc !=3D '= US' AND > map1.c_long !=3D 'United States' > LEFT JOIN mappings AS map2 ON event.dst_ip =3D map2.ip AND map2.cc !=3D '= US' AND > map2.c_long !=3D '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_prot= o > ORDER BY maxTime DESC LIMIT 5000 > > PB > Is it OK to keep adding to those joins? In a view there could be say 50 countries. The user can keep on adding more to exclude. So would I just expand on the ANDs like so: AND map1.cc !=3D'CA' AND map1.cc !=3D'US' AND map1.cc !=3D'LV'... ?