The query in question:
SELECT switch, hostname, INET_NTOA(it_iFace.ip) AS IP, location,
contact, port
FROM cam
LEFT JOIN iFace ON ( switch = it_iFace.hostID )
LEFT JOIN host USING ( hostID )
LEFT JOIN arp ON ( cam.mac = arp.mac )
WHERE arp.ip <=> if( "<ip>" = "",NULL,INET_ATON("<ip>") );
(both instances of <ip> are the same value in this case)
If IP is empty, this returns an empty set, if IP exists it gives me what
I expect.
Simplifying the query so the WHERE clause reads:
WHERE arp.ip <=> NULL
...returns an empty set.
Whereas:
WHERE arp.ip IS NULL
...returns tens of thousands of records.
-----Original Message-----
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 17, 2003 8:44 AM
To: Knepley, Jim
Cc: [EMAIL PROTECTED]
Subject: RE: Null-safe equal help, please
Please reply to the list so that others can follow this discussion.
Thanks.
At 8:26 -0700 12/17/03, Knepley, Jim wrote:
>
>
>-----Original Message-----
>From: Paul DuBois [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, December 16, 2003 5:44 PM
>To: Knepley, Jim; [EMAIL PROTECTED]
>Subject: Re: Null-safe equal help, please
>
>At 15:22 -0700 12/16/03, Knepley, Jim wrote:
>>>I've got a WHERE clause:
>>>WHERE possibly_null_value IS NULL
>>>
>>>That works fine. This null-safe equal doesn't do what I expect:
>>>WHERE possibly_null_value <=> NULL
>>>
>>>The manual, and my testing, shows that NULL <=> NULL evaluates to 1,
>so
>
>>Are you saying that this is not what you expect? Why not?
>
>It is what I expect, but it doesn't seem to be the behavior.
You indicated before that your own testing shows that NULL <=> NULL
evaluates to 1.
You now say that this *doesn't* seem to be the behavior.
I don't follow you.
>
><snip>
>
>>>
>>>What I _really_ want to do is this:
>>>WHERE possibly_null_value <=> INET_ATON(<IP>)
>>>
>>>...so that if no IP is specificied it'll return those
>>>possibly_null_value columns that are, in fact, NULL.
>
>>Your requirements are unclear. I can see two ways to interpret that
>>statement:
>
>>1) You want only possibly_null_value values that are NULL.
>>2) You can possibly_null_value values that are NULL *and*, if IP is
>specified, possbly_null_value
>> values that are equal to INET_ATON(IP).
>
>>Those are not the same thing. (In other words, it's clear what you
>want only for the case that IP is NULL. >It's not clear what you want
>when IP isn't NULL.) Can you clarify?
>
>I can see where I was unclear, as I had simplified the statement (in an
>attempt to be more clear, go figure). I'm looking for case 2 that you
>described.
In that case, it looks to me (without knowing more) that the statement
you show above should do what you want. It apparently does not, so
perhaps you could post to the list a few combinations of
possibly_null_value and IP values. Indicate what results you get, and
how that differs from what you expect.
>
><snip>
>
>Thanks for your time on this.
>
>Cheers,
> Jim
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
