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. 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
