Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
3 tasks done
fhyy opened this issue May 12, 2024 · 1 comment · May be fixed by #28444
Open
3 tasks done

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

fhyy opened this issue May 12, 2024 · 1 comment · May be fixed by #28444

Comments

@fhyy
Copy link

fhyy commented May 12, 2024

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

  1. Connect to a Drill database
  2. Create a dataset of the Drill database
  3. Create a chart from that dataset
  4. Select visualization type Table with query mode Aggregate
  5. Add two columns in the dimensions
  6. Aggregate the data of one of the columns (e.g. length(column_a))
  7. 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.
@dosubot DosuBot
Copy link

dosubot bot commented May 12, 2024 •
edited

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 fhyy linked a pull request May 12, 2024 that will close this issue
Open
9 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
1 participant
@fhyy