| Bug #107307 | Inaccurate query result using SQLBindCol for SQL_C_DOUBLE type | ||
|---|---|---|---|
| Submitted: | 16 May 2022 16:42 | Modified: | 13 Jun 2022 19:49 |
| Reporter: | Stephen Lin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 8.0.27 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[16 May 2022 16:43]
Stephen Lin
Update buy title
[17 May 2022 13:02]
Bogdan Degtyariov
Thank you for reporting an issue in Connector/ODBC. First and most important is this: SQLINTEGER ind = 0; RetCode = SQLBindCol(hStmt, 1, SQL_C_DOUBLE, (SQLPOINTER)&value, 0, (SQLLEN*)&ind); The above code will most likely cause buffer overrun and all sorts of unpredictable behavior because a 32-bit SQLINTEGER variable ind is used as 64-bit SQLLEN buffer. I need to check how it works with the correct buffer when `ind` variable is declared as SQLLEN ind = 0;
[17 May 2022 13:12]
Bogdan Degtyariov
I was able to repeat the problem even with the correct index buffer.
The bug is verified.
The test code is as this:
DECLARE_TEST(t_bug107307)
{
SQLCHAR buff[128];
SQLRETURN rc;
double value = 0;
SQLLEN ind = 0;
DECLARE_BASIC_HANDLES(henv1, hdbc1, hstmt1);
alloc_basic_handles_with_opt(&henv1, &hdbc1, &hstmt1, NULL, NULL,
NULL, NULL, "OPTION=1048576");
ok_sql(hstmt1, "DROP TABLE IF EXISTS t_bug107307");
ok_sql(hstmt1, "CREATE TABLE t_bug107307 (num0 DOUBLE)");
ok_sql(hstmt1, "INSERT INTO t_bug107307 VALUES (12.3), (-12.3), (15.7),"
"(-15.7), (3.5), (-3.5), (0), (NULL), (10)");
ok_stmt(hstmt1, SQLPrepare(hstmt1, "SELECT * FROM t_bug107307", SQL_NTS));
ok_stmt(hstmt, SQLExecute(hstmt1));
ok_stmt(hstmt1, SQLBindCol(hstmt1, 1, SQL_C_DOUBLE, (SQLPOINTER)&value, 0, &ind));
for (int i = 0; ; i++) {
rc = SQLFetch(hstmt1);
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
if (ind <= 0) {
printf("Record %d: %s\n", i + 1, "NULL");
}
else {
printf("Record %d: %f\n", i + 1, value);
}
} else {
if (rc != SQL_NO_DATA) {
printf("Error\n");
} else {
break;
}
}
}
ok_sql(hstmt1, "DROP TABLE IF EXISTS t_bug107307");
free_basic_handles(&henv1, &hdbc1, &hstmt1);
return OK;
}
The output is
Record 1: 12.330000
Record 2: -12.300000
Record 3: 15.700000
Record 4: -15.700000
Record 5: 3.500000
Record 6: -3.500000
Record 7: 0.000000
Record 8: NULL
Record 9: 10.000000
[17 May 2022 20:15]
Stephen Lin
Bogdan, This defect and another defect I filed before were found when we run Tableau TDVT. Are you interested in running TDVT to test new MySQL ODBC driver releases? TDVT is publicly available on Github. I can help you set up if you are interested. https://tableau.github.io/connector-plugin-sdk/docs/tdvt Thanks, Stephen
[18 May 2022 12:24]
Bogdan Degtyariov
Posted by developer: A workaround for this problem: do not use option NO_CACHE=1 However, the incorrect handling of the result with NO_CACHE=1 must be fixed.
[3 Jun 2022 13:14]
Bogdan Degtyariov
Posted by developer: The problem is fixed. The patch is now pushed into the release branch. Now the type SQL_C_DOUBLE is returned correctly with NO_CACHE=1.
[13 Jun 2022 19:49]
Philip Olson
Posted by developer: Fixed as of the upcoming MySQL Connector/ODBC 8.0.30 release, and here's the proposed changelog entry from the documentation team: The SQL_C_DOUBLE type could return as an inaccurate result with NO_CACHE enabled. Thank you for the bug report.
Description: Fetching query result using SQLBindCol() and SQLFetch() returns inaccurate result for column of SQL_C_DOUBLE type. The following repro steps is only one of examples I have seen with wrong results with SQL_C_DOUBLE type column. How to repeat: Table Calcs with one column num0 (type double): 12.3 -12.3 15.7 -15.7 3.5 -3.5 0 NULL 10 Query using the following code: SQLHENV hEnv = NULL; SQLHDBC hDbc = NULL; SQLHSTMT hStmt = NULL; WCHAR* pwszConnStr = (WCHAR *)L"DRIVER=\"{MySQL ODBC 8.0 Unicode Driver}\";DESC=;DATABASE=TestV1;SERVER=mysql;UID=test;PASSWORD=password;PORT=3306;SOCKET=;OPTION=1048576;STMT=;SSLMODE=disabled"; // Allocate an environment if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv) == SQL_ERROR) { fwprintf(stderr, L"Unable to allocate an environment handle\n"); exit(-1); } // Register this as an application that expects 3.x behavior, // you must register something if you use AllocHandle TRYODBC(hEnv, SQL_HANDLE_ENV, SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0)); // Allocate a connection TRYODBC(hEnv, SQL_HANDLE_ENV, SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc)); if (argc > 1) { pwszConnStr = *++argv; } // Connect to the driver. Use the connection string if supplied // on the input, otherwise let the driver manager prompt for input. TRYODBC(hDbc, SQL_HANDLE_DBC, SQLDriverConnect(hDbc, GetDesktopWindow(), pwszConnStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE)); fwprintf(stderr, L"Connected!\n"); RETCODE RetCode; // Execute the query TRYODBC(hDbc, SQL_HANDLE_DBC, SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)); SQLWCHAR* query = (SQLWCHAR*)L"SELECT `Calcs`.`num0` FROM `Calcs`"; TRYODBC(hDbc, SQL_HANDLE_DBC, SQLPrepare(hStmt, query, SQL_NTS)); RetCode = SQLExecute(hStmt); SQLINTEGER ind = 0; double value = 0; RetCode = SQLBindCol(hStmt, 1, SQL_C_DOUBLE, (SQLPOINTER)&value, 0, (SQLLEN*)&ind); for (int i=0; ; i++) { RetCode = SQLFetch(hStmt); if (RetCode == SQL_SUCCESS || RetCode == SQL_SUCCESS_WITH_INFO) { if (ind <= 0) { printf("Record %d: %s\n", i + 1, "NULL"); } else { printf("Record %d: %f\n", i + 1, value); } } else { if (RetCode != SQL_NO_DATA) { printf("Error\n"); ; } else { break; } } } Actual result: Record 1: 12.330000 (12.3 expected) Record 2: -12.300000 Record 3: 15.700000 Record 4: -15.700000 Record 5: 3.500000 Record 6: -3.500000 Record 7: 0.000000 Record 8: NULL Record 9: 10.000000 Another example is ROUND(-9.31, 0) returns -92, when -9 is expected.