Bug description
The bug is reproduced for datasets on PostgreSQL with columns containing capital letters.
Steps:
1. Create RLS
Identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Identifiers created with double quotes retain upper case letters.
Analyzing the log we've found that even we are using double quotes for column name in RLS, query that are send to DB from SQL lab doesn't contain double quotes.
This bug is not reproduced on chart
Screenshots/recordings
No response
Superset version
4.1.1
Python version
3.9
Node version
16
Browser
Chrome
Additional context
flag RLS_IN_SQLLAB is applied
Worker container log [2025-03-10 16:44:05,738: INFO/ForkPoolWorker-7] Task sql_lab.get_sql_results[28448e7f-351f-45c4-9624-441718fa923a] succeeded in 0.19986815191805363s: {'query_id': 12018, 'status': 'failed', 'error': 'postgresql error: column flights.airline does not exist
LINE 3: FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE li...
^', 'errors': [{'message': 'postgresql error: column flights.airline does not exist
LINE 3: FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE li...
^
', 'error_type': 'GENERIC_DB_ENGINE_ERROR', 'level': 'error', 'extra': {...}}]}
DB container log: 2025-03-10 16:44:05.720 [27282] ERROR: column flights.airline does not exist at character 54 2025-03-10 16:44:05.720 [27282] STATEMENT: SELECT * FROM (SELECT * FROM public.flights WHERE (flights.AIRLINE like 'A%')) AS flights LIMIT 101
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.
Comment From: dosubot[bot]
The issue you're encountering seems to be related to how Superset's SQL Lab handles column name casing when Row Level Security (RLS) is applied. In PostgreSQL, unquoted identifiers are automatically converted to lowercase, which can lead to issues if your column names contain uppercase letters and are not properly quoted in the SQL query.
In your case, it appears that even though you are using double quotes for column names in the RLS configuration, the SQL Lab query sent to the database does not include these quotes, resulting in a casing mismatch and the error you're seeing.
This behavior is consistent with the known handling of SQL query generation in Superset, where column names might not be quoted correctly, especially when RLS is involved. The RLS_IN_SQLLAB flag attempts to apply RLS rules to SQL Lab queries, but this can lead to issues if the query parsing and manipulation do not preserve the necessary quoting for column names [1].
To address this issue, you might consider:
- Ensuring that all column names in your queries are explicitly quoted to preserve their casing.
- Reviewing the RLS configuration to ensure that it aligns with how SQL Lab processes queries.
- Checking if there are any updates or patches in newer versions of Superset that address this specific issue.
If the problem persists, you may need to explore alternative ways to handle RLS or adjust your dataset schema to avoid casing issues.
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
Comment From: rusackas
Maybe we can automatically double quote these using SQLGlot? I'm not sure, but at least there's a workaround of doing so manually for now. CC @betodealmeida
Comment From: rusackas
Can you check on master and see if the SQLGlot migration resolved this issue?