| 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: | |
| 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 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.
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: .