Unable to group by DATE_TRUNC

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.
I’ll raise an issue with Superset, but I think a better fix is to enable a column alias with same name as the original value.

@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