Bug #32494 Strange behaviour in counting rows part two

• Submitted: 19 Nov 2007 14:39
• Modified: 19 Nov 2007 18:33
• Reporter: Anton Dischner
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: Linux (SLES9 SP3)
• CPU Architecture: Any
• Tags: count(*), curdate, date, null, SELECT

[19 Nov 2007 14:39] Anton Dischner

Description:
selecting count(*) from a tables produces different results 

How to repeat:
create table ttt (datumsfeld date);
insert into ttt values (curdate());
insert into ttt values (null);
insert into ttt values ('2007-01-01');

select * from ttt;
+------------+
| datumsfeld |
+------------+
| 2007-11-19 |
| NULL       |
| 2007-01-01 |
+------------+

select count(*) from ttt where date(datumsfeld) < curdate() and datumsfeld is not null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
This is wrong

select count(*) from ttt where datumsfeld is not null and date(datumsfeld) < curdate();
+----------+
| count(*) |
+----------+
|        1 |
+----------+
This is correct

[19 Nov 2007 18:33] Valeriy Kravchuk

Sorry, but this is a duplicate of bug #31709. This is a known bug (I still can repeat it on 5.0.45) that is already fixed in current sources.