MySQL Bugs: #89542: ODBC Parameter NO_CACHE Lead To Function Call Error Not Return

Bug #89542	ODBC Parameter NO_CACHE Lead To Function Call Error Not Return
Submitted:	6 Feb 2018 2:38	Modified:	4 Jun 2021 20:57
Reporter:	Sun Edward	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.24	OS:	Windows (WIndows 7 Enterprise Service Pack 1)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
If set NO_CACHE=1 parameter in ODBC connection string, the function call error in the select list will not return. 

How to repeat:
there is function definition like:

DELIMITER |
DROP FUNCTION IF EXISTS quest_stage.avglinda;
CREATE FUNCTION quest_stage.`avglinda`(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED, num3 SMALLINT UNSIGNED) RETURNS float(3,2)
BEGIN
RETURN (num1+num2+num3)/3;
END|
DELIMITER ;

function avglinda have three parameters.

And there is a query like:
SELECT `REGION_ID`    AS `COL_1`, `REGION_NAME`  AS `COL_2`, avglinda(1, 2) AS `COL_3`  FROM `quest_stage`.`region`

the function call of avglinda only provide two parameter.
Normally it will return error as:
# ERR: Incorrect number of arguments for FUNCTION quest_stage.avglinda; expected
 3, got 2 (MySQL error code: 1318, SQLState: 42000 )

But with the following connection string, it will return SQL_SUCCESS with empty result.

DRIVER={MySQL ODBC 5.3 ANSI Driver};server=localhost;port=3306;database=quest_stage;UID=linda;PWD=xxx;FBS=3000000;NO_CACHE=1;FORWARD_CURSOR=1

After remove the parameter 'NO_CACHE=1;' the issue gone. As I need to query on very large table in production environment, so I can not remove the parameter.
But the incorrect return status make me not able to display proper message for client.

Suggested fix:
parameter NO_CACHE=1 should not hide function call error.

Hello !!,

Thank you for the bug report.
Verified this behavior on Visual Studio 2017 (C#.Net) and Connector / ODBC 5.3.10.

Thanks,
Chiranjeevi.

Screenshot

Attachment: Bug_89542.PNG (image/png, text), 33.84 KiB.

Screenshot

Attachment: Bug_89542_1.PNG (image/png, text), 42.32 KiB.

@Chiranjeevi, thanks for your verification. 
Do you know is there any workaround before I get the fixing?
Obviously I can not remove the NO_CACHE parameter, because the sharing connections may work on large table with millions of rows.

This defect is still reproducible in ODBC driver 8.0.24. Here is my repro steps,

1. Set up a MySQL DSN with "Don't cache results of forward-only cursors" and "Force use of forward-only cursors" checked. 

2. Open ODBCTest. Connect using the DSN created above. 

3. SQLPrepare with query "SELECT 1 + 9223372036854775807;"

4. SQLExecute returns SQL_SUCCESS.

5. If "Don't cache results of forward-only cursors" and "Force use of forward-only cursors" are not checked in DSN, SQLExecute returns SQL_ERROR correctly.

Verified against 8.0.24.

Posted by developer:
 
By the nature of NO_CACHE=1 option the error could not be returned upon calling of SQLPrepare()/SQLExecute().
This is happening because NO_CACHE causes the driver not to fetch and buffer the resultset produced by the statement execution.
The actual error is received from the server not when the statement is executed, but when the resultset is read using mysql_stmt_store_result() or mysql_stmt_fetch().
Normally SQLEcecute() would pre-buffer the resultset and receive the error from the server causing reporting error immediately.
However, NO_CACHE=1 causes SQLExecute() not to read the resultset immadiately, so the error could only be detected upon calling of SQLFetch() function which starts reading resultset row by row.

Currently SQLFetch() does not report the error correctly and this is the bug.

Posted by developer:
 
The patch is pushed into the source tree.
With NO_CACHE=1 the error can only be returned from SQLFetch() instead of SQLExecute().

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

Setting NO_CACHE=1 in the ODBC connection string would cause function
errors to go undetected.

Thank you for the bug report.