MySQL Bugs: #114289: execute(...,multi=True) returns inconsistent results depending on iteration

Bug #114289	execute(...,multi=True) returns inconsistent results depending on iteration
Submitted:	8 Mar 2024 23:57	Modified:	14 Mar 2024 14:04
Reporter:	Jeremy Todd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / Python	Severity:	S2 (Serious)
Version:	8.0.3	OS:	Ubuntu
Assigned to:	Oscar Pacheco	CPU Architecture:	Any

Description:
execute(...,multi=True) returns a generator, and I'm getting different results depending on how I iterate over the generator.

When I take results with next() and do something with them it behaves as expected. When I try to take results all at once (e.g. by converting to a tuple) then I get duplicate results.

How to repeat:
My query is a SET statement followed by a SELECT, so this behaves as expected:
>>>    result = cursor.execute(query, multi=True)
>>>    print(next(result))
>>>    print(next(result))
CMySQLCursorBuffered: SET @a=1, @b='2024-02-01'..
CMySQLCursorBuffered: SELECT c, d, sum(e..

When I convert to a tuple (e.g. to do something like tuple(result)[1]), the resulting addresses are repeated:
>>>    result = cursor.execute(query, multi=True)
>>>    print(tuple(result))
(<mysql.connector.cursor_cext.CMySQLCursorBuffered object at 0xffff7e87f8c0>, <mysql.connector.cursor_cext.CMySQLCursorBuffered object at 0xffff7e87f8c0>)

Calling next() in rapid succession also seems to yield duplicate results:
>>>    result = cursor.execute(query, multi=True)
>>>    v1, v2 = next(result), next(result)
>>>    print(v1)
>>>    print(v2)
CMySQLCursorBuffered: SELECT c, d, sum(e..
CMySQLCursorBuffered: SELECT c, d, sum(e..

I dug a bit and see now that CMySQLCursor._execute_iter uses `yield self` to yield results, so this behavior makes more sense. It was surprising and I'm still looking for a good way to skip the first result and take the second in one line (i.e. like `tuple(result)[1]`)

Hello Jeremy, 

Thanks for reaching us.

What you are trying to do is an unsupported use case. 

I wouldn’t flag it a bug strictly speaking because this use case was never intended to be supported by the current `multi-statement execution results` design, see [MySQLCursor.execute() Method](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.htm...) for usage info. 

It could be flagged as an enhancement, but we'd need to go over the possible trade-offs. What you're trying to do (as far as I understand) can be accomplished as follows:

```
# `Cursor` results after executing a multi-statement are expected to be processed one by one where the caller (user) should check for results of the ongoing cursor before moving to the next one, like this:
with cpy.connect(**config) as cnx:
    with cnx.cursor(buffered=True) as cur:
        result = tuple(
            [
                (res.statement, res.fetchall() if res.with_rows else None)
                for res in cur.execute(stmt, multi=True)
            ]
        )
        print(result)
```

As multi-statement execution works right now, using `tuple(cur.execute(stmt, multi=True))` isn't expected to work because the `multi-statement execution results` was designed to return a cursor, and this cursor isn't a copy of the main cursor, but a reference to it. In this regard, the caller takes ownership of the returned cursor which means the caller is expected to manage the resource (e.g., inspect the executed statement, fetch results, etc.).

So, making a sequence with `tuple(...)` out from the generator of cursors returned by `cur.execute(stmt, multi=True)` cannot work because cursors will point to the same object whose inner state is changing as statements go by. Thus, all cursors will reflect the same final state.

In-line reply
---------------
a) “My query is a SET statement followed by a SELECT, so this behaves as expected”: right because you’re processing cursor results one by one.

b) “When I convert to a tuple (e.g. to do something like tuple(result)[1]), the resulting addresses are repeated”: `cursor results` reference the same cursor object (the main cursor being used) but with different internal states. In this regard, the addresses are expected to be the same.

c) “Calling next() in rapid succession also seems to yield duplicate results”: if you don’t intercept the wanted data of a certain cursor result before moving to the next one, this inner state will be lost. Furthermore, since all cursor results reference the same cursor object, previous cursor results will reflect the current inner state. This is the reason for the duplicates.

Makes sense, I think this was just my misunderstanding of the interface - agreed it's not a bug.