You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by GitBox <gi...@apache.org> on 2022/01/31 17:54:32 UTC
[GitHub] [superset] abhinavsood opened a new issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] villebro commented on issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
Posted by GitBox <gi...@apache.org>.
villebro commented on issue #8074:
URL: https://github.com/apache/superset/issues/8074#issuecomment-1044485533
@abhinavsood please check #18746 which should fix this issue
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
[GitHub] [superset] villebro closed issue #8074: Common Table Expressions (CTE's) do not work for Microsoft SQL Server database
Posted by GitBox <gi...@apache.org>.
villebro closed issue #8074:
URL: https://github.com/apache/superset/issues/8074
--
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: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org