Bug #60646 Fractional parts of second is ignored in SQLGetData
Submitted: 25 Mar 2011 17:35 Modified: 1 Mar 2012 13:56
Reporter: Vladislav Vaintroub Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1.9 OS:Any
Assigned to: Lawrenty Novitsky

[25 Mar 2011 17:35] Vladislav Vaintroub
Description:
MySQL server supports fractional parts of second for temporal datatypes 
in some specific circumctances, e.g in literals. Also, ODBC supports fractional seconds with SQL_TIMESTAMP_STRUCT, its SQLUINTEGER fraction member has nanoseconds precision.

However, this 'fraction' member in SQL_TIMESTAMP_STRUCT is always set to 0 when timestamp is retrieved using SQLGetData, 

How to repeat:
Here is a test case using odbctap format, for easy integration into the ODBC test suite

It is using select time('literal') with literal having non-zero fractional part
(0.5 secs).

The result, retrieved using SQLGetData with SQL_C_TIMESTAMP has ts.fraction set to zero, while it should be 500000000 (nanoseconds). 

If result is retrieved as string ( SQLGetData with SQL_C_CHAR), the fractional part is correct.

Test: 

DECLARE_TEST(t_fractional_get_data)
{
  char buf[100];
  SQL_TIMESTAMP_STRUCT ts;
  SQLLEN len;

  ok_sql(hstmt, "SELECT time('00:00:00.5')");
  ok_stmt(hstmt, SQLFetch(hstmt));
  ok_stmt(hstmt, SQLGetData(hstmt, 1, SQL_C_TIMESTAMP, &ts, sizeof(ts), &len));
  is_num(ts.fraction,500000000);
  ok_stmt(hstmt, SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &len));
  return OK;
}

Suggested fix:
The problem is in str_to_ts() function in utility.c
It assumes that timestamp string after will be no longer than 14 characters after canonicalization (stripping non-digits and converting 2-digit years to 4-digit). However, with fractional part, there might be up to 9 digits more.

A naive (unoptimized) fix might be following
 
1) extend the 'buff' buffer from 15 characters to 24 characters

2) change
if ( length < 14 )
        strfill(to,14 - length,'0');
to
if ( length < 23 )
        strfill(to,23 - length,'0');

3) instead of  
ts->fraction= 0;
use
ts->fraction= atoi(buff+14);

an optimized for common case fix (no fractional part) could avoid atoi() (e.g if length after canonicalization is <=14, then fractional part is 0 anyway)
[20 May 2011 7:06] Bogdan Degtyariov
Vlad,

Thanks for the test and the proposed solution. 
You really know how to make it easier for us :)
The patch needs a bit of changing because SQLGetData() is not using str_to_ts() function (it calls str_to_time_st(), which works in the similar way). 
Well, developers on MySQL side have to do at least some part of the job :)
Thanks again.
[5 Aug 2011 9:02] Lawrenty Novitsky
will attach diff in a second. parameters part(60648) will follow
[5 Aug 2011 9:03] Lawrenty Novitsky
the patch

Attachment: bug60646.diff (application/octet-stream, text), 15.25 KiB.

[1 Mar 2012 13:56] Lawrenty Novitsky
The patch has been pushed to 5.1 as rev#1030(i don't know why, but log message for that revision is just wrong), with some changes applied in revisions 1032, 1034
[18 Apr 2012 23:52] John Russell
Added to changelog for 5.1.11: 

The fraction member in SQL_TIMESTAMP_STRUCT was always set to 0 when
a timestamp was retrieved using SQLGetData(). The fix causes the
fraction member to be correctly set, with a value representing
nanoseconds. This issue did not occur when a result was retrieved as
a string (SQLGetData() with SQL_C_CHAR).