List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:October 11 2011 5:26pm
Subject:Re: Inconsistent query result.
View as plain text  
On Tue, Oct 11, 2011 at 10:37 AM, Peter Brawley
<peter.brawley@stripped> 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 = 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
>

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 !='CA' AND map1.cc !='US' AND map1.cc !='LV'... ?
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