Summary
The tsql dialect cannot parse an Azure Synapse OPENROWSET table source when it is followed by the documented WITH (...) schema definition. This prevents SQLMesh from parsing valid Synapse views and caused a QESD Synapse lineage build to fail.
OPENROWSET appears to be partially supported: SQLGlot can parse the table-valued function and its BULK options, but the WITH (...) AS alias schema clause is rejected.
Reproduction
Tested with SQLGlot 30.17.0:
import sqlglot
sql = """
SELECT sample_id
FROM OPENROWSET(
BULK (
'/lake-landing/reference data/area_type/*.*',
'/lake-landing/this_file_doesnt_exist.parquet'
),
DATA_SOURCE = 'datasource_gbrwlake',
FORMAT = 'csv',
PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
sample_id VARCHAR(50)
) AS area_type
"""
sqlglot.parse_one(sql, read="tsql")Actual result:
sqlglot.errors.ParseError: Expecting ).
The parse error is reported at the WITH schema clause. The same construct failed in SQLGlot 30.8.0, which was the version installed by SQLMesh 0.236.1 in the CI job.
For comparison, removing the WITH (...) clause allows the corresponding table source to parse:
SELECT *
FROM OPENROWSET(
BULK ('file.csv'),
DATA_SOURCE = 'datasource_gbrwlake',
FORMAT = 'csv'
) AS source_tableExpected behavior
The T-SQL parser should represent the OPENROWSET source, its options, the WITH column definitions, and the table alias in the AST, and should round-trip the statement for the tsql dialect. A focused parser and generator test for Azure Synapse OPENROWSET ... WITH (...) syntax would be useful.
Related issues and compatibility context
- SQLGlot #7428 reports Azure Synapse-specific T-SQL syntax that is unsupported. The maintainer response says Azure Synapse is not officially supported and considers the issue out of scope.
- SQLGlot #5142 documents additional Azure SQL/T-SQL syntax gaps and describes this support as low priority, while welcoming a well-tested PR.
- SQLMesh #5948 records that SQLMesh 0.236.1 pins SQLGlot to
~=30.8.0 and that independently overriding SQLGlot to >=30.14.0 exposed import incompatibilities. SQLGlot 30.17.0 still produces the OPENROWSET ... WITH (...) parse error, so upgrading SQLGlot alone does not resolve this syntax gap.
If Azure Synapse-specific syntax is intentionally out of scope for the core tsql dialect, please advise whether this should be implemented as a separate dialect or whether a narrowly scoped, well-tested OPENROWSET contribution would be considered.
Summary
The
tsqldialect cannot parse an Azure SynapseOPENROWSETtable source when it is followed by the documentedWITH (...)schema definition. This prevents SQLMesh from parsing valid Synapse views and caused a QESD Synapse lineage build to fail.OPENROWSETappears to be partially supported: SQLGlot can parse the table-valued function and itsBULKoptions, but theWITH (...) AS aliasschema clause is rejected.Reproduction
Tested with SQLGlot 30.17.0:
Actual result:
The parse error is reported at the
WITHschema clause. The same construct failed in SQLGlot 30.8.0, which was the version installed by SQLMesh 0.236.1 in the CI job.For comparison, removing the
WITH (...)clause allows the corresponding table source to parse:Expected behavior
The T-SQL parser should represent the
OPENROWSETsource, its options, theWITHcolumn definitions, and the table alias in the AST, and should round-trip the statement for thetsqldialect. A focused parser and generator test for Azure SynapseOPENROWSET ... WITH (...)syntax would be useful.Related issues and compatibility context
~=30.8.0and that independently overriding SQLGlot to>=30.14.0exposed import incompatibilities. SQLGlot 30.17.0 still produces theOPENROWSET ... WITH (...)parse error, so upgrading SQLGlot alone does not resolve this syntax gap.If Azure Synapse-specific syntax is intentionally out of scope for the core
tsqldialect, please advise whether this should be implemented as a separate dialect or whether a narrowly scoped, well-testedOPENROWSETcontribution would be considered.