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 CPU Architecture:Any
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] Sergei 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.069 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.