Bug #33088 SELECT query with a definite WHERE clause stops returning rows after NULL value

• Submitted: 8 Dec 2007 18:04
• Modified: 8 Dec 2007 18:43
• Reporter: Martin Smith
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: Ver 14.12 Distrib 5.0.44, for pc-linux-g
• OS: Any
• CPU Architecture: Any

[8 Dec 2007 18:04] Martin Smith

Description:
See the script below for a brief example; MySQL will stop after encountering the first null value that is being filtered wih a where clause. I expected to see ALL rows that had a field value that was NOT NULL and matched my WHERE would be returned. They are not. There's no index defined, and this is an innoDB table.

How to repeat:
A script:

create database test;
use test;
create table test (id integer, dt datetime);
insert into test(1, now());
insert into test(2, NULL);
insert into test(3, now());
select * from test where DATE(dt) = DATE(NOW());

[8 Dec 2007 18:14] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51 or 5.0.52, and inform about the results.

[8 Dec 2007 18:26] Martin Smith

Hello -- the latest stable in my distro is 5.0.44. I can't get any newer without breaking my packaging system. This is such a small script -- can any MySQL bug folks reproduce it?

[8 Dec 2007 18:43] Sveta Smirnova

Thank you for the report.

I can not repeat described behaviour with version 5.0.50 although bug was repeatable with earlier versions. Please upgrade.