SupersetErrorType.ADHOC_SUBQUERY_NOT_ALLOWED_ERROR thrown for valid quoted column name

[cezudas]

Bug description

I set up a bar chart X axis value to a column named 'Created on'.
When the chart data loads I get an error: "Custom SQL fields cannot contain sub-queries."

Sample chart SQL query: SELECT DATE_TRUNC('day', "Created on") AS "Created on", sum("Estimated savings / Month") AS "SUM(Estimated savings / Month)" FROM public."Opportunities_4_userfriendly" WHERE ("Status" NOT IN ('Dismissed', 'Completed')) GROUP BY DATE_TRUNC('day', "Created on") LIMIT 50000;

In my case, choosing any other column containing the token 'on' for the X axis, such as "Update on" will lead to the same error.
My guess is the bug originates from superset/models/helpers.py#validate_adhoc_subquery. The parser is likely incorrectly interpreting "Created on" as a table reference because of the word "on"

Screenshots/recordings

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• 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]

Hi @cezudas! I'm here to help you with any bugs, questions, or contributions you have for the project. Let's tackle this issue together!

The issue you're encountering seems to be related to how the validate_adhoc_subquery function interprets SQL queries. This function checks for sub-queries and raises an error if it detects them, unless the feature flag ALLOW_ADHOC_SUBQUERY is enabled ^[1].

In your case, it appears that the parser might be misinterpreting the quoted column names containing the word "on" as sub-queries or table references. This could be due to the way the SQL parsing logic handles certain keywords or patterns within column names.

To address this issue, you might want to check if the ALLOW_ADHOC_SUBQUERY feature flag is enabled in your Superset configuration. If it's not enabled, you can try enabling it to see if it resolves the error. You can do this by setting the feature flag in your configuration file or via an environment variable:

FEATURE_FLAGS = {
    "ALLOW_ADHOC_SUBQUERY": True,
}

Or set the environment variable:

export SUPERSET_FEATURE_ALLOW_ADHOC_SUBQUERY=True

If the issue persists even with the feature flag enabled, it might require further investigation into the SQL parsing logic to ensure that quoted column names are correctly handled without being misinterpreted as sub-queries.

To continue talking to Dosu, mention @dosu.

[cezudas]

ALLOW_ADHOC_SUBQUERY

I appreciate your support +1
I was aware of the feature flag mentioned above.
However, using it is not acceptable for my use case. According to the changelog entry, the flag is turned off by default for security reasons

• 19242: Adhoc subqueries are now disabled by default for security reasons. To enable them, set the feature flag ALLOW_ADHOC_SUBQUERY to True.

I would appreciate if the maintainers would take this bug report under consideration, and maybe we will get a proper fix one day.

[cezudas]

@dosu
Please consider my previous response, I don't think valid column names such as "Update on" should be flagged as a subquery to begin with.

However, using it is not acceptable for my use case. According to the changelog entry, the flag is turned off by default for security reasons

#19242: Adhoc subqueries are now disabled by default for security reasons. To enable them, set the feature flag ALLOW_ADHOC_SUBQUERY to True.

[dosubot]

@rusackas, this one's a bit out of my wheelhouse, could you step in and provide your guidance?

To continue talking to Dosu, mention @dosu.

[pavelplus]

I have the same issue in TABLE chart in RAW RECORDS query mode for columns with a dot in their names - this showed up after upgrading from 4.0.2 to 4.1.1. Enabling "ALLOW_ADHOC_SUBQUERY": True resolved issue by now...