List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 12 2002 5:36pm
Subject:Re: NULL values -- expected behavior or bug?
View as plain text  
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: aron@stripped
>:"SQL Forever"

Thread
fulltext index update crashRyan Barber12 Aug
  • NULL values -- expected behavior or bug?Aron Pilhofer12 Aug
    • Re: NULL values -- expected behavior or bug?Paul DuBois12 Aug
      • RE: NULL values -- expected behavior or bug?Aron Pilhofer12 Aug
        • Re: NULL values -- expected behavior or bug?Gerald Clark13 Aug
    • Re: NULL values -- expected behavior or bug?Keith C. Ivey12 Aug
  • Re: fulltext index update crashSergei Golubchik12 Aug