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

[22 Sep 2021 20:51] Andreas Lennartz
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
[22 Sep 2021 20:52] Andreas Lennartz
Source code

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

[27 Sep 2021 13:10] MySQL Verification Team
Hello Andreas Lennartz,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil
[30 Aug 2022 11:36] Bogdan Degtyariov
Posted by developer:
 
The bug in the server is fixed.
Adding a test case to ODBC unit tests.
[30 Aug 2022 13:13] Bogdan Degtyariov
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.
[30 Aug 2022 13:26] Andreas Lennartz
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!
[23 Sep 2022 19:32] Philip Olson
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.