MySQL Bugs: #30942: select str_to_date from derived table returns varying results

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

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

if results appear like garbled binary, you have repeated the bug.

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

same results on latest builds from bitkeeper.

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.

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.

ok to push

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.

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.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

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.