Can't save a dataset from SQL Lab if it contains a CTE named "data"

[ghost]

I have a working query with a set a columns. When I try to save it and create a chart I get the following error in the UI:
Unexpected error: Columns missing in dataset

I tried to add / remove columns and it did not help. There are no errors in the log. I'm using the latest superset docker image

[sfirke]

Hello, this would be a good candidate for posting in the Superset Slack chat as it's more of a request for troubleshooting help than a bug report. It's unlikely though possible this is a bug in Superset; more info would be needed to determine either way. One way you could test would be to replicate the problem on a stable Superset release (e.g., 3.0.2). If the same thing doesn't throw an error there, it would indicate a possible bug on the the latest image.

Are you using Jinja templating? If so, you may need to remove the Jinja temporarily to save the dataset or refresh columns.

[ghost]

I'm using the docker image, and from looking at the tags I did not understand how do I get the latest stable image - so I used the default. Maybe that's the source of the problem - if you could write the name of the tag I should use it would be great.
I'm not using Jinja templates. I did reinstall superset and recreate the query and dataset before I opened this issue - I also added and removed columns but it did not help.

[michael-s-molina]

if you could write the name of the tag I should use it would be great

Hi @ori8954. You can follow the Quickstart guide to obtain the latest stable image. The latest stable version is 3.0.2.

[ghost]

I would expect to get the latest stable docker image when no version is specified, I'm sure it can save trouble for the docker users.
I found the issue - I'm using SQL Server as a database and I had the reserved keyword 'data' in my query. While it runs as expected in the SQL Lab it fails to run after saving it as a dataset.
You can try the following query to reproduce the error:

WITH data AS (
SELECT 1 AS column_name)
SELECT *
FROM data

If I change the name 'data' to another name problem is solved. This query gives an SQL error + missing columns error. The original query I had did not give a SQL error

[sfirke]

Thanks for the additional info, good troubleshooting. If you don't mind I'm going to change the issue title to be more specific.

I also use SQL Server. I agree I can run the query you shared in SQL Lab successfully (Superset 3.0.0 for me). When I save as a dataset, I get a different error message on the chart building view:

Error: (102, b"Incorrect syntax near ','.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

I clicked "View Query" to see what Superset is sending to SQL Server and it's this:

WITH data,
     __cte AS ( AS (SELECT 1 AS column_name)SELECT * FROM data)
SELECT TOP 1000 column_name AS column_name
FROM __cte;

Which errors when I run it directly in SQL Server too.

When I repeat the test but with data2 instead of data, it works. This is the SQL generated and sent to SQL Server:

WITH data2 AS
  (SELECT 1 AS column_name),
     __cte AS
  (SELECT *
   FROM data2)
SELECT TOP 1000 column_name AS column_name
FROM __cte;

Different enough that maybe the problem is before SQL Server, it's with how the query is getting shaped along the way? I wonder if others can replicate this with a different backend.

[rusackas]

Docker builds and docs have been significantly updated, and this thread has been quiet for a long time. Also, it's a support request more than a bug. Closing due to all of those reasons, but please do file a new issue with fresh context if you're still experiencing docker issues... we're trying to make it easier.