CODEWITHSUNDEEP
mysqlsqlperl
Peter V. Mørch
Peter V. Mørch

Reputation: 15907

SQL: Binding an undef (NULL) to the placeholder will not select rows which have a NULL value

I'm using perl 5.20 and MySQL 5.7, but I think the question is about SQL in general:

perldoc DBI says:

Binding an undef (NULL) to the placeholder will not select rows which have a NULL age! At least for database engines that conform to the SQL standard. Refer to the SQL manual for your database engine or any SQL book for the reasons for this. To explicitly select NULLs you have to say "WHERE age IS NULL".

I don't even know what to google for... My question is: What are the reasons behind = ? not matching a binding to NULL/undef? (Beyond "that is how it is defined and documented".)

I've discovered that MySQL has an operator <=> that allows comparisons with NULL and so:

my $sth = $dbh->prepare('select count(*) from table where field <=> ?');
$sth->execute(345);
$sth->execute(undef);

both work as expected. Unfortunately, the doc says:

The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

And MySQL doesn't support the IS NOT DISTINCT FROM operator :-(. So there seems to be no portable way to do this. Except for the very hackish:

my $sth = $dbh->prepare('
    select count(*) from table
    where field = ? OR ( ? IS NULL AND field IS NULL )
');
$sth->execute(345, 345);
$sth->execute(undef, undef);

or the even more hackish snippet from perldoc DBI

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq{
  SELECT fullname FROM people WHERE $sql_clause
});
$sth->execute(defined $age ? $age : ());

Is there a portable way to do WHERE FIELD = ? and have it do what I mean also with NULLs/undefs? What did I miss?

EDIT: I also came up with this workaround, which especially works great if field is a numeric type such as an INT, so we're sure the string "NULL" is not a possible non-NULL value.

my $sth = $dbh->prepare('
    select count(*) from table
    where COALESCE(field, "NULL") = COALESCE(?,"NULL")
');
$sth->execute(345);
$sth->execute(undef);

But performance goes out the window, as I don't think any indexes can be used....

Upvotes: 3

Views: 257

Answers (1)

bolav
bolav

Reputation: 6998

I understand that this is not exactly what you asked for but if you use DBIx::Class, the ORM will do that lifting for you.

my $res = $schema->resultset('table')->search({ field => [345, undef] });
print $res->count;

It will be translated to this SELECT COUNT( * ) FROM table me WHERE ( ( field = ? OR field IS NULL ) ): '345'

Upvotes: 1

Related Questions