MySQL Bugs: #106683: Query using prepared statement return wrong result

Bug #106683	Query using prepared statement return wrong result
Submitted:	9 Mar 2022 23:31	Modified:	7 Apr 2022 21:25
Reporter:	Stephen Lin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	8.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Query using prepared statement return wrong result.  The query works from using SQLExecuteDirect().

This issue started with ODBC driver 8.0.27 and it's reproducible in 8.0.28.

If we remove OPTION=1048576 from the connection string, the issue is not reproducible.

If we add NO_SSPS=1 to the connection string, the issue is not reproducible.

How to repeat:
1. Create table test
[Field]		[Type]		[Null]	[Key]	[Default]	[Extra]
datetime0	datetime	YES

2. Insert data
[datetime0]
2010-06-01 14:56:00
null

3. Connect to server using the following connection string example.
DRIVER="{MySQL ODBC 8.0 Unicode Driver}";DESC=;DATABASE=dbo;SERVER=mysql;UID=test;PASSWORD=password;PORT=3306;SOCKET=;OPTION=1048576;STMT=;SSLMODE=disabled

4. Run the query using following statements.
    SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    SQLSetStmtAttr(hStmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_FORWARD_ONLY, SQL_IS_UINTEGER);
    SQLWCHAR* query = (SQLWCHAR*)L"select * from `1358602`;";
    SQLPrepare(hStmt, query, SQL_NTS);
    RetCode = SQLExecute(hStmt);

The actual result is,
| datetime0            |
| <NULL>               |
| <NULL>               |

The expected result is,
| datetime0            |
| 2010-06-01 14:56:00  |
| <NULL>               |

test code

Attachment: odbcsql.cpp (text/plain), 19.29 KiB.

Verified, the problem occurs when NO_CACHE=1 is activated.

Posted by developer:
 
Documentation Note:
The fix for this bug is to make the ODBC driver to correctly process NULL values obtained from prepared statement resultset when records caching is disabled by NO_CACHE=1.
Previously having a NULL value in a row column would nullify a value in the same column of next row. Thus producing wrong result and giving NULL where a non-NULL value should be.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/ODBC 8.0.29 release, and here's the proposed changelog entry from the documentation team:

With prepared statements and NO_CACHE=1, having a NULL value in a row
column would nullify a value in the same column of the next row. This
produced an incorrect value giving NULL where a non-value was expected.

Thank you for the bug report.