soda analyze fails on mssql-databases having objects containing white-space characters

[geertvanzoest]

Describe the bug When targeting MS SQL Server and executing soda analyze on database objects containing white-space characters (like column name instead of column_name), the ODBC Driver throws an error about incorrect syntax: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected.

To Reproduce

1. Have a Microsoft SQL Server instance with target database containing schema-objects (tables, columns) having white-space characters in their name.
2. Configure warehouse.yml accordingly
3. Run soda analyze

Cause This error occurs because Soda is not calling for objects using identifiers (or MS SQL Server requires identifiers, depending on how you look at it). Soda calls objects as column name instead of [column name], which causes SQL Server to consider only the first term as the object name (thus considering the second term syntactically as a new function or property).

Suggested solution In .\soda-sql\packages\sqlserver\sodasql\dialects\sqlserver_dialect.py:

• at line 120 replace SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE with SELECT QUOTENAME(COLUMN_NAME), DATA_TYPE, IS_NULLABLE.

OS: All Python Version: All Soda SQL Version: All Warehouse Type: Microsoft SQL Server (all versions)

[geertvanzoest]

Unfortunately, when using the QUOTENAME()-function, Soda stores the column name with brackets in it's model of the target database too. As if the column names are all pre- and suffixed with brackets. This causes tests in scan-files at column-level not getting invoked, because the column names don't match any longer.

[geertvanzoest]

Is see this issue was already raised in sodadata/soda-core#890. Closing this one as duplicate.