Bug #60648 ODBC prepared statements ignore fractional part of temporal data types
Submitted: 25 Mar 2011 18:03 Modified: 1 Mar 2012 14:16
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: Bogdan Degtyariov CPU Architecture:Any

[25 Mar 2011 18:03] Vladislav Vaintroub
Description:
Fractional seconds part of timestamp is ignored in prepared statements that use SQLBindParameter and SQL_C_TIMESTAMP type.

How to repeat:
Here is the test case using odbctap format, for better integration into the test suite. 

The prepared SQL statement compares 2 different timestamps, that differ only in fractional second part ("2001-01-01 01:01:01" vs "2001-01-01 01:01:01.5")

The expected result of prepared "SELECT ?=?" is false (or "0"), however the actual result is "1". 

DECLARE_TEST(t_fractional_bind_param)
{
  char buf[100];
  SQL_TIMESTAMP_STRUCT ts0, ts1;
  SQLLEN len;
  SQLRETURN rc;
  
  ts0.year=2001;
  ts0.month=1;
  ts0.day=1;
  ts0.hour=1;
  ts0.minute=1;
  ts0.second=1;
  ts0.fraction=0;

  ts1= ts0;
  /* Add fraction part 0.5 seconds */
  ts1.fraction= 500000000;
  
  rc = SQLPrepare(hstmt,"SELECT ? = ?",SQL_NTS);
  mystmt(hstmt,rc);
  ok_stmt(hstmt, SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 0, &ts0, sizeof(ts0), NULL));
  ok_stmt(hstmt, SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 0, &ts1, sizeof(ts1), NULL));

  rc = SQLExecute(hstmt);
  mystmt(hstmt, rc);
  ok_stmt(hstmt, SQLFetch(hstmt));
  ok_stmt(hstmt, SQLGetData(hstmt, 1, SQL_C_CHAR, buf, sizeof(buf), &len));
  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
  myassert(buf[0] == '0');
  return OK;
}

Suggested fix:
I think the place to fix is insert_param() function, where currently timestamps are converted to string using 

sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d",
                          time->year, time->month, time->day,
                          time->hour, time->minute, time->second);

When fractional part is not 0,
-  string format could be changed to 
"%04d-%02d-%02d %02d:%02d:%02d.%09d"
- time->fraction can be added as last parameter 
- string length of buff changes from 19 to 29

It can  be optimized further (e.g sending triling zeros in fractional part to server is not necessary).
[20 May 2011 11:43] Bogdan Degtyariov
Thanks Vlad,
The bug is verified.
[28 Aug 2011 12:45] Lawrenty Novitsky
patch pushed to the branch bzr+ssh://lawrin@bazaar.launchpad.net/~myodbc-developers/myodbc/bug60648
it relies a modifies code from the patch for 60646. Patch's revno is 987(-2). Branch also contains patch for 60646 - 985(-4), patch for queries log location - 986(-3). And small patch for SQLCancel, it's more like a part of the transition to the 5.5. That is last revision in the branch.
[1 Mar 2012 14:16] Lawrenty Novitsky
pushed as rev#1032, with some changes in rev#1034. If nothing goes wrong will be included to the next maintenance release(5.1.11).
[18 Apr 2012 23:46] John Russell
Added to changelog for 5.1.11: 

Fractional seconds part of timestamp was ignored in prepared
statements that use SQLBindParameter and SQL_C_TIMESTAMP type. For
example, a prepared query comparing two timestamp values that only
differed in the fractional part would consider the values identical.