ruifpedro opened a new issue, #27034:
URL: https://github.com/apache/superset/issues/27034
### Bug description
Heya Superset team!
I ran into a bug that prevents me from using nested columns in charts and
especially dashboard filters.
This happens with datasets that use a Trino database with the `Enable row
expansion in schemas` enabled (introduced in Superset 3.1.0), which, as the
name states, expands nested columns and effectively adds them to the dataset
columns list.
This happens due to a possible problem with Superset's query builder, where
a given nested column, let's say `metadata.field_a` is parsed into the query as
`"metadata.field_a"`, while Trino only accepts either `"metadata"."field_a"` or
`metadata.field_a`.
This causes the column selection in chart exploration to create invalid
queries (although it can be mitigated by always using Custom SQL).
Additionally, this completely breaks the usage of dashboard filters, where the
custom SQL workaround is not possible.
I provide evidence below and am available to provide more details if needed.
Looking forward towards your response.
### How to reproduce the bug
In chart exploration:
1. Create a new chart
2. Select a Trino based dataset with nested columns
3. Add a nested column to dimensions
4. Press create chart
5. The chart now should display `Cannot load filter Error: line 1:8: Column
'<nested_column_name>' cannot be resolved`
In dashboard filters:
1. Create a dashboard or open an existing one
2. Add a filter
2. Select a Trino based dataset with nested columns
3. Select a nested column as the column
4. Save
6. The filter now should display `Cannot load filter Error: line 1:8: Column
'<nested_column_name>' cannot be resolved`
### Screenshots/recordings
Stacktrace
```
superset Query SELECT "metadata.uuid" AS "metadata.uuid",
superset COUNT(*) AS count
superset FROM raw_runtime_broker.intenthit
superset GROUP BY "metadata.uuid"
superset ORDER BY count DESC
superset LIMIT 1000 on schema raw_runtime_broker failed
superset Traceback (most recent call last):
superset File "/app/superset/connectors/sqla/models.py", line 1795, in
query
superset df = self.database.get_df(sql, self.schema,
mutator=assign_column_label)
superset File "/app/superset/models/core.py", line 612, in get_df
superset self.db_engine_spec.execute(cursor, sqls[-1])
superset File "/app/superset/db_engine_specs/base.py", line 1596, in
execute
superset raise cls.get_dbapi_mapped_exception(ex) from ex
superset File "/app/superset/db_engine_specs/base.py", line 1594, in
execute
superset cursor.execute(query)
superset File "/usr/local/lib/python3.9/site-packages/trino/dbapi.py",
line 592, in execute
superset self._iterator = iter(self._query.execute())
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 810, in execute
superset self._result.rows += self.fetch()
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 830, in fetch
superset status = self._request.process(response)
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 609, in process
superset raise self._process_error(response["error"], response.get("id"))
superset trino.exceptions.TrinoUserError: TrinoUserError(type=USER_ERROR,
name=COLUMN_NOT_FOUND, message="line 1:8: Column 'metadata.uuid' cannot be
resolved", query_id=20240207_121856_11010_858tg)
superset 2024-02-07
12:18:56,581:WARNING:superset.connectors.sqla.models:Query SELECT
"metadata.uuid" AS "metadata.uuid",
superset COUNT(*) AS count
superset FROM raw_runtime_broker.intenthit
superset GROUP BY "metadata.uuid"
superset ORDER BY count DESC
superset LIMIT 1000 on schema raw_runtime_broker failed
superset Traceback (most recent call last):
superset File "/app/superset/connectors/sqla/models.py", line 1795, in
query
superset df = self.database.get_df(sql, self.schema,
mutator=assign_column_label)
superset File "/app/superset/models/core.py", line 612, in get_df
superset self.db_engine_spec.execute(cursor, sqls[-1])
superset File "/app/superset/db_engine_specs/base.py", line 1596, in
execute
superset raise cls.get_dbapi_mapped_exception(ex) from ex
superset File "/app/superset/db_engine_specs/base.py", line 1594, in
execute
superset cursor.execute(query)
superset File "/usr/local/lib/python3.9/site-packages/trino/dbapi.py",
line 592, in execute
superset self._iterator = iter(self._query.execute())
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 810, in execute
superset self._result.rows += self.fetch()
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 830, in fetch
superset status = self._request.process(response)
superset File "/usr/local/lib/python3.9/site-packages/trino/client.py",
line 609, in process
superset raise self._process_error(response["error"], response.get("id"))
superset trino.exceptions.TrinoUserError: TrinoUserError(type=USER_ERROR,
name=COLUMN_NOT_FOUND, message="line 1:8: Column 'metadata.uuid' cannot be
resolved", query_id=20240207_121856_11010_858tg)
superset 127.0.0.6 - - [07/Feb/2024:12:18:56 +0000] "POST /api/v1/chart/data
HTTP/1.1" 400 73
"https://superset.factory-staging.scottytechnologies.com/explore/
```
## Screenshots
### The chart evidence and error
### The dashboard filter evidence and error
### SQL Lab evidence on how SQL looks and should look like
This is what Superset currently generates in charts and filters
Evidence on what works and what doesn't work with Trino
### Superset version
3.1.0
### Python version
3.9
### Node version
Not applicable
### Browser
Firefox
### Additional context
I have the following feature flags enabled:
```
FEATURE_FLAGS = {
"ALERT_REPORTS": True,
"DASHBOARD_RBAC": True,
"ENABLE_TEMPLATE_PROCESSING": True,
}
```
There are no customization made on Superset, but the container image is
packaged by me in order to include necessary dependencies like the Trino
connector (python lib: trino==0.327.0).
The Superset is deployed in Kubernetes using the official Superset helm
chart.
About the data source I am querying, I've already provided details on it on
the post.
### 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscr...@superset.apache.org
For additional commands, e-mail: notifications-h...@superset.apache.org
