Bug #21475 incorrect result and warning with datetime and subquery
Submitted: 7 Aug 2006 10:57 Modified: 29 Sep 2006 2:48
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.24, 5.1.11 OS:Any (*)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[7 Aug 2006 10:57] Shane Bester
Description:
Run SQL in "How to repeat section":

A warning about Truncated incorrect DOUBLE value appears when run on 5.0 and 5.1.  4.1 returned a correct result.

On 4.1.21:
----------

mysql> EXECUTE `s`;
+---------------------+
| a                   |
+---------------------+
| 2006-06-06 15:55:55 |
+---------------------+
1 row in set (0.00 sec)

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

On 5.0.24:
-----------

mysql> EXECUTE `s`;
Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-06-06 15:55:55' |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

5.1.11-beta:
-----------
mysql> EXECUTE `s`;
Empty set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------+
| Level   | Code | Message                                                 |
+---------+------+---------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2006-06-06 15:55:55' |
+---------+------+---------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1`(`a` DATETIME NOT NULL);
INSERT INTO `t1`(`a`) VALUES ('20060606155555');

PREPARE `s` FROM 'SELECT `a` FROM `t1` WHERE `a`=(SELECT MAX(`a`) FROM `t1`) AND (`a`="20060606155555")';
EXECUTE `s`;
SHOW WARNINGS;
DROP PREPARE `s`;

Suggested fix:
.
[7 Aug 2006 17:30] Tonci Grgin
Verified as described by reporter on Suse 10 host:
MySQL server 5.1.12 beta log
munja:/home/Tonci/bkwork/mysql-5.1 # bk changes |head
ChangeSet@1.2263, 2006-08-03 19:43:52+02:00, tsmith@maint1.mysql.com +1 -0
  Manual merge resolve, part 6 of 6+
ChangeSet@1.2257.1.3, 2006-08-03 19:35:00+02:00, msvensson@neptunus.(none) +1 -0
  Use "--source" command instead of "source", makes mysql-test-run.pl dtecte this as test case that need binlog format row.
ChangeSet@1.2258.1.1, 2006-08-03 19:27:00+02:00, tsmith@maint1.mysql.com +2 -0
  5.0 -> 5.1 manual merge, part 5 of 5 (or more?)
ChangeSet@1.2257.1.2, 2006-08-03 19:15:55+02:00, msvensson@neptunus.(none) +1 -0

MySQL server 5.0.25 log:
munja:/home/Tonci/bkwork/mysql-5.0 # bk changes |head
ChangeSet@1.2242, 2006-08-07 07:41:49+04:00, evgen@sunlight.local +1 -0
  sql_view.cc:
    Memory leak fix
ChangeSet@1.2241, 2006-08-07 02:29:59+04:00, evgen@sunlight.local +1 -0
  Merge epotemkin@bk-internal.mysql.com:/home/bk/mysql-5.0
  into  sunlight.local:/local_work/leak_fix
  MERGE: 1.2205.11.2
ChangeSet@1.2205.11.2, 2006-08-07 00:06:03+04:00, evgen@sunlight.local +1 -0
Nothing to be seen in general log.
[20 Aug 2006 20:22] Evgeny Potemkin
See also bug#21159
[20 Aug 2006 20:23] 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/10652

ChangeSet@1.2263, 2006-08-21 00:23:57+04:00, evgen@moonbone.local +6 -0
  Fixed bug#21475: Wrongly applied constant propagation leads to a false comparison.
  
  A date can be represented as an int (like 20060101) and as a string (like
  "2006.01.01"). When a DATE/TIME field is compared in one SELECT against both
  representations the constant propagation mechanism leads to comparison
  of DATE as a string and DATE as an int. In this example it compares 2006 and
  20060101 integers. Obviously it fails comparison although they represents the
  same date.
  
  
  Now the Item_bool_func2::fix_length_and_dec() function sets the comparison
  context for items being compared. I.e. if items compared as strings the
  comparison context is STRING.
  The constant propagation mechanism now doesn't mix items used in different
  comparison contexts. The context check is done in the
  Item_field::equal_fields_propagator() and in the change_cond_ref_to_const() 
  functions.
  
  Also the better fix for bug 21159 is introduced.
[29 Aug 2006 13:24] Evgeny Potemkin
Fixed in 5.0.25
[4 Sep 2006 11:36] Evgeny Potemkin
Fixed in 5.1.12
[29 Sep 2006 2:48] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.