CTEs with name of 'roles' breaks query parser

Bug description

Create an SQL query against a Postgresql database like so:

WITH roles AS (
  SELECT 'Goodbye!'
)
SELECT 'Hello!'

See Only SELECT statements are allowed against this database.

It should either allow roles be a name for a CTE or call this out somewhere in the documentation. I looked and couldn't find any mention of it.

Screenshots/recordings

No response

Superset version

4.0.2

Python version

3.11

Node version

Browser

Chrome

Additional context

Stacktrace:

Traceback (most recent call last):
<removed>
File "/srv/.../venv/lib/python3.11/site-packages/superset/commands/sql_lab/execute.py", line 103, in run
    status = self._run_sql_json_exec_from_scratch()
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/srv/.../venv/lib/python3.11/site-packages/superset/commands/sql_lab/execute.py", line 163, in _run_sql_json_exec_from_scratch
    raise ex
  File "/srv/.../venv/lib/python3.11/site-packages/superset/commands/sql_lab/execute.py", line 158, in _run_sql_json_exec_from_scratch
    return self._sql_json_executor.execute(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
superset.exceptions.SupersetErrorsException: [SupersetError(message='Only SELECT statements are allowed against this database.', error_type=<SupersetErrorType.DML_NOT_ALLOWED_ERROR: 'DML_NOT_ALLOWED_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'issue_codes': [{'code': 1022, 'message': 'Issue 1022 - Database does not allow data manipulation.'}]})]

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.

The issue you're encountering is related to how Superset's SQL parser handles Common Table Expressions (CTEs). Superset's parser identifies CTEs by looking for the "WITH" keyword and expects the body of the CTE to be a SELECT statement. If the parser encounters any statement within the CTE that it cannot confirm as a SELECT, it raises the DML_NOT_ALLOWED_ERROR ^[1].

In your case, naming the CTE as roles seems to trigger this error, possibly due to a reserved keyword or a specific parsing rule that misinterprets the CTE. Unfortunately, there isn't explicit documentation or code comments specifying reserved keywords or naming conventions for CTEs in Superset ^[2].

To work around this issue, you might try renaming the CTE to something other than roles to see if it resolves the parsing error. If the problem persists, it might be worth investigating further into the SQL parser's handling of specific keywords or reporting this as a potential bug in the Superset codebase.

To continue talking to Dosu, mention @dosu.

apache / superset