Can't sync column from source on complex queries

[ValentinC-BR]

I can't use "sync column from source" on virtual datasets whose query doesn't start with SELECT.

Expected results

Whatever my query is, I should be able to sync column from source for my virtual datasets.

Actual results

When my query starts with SELECT, there's no problem.
When my query start with "WITH" statements :

• I can create a virtual dataset using the "EXPLORE" feature in SQL Lab
• I can't update it and sync columns from source : I get the "ONLY SELECT STATEMENTS ARE ALLOWED" error

I'm forced to re-run the query in SQL Lab, and save is over the previous virtual dataset

What's more, contrary to the previous version of superset (0.x.x), the time columns are not detected, but this seems to be a different issue.

Screenshots

How to reproduce the bug

1. Run a query in SQL Lab starting with a "WITH" statement
2. Click on Explore and create a virtual dataset
3. Go to dataset, update the query (you can add/remove/modify a column, anything works, even a mere filter)
4. Save
5. Click on 'Sync Columns from source' (it should get your new column list)
6. See error

Environment

(please complete the following information):

• superset version: 1.0.1
• python version: 3.7.9
• node.js version: doesn't apply, I run on Kubernetes, using gunicorn as server
• source : Athena

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Nothing to report

[betodealmeida]

Taking a look.

[betodealmeida]

I tested with a simple query and it worked:

WITH RAW AS (SELECT name, color FROM bart_lines)
SELECT * FROM RAW

We've had problems in the past with our SQL parser not being able to identify CTEs as SELECT statements correctly, and I fixed a few of them lately:

• fix: handle CTEs with comments on is_select #16769
• fix: extract tables doesn't work with reserved keywords #17654
• feat: improve logic in is_select #17329

I suspect the original SQL that triggered this problem fell into this category.

@ValentinC-BR I'm going to close the ticket, but feel free to reopen if you can still repro it — if I have the SQL of the virtual dataset I can fix it.

[ValentinC-BR]

My bad, maybe it didn't come from queries that don't start with SELECT, but with queries with comments.
.
It seems that the comments (-- MY COMMENT HERE) are not well supported.

[ValentinC-BR]

The title of this issue could be changed, as it might not be related to complex queries.

[betodealmeida]

No worries, sorry for the delay, this ticket fell under my radar.

If the problem was comments then #16769 should fix it. :)