Bug #70286 Case does compare against null

• Submitted: 10 Sep 2013 15:38
• Modified: 10 Oct 2013 21:17
• Reporter: George Kremenliev
• Status: No Feedback
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[10 Sep 2013 15:38] George Kremenliev

Description:
It looks like that case cannot compare the value of a column to null

both syntaxes do not work:

case nullable_column when null then ...
case nullable_column when nullable_column is null then ...

(if function works for the above)

How to repeat:
create my_table (nullable_column datetime);

insert mytable values (null), (now());

select case nullable_column when null then now() else nullable_column end from my_table;

expected is that the select will return a value always.

[10 Sep 2013 21:17] MySQL Verification Team

http://dev.mysql.com/doc/refman/5.7/en/case.html
This syntax cannot be used to test for equality with NULL because NULL = NULL is false. See Section 3.3.4.6, “Working with NULL Values”.

A test:
---
drop table if exists f;
create table f(a datetime default null);
insert f values (null), ('20130101120020');
select case a when null then now() else a end from f; #expect a null return
select ifnull(a,now()) from f; 
select coalesce(a,now()) from f;
----

So the situation seen seems expected..

[11 Oct 2013 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".