MySQL Bugs: #105013: Odbc parameter not properly replaced when put into JOIN/VALUES statement

Bug #105013	Odbc parameter not properly replaced when put into JOIN/VALUES statement
Submitted:	22 Sep 2021 20:51	Modified:	30 Aug 2022 13:26
Reporter:	Andreas Lennartz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Windows (Windows 10 latest)
Assigned to:		CPU Architecture:	x86 (Intel)
Tags:	regression

Description:
I use the Odbc connector with C# and the System.Data.Odbc package (5.0.0). I installed the latest Odbc connector (8.0.26) on my windows system.

When I send a query with 2 parameters through an Odbc connection to the MySql 8.0.26 database, it will be executed without errors. But I can tell from the table that the parameters in the query where not properly replaced, as the table has been unchanged. 

Here is the relevant part of the code that don't work: 

string sqlUpdate = @"
UPDATE test ut
    INNER JOIN ( VALUES ROW( ? , ? ) ) vt (id,value)
    ON (ut.id = vt.id)
SET ut.value = vt.value";
OdbcCommand cmdUpdate = new OdbcCommand(sqlUpdate, conn);            
var updatePar1 = new OdbcParameter("UpdatePar1", 1);
var updatePar2 = new OdbcParameter("UpdatePar2", "B");
cmdUpdate.Parameters.Add(updatePar1);
cmdUpdate.Parameters.Add(updatePar2); 
cmdUpdate.ExecuteNonQuery();

The target table test has 2 columns (id, value) and contains one row: id: 1 and value: 'A'.
This will execute, but it won't update the destination table when running on MySql 8.0.26. 

If I move the parameter placeholders (?) outside the JOIN / VALUES clause, e.g. into the ON or SET area, the statement will work as expected. If I hardcode the values into the statement, this will also work. 

If I run this statement against MySql 8.0.21 using the exact same setup, everything works fine. 

I will attach the whole C# example code as a file. The only dependency of the code is System.Data.Odbc (5.0.0)

How to repeat:
CREATE TABLE test (
     id INT NOT NULL,
     value VARCHAR(100) NULL
)

INSERT INTO test VALUES ROW(1,'A')

Use C# and System.Data.Odbc and try to update the table:

 string sqlUpdate = @"
UPDATE test ut
    INNER JOIN ( VALUES ROW( ? , ? ) ) vt (id,value)
    ON (ut.id = vt.id)
SET ut.value = vt.value";
OdbcCommand cmdUpdate = new OdbcCommand(sqlUpdate, conn);            
var updatePar1 = new OdbcParameter("UpdatePar1", 1);
var updatePar2 = new OdbcParameter("UpdatePar2", "B");

cmdUpdate.Parameters.Add(updatePar1);
cmdUpdate.Parameters.Add(updatePar2);
cmdUpdate.ExecuteNonQuery();

Suggested fix:
Works with 8.0.21. 
Should also work with 8.0.26

Source code

Attachment: Program.cs (text/plain), 2.15 KiB.

Hello Andreas Lennartz,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

Posted by developer:
 
The bug in the server is fixed.
Adding a test case to ODBC unit tests.

Posted by developer:
 
Added the Unit test.
To pass the test it needs to be executed against MySQL Server 8.0.31 or newer because this version contains the fix for the base server bug.

Thanks a lot for fixing this!
Once 8.0.31 is released, I will adjust the workaround in my code and double check if the issue is solved. 
Thanks!

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

Added a test case for a Server bug that was fixed in MySQL Server 8.0.31;
its release note is as follows: 

A prepared statement with
parameters could fail to update a row, but the same statement with the
same data did update the row when issued as a query. The fix for the
problem is to assign a default data type to the parameters, although this
can be inefficient because there is no context available for data type
propagation and a character string type is given implicitly. In this case,
the best practice is to wrap such parameter declarations in CAST clauses
that supply the desired data types.

Thank you for the bug report.