kingo55 opened a new issue, #23902:
URL: https://github.com/apache/superset/issues/23902
In Superset version 2.1.0, the SQL generated for querying time series data
from a BigQuery data warehouse is invalid. The GROUP BY clause incorrectly
includes a SQL function rather than the alias of the function's result, leading
to an error in BigQuery. This issue does not occur in Superset version 2.0.1.
#### How to reproduce the bug
1. Upgrade Apache Superset to version 2.1.0 from 2.0.1
2. Connect to a BigQuery data warehouse.
3. Create a time series chart using a SQL function in the GROUP BY clause.
4. See the error in the BigQuery SQL execution due to incorrect SQL syntax.
### Expected results
The SQL generated by Superset should be valid and executable in BigQuery. In
the GROUP BY clause, the alias of the SQL function's result should be used,
rather than the function itself.
E.g. `GROUP BY`:
```
SELECT TIMESTAMP_TRUNC(`derived_tstamp`, DAY) AS `__timestamp`,
COUNT(*) AS `count`
FROM `dbt`.`boq_activity`
WHERE `derived_tstamp` >= CAST('2023-04-02T00:00:00.000000' AS TIMESTAMP)
AND `derived_tstamp` < CAST('2023-05-02T00:00:00.000000' AS TIMESTAMP)
AND `page_title` = '404 Page not found'
GROUP BY `__timestamp`
ORDER BY count DESC
LIMIT 10000;
```
### Actual results
The SQL generated by Superset 2.1.0 uses the SQL function directly in the
GROUP BY clause, which leads to an error in BigQuery execution.
E.g. See `GROUP BY`:
```
SELECT TIMESTAMP_TRUNC(`derived_tstamp`, DAY) AS `__timestamp`,
COUNT(*) AS `count`
FROM `dbt`.`boq_activity`
WHERE `derived_tstamp` >= CAST('2023-04-02T00:00:00.000000' AS TIMESTAMP)
AND `derived_tstamp` < CAST('2023-05-02T00:00:00.000000' AS TIMESTAMP)
AND `page_title` = '404 Page not found'
GROUP BY TIMESTAMP_TRUNC(`derived_tstamp`, DAY)
ORDER BY count DESC
LIMIT 10000;
```
#### Screenshots
<img width="1014" alt="Screenshot 2023-05-02 at 21 02 50"
src="https://user-images.githubusercontent.com/2361388/235650316-37a754e7-9893-4029-bbd6-e7667854a686.png";>
### Environment
(please complete the following information):
- browser type and version: 112.0.5615.137
- superset version: 2.1.0 via the `amancevice/superset` docker image
- python version: 3.8
- node.js version: ?
- any feature flags active: `ENABLE_TEMPLATE_PROCESSING`,
`DASHBOARD_NATIVE_FILTERS`, `THUMBNAILS`
### Checklist
- [X] I have checked the superset logs for python stacktraces and included
it here as text if there are any.
- [X] I have reproduced the issue with at least the latest released version
of superset.
- [X] I have checked the issue tracker for the same issue and I haven't
found one similar.
### Additional context
This issue appears to be a regression from Superset version 2.0.1, which
generated valid SQL for the same use case.
--
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]
![https://user-images.githubusercontent.com/2361388/235650316-37a754e7-9893-4029-bbd6-e7667854a686.png"](https://user-images.githubusercontent.com/2361388/235650316-37a754e7-9893-4029-bbd6-e7667854a686.png"){kind=link}