superset: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database

Problem

Common Table Expressions (CTE) queries are not working as expected in SQL Lab against SQL Server databases. These are queries written using the WITH clause.

Steps to Reproduce

Start superset using:

superset run -p 8080 --with-threads --reload --debugger

Add a SQL Server database and tables in Sources.

Make sure to place a check for Allow DML and ensure that it is selected and set to True.

Navigate to SQL Lab. Run the following query:

WITH t AS (SELECT 1 a) SELECT * FROM t

Expected Result

------
| a  |
------
| 1  |
------

Observed Result

The following error message is seen:

2019-08-20 14:07:53,005:ERROR:root:('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)")
Traceback (most recent call last):
  File "/home/user/anaconda3/envs/superset/lib/python3.6/site-packages/superset/sql_lab.py", line 190, in execute_sql_statement
    db_engine_spec.execute(cursor, sql, async_=True)
  File "/home/user/anaconda3/envs/superset/lib/python3.6/site-packages/superset/db_engine_specs.py", line 421, in execute
    cursor.execute(query)
pyodbc.ProgrammingError: ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near the keyword 'WITH'. (156) (SQLExecDirectW)")
2019-08-20 14:07:53,017:INFO:werkzeug:127.0.0.1 - - [20/Aug/2019 14:07:53] "POST /superset/sql_json/ HTTP/1.1" 500 -

System and Environment Details

Database: Microsoft SQL Server 2017

Operating System: Red Hat Enterprise Linux 7 x86_64 Python: Python 3.6.7 (Anaconda Python)

Superset:

Superset 0.33.0rc1
flask 1.1.1
werkzeug 0.15.5

Other packages:

sqlalchemy 1.3.7
sqlparse 0.3.0
unixodbc 2.3.7
pyodbc 4.0.23

FreeTDS 1.1.11

Additional Notes

The query runs as expected against the same database:

Directly in Microsoft SQL Server Management Studio 17
Through FreeTDS (by running tsql -S <FQDN> and then executing the query)
Through pyODBC and SQLAlchemy in a python program.

The query runs as expected against a PostGreSQL database through SQL Lab.

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 15 (8 by maintainers)

Commits related to this issue

fix(mssql): support cte in virtual tables (#18567) * Fix for handling regular CTE queries with MSSQL,#8074 * Moved the get_cte_query function from mssql.py to base.py for using irrespetcive of dbe... — committed to apache/superset by sujiplr 2 years ago

Most upvoted comments

If you check the diff, I added a warning about using CTEs on non-ANSIs.

Btw, I’m working on something that might improve CTE support on all engines and should fix this problem on non-ANSIs, too, but we’ll see how that works out. But in the meantime this is probably the best compromise I can offer.

villebro on Aug 22, 2019

Reproduced on master; running the same on sqlite works just fine. Will take a closer look.

villebro on Aug 20, 2019

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.94. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

issue-label-bot[bot] on Aug 20, 2019