NULL-safe (in)equality <=>

It is, of course, generally considered more natural to make equality primary, not inequality, but that symbol that MySQL uses for NULL-safe equality, <=>, looks much more like inequality than equality. Furthermore, I find that in my code I am far oftener interested in NULL-safe _in_equality than equality. If I write
IF A = B THEN
then if one is NULL and the other not, and the code is such that never are both NULL, well, for my purpose they are not equal: so good. But if I write
IF A <> B THEN
often I want it NULL-safe, for if one is NULL and the other not, I want that true--and MySQL s symbol for NULL-safe equality looks just right for inequality.

*sigh*


--
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: NULL-safe (in)equality <=>

Hi,

On 1-10-2011 21:51, Halász Sándor wrote:
> It is, of course, generally considered more natural to make equality
> primary, not inequality, but that symbol that MySQL uses for
> NULL-safe equality,<=>, looks much more like inequality than
> equality.

The whole concept and the name of this operator is wrong IMO. There is
nothing NULL-*safe* about it. Equal and unequal operators are in fact
more NULL-*safe* than <=>.

> But if I write IF A <> B THEN often I want it NULL-safe, for if
> one is NULL and the other not, I want that true

You are not using NULL as the original concept of it was. NULL means
that the value is undefined or unknown.

If a value is undefined it may have *any* value.
So, if you evaluate (A = NULL) the NULL part can have *any* value, even
A. The result of this compare can only be NULL, because it is not known
whether it's equal or unequal.

Because of this (NULL = NULL) must be NULL too. (NULL <> NULL) must also
result in NULL. The result is just as undefined/unknown as both values
which were compared.

The usual solution in the case you describe is that you use a normal
value in the range of the field type which is not used normally. E.g.
for an INT field where you only use values of zero or larger you can use
e.g. -1 as a special value.

If you insist on using NULL and the crazy <=> operator you can use NOT
to invert it:
SELECT NOT(A <=> B);

--
Kind regards / met vriendelijke groet,

Jigal van Hemert.

--
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: NULL-safe (in)equality <=>; overloaded NULL

>>>> 2011/10/02 15:01 +0200, Jigal van Hemert >>>>
You are not using NULL as the original concept of it was. NULL means that the value is undefined or unknown.
<<<<<<<<
That is quite true, especially in a table. But, almost from the beginning, NULL was overloaded:

set @m = (select sins from emailinglist where email = 'handl@gmail.com');

This is allowed if the query yields at most one row. If it yields no row "@m" is made NULL--and if field "sins" may be NULL (not in my case), the outcome is indeterminate.

With the aggregate functions MAX and MIN there is a subtler problem: over an empty set they yield NULL, even as over a set where every matched value is NULL. It is, maybe, more natural if MAX over an empty set yields bottom, and MIN over an empty set yields top (likewise for BIT_OR and BIT_AND).

I once worked on a programming language with symbols for no data, bad result, indeterminate result, .... One can go too far.

But I originally said that the symbol <=> looks more like inequality than equality.


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