### Bug description

How to reproduce the bug 1) In SQL lab, run a query similar to:

 CREATE TEMP TABLE temp_file_failures AS
    SELECT 
      DATE(timestamp) AS processing_date,
      COUNT(*) AS file_failures
    FROM file_audit
    WHERE audit_error != '' 
      AND successful_policies_no = 0 
      AND partner != ''
    GROUP BY DATE(timestamp);

    select * from temp_file_failures;

2) Select "Save Dataset" 3) Populate Dataset name 4) Click "Save and Explorer"

Expected results: Dataset Saved and Chart Editor Launched

Actual results: Fatal Error on Saving

Image

Screenshots/recordings

No response

Superset version

4.0.2

Python version

3.10

Node version

16

Browser

Chrome

Additional context

Modifying the query to the following allows the dataset to save correctly.

with temp_file_failures AS ( SELECT DATE(timestamp) AS processing_date, COUNT(*) AS file_failures FROM file_audit WHERE audit_error != '' AND successful_policies_no = 0 AND partner != '' GROUP BY DATE(timestamp))

select * from temp_file_failures;

Banging my head to figure out why this isn't working (since a lot of our user base uses create temp table queries in SQL Lab.

Checklist

  • [x] I have searched Superset docs and Slack and didn't find a solution to my problem.
  • [x] I have searched the GitHub issue tracker and didn't find a similar bug report.
  • [x] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

Comment From: dosubot[bot]

Hello @TArthur1975! I'm here to help you with bugs, answer your questions, and assist with contributing. While you're waiting for a human maintainer, feel free to ask anything you need!

I found a similar closed issue titled "Unable to save a query as a new dataset" on the Superset GitHub repository. Unfortunately, no solution was found in the comments of that issue [1].

In your case, it seems that using a Common Table Expression (CTE) instead of a temporary table allows the dataset to save correctly. This might be due to how Superset handles temporary tables internally. Superset uses the CreateTableAsSelect class and the SqlJsonExecutionContext class to manage temporary tables, which might not fully support saving datasets when temporary tables are involved [2].

If modifying the query to use a CTE resolves the issue, it might be a viable workaround for now.

To continue talking to Dosu, mention @dosu.

Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Bug Report | Other

Comment From: rusackas

Can you check on current master or in the 6.0.0rc1 candidate to see if this is still the issue? I'm not sure if this is allowed or not, but you should see an error if not. Things have been migratefd from SQLParse to SQLGlot, so it my behave better now.