abhinavsood opened a new issue #8074:
URL: https://github.com/apache/superset/issues/8074
### 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:
```bash
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:
```sql
WITH t AS (SELECT 1 a) SELECT * FROM t
```
#### Expected Result
```text
------
| a |
------
| 1 |
------
```
#### Observed Result
The following error message is seen:
```text
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:
```text
Superset 0.33.0rc1
flask 1.1.1
werkzeug 0.15.5
```
Other packages:
```text
sqlalchemy 1.3.7
sqlparse 0.3.0
unixodbc 2.3.7
pyodbc 4.0.23
```
```text
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
