MySQL Bugs: #116087: Time type not formatted correctly in SSPS mode

Bug #116087	Time type not formatted correctly in SSPS mode
Submitted:	12 Sep 2024 10:44	Modified:	4 Mar 20:28
Reporter:	Lukasz Selwa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	9.0.0	OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	prepared statement, time

Description:
For mysql odbc connector, when fetching time type column bind to SQL_C_CHAR the returned string is incorrect if SSPS is enabled and hour has 3 digits.

In mysql, the TIME values may range from '-838:59:59' to '838:59:59'. However, mysql odbc connector doesn't format it correctly to C-char if SSPS is enabled.
For example, the last digits of seconds are lost for corner-case values '-838:59:59', '838:59:59'.

How to repeat:
The program bellow prints different output in default SSPS enabled mode then in NO_SSPS mode.

On mysql database create table with TIME type and corner case values.

CREATE TABLE time_tab (typetime TIME(0));
INSERT INTO time_tab VALUES ('-838:59:59'), ('838:59:59');

Run the following code 

#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

#define CONNECTION_STRING "..."
#define CONNECTION_STRING_LEN sizeof(CONNECTION_STRING)
#define    BUFF_SIZE 256
int main() {
    SQLHENV h_env = NULL;
    SQLHDBC h_dbc = NULL;
    SQLHSTMT h_stmt = NULL;
    SQLLEN    i_text_len = 0;
    SQLSMALLINT i_out_connection_string_len = 0;
    char psz_txt_buff[BUFF_SIZE];
    int ret = 0;

    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &h_env);
    SQLSetEnvAttr( h_env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0 );
    SQLAllocHandle(SQL_HANDLE_DBC, h_env, &h_dbc);

    SQLDriverConnect(h_dbc, NULL, CONNECTION_STRING, CONNECTION_STRING_LEN, NULL, 0, &i_out_connection_string_len, SQL_DRIVER_NOPROMPT);

    SQLAllocHandle(SQL_HANDLE_STMT, h_dbc, &h_stmt);
    SQLPrepare(h_stmt, "SELECT typetime FROM time_tab;", SQL_NTS);
    SQLBindCol(h_stmt, 1, SQL_C_CHAR, &psz_txt_buff,    sizeof(psz_txt_buff), &i_text_len);

    SQLExecute(h_stmt);
    do {
        ret = SQLFetch(h_stmt);
        if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
            printf("text='%s' len=%ld\n", psz_txt_buff, i_text_len);
    } while (ret == SQL_SUCCESS);
}

If the connection is in SSPS disabled mode (NO_SSPS=1; in connection string), then this code prints as expected:

text='-838:59:59' len=10
text='838:59:59' len=9

When SSPS is enabled then the last characters are lost:

text='-838:59:5' len=9
text='838:59:5' len=8

Suggested fix:
The program should print same output regardless of if SSPS is enabled or not.

I think the issue is with the source code of mysql-connector-odbc, in file driver/my_prepared_stmt.cc, function fetch_varlength_columns.
The following code doesn't take into account that hour value for mysql can have 3 digits.

    case MYSQL_TYPE_TIME:
    {
      MYSQL_TIME * t = (MYSQL_TIME *)(col_rbind->buffer);

      buffer= ALLOC_IFNULL(buffer, 20);
      myodbc_snprintf(buffer, 10, "%s%02u:%02u:%02u", t->neg? "-":"", t->hour,
                                              t->minute, t->second);
      *length= t->neg ? 9 : 8;

      if (t->second_part > 0)
      {
        myodbc_snprintf(buffer+*length, 8, ".%06lu", t->second_part);
        *length+= 7;
      }
      return buffer;
    }

The length and buffers are too small if t->hour >= 100.

Also I noticed that this code doesn't handle printing milliseconds in the sam way as in NO_SPSS mode.
It would be best if it instead reused function "int my_time_to_str(const MYSQL_TIME &my_time, char *to, uint dec)" from my_time.h

Hi Lukasz,

Thank you very much for the detailed description of the bug, the test code and for proposing how to fix it.

The bug is verified.

Thank you, Bogdan, so much !!!!!!

Posted by developer:
 
The problem was in the code logic, which did not take into account that the number for hours can be three digits where the values range from -838 to 838.
This is now fixed and a unit test added to check for this issue, including the cases with fractional parts of the second having up to 6 digits of precision.

Posted by developer:
 
Added the following entry to the Connector/ODBC 9.3.0 changelog: 

"When using server-side prepared statements, fetching a time column bound to the SQL_C_CHAR type returned an incorrect string if the hour value had 3 digits."