Bug #105332 SQLColAttribute SQL_DESC_PRECISION timestamp, datetime and time
Submitted: 26 Oct 2021 19:14 Modified: 17 May 17:22
Reporter: Farid Zidan (Candidate Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:8.00.0026 OS:Windows (64-bit)
Assigned to: CPU Architecture:x86

[26 Oct 2021 19:14] Farid Zidan
Description:
ODBC driver does not return the precision for timestamp, datetime and time columns when client describe the resultset with SQLColAttribute and SQL_DESC_PRECISION.

Driver always return 19 instead of actual precision for the column.

How to repeat:
1 create test table.

-- MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision
create table test_timestamp_precision(
ts  timestamp default current_timestamp,
ts2 timestamp(2) default current_timestamp(2),

dt   datetime default current_timestamp,
dt3 datetime (3) default current_timestamp(3),

tm   time default (curtime()),
tm4 time(4) default (curtime(4)))

2 execute 
select *
from
test_timestamp_precision

and iterate over the resultset columns calling ODBC function. Example, code:

        // for exact numeric data types, column size is the precision
		// For data types SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, and all the 
		// interval data types that represent a time interval, its value is the
		// applicable precision of the fractional seconds component
		SQLLEN nDescPrecision = 0;
        rc = SQLColAttribute(
            hstmt,
            nCol,
            SQL_DESC_PRECISION,
            NULL,
            0,
            NULL,
            &nDescPrecision);

3 Observe that the driver returns 19 for all the resultset column nDescPrecision.

Should return 7, 2, 7, 3, 7 and 4 respectively.

Suggested fix:
Update driver to return the correct precision for timestamp, datetime and time data types per ODBC specs for SQLColAttribute / SQL_DESC_PRECISION.
[26 Oct 2021 23:45] Farid Zidan
Correction:
Should return 0, 2, 0, 3, 0 and 4 respectively.

Since the default MySQL precision for timestamp, datetime and time is 0 decimal places.
[16 May 7:47] MySQL Verification Team
Hello Farid Zidan,

Thank you for the bug report.
Please try latest version 8.0.29.

Regards,
Ashwini Patil
[16 May 15:48] Farid Zidan
Tested with latest 8.00.0029 ODBC driver.
Still not returning expected values.
I get these values: 19, 19, 19, 19, 8, 8.
Should return 0, 2, 0, 3, 0, 4 respectively.
[16 May 15:58] Farid Zidan
columns info screen shot

Attachment: SQL_DESC_PRECISION Screenshot 2022-05-16 115628.jpg (image/jpeg, text), 118.04 KiB.

[17 May 10:22] Bogdan Degtyariov
Hi Farid,

Thank you for reporting the problem.
The bug has been verified.

The test code repeating the problem is as follows:

DECLARE_TEST(t_bug105332)
{
  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug105332");
  ok_sql(hstmt, "create table t_bug105332("
    "ts  timestamp default current_timestamp,"
    "ts2 timestamp(2) default current_timestamp(2),"
    "dt   datetime default current_timestamp,"
    "dt3 datetime(3) default current_timestamp(3),"
    "tm   time default (curtime()),"
    "tm4 time(4) default (curtime(4)))");

  ok_sql(hstmt, "SELECT * FROM t_bug105332");

  int expected_prec[] = { 7, 2, 7, 3, 7, 4 };

  for (int i = 1; i < 7; ++i)
  {
    SQLLEN precision = 0;
    ok_stmt(hstmt, SQLColAttribute(hstmt, i, SQL_DESC_PRECISION,
      NULL, 0, NULL, &precision));

    printf("Column %d, precision [%d], expected precision [%d]\n",
      i, (int)precision, expected_prec[i - 1]);
    is_num((int)precision, expected_prec[i - 1]);
  }

  ok_stmt(hstmt, SQLFreeStmt(hstmt, SQL_CLOSE));
  ok_sql(hstmt, "DROP TABLE IF EXISTS t_bug105504");

  return OK;
}
[17 May 17:22] Farid Zidan
Hi Bogdan
Thank you for the update.

Please note that MySQL default precision for timestamp, datetime and time is 0 and not 7. So the expected precision values would be:
int expected_prec[] = { 0, 2, 0, 3, 0, 4 };

Thank you