Drill uses aliases in group by statements generated by SQLA resulting in no data when aggregating columns #28443

fhyy · 2024-05-12T17:22:38Z

Bug description

GROUP BY statements usually do not support the use of aliases, but it is supported in Drill since 2019. This added support results in the alias being picked in GROUP BY statements instead of the source column if they have the same name. This is problematic if the alias refers to an aggregation of the source column.

These types of statements are generated when creating a chart and aggregating the values of an axis:

SELECT length(n_name) AS n_name
FROM
  (select * from cp.`tpch/nation.parquet`)
GROUP BY length(n_name)
LIMIT 10;

Testing this statement in the SQL Lab against a Drill connection results in the following error:

Apache Drill Error

drill error: Final Drill query state is FAILED. From line 1, column 15 to line 1, column 20: Expression 'EXPR$0.**' is not being grouped: Expression 'EXPR$0.**' is not being grouped

This may be triggered by:
Issue 1002 - The database returned an unexpected error.
See less

and an exception in the logs:

sqlalchemy_drill.drilldbapi.api_exceptions.ProgrammingError: 'has no row data, have you executed a query that returns data? HTTP ERROR: None'

The problem is the statement GROUP BY length(n_name). The aggregation length(n_name) refers to the source column n_name, but the Drill engine uses the alias n_name instead. The resulting executed statement is thus GROUP BY length(length(n_name)), which returns no data.

Suggestion

