bug: TSQL temp table creates permanent ibis_memtable

[JonaGeishauser]

What happened?

Using self.con.create_table(temp=True,...) on the MSSQL Backend, creates a persistent ibis_polars_memtable:

I logged the raw_ddl statement that gets executed by ibis and it uses this syntax which means it actually creates a persistent normal database table if I am not mistaken:

CREATE TABLE [ibis_polars_memtable_rlcxzq53jvg7vkj3w4o2d4725y] ...

What version of ibis are you using?

10.5

What backend(s) are you using, if any?

MSSQL

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Thanks for the report!

I thought we were using the # syntax (MSSQL's temp table indicator), but it seems like we're not.

[cpcloud]

It's actually that temp=True has nothing to do with the memtable that's created. We use memtables to abstract over getting the different in memory containers into the database inside of most create_table implementations, and those aren't necessarily themselves temporary tables.

[cpcloud]

I think the memtable implementation needs to be reworked a bit to allow backends more control over the lifetime of their memtables. MS SQL in particular has an odd way of dealing with temporary tables (prefixing with either # [local temp table, visible only to the current session], and ## [global temp table, visible to effectively anyone AFAICT]).

For Ibis, ideally we can use local temp tables, since that is the closest MS SQL concept to Ibis's memtable.