MySQL Timestamp and NULL – ANNOYING Part 2

Today I discovered a new bug/feature in MySQL that is very annoying and ALSO involves NULL (see previous post). In short, a NULL-enabled column can’t be set to NULL if it is of the type timestamp.

So here’s the run down.

If you create a timestamp column that is…

  • NULL enabled and has a default value to NULL, things work exactly as you imagine. You can set it to NULL, you can set it to other time values, and then set it back to NULL. No problem.
  • NULL enabled and has a default value of “0000-00-00 00:00:00” or CURRENT_TIMESTAMP, it can NOT be set to NULL. Ever. If you try to set it to NULL, it will instead *DANGER DANGER* set it to CURRENT_TIMESTAMP!

Did you catch that? NULL is converted to CURRENT_TIMESTAMP in a NULL enabled table without NULL set as the default value. Can you say insanely stupid bug? Well, supposedly this is a legacy feature, but you know how I feel about that.

To fix this, make sure your NULL enabled timestamp columns are correctly defaulted to NULL.

I confirmed this DOES NOT apply to datetime columns. Great.

Isset and NULL, Array Keys and NULL – ANNOYING Part 1

I have two VERY annoying bugs to share with you in PHP. After this, I’ll make a post about an annoying thing I found in MySQL regarding NULL values too.

This is a relatively known “bug” to some people, but often forgotten. In fact, I learned about this over a year ago, but completely forgot until now when I came across impossible bugs caused by it. The function isset() returns FALSE for variables equal to NULL, despite the fact they are set!!

The PHP function isset() returns whether a variable “is set” or “does exist.” UNFORTUNATELY, that’s only half true. Take the two following code samples. One will act as you think, and the other will not.

$myName = ‘Michi’;
isset($myName); // TRUE

$myName = NULL;
isset($myName); // FALSE. @$!%#*?

For those of you screaming bloody murder, it gets worse. Consider the following:

$names = array();
$names[‘a’] = ‘Michi’;
$names[‘b’] = ‘Kono’;
$names[‘c’] = NULL;

Now observe the results of the following applied to that array.

isset($names); // TRUE
isset($names[‘a’]); // TRUE!
isset($names[‘b’]); // TRUE…
isset($names[‘c’]); // FALSE, %@#*^!

That’s right, the function ALSO ignores NULL array keys!!!

 Anyway, I’m sure this will introduce more than its fair share of bugs in code without people ever even realizing it. Ever. Since this throws up no warnings and will very, very rarely be a factor. But trust me, sooner or later, it will cause bugs, whether or not you’ve noticed.

Here are the fixes to this insanely stupid bug. Well, some people call it a “feature,” but it’s a bug in my book. That or the function should be renamed to not_null().

array_key_exists(‘myName’ ,get_defined_vars()); // TRUE
array_key_exists(‘c’, $names); // TRUE

The first line checks to see if the variable is set by comparing it to an array that contains all variables in the current scope. The second line checks to see if the specific key is set (obviously).

Isset() is only good for variables you know will never be NULL, and for cases where you are not doing type-sensitive checks (triple equals sign). Luckily, that’s 99% of the time in PHP.