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,
>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.
>: Aron Pilhofer
>: Director, Campaign Finance Information Center
>: Investigative Reporters and Editors
>: National Institute for Computer-Assisted Reporting
>: Phone: (202) 362-3223
>: Email: aron@stripped