MySQL Bugs: #22824: strict, datetime, NULL, wrong warning

Bug #22824	strict, datetime, NULL, wrong warning
Submitted:	29 Sep 2006 13:08	Modified:	8 May 2007 17:16
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
CREATE TABLE t1 ( dt1 DATETIME NOT NULL);
SET SESSION SQL_MODE = 'NO_ZERO_DATE';
SELECT COUNT(*) FROM t1
WHERE t1.dt1 IS NULL;
COUNT(*)
0
Warnings:
Warning	1292	Incorrect datetime value: '0' for column 'dt1' at row 1
The warning is wrong, because the tables do not contain any rows.
But fortunately the result is correct.

Some observations:
- InnoDB and MyISAM show the same wrong behaviour.
- The wrong warnings disappers if
  1. dt1 DATETIME (without NOT NULL)        or
  2. dt1 INT NOT NULL                       or
  3. SET SESSION SQL_MODE = ''              or
  4. WHERE t1.dt1 = '2001-01-01 17:33:00';  
  are used.

My environment:
 - PC Intel Pentium M (x86-32Bit) with Linux(SuSE 10.1)
 - MySQL compiled from source
     mysql-5.1 last ChangeSet@1.2327, 2006-09-26
     BUILD/compile-pentium-debug-max

How to repeat:
Please use the statements above or the attached testscript
  cd <top directory of release>/mysql-test
  cp ml003.test t
  echo "Dummy" > r/ml003.result
  ./mysql-test-run --skip-ndb ml003
  inspect r/ml003.log

test script

Attachment: ml003.test (application/octet-stream, text), 597 bytes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17256

ChangeSet@1.2336, 2006-12-21 13:53:34+04:00, ramil@mysql.com +3 -0
  Fix for bug #22824: strict, datetime, NULL, wrong warning
  
  During optimization we replace NULL with 0 for not null 
  date{time} fields, so uset MODE_NO_ZERO_DATE flag for a while 
  as we don't want to give extra warnings.

I am not sure whether this is a problem or not,
but after the fix, the result is that if we have
a '0000-00-00' row in the table (which is not the
same as NULL), then executing:
SELECT COUNT(*) FROM t1 WHERE t1.dt1 IS NULL;
still gives no warning.

Do you think this is intended behavior?

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 5.0.42, 5.1.18 changelogs.

SELECT COUNT(*) from a table containing a DATETIME NOT NULL column
could produce spurious warnings with the NO_ZERO_DATE SQL mode
enabled.