There was a similar issue with ORDER BY statements which was fixed by appending "__" to the name of such aliases, ensuring that the source column is used. This was done in PR 13739 (#13739)

The same fix can also be applied for GROUP BY statements, and the problematic statement above would instead look like this:

SELECT length(n_name) AS n_name__
FROM
  (select * from cp.`tpch/nation.parquet`)
GROUP BY length(n_name)
LIMIT 10;

How to reproduce the bug

Connect to a Drill database
Create a dataset of the Drill database
Create a chart from that dataset
Select visualization type Table with query mode Aggregate
Add two columns in the dimensions
Aggregate the data of one of the columns (e.g. length(column_a))
Press UPDATE CHART
An error message should appear in the results view

Screenshots/recordings

No response

Superset version

4.0.0

Python version

Not applicable

Node version

Not applicable

Browser

Firefox

Additional context

From the logs:

superset  | 2024-05-12 16:33:40,535:DEBUG:urllib3.connectionpool:http://localhost:8047 "POST /query.json HTTP/1.1" 200 228
superset  | 2024-05-12 16:33:40,535:DEBUG:drilldbapi:{'queryType': 'SQL', 'query': 'SELECT\n  n_regionkey AS n_regionkey,\n  length(n_name) AS n_name\nFROM (\n  SELECT\n    *\n  FROM cp.`tpch/nation.parquet`\n) AS virtual_table\nGROUP BY\n  n_regionkey,\n  length(n_name)\nLIMIT 10', 'userName': None}
superset  | 2024-05-12 16:33:40,536:DEBUG:drilldbapi:sends an HTTP POST with payload
superset  | 2024-05-12 16:33:40,536:DEBUG:drilldbapi:{'queryType': 'SQL', 'query': 'SELECT\n  n_regionkey AS n_regionkey,\n  length(n_name) AS n_name\nFROM (\n  SELECT\n    *\n  FROM cp.`tpch/nation.parquet`\n) AS virtual_table\nGROUP BY\n  n_regionkey,\n  length(n_name)\nLIMIT 10', 'userName': None}
[...]
superset  | 2024-05-12 16:33:40,759:DEBUG:drilldbapi:ijson parsed , start_map, None
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:ijson parsed , map_key, queryId
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:found and will now parse an occurrence of queryId
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:ijson parsed , map_key, exception
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:found and will now parse an occurrence of exception
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:ijson parsed , map_key, errorMessage
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:found and will now parse an occurrence of errorMessage
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:ijson parsed , map_key, stackTrace
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:found and will now parse an occurrence of stackTrace
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:ijson parsed , map_key, queryState
superset  | 2024-05-12 16:33:40,760:DEBUG:drilldbapi:found and will now parse an occurrence of queryState
superset  | 2024-05-12 16:33:40,761:DEBUG:drilldbapi:ijson parsed , end_map, None
superset  | 2024-05-12 16:33:40,761:INFO:drilldbapi:reached the end of the result stream, parsing complete.
superset  | 2024-05-12 16:33:40,761:INFO:drilldbapi:received final query state FAILED.
superset  | 2024-05-12 16:33:40,761:WARNING:drilldbapi:org.apache.calcite.runtime.CalciteContextException
superset  | 2024-05-12 16:33:40,761:WARNING:drilldbapi:From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped
superset  | 2024-05-12 16:33:40,761:WARNING:drilldbapi:["org.apache.calcite.runtime.CalciteContextException: From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped", '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)', '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)', '\tat jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)', '\tat .......(:0)', '\tat org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)', '\tat org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)', '\tat org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:138)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)', '\tat org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:125)', '\tat org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:960)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:243)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)', '\tat org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:125)', '\tat org.apache.calcite.sql.SqlAsOperator.acceptCall(SqlAsOperator.java:121)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:243)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)', '\tat org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:233)', '\tat org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:242)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4676)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4637)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)', '\tat org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)', '\tat org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)', '\tat org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)', '\tat org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:212)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:662)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:298)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:179)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:129)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94)', '\tat org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:594)', '\tat org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:274)', '\tat .......(:0)', "Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression 'virtual_table.**' is not being grouped", '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)', '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)', '\tat jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)', '\tat .......(:0)', '\tat org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)', '\tat org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)', '\t... 39 more']
superset  | 2024-05-12 16:33:40,766:INFO:drilldbapi:FAILED
superset  | 2024-05-12 16:33:40,771:ERROR:drilldbapi:From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped
superset  | 2024-05-12 16:33:40,771:INFO:drilldbapi:received Drill query ID 19bf109d-3e50-1d4c-5b29-247d9ef9d42a.
superset  | 2024-05-12 16:33:40,776:DEBUG:drilldbapi:ijson parsed , start_map, None
superset  | 2024-05-12 16:33:40,776:DEBUG:drilldbapi:ijson parsed , map_key, queryId
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:found and will now parse an occurrence of queryId
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:ijson parsed , map_key, exception
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:found and will now parse an occurrence of exception
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:ijson parsed , map_key, errorMessage
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:found and will now parse an occurrence of errorMessage
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:ijson parsed , map_key, stackTrace
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:found and will now parse an occurrence of stackTrace
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:ijson parsed , map_key, queryState
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:found and will now parse an occurrence of queryState
superset  | 2024-05-12 16:33:40,777:DEBUG:drilldbapi:ijson parsed , end_map, None
superset  | 2024-05-12 16:33:40,777:INFO:drilldbapi:reached the end of the result stream, parsing complete.
superset  | 2024-05-12 16:33:40,778:INFO:drilldbapi:received final query state FAILED.
superset  | 2024-05-12 16:33:40,778:WARNING:drilldbapi:org.apache.calcite.runtime.CalciteContextException
superset  | 2024-05-12 16:33:40,778:WARNING:drilldbapi:From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped
superset  | 2024-05-12 16:33:40,778:WARNING:drilldbapi:["org.apache.calcite.runtime.CalciteContextException: From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped", '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)', '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)', '\tat jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)', '\tat .......(:0)', '\tat org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)', '\tat org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)', '\tat org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5464)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:138)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlIdentifier.accept(SqlIdentifier.java:324)', '\tat org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:125)', '\tat org.apache.calcite.sql.SqlOperator.acceptCall(SqlOperator.java:960)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:243)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)', '\tat org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild(SqlBasicVisitor.java:125)', '\tat org.apache.calcite.sql.SqlAsOperator.acceptCall(SqlAsOperator.java:121)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:243)', '\tat org.apache.calcite.sql.validate.AggChecker.visit(AggChecker.java:45)', '\tat org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)', '\tat org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr(AggregatingSelectScope.java:233)', '\tat org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr(AggregatingSelectScope.java:242)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr(SqlValidatorImpl.java:4676)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4637)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3780)', '\tat org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:61)', '\tat org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1135)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:1106)', '\tat org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:282)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1081)', '\tat org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:787)', '\tat org.apache.drill.exec.planner.sql.conversion.SqlConverter.validate(SqlConverter.java:212)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:662)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:198)', '\tat org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:172)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:298)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:179)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.convertPlan(DrillSqlWorker.java:129)', '\tat org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:94)', '\tat org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:594)', '\tat org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:274)', '\tat .......(:0)', "Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expression 'virtual_table.**' is not being grouped", '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)', '\tat jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)', '\tat jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)', '\tat .......(:0)', '\tat org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)', '\tat org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:599)', '\t... 39 more']
superset  | 2024-05-12 16:33:40,778:INFO:drilldbapi:FAILED
superset  | 2024-05-12 16:33:40,778:ERROR:drilldbapi:From line 3, column 10 to line 3, column 15: Expression 'virtual_table.**' is not being grouped: Expression 'virtual_table.**' is not being grouped
superset  | 2024-05-12 16:33:40,778:INFO:drilldbapi:received Drill query ID 19bf109c-9d4f-9ddb-3d2e-0da441d1083a.
superset  | Query SELECT
superset  |   n_regionkey AS n_regionkey,
superset  |   length(n_name) AS n_name
superset  | FROM (
superset  |   SELECT
superset  |     *
superset  |   FROM cp.`tpch/nation.parquet`
superset  | ) AS virtual_table
superset  | GROUP BY
superset  |   n_regionkey,
superset  |   length(n_name)
superset  | LIMIT 10 on schema None failed
superset  | Traceback (most recent call last):
superset  |   File "/app/superset/connectors/sqla/models.py", line 1746, in query
superset  |     df = self.database.get_df(sql, self.schema, mutator=assign_column_label)
superset  |   File "/app/superset/models/core.py", line 610, in get_df
superset  |     data = self.db_engine_spec.fetch_data(cursor)
superset  |   File "/app/superset/db_engine_specs/drill.py", line 138, in fetch_data
superset  |     return super().fetch_data(cursor, limit)
superset  |   File "/app/superset/db_engine_specs/base.py", line 787, in fetch_data
superset  |     raise cls.get_dbapi_mapped_exception(ex) from ex
superset  |   File "/app/superset/db_engine_specs/base.py", line 762, in fetch_data
superset  |     data = cursor.fetchall()
superset  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 73, in func_wrapper
superset  |     return func(self, *args, **kwargs)
superset  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 299, in fetchall
superset  |     return self.fetchmany(-1)
superset  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 73, in func_wrapper
superset  |     return func(self, *args, **kwargs)
superset  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy_drill/drilldbapi/_drilldbapi.py", line 262, in fetchmany
superset  |     raise ProgrammingError(
superset  | sqlalchemy_drill.drilldbapi.api_exceptions.ProgrammingError: 'has no row data, have you executed a query that returns data? HTTP ERROR: None'
superset  | 172.20.0.1 - - [12/May/2024:16:33:40 +0000] "POST /api/v1/chart/data?form_data=%7B%22slice_id%22%3A2%7D HTTP/1.1" 400 83 "http://0.0.0.0:8088/explore/?form_data_key=svhBqH5hYJiaPUZsZ6Dfudu5Qz1X6cIx3hchWNDUmvLUZFpgTF6JPKkfLAp0KKkP&dashboard_page_id=-TdaiwDp7Q&slice_id=2" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:124.0) Gecko/20100101 Firefox/124.0"
superset  | 172.20.0.1 - - [12/May/2024:16:33:40 +0000] "POST /api/v1/chart/data?form_data=%7B%22slice_id%22%3A2%7D HTTP/1.1" 400 83 "http://0.0.0.0:8088/explore/?form_data_key=****&dashboard_page_id=-TdaiwDp7Q&slice_id=2" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:124.0) Gecko/20100101 Firefox/124.0"
superset  | 172.20.0.1 - - [12/May/2024:16:33:40 +0000] "GET /api/v1/chart/2 HTTP/1.1" 200 1424 "http://0.0.0.0:8088/explore/?form_data_key=****&dashboard_page_id=-TdaiwDp7Q&slice_id=2" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:124.0) Gecko/20100101 Firefox/124.0"

