Inconsistent query result.

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/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inconsistent query result.

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.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inconsistent query result.

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'... ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Inconsistent query result.

----- Original Message -----
> From: "Paul Halliday"
>
> 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'... ?

Yes, although "not in ()" is probably going to look a bit more manageable.

I'm also not a big fan of putting where predicates in the join clause - it's a dirty trick - although in some cases it does shave quite a bit off the execution time.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Inconsistent query result.

On 10/11/2011 12:26 PM, Paul Halliday wrote:
> 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 = 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?
Sure. At some point, though, you might consider an intermediate table to
precompute/hide some of the complexity.

PB

-----
> 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'... ?
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org