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