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 = 'ha...@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=arch...@jab.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=arch...@jab.org
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=arch...@jab.org