Checklist

I have searched Superset docs and Slack and didn't find a solution to my problem.
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.

The text was updated successfully, but these errors were encountered:

dosubot · 2024-05-12T17:23:22Z

To address the issue with Apache Drill and GROUP BY statements in Apache Superset, it's essential to understand that Drill might not support using aliases in GROUP BY that are defined in the SELECT part of the query for aggregation purposes directly. Instead, you should directly use the aggregation expression in the GROUP BY clause or reference the original column being aggregated.

For instance, if your query is:

SELECT length(n_name) AS n_name
FROM
  (select * from cp.`tpch/nation.parquet`)
GROUP BY length(n_name)
LIMIT 10;

Modify it to avoid using the alias in the GROUP BY clause:

SELECT length(n_name) AS n_name
FROM
  (select * from cp.`tpch/nation.parquet`)
GROUP BY n_name
LIMIT 10;

This adjustment ensures compatibility with Apache Drill's SQL parsing and execution behavior, working around limitations related to the use of aliases in GROUP BY clauses for aggregated expressions.

Regarding appending "__" to aliases, this solution was specific to ORDER BY statements to ensure compatibility with the database engine's requirements. While a similar approach could theoretically apply to GROUP BY statements, it's crucial to test any such changes thoroughly to ensure they do not introduce compatibility issues with other supported database engines in Superset.

