dosubot[bot] commented on issue #29616:
URL: https://github.com/apache/superset/issues/29616#issuecomment-2232983307
<!-- Greeting -->
Hey @ThChatz! I'm here to help you with any bugs, questions, or
contributions you have for the repository. Let's tackle this issue together!
<!-- Answer -->
To address the issue where selecting a subquery calculated column as a
dimension breaks charts in Superset version 4.0.2, you need to handle the
`sql_mode=only_full_group_by` setting. This setting enforces strict SQL
standards, which can cause issues when using subqueries in SELECT statements
without proper GROUP BY clauses.
Here are the steps to fix this:
1. **Disable `ONLY_FULL_GROUP_BY` Mode**: Modify your MySQL configuration
file (`my.cnf` or `my.ini`) to remove `ONLY_FULL_GROUP_BY` from the `sql_mode`
setting, then restart your MySQL server.
```ini
[mysqld]
sql_mode =
"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
```
2. **Modify the Query in Superset**: Ensure that your subquery includes all
necessary columns in the `GROUP BY` clause. This can be done by modifying the
query generation logic in Superset to include all selected columns in the
`GROUP BY` clause.
Example modification in the query generation logic:
```py
if need_groupby:
# dedup columns while preserving order
columns = groupby or columns
for selected in columns:
if isinstance(selected, str):
# if groupby field/expr equals granularity field/expr
if selected == granularity:
table_col = columns_by_name[selected]
outer = table_col.get_timestamp_expression(
time_grain=time_grain,
label=selected,
template_processor=template_processor,
)
# if groupby field equals a selected column
elif selected in columns_by_name:
outer = self.convert_tbl_column_to_sqla_col(
columns_by_name[selected],
template_processor=template_processor,
)
else:
selected = validate_adhoc_subquery(
selected,
self.database_id,
self.schema,
)
outer = literal_column(f"({selected})")
outer = self.make_sqla_column_compatible(outer, selected)
else:
outer = self.adhoc_column_to_sqla(
col=selected, template_processor=template_processor
)
groupby_all_columns[outer.name] = outer
if (
is_timeseries and not series_column_labels
) or outer.name in series_column_labels:
groupby_series_columns[outer.name] = outer
select_exprs.append(outer)
```
This code ensures that all selected columns are included in the `GROUP BY`
clause, which should prevent issues related to the `ONLY_FULL_GROUP_BY` setting
<sup>[[1]](https://github.com/apache/superset/blob/master/superset/models/helpers.py)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
