Sql Lab: Getting incorrect error line number in Superset while executing queries

[SamraHanifCareem]

Bug description

In the SQL lab, when executing statements, comments are stripped in sql_lab.py before sending the query to the db_engine. However, this causes discrepancies in error referencing. For example, if there's an error in the SQL query (e.g., invalid SQL), the incorrect line number is provided. This issue also affects Validators. Any solutions or suggestions to resolve this?

In sql_lab.py: parsed_query = ParsedQuery(rendered_query, strip_comments=True)

In superset:

1. select *
2. 
3. -- comment 1
4. 
5. --comment 2
6. 
7. from abc.xyz
8. limits 20

Now in the above query, superset strips the comments and send it to db_engine and the engine will get:

1. select *
2. 
3. 
4. 
5. from abc.xyz
6. limits 20

The db_engine will return error at: line 6 The actual error in superset will be at: line 8 which is incorrect because we are stripping the comments and not sending to the engine

How to reproduce the bug

• Go to Sql Lab
• Select any database
• Write invalid query
• Execute the query by clicking "Run"

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

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.
• [ ] 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]]

To address the issue of incorrect error line numbers in Superset's SQL Lab due to comments being stripped before query execution, consider the following approach:

1. Modify Error Handling: Enhance the handle_query_error function to adjust error line numbers based on the modifications made to the SQL query, such as comment stripping. This involves parsing and adjusting the line numbers in error messages to reflect the original query's line numbers.

2. Preserve Comments: Adjust the SQL execution process to include comments in the queries sent to the database engine. This ensures that the error line numbers reported by the database engine match the line numbers in the SQL Lab.

3. Line Number Mapping: If pre-processing (like removing comments) is necessary, create a mapping of line numbers from the original query to the modified query. Use this mapping to correct the line numbers in any error messages.

4. Enhance ParsedQuery Usage: Ensure that the ParsedQuery class, which handles SQL parsing and modification, accurately manages comments and line numbers. This may involve updating the class to better preserve comments or to adjust line numbers during query transformations.

5. Implement Custom Error Reporting: If automatic line number adjustment proves challenging, consider a custom error reporting mechanism within Superset that allows users to report discrepancies. This can help in manually mapping reported line numbers to the correct lines in SQL Lab queries.

6. Educate Users: Provide guidelines for writing SQL queries in SQL Lab to minimize discrepancies caused by comments and query modifications.

7. Contribute Solutions: If you devise a solution, contributing your improvements back to the Apache Superset project can help others facing similar issues.

This approach requires a good understanding of Superset's codebase and the behavior of the database engine regarding SQL execution and error reporting. For implementation details, you might need to refer to the superset/sql_lab.py file in the Superset repository.

Details

Sources

• superset/sql_lab.py
• superset/sql_parse.py
• superset/models/helpers.py
• superset-frontend/src/SqlLab/components/HighlightedSql/index.tsx
• superset/db_engine_specs/base.py
• superset-frontend/src/SqlLab/components/AceEditorWrapper/useAnnotations.ts

About Dosu This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

To continue the conversation, mention @dosu.

[rusackas]

Oof... that's annoying. Assigning to @betodealmeida / @john-bodley who are (I think?) the most up to date on SQL parser migrations (i.e. SQLGlot)