If you encounter further issues or have additional questions, feel free to ask for more assistance.

Details

Sources

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.

fhyy · 2024-07-03T08:10:41Z

I have implemented a fix based on the suggestion. This can be found in PR #29455 .

I'm currently maintaining an image with this fix for my company, keeping it up to date with your changes. I would greatly appreciate it if this issue can be resolved so that I can use the official versions.

fhyy mentioned this issue May 12, 2024

fix: added parameter to optionally rename aliases of aggregated columns used in GROUP BY statments #28444

Closed

9 tasks

mbrannstrom mentioned this issue Jun 3, 2024

Time granularity generates invalid query for Dremio #20349

Open

3 tasks

fhyy linked a pull request Jul 2, 2024 that will close this issue

fix: added parameter to rename aliases of aggregated columns used in GROUP BY statments #29455

Open

9 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Drill uses aliases in group by statements generated by SQLA resulting in no data when aggregating columns #28443

Drill uses aliases in group by statements generated by SQLA resulting in no data when aggregating columns #28443

fhyy commented May 12, 2024

dosubot bot commented May 12, 2024 •

edited

Loading

Details

fhyy commented Jul 3, 2024

Drill uses aliases in group by statements generated by SQLA resulting in no data when aggregating columns #28443

Drill uses aliases in group by statements generated by SQLA resulting in no data when aggregating columns #28443

Comments

fhyy commented May 12, 2024

Bug description

How to reproduce the bug

Screenshots/recordings

Superset version

Python version

Node version

Browser

Additional context

Checklist

dosubot bot commented May 12, 2024 • edited Loading

Details

fhyy commented Jul 3, 2024

dosubot bot commented May 12, 2024 •

edited

Loading