This query gives the error - Expression 'my_table.dob' is not being grouped
SELECT DATE_TRUNC('day', dob) AS dob,
group_name AS group_name,
location_name AS location_name,
sum(amount) AS net
FROM my_table
WHERE dob >= TO_DATE('2021-09-03', 'YYYY-MM-DD')
AND dob < TO_DATE('2021-09-10', 'YYYY-MM-DD')
GROUP BY DATE_TRUNC('day', dob),
group_name,
location_name
ORDER BY net DESC
LIMIT 10000
But if I give the column a different alias, it works -
SELECT DATE_TRUNC('day', dob) AS d,
group_name AS group_name,
location_name AS location_name,
sum(amount) AS net
FROM my_table
WHERE dob >= TO_DATE('2021-09-03', 'YYYY-MM-DD')
AND dob < TO_DATE('2021-09-10', 'YYYY-MM-DD')
GROUP BY DATE_TRUNC('day', dob),
group_name,
location_name
ORDER BY net DESC
LIMIT 10000
My visualization tool [Superset] uses an alias of the original column name, so dynamically changing the datetime granularity is broken.
@jdbranham Was able to reproduce the behavior, will check internally why this behavior and get back to you
1 Like
Do you have any solution on that? I got completely the same problem. Here’s the trace:
VALIDATION ERROR: Expression 'incidents_2.Date' is not being grouped
SQL Query SELECT DATE_TRUNC('day', "Date") AS "Date",
count("PdId") AS "COUNT(PdId)"
FROM "superset"."incidents_2"
GROUP BY DATE_TRUNC('day', "Date")
ORDER BY "COUNT(PdId)" DESC
LIMIT 10000
startLine 1
startColumn 26
endLine 1
endColumn 31
(org.apache.calcite.runtime.CalciteContextException) From line 1, column 26 to line 1, column 31: Expression 'incidents_2.Date' is not being grouped
sun.reflect.GeneratedConstructorAccessor261.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():789
org.apache.calcite.sql.SqlUtil.newContextException():774
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4818
org.apache.calcite.sql.validate.AggChecker.visit():113
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlIdentifier.accept():341
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild():123
org.apache.calcite.sql.SqlOperator.acceptCall():930
org.apache.calcite.sql.validate.AggChecker.visit():212
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild():123
org.apache.calcite.sql.SqlAsOperator.acceptCall():121
org.apache.calcite.sql.validate.AggChecker.visit():212
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr():231
org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr():240
org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr():4140
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():4113
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3388
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():957
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():938
org.apache.calcite.sql.SqlSelect.validate():242
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():913
com.dremio.exec.planner.sql.SqlValidatorImpl.validate():73
com.dremio.exec.planner.sql.SqlConverter.validate():234
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():190
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():175
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():171
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():71
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():59
com.dremio.exec.work.foreman.AttemptManager.plan():488
com.dremio.exec.work.foreman.AttemptManager.lambda$run$1():387
com.dremio.service.commandpool.ReleasableBoundCommandPool.lambda$getWrappedCommand$3():138
com.dremio.service.commandpool.CommandWrapper.run():62
com.dremio.context.RequestContext.run():95
com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$3():199
com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():180
java.util.concurrent.Executors$RunnableAdapter.call():511
java.util.concurrent.FutureTask.run():266
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():750
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Expression 'incidents_2.Date' is not being grouped
sun.reflect.GeneratedConstructorAccessor260.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():789
org.apache.calcite.sql.SqlUtil.newContextException():774
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4818
org.apache.calcite.sql.validate.AggChecker.visit():113
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlIdentifier.accept():341
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild():123
org.apache.calcite.sql.SqlOperator.acceptCall():930
org.apache.calcite.sql.validate.AggChecker.visit():212
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.util.SqlBasicVisitor$ArgHandlerImpl.visitChild():123
org.apache.calcite.sql.SqlAsOperator.acceptCall():121
org.apache.calcite.sql.validate.AggChecker.visit():212
org.apache.calcite.sql.validate.AggChecker.visit():40
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.validate.AggregatingSelectScope.checkAggregateExpr():231
org.apache.calcite.sql.validate.AggregatingSelectScope.validateExpr():240
org.apache.calcite.sql.validate.SqlValidatorImpl.validateExpr():4140
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():4113
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3388
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():957
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():938
org.apache.calcite.sql.SqlSelect.validate():242
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():913
com.dremio.exec.planner.sql.SqlValidatorImpl.validate():73
com.dremio.exec.planner.sql.SqlConverter.validate():234
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():190
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():175
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():171
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():71
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():59
com.dremio.exec.work.foreman.AttemptManager.plan():488
com.dremio.exec.work.foreman.AttemptManager.lambda$run$1():387
com.dremio.service.commandpool.ReleasableBoundCommandPool.lambda$getWrappedCommand$3():138
com.dremio.service.commandpool.CommandWrapper.run():62
com.dremio.context.RequestContext.run():95
com.dremio.common.concurrent.ContextMigratingExecutorService.lambda$decorate$3():199
com.dremio.common.concurrent.ContextMigratingExecutorService$ComparableRunnable.run():180
java.util.concurrent.Executors$RunnableAdapter.call():511
java.util.concurrent.FutureTask.run():266
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():750
@balaji.ramaswamy do you have any updates on that?
@kzubarev Have checked on this, will update
joao
June 15, 2022, 12:30pm
6
Hi, I’m also having this issue with Superset. I posted a bug report there:
opened 01:55PM - 10 Jun 22 UTC
#bug
I'm using Dremio database and it throws a VALIDATION ERROR on any query that tri… es to group by a time field that has a granularity expression. This might be valid SQL for another database system, but not Dremio. This makes superset useless for Dremio tables since grouping by time is a very common and used feature
#### How to reproduce the bug
1. Create a new table chart and pick a timestamp field like `2022-06-10 12:34:56` and select time grain `day` or `month`
2. Select as dimension the same timestamp field
3. Add any metric such as `COUNT(*)`
4. Superset will generate this query:
```
SELECT DATE_TRUNC('day', "OperationDate") AS "OperationDate",
COUNT(*) AS "count"
FROM "no-partitions-queries"."messages_operations_v"
GROUP BY DATE_TRUNC('day', "OperationDate")
ORDER BY "count" DESC
LIMIT 10000;
```
It is not using the name of the column `OperationDate` in the group by and is rewriting the same expression.
### Expected results
to have this clause:
```
GROUP BY "OperationDate"
```
### Actual results
Query execution error. Details:
VALIDATION ERROR: Expression \'messages_operations_v.OperationDate\' is not being grouped
> Unexpected error
Error: ('HY000', '[HY000] [Dremio][Connector] (1040) Dremio failed to execute the query: SELECT DATE_TRUNC(\'day\', "OperationDate") AS "OperationDate",\n COUNT(*) AS "count"\nFROM "no-partitions-queries"."messages_operations_v"\nGROUP BY DATE_TRUNC(\'day\', "OperationDate")\nORDER BY "count" DESC\nLIMIT 10000\n[30038]Query execution error. Details:[ \nVALIDATION ERROR: Expression \'messages_operations_v.OperationDate\' is not being grouped\n\nSQL Query SELECT DATE_...[see log] (1040) (SQLExecDirectW)')
#### Screenshots
### Environment
- browser type and version: Chrome v102.0.5005.61
- superset version: It show Superset 0.0.0dev -- I'm using the latest docker image
- python version: 3.8.12
- node.js version: none
- any feature flags active: no
### Checklist
Make sure to follow these steps before submitting your issue - thank you!
- [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
Add any other context about the problem here.
But they pointed me to this thread. Do you have any news on it?
@joao The original bug is not fixed, can you alias to a different name and group by that?
joao
June 24, 2022, 12:57pm
8
Hi @balaji.ramaswamy