RE: Null-safe equal help, please

[Paul DuBois]

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]