At 13:20 -0400 8/12/02, Aron Pilhofer wrote: >Hey folks. I was getting some odd and unexpected results via a web query the >other day, and I finally traced the problem down -- but it lead me to wonder >if the problem was expected or a bug. Here's the deal: > >When I initially created the MySQL table, I forgot to specify that I wanted >to allow NULL in certain fields. I populated the table, realized my mistake >and then changed the field to allow NULLs. Here's where things got weird: >When I ran a "select..where <field> is not NULL" against one of the fields, >in which there had never been data entered, I got back no results. That >happened even after I switched the field back to accept NULLs.
Changing the column to allow NULL doesn't mean that any of the existing values will be *changed* to NULL. That seems to be what you're expecting, but why? > >For kicks, I created a new table from scratch allowing nulls from the >get-go, and everything worked as expected. Fixing this particular problem >was no big deal; I just changed the select to "like """ and it worked fine. >But it led me to wonder whether this isn't a real issue. For example, if you >create the table to accept NULLs from the beginning, a count() will >correctly return an empty set. If you create the table, and then change the >field to accept NULLs a count() will return an incorrect result. > >I didn't see anything in the docs about this (or did I miss it?). Wondering >if other folks have noticed this, and if this is just one of those traps we >have to watch out for. I sure could see how this could cause serious >problems with aggregate functions if you didn't know better. > >Thanks! > > >---------------------------------------------------- >: Aron Pilhofer >: Director, Campaign Finance Information Center >: Investigative Reporters and Editors >: National Institute for Computer-Assisted Reporting >~ >: Phone: (202) 362-3223 >: Email: [EMAIL PROTECTED] >:"SQL Forever" --------------------------------------------------------------------- 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
