Querying temporary table in same query it was created yields no results

[ldtcooper]

Describe the bug When creating and querying temporary tables in the same query/tab, selecting from the temporary table yields no results. The temporary table is created and I see the schema in the results pane, but there are no rows in it. Furthermore, if I open up a new query and run the same select there, I do get the intended results.

To Reproduce Open one query on a MySQL connection and enter the following code:

DROP TEMPORARY TABLE IF EXISTS a;

CREATE TEMPORARY TABLE a (
    SELECT 
        1 AS first,
        2 AS second
);

SELECT * FROM a;

That should be enough to see the first part of the issue, i.e. a lack of results. To see the second part of the issue, open a new query and just enter:

SELECT * FROM a;

Expected behavior Selecting from the temporary table in the same query as it was created should return the table with results, i.e. the select in the first screenshot should yield the results seen in the second.

Screenshots

Running the first query with no results:

Running the second query with results:

Desktop (please complete the following information):

• SQLTools Version 0.28.1
• VS Code Version: 1.84.2
• OS: Windows 11 Pro OS Build 22621.2428
• Driver:
  • [ ] PostgreSQL/Redshift
  • [x] MySQL/MariaDB
  • [ ] MSSQL/Azure
  • [ ] SQLite
  • [ ] Other? Which...
• Database version: MySQL 8.0.33

Additional context Seems like this may be the same issue as this one but with MySQL instead of Postgres. Additionally, running the same code in the reproduce section in MySQLWorkbench works as intended.

[ldtcooper]

I've discovered this may be an issue with multiple commands, not just with temp tables. I recently tried doing a select after defining a variable with SET and ran into the same issue. Try the following code:

SET @foo := 20;

SELECT @foo AS bar;

[gjsjohnmurray]

I suspect that the MySQL driver distributes a multi-command statement across a pool of connections. I've seen reports of the same behaviour with one of the other drivers, for which a workaround existed involving setting the maximum number of connections to 1.

[ldtcooper]

I think I found the issue you're talking about here. It looks like that setting might be specific to the Postgres and Redshift drivers which use node-pg library. This other open issue actually seems to be the same as my issue, but it's been open since 2020 without a resolution. The one comment about a resolution mentions that fix as well. The problem is, I can't find any sort of setting in the SQLTools or the MySQL driver settings to change the maximum connections.