Bug #30942 select str_to_date from derived table returns varying results
Submitted: 11 Sep 2007 4:52 Modified: 5 Nov 2007 3:09
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.46, 5.0.48,5.1.23BK,5.0.50BK OS:Any
Assigned to: Martin Hansson
Tags: STR_TO_DATE

[11 Sep 2007 4:52] Shane Bester
Description:
Sometimes the resultset contains garbled hex, and other times it contains proper dates, such as 0000-00-00.  Repeatable about 50% of the time.  Could be a memory corruption somewhere..

The following sequence of commands shows a bug, somewhere: 

mysql> create table t1 as select col9,col20 from table0;
Query OK, 48 rows affected (0.00 sec)
Records: 48  Duplicates: 0  Warnings: 0

mysql> select * from (select str_to_date(col9,col20) as a from t1) a;
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      `a`
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     29
Max_length: 29
Decimals:   0
Flags:      BINARY

+-------------------------------+
| a                             |
+-------------------------------+
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
|   ↓☻  N♠  ↓☻  N♠ ↓☻  4♠└φo♠╠ |
| NULL                          |
| NULL                          |
| NULL                          |
| NULL                          |
+-------------------------------+
48 rows in set (0.00 sec)

mysql> select hex(a) from (select str_to_date(col9,col20) as a from t1) a;
Field   1:  `hex(a)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     58
Max_length: 38
Decimals:   0
Flags:

+----------------------------------------+
| hex(a)                                 |
+----------------------------------------+
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| 303030302D30302D30302030303A30303A3030 |
| 303030302D30302D30302030303A30303A3030 |
| NULL                                   |
| NULL                                   |
| NULL                                   |
| NULL                                   |
+----------------------------------------+
48 rows in set (0.00 sec)

mysql> select * from (select str_to_date(col9,col20) as a from t1) a;
Field   1:  `a`
Catalog:    `def`
Database:   ``
Table:      `a`
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     29
Max_length: 19
Decimals:   0
Flags:      BINARY

+---------------------+
| a                   |
+---------------------+
| NULL                |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| NULL                |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| NULL                |
| NULL                |
| NULL                |
| NULL                |
+---------------------+
48 rows in set (0.00 sec)

How to repeat:
stay tuned for testcase.

Suggested fix:
.
[11 Sep 2007 4:58] Shane Bester
if results appear like garbled binary, you have repeated the bug.

Attachment: bug30942.sql (application/octet-stream, text), 2.35 KiB.

[11 Sep 2007 9:43] Shane Bester
same results on latest builds from bitkeeper.
[24 Sep 2007 9:55] 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/34504

ChangeSet@1.2490, 2007-09-24 11:57:57+02:00, mhansson@linux-st28.site +3 -0
  Bug #30942: select str_to_date from derived table returns varying results
  
  The function str_to_date has a field to say whether it's invoked constant 
  arguments. But this member was not initialized, causing the function to
  think that it could use a cache of the format type when said cache was in 
  fact not initialized.
  Fixed by initializing the field to false.
[24 Sep 2007 10:44] 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/34511

ChangeSet@1.2490, 2007-09-24 12:47:28+02:00, mhansson@linux-st28.site +3 -0
  Bug #30942: select str_to_date from derived table returns varying results
  
  The function str_to_date has a field to say whether it's invoked constant 
  arguments. But this member was not initialized, causing the function to
  think that it could use a cache of the format type when said cache was in 
  fact not initialized.
  Fixed by initializing the field to false.
[24 Sep 2007 10:50] Sergey Glukhov
ok to push
[4 Oct 2007 7:12] 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/34869

ChangeSet@1.2490, 2007-10-04 09:15:26+02:00, mhansson@linux-st28.site +3 -0
  Bug #30942: select str_to_date from derived table returns varying results
  
  The function str_to_date has a field to say whether it's invoked constant 
  arguments. But this member was not initialized, causing the function to
  think that it could use a cache of the format type when said cache was in 
  fact not initialized.
  Fixed by initializing the field to false.
[4 Oct 2007 7:53] Martin Hansson
I will push this to 5.0, 5.1, and 5.2. The bug is hidden (but not fixed in the sense that this patch fixeds it) in 5.2, but I push there as well so that internal behavior is the same.
[29 Oct 2007 8:42] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:45] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:49] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 3:09] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

When invoked with constant arguments, STR_TO_DATE() could use a 
cached value for the format string and return incorrect results.