bug: TSQL Temp table creation fails for datetime column

[JonaGeishauser]

What happened?

Creating a temp table that contains a datetime column:

self.con = ibis.mssql.connect(...)

test_df = pl.DataFrame(
                {"col1": [datetime(2023, 10, 21, 6, 54)]},
                schema={"col1": pl.Datetime(time_unit="ns", time_zone=None)},
            )

self.con.create_table("TestTableTemp1", obj=test_df, temp=True)

Fails as the datetime data type gets transformed to wrong syntax:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Line 1: Specified scale 9 is invalid. (1002) (SQLExecDirectW)')

logged raw ddl:

CREATE TABLE [ibis_polars_memtable_5kiobxlxojdkriwrpqyv5l33vi] ([col1] DATETIME2(9))

Per sql server docs(https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver17):

Precision, scale | 0 to 7 digits, with an accuracy of 100 nanoseconds (100 ns). The default precision is 7 digits.In Microsoft Fabric Data Warehouse, this precision can be an integer from 0 to 6, with no default. Precision must be specified in Microsoft Fabric Data Warehouse.

What version of ibis are you using?

10.5.0

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

MSSQL

Relevant log output

DB Error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Line 1: Specified scale 9 is invalid. (1002) (SQLExecDirectW)')


DDL statement excuted in ibis:

CREATE TABLE [ibis_polars_memtable_5kiobxlxojdkriwrpqyv5l33vi] ([col1] DATETIME2(9))

Code of Conduct

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

[cpcloud]

@JonaGeishauser What are you expecting to happen?

It looks like you explicitly asked for time_unit="ns", which isn't supported by SQL Server.

What do you expect Ibis to do here?

[JonaGeishauser]

@cpcloud I encountered this issue in a bit more complex scenario and went with this example as a "dumbed down" version to reproduce this issue in a simple way.
I used time_unit = "ns" as this was the dtype I got when pulling data from a SQL Server table which I was using as my input data. Seems like this is then an issue on polars' side.
It would be cool if ibis could handle this faulty user input gracefully and defaulting down to the highest precision but I understand that this is not a bug on your side and more or less expected behaviour

Thanks for the response, feel free to close this issue if there will be no change on your side! 👍