Apache Superset "Error: Custom SQL fields cannot contain sub-queries" on columns with multiple spaces

Bug description

I'm experiencing an issue while using a view with column names aliased with multiple spaces. I'm using 4.1.1

For example, if I create the following MySQL view, when I add 'Answer Created Time' to my dataset, even as a column in raw table, I get "Error: Custom SQL fields cannot contain sub-queries." Using 'Correction Time' works fine.

select
a.created_at as "Answer Created Time",
ac.created_at as "Correction Time"
from
answers a
left join answer_corrections ac ON a.id=ac.answer_id

But when I change my view to be snake_case, then everything works as expected:

select
a.created_at as answer_created_time,
ac.created_at as correction_time
from
answers a
left join answer_corrections ac ON a.id=ac.answer_id

Clearly this is sub-optimal because then I have to add a label to each column to have it appear in Title Case.

Screenshots/recordings

No response

Superset version

4.1.1

Python version

3.9

Node version

Browser

Chrome

Additional context

{"source": "sqlLab", "ts": 1741360907834, "event_name": "load_chart", "slice_id": 0, "has_err": true, "error_details": "Custom SQL fields cannot contain sub-queries.", "datasource": "27__table", "start_offset": 174522, "duration": 57, "event_type": "timing", "trigger_event": "8rTWIoP9Fhd0V0xFl1ZbD"}

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]

Hey @lowrykun! 👋 I'm here to help you with any bugs, questions, or contributions you have for Superset. Let's tackle this issue together!

The error message "Custom SQL fields cannot contain sub-queries" you're encountering seems to be related to how Superset handles SQL parsing and validation, particularly with column aliases that contain spaces. In Superset 4.1.1, the validate_adhoc_subquery function is responsible for checking if SQL contains sub-queries, and it raises an error if sub-queries are not allowed ^[1].

The issue with spaces in column aliases might be causing the SQL parser to misinterpret the query structure, leading to this error. While there isn't a direct workaround documented for handling spaces in aliases, using snake_case or other naming conventions without spaces seems to avoid the issue, as you've discovered.

Additionally, the SQLStatement class in the superset/sql/parse.py file is involved in parsing SQL statements and could be affected by spaces in aliases ^[2]. You might want to consider using underscores or other characters instead of spaces in your column aliases to prevent this parsing issue.

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

Sounds like it might be a SQLParse issue - maybe @betodealmeida has an idea here?

Comment From: rusackas

Pinging @betodealmeida again on this now that SQLGlot has replaced SQLParse