SQL Server query hints #38694
Replies: 5 comments
-
|
This is a known limitation with how Superset processes SQL queries differently between SQL Lab and charts. Why it happens: When creating charts, Superset wraps queries in subqueries to apply limits and other transformations. SQL Server's This is part of a broader pattern affecting T-SQL features since Superset adopted sqlglot for SQL parsing in 4.1.0+. Workarounds:
To reply, just mention @dosu. How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other |
Beta Was this translation helpful? Give feedback.
-
Beta Was this translation helpful? Give feedback.
-
|
Going to move this over to a discussion. |
Beta Was this translation helpful? Give feedback.
-
|
The Dosu explanation is correct about the root cause -- Superset wraps your virtual dataset query in a subquery, and The database view workaround is the most practical path, but there's a subtlety: you cannot put What you can do:
Option 2 gives you the most control without affecting other applications. |
Beta Was this translation helpful? Give feedback.
-
|
This issue is likely related to how Apache Superset wraps the query internally when generating charts. In SQL Server, the OPTION (MAXDOP 1) query hint must appear at the end of the final SELECT statement. However, Superset wraps the query inside a subquery (virtual table), as seen in the log: SELECT TOP 50 Because of this wrapping, the OPTION clause is no longer at the valid position, which results in the syntax error: Possible Workarounds
Summary The issue is not with SQL Server itself but with how Superset rewrites the query. Since SQL Server does not allow OPTION hints inside subqueries, this leads to the syntax error. Let me know if this helps or if you need help testing one of the workarounds. |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
Bug description
I am trying to create a chart that queries a SQL Server instance. To prevent excessive resource consumption, I am using the query hint OPTION (MAXDOP 1). It works perfectly in SqlLab, but when creating the chart, it triggers the following error:
Data error
Error: (156, b"Incorrect syntax near the keyword 'OPTION'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
Is there anything I can do to overcome this error?
Screenshots/recordings
2026-03-14 02:09:33,479:WARNING:superset.connectors.sqla.models:Query SELECT TOP 50
<my_query> OPTION(
MAXDOP 1
)) AS virtual_table ORDER BY [column] DESC on schema None failed
Traceback (most recent call last):
File "/app/superset/connectors/sqla/models.py", line 1659, in query
df = self.database.get_df(
File "/app/superset/models/core.py", line 710, in get_df
self.db_engine_spec.execute(cursor, sql_, self)
File "/app/superset/db_engine_specs/base.py", line 1855, in execute
raise cls.get_dbapi_mapped_exception(ex) from ex
File "/app/superset/db_engine_specs/base.py", line 1851, in execute
cursor.execute(query)
File "src/pymssql/_pymssql.pyx", line 465, in pymssql._pymssql.Cursor.execute
pymssql.exceptions.OperationalError: (156, b"Incorrect syntax near the keyword 'OPTION'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
Superset version
master / latest-dev
Python version
3.9
Node version
16
Browser
Chrome
Additional context
No response
Checklist
Beta Was this translation helpful? Give feedback.
All reactions