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

[29 Sep 2006 13:08] Matthias Leich
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
[29 Sep 2006 13:11] Matthias Leich
test script

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

[21 Dec 2006 9:58] Bugs System
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.
[6 Apr 2007 14:37] Timour Katchaounov
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?
[26 Apr 2007 11:37] Bugs System
Pushed into 5.0.42
[26 Apr 2007 11:38] Bugs System
Pushed into 5.1.18-beta
[8 May 2007 17:16] Paul Dubois
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.