SQL with inline comment starting with `--` fails in dashboards `view query` and sqllab about superset HOT 3 CLOSED

@PedroMartinSteenstrup Hello there! I'm here to help you with any bugs, questions, or contributions you have in mind. Let's squash those bugs together! 😊

The issue you're encountering with SQL queries containing inline comments has been addressed in several pull requests, including #27725, #28363, #28567, and #29248. These changes ensure that comments are not stripped when executing SQL statements, which should resolve the errors you're seeing in both the dashboards' "view query" and SQL Lab.

To fix the issue, ensure you are using the latest version of Superset where these fixes have been merged. Specifically, the changes in pull request #29248 should address the problem you're facing with inline comments starting with -- ^[1].

If you are already on the latest version and still experiencing issues, double-check your sql_mutator function implementation and ensure it aligns with the updated handling of SQL comments. Here is a simplified example of how you might define the sql_mutator function:

def sql_mutator(sql, security_manager, database):
    from superset.utils.core import get_username
    user_name = get_username()
    q_suffix = f'Superset query context \'{{"user": "{user_name}"}}\''
    return f"{sql} -- {q_suffix}"

Make sure your Superset configuration includes this function and that MUTATE_AFTER_SPLIT is set according to your needs:

SQL_QUERY_MUTATOR = sql_mutator
MUTATE_AFTER_SPLIT = False

This should help resolve the errors related to inline comments in your SQL queries.

To continue talking to Dosu, mention @dosu.

from superset.

The issue with SQL Lab in Apache Superset when a SQL statement containing inline comments ends with a semicolon can be resolved by ensuring that the SQL statement is properly parsed and executed. The ParsedQuery class's get_statements method is responsible for splitting SQL queries into individual statements. It ensures that each statement is cleaned and separated properly before being added to the list of statements.

Here is the specific logic used in the get_statements method:

def get_statements(self) -> list[str]:
    """Returns a list of SQL statements as strings, stripped"""
    statements = []
    for statement in self._parsed:
        if statement:
            sql = str(statement).strip(" \n;\t")
            if sql:
                statements.append(sql)
    return statements

This method strips any leading or trailing whitespace, newline characters, semicolons, and tabs from each statement, ensuring they are correctly parsed ^[1].

Additionally, there are known issues with the sqlparse library used by Superset for parsing SQL queries, particularly with handling inline comments and semicolons. A workaround has been implemented in Superset's code to address these issues ^[1].

To further troubleshoot and resolve the issue, you can:

1. Verify the ParsedQuery Class: Ensure that the ParsedQuery class and its get_statements method correctly parse the SQL statements, especially handling inline comments and semicolons.

2. Check for Known Issues: Be aware of the known issues with the sqlparse library and ensure that any necessary workarounds are applied.

3. Update Superset: Make sure you are using the latest version of Superset where fixes for handling comments and semicolons have been merged.

By following these steps, you should be able to resolve the issue with SQL Lab when a SQL statement containing inline comments ends with a semicolon.

To continue talking to Dosu, mention @dosu.

from superset.

good bot @dosu, setting MUTATE_AFTER_SPLIT = True helped with the dashboards at least.

Sadly, still a problem in sqllab, and I can now even more precisely point it out:

That will work:

select * from reporting.dim_date limit 100
-- select * from information_schema.functions limit 10;

That will not work:

select * from reporting.dim_date limit 100;
-- select * from information_schema.functions limit 10;

The difference? Statement ending with semicolon.

from superset.