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

[lowrykun]

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

16

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.

[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}

[rusackas]

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

[rusackas]

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

[Alvie]

Thank you for opening this issue @lowrykun and sharing what fixed it for you! I have had the same issue and never would have guessed that removing the spaces in the name would fix it. It's certainly not ideal but we can live with it for now. Hopefully, there will be a fix :)