Bug #2265 Returns wrong result on string query with null or empty

• Submitted: 3 Jan 2004 6:38
• Modified: 3 Jan 2004 10:56
• Reporter: Kenneth Nilsen
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.17
• OS: Windows (Windows 2000 sp4)
• CPU Architecture: Any

[3 Jan 2004 6:38] Kenneth Nilsen

Description:
With query on a table:
select count(id) from myTable where myField = '';
or
select count(id) from myTable where myField = null;

returns 0 or wrong result even if there are records containing NULL in myField.

By listing all fields with:
select * from myTable;
the empty fields shows up.

In this table there are about 1000 records, but 3 doesn't get counted. The rest where ok. I tried to run the table through repair and optimize, but with no result.

How to repeat:
select count(id) from myTable where myField = '';
or
select count(id) from myTable where myField = null;

The myField is in this case a VarChar type-of field.

Suggested fix:
Either I am missing something obvious here or I would guess it's an internal bug.

[3 Jan 2004 10:56] MySQL Verification Team

This is documented behaviour.

For NULL comparinons '=' is not used. As SQL standard definies IS NULL or
IS NOT NULL are used.