MySQL Bugs: #29883: MyODBC 3/ADO recordset update if SQL includes a Limit clause

Bug #29883	MyODBC 3/ADO recordset update if SQL includes a Limit clause
Submitted:	18 Jul 2007 23:48	Modified:	6 Feb 2008 4:48
Reporter:	Erica Moss	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.17	OS:	Windows (xp sp 2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If the code below is run with a command that has a LIMIT clause, and a server side cursor, it fails when and update is called because the update statement looks like this...
"UPDATE `ado`..`ado_test` SET c3=? WHERE (c1=? AND c2=? AND c3=? )"

If the same code is run with the same command but a client side cursor it succeeds and the update statement looks like this...
"UPDATE `ado`.`ado_test` SET `c3`=? WHERE `c1`=? AND `c3`=?\ 0"

If the same code is run without the LIMIT clause and a server side cursor it also succeeds and the update looks like this...
"UPDATE ado_test SET c3=? WHERE (c1=? AND c2=? AND c3=? )"

It's not clear whether this might be an ADO bug, or whether we might be able to create some workaround for it.  ODBC traces for the three cases are attached

How to repeat:
SETUP:
DROP TABLE IF EXISTS ado_test;
CREATE TABLE ado_test (C1 INT PRIMARY KEY, C2 CHAR(10), C3 INT);
INSERT INTO ado_test VALUES (1,'One',101),(2,'Two',102),(3,'Three',103);

VB Code:
Sub LimitTest()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    strSQL = "SELECT c1, c2, c3 from ado_test limit 2"
'    strSQL = "SELECT c1, c2, c3 from ado_test"
    
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
'    connection1.CursorLocation = adUseClient
    connection1.Open
    
    ' open recordset
    recset1.Open strSQL, connection1, adOpenStatic, adLockOptimistic, adCmdText

    Set fld = recset1.Fields(2)
    fld.Value = 123
 
    recset1.Update  ' error

    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    Debug.Print Err.Number & ":" & Err.Description
    recset1.CancelUpdate
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
End Sub

SQL with limit client cursor

Attachment: SQL with limit client cursor.LOG (application/octet-stream, text), 66.25 KiB.

SQL with limit server cursor

Attachment: SQL with limit server cursor.LOG (application/octet-stream, text), 63.84 KiB.

SQL without limit

Attachment: SQL without limit.LOG (application/octet-stream, text), 64.22 KiB.

This works fine in both 3.51 and 5.1. The following update is issued:

980 Query       UPDATE `ado_test` SET `c3`=123 WHERE `C1`=1 LIMIT 1

Please add ADO regression test.

Tested against 3.51.23 = problem still exists
"Trace: 3219::Operation is not allowed in this context."

Test case added to ADO Conformance tests
/bugs/29883.vbs

ODBC trace added for the server side test case

from trace:
"UPDATE `ado`..`29883` SET C3=? WHERE (C1=? AND C2=? AND C3=? )"

new odbc trace 3.51.23

Attachment: SQL.LOG (application/octet-stream, text), 107.33 KiB.

This is a duplicate of bug#27351.