[SQL Server] Consecutive delete mutation fails with: "There is already an object named #deleted"

[weiskopfsodefa]

Version Information

Server Version: 2.6.1
Database driver: [Microsoft][ODBC Driver 17 for SQL Server]

Environment

OSS
Docker with image: hasura/graphql-engine:v2.6.1

What is the expected behaviour?

Executing two consecutive delete mutations on the same database table should not throw an error.

Keywords

sqlserver consecutive delete mutation
there is already an object named #deleted

What is the current behaviour?

Executing a delete mutation on the same table with different variables throws an error. Not sure if its time gated, but at some point the mutation will work again.

How to reproduce the issue?

1. Connect an Azure MS SQL database (thats at least our DB)
2. Execute a delete mutation twice in a row (with different where clauses)

Screenshots or Screencast

[AndrewCraswell]

I'm experiencing this in Hasura Cloud as well. It seems like it might be intermittent though. I encounter the issue if I execute two delete mutations within a short amount of time.

[sassela]

Thanks for the bug report, we'll triage this soon and respond here with any questions or updates

[eviefp]

I think we need to be deleting temporary tables after use. I think this is happening only when the same connection is reused for two consecutive deletes. I am not sure when/how they get cleaned up though (if ever -- maybe whenever we close/recreate connections).

One way to validate this would be to set the MSSQL pool size to 1, then run multiple deletes. This should reproduce then.

If that is the case, a fix would be to manually drop the temporary table after each delete statement.

We should also add a test which reproduces this scenario (single connection, multiple deletes).

[soupi]

Thank you for your patience, this issue has been fixed via 2b3d7c4 and will be available in the next release. In the meantime, you can try it out using the Docker image: hasurabuild/graphql-engine:mono-branch-dev-f5f5815d9-main.