Hi, Getting non-empty results when comparing something with NULL seems very odd to me too. Even if it is undefined, id expect to see an error message telling me i shouldnt compare an undefined variable rather than receiving results.
Why not ask someone to change things so that "WHERE xx=NULL" gives the same results as " WHERE xx IS NULL". Or would that be an unusual thing to request. Andrew Murphy -----Original Message----- From: jim barchuk [mailto:[EMAIL PROTECTED]] Sent: 27 October 2001 8:32 pm To: Paul DuBois Cc: [EMAIL PROTECTED] Subject: Re: Problem with query Hi Paul! On Sat, 27 Oct 2001, Paul DuBois wrote: > At 9:38 -0400 10/27/01, jim barchuk wrote: > >Hi Carl! > > > >> Paul DuBois writes: > >> > >> > NULL basically means "unknown value", so saying WHERE x = NULL cannot > >> > work, even if x is NULL. That means "where one unknown value = another > >> > unknown value", which cannot be evaluated with any certainty. :-) > >> > >> It is sort of odd, though, that x = NULL returns something which > >> appears to be undefined rather than something well-defined > >> (such as false all the time) or an error (which could be said to > >> be well-defined, if errors are considered to be valid responses > >> to queries...). > > > >Well which would you prefer, false or error? Dealing with errors is > >annoying if not necessary. 'False' is incorrect -if- the field is allowed > >to contain 'nothing' because NULL means it does contain nothing. > > I think what Carl meant was that it was strange that the WHERE x = NULL > query returned a non-empty result set. I noticed that in the original > message, too. I don't know what accounts for it. I think WHERE x = NULL 'may' return non-empty results, or not, simply because NULL is undefined and most bets are off for that query. Undefined doesn't mean random. Yes, http://www.mysql.com/doc/W/o/Working_with_NULL.html does say "In MySQL, 0 or NULL means false and anything else means true." In this case I take undefined to mean that what appears to work this time may not work next time, depending on the structure of the query, or even if it works consistently with one version of MySQL it may not with another. Quoting page 47 of your book, "If you attempt to use NULL with the usual arithmetic comparison operators, the result is undefined." Similarly, quoting http://www.mysql.com/doc/P/r/Problems_with_NULL.html , "To look for NULL values, you must use the IS NULL test." 'Must' is a very strong word and I take it as gospel. I think I have less problem with trying to understand what accounts for something that may appear odd to others is that I don't try to think about 'why' when given such clear instrutions. I had a -very- educational experience with NULL with my very first attempt at MySQL --> HTML rendering. TABLE cells that -appeared- to contain something but shouldn't have and really didn't. Had my head spinning for hours until I finally got a grip on the idea that NULL is a Very Strange Thing and that I *must* deal with it only in certain ways. NULL rocks. :) Have a :) day! jb -- jim barchuk [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
