Bug #210 SELECT WHERE tuble = NULL (SQL not handled correct)

• Submitted: 29 Mar 2003 6:11
• Modified: 29 Mar 2003 7:31
• Reporter: Poul-Erik Hansen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Linux (Linux Redhat 8.0 I386)
• CPU Architecture: Any

[29 Mar 2003 6:11] Poul-Erik Hansen

Description:
The WHERE statment in a SELECT is not able to handle the NULL value correct in a compare.

No tubles are selected in this case:
select * from a left join b on a.a_key = b.b_key WHERE b.b_key = NULL;

A work around the prblem is:
select *,IFNULL(b.b_key,0)  from a left join b on a.a_key = b.b_key
WHERE IFNULL(b.b_key,0) = 0;

This dosn't work, it gives a syntax error:
select *,IFNULL(b.b_key,0) AS c  from a left join b on a.a_key = b.b_key
WHERE c = 0;

How to repeat:
CREATE DATABASE error_demo;
USE error_demo;
create table a (
a_key    integer,
a_name   varchar(129));
create table b (
b_key    integer,
b_name   varchar(129));
nsert into a (a_key,a_name) values (1,"tuble 1");
insert into a (a_key,a_name) values (2,"tuble 2");
insert into b (b_key,b_name) values (1,"table b tuble 1");
select * from a left join b on a.a_key = b.b_key WHERE b.b_key = NULL;

[29 Mar 2003 7:31] Indrek Siitan

This is not a bug, but rather a way how NULLs are handled in SQL.  You can read more about NULL values in our user's manual at: http://www.mysql.com/doc/en/Working_with_NULL.html