Presto: java.lang.IllegalArgumentException: Final aggregation with default value not separated from partial aggregation by remote hash exchange

Created on 19 Mar 2020 · 21Comments · Source: prestosql/presto

java.lang.IllegalArgumentException: Final aggregation with default value not separated from partial aggregation by remote hash exchange
    at com.google.common.base.Preconditions.checkArgument(Preconditions.java:141)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitAggregation(ValidateAggregationsWithDefaultValues.java:119)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitAggregation(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.AggregationNode.accept(AggregationNode.java:204)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:87)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:39)
    at io.prestosql.sql.planner.plan.ProjectNode.accept(ProjectNode.java:82)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:87)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitTopN(PlanVisitor.java:44)
    at io.prestosql.sql.planner.plan.TopNNode.accept(TopNNode.java:107)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitExchange(ValidateAggregationsWithDefaultValues.java:136)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitExchange(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:243)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitExchange(ValidateAggregationsWithDefaultValues.java:136)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitExchange(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.ExchangeNode.accept(ExchangeNode.java:243)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:87)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitTopN(PlanVisitor.java:44)
    at io.prestosql.sql.planner.plan.TopNNode.accept(TopNNode.java:107)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:87)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitProject(PlanVisitor.java:39)
    at io.prestosql.sql.planner.plan.ProjectNode.accept(ProjectNode.java:82)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.lambda$aggregatedSeenExchanges$0(ValidateAggregationsWithDefaultValues.java:157)
    at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
    at java.util.Collections$2.tryAdvance(Collections.java:4717)
    at java.util.Collections$2.forEachRemaining(Collections.java:4725)
    at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
    at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
    at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
    at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:479)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.aggregatedSeenExchanges(ValidateAggregationsWithDefaultValues.java:158)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:87)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues$Visitor.visitPlan(ValidateAggregationsWithDefaultValues.java:68)
    at io.prestosql.sql.planner.plan.PlanVisitor.visitOutput(PlanVisitor.java:49)
    at io.prestosql.sql.planner.plan.OutputNode.accept(OutputNode.java:82)
    at io.prestosql.sql.planner.sanity.ValidateAggregationsWithDefaultValues.validate(ValidateAggregationsWithDefaultValues.java:65)
    at io.prestosql.sql.planner.sanity.PlanSanityChecker.lambda$validateFinalPlan$0(PlanSanityChecker.java:65)
    at com.google.common.collect.ImmutableList.forEach(ImmutableList.java:407)
    at io.prestosql.sql.planner.sanity.PlanSanityChecker.validateFinalPlan(PlanSanityChecker.java:65)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:203)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:185)
    at io.prestosql.sql.planner.LogicalPlanner.plan(LogicalPlanner.java:180)
    at io.prestosql.execution.SqlQueryExecution.doPlanQuery(SqlQueryExecution.java:395)
    at io.prestosql.execution.SqlQueryExecution.planQuery(SqlQueryExecution.java:383)
    at io.prestosql.execution.SqlQueryExecution.start(SqlQueryExecution.java:338)
    at io.prestosql.$gen.Presto_330____20200221_121842_2.run(Unknown Source)
    at io.prestosql.execution.SqlQueryManager.createQuery(SqlQueryManager.java:240)
    at io.prestosql.dispatcher.LocalDispatchQuery.lambda$startExecution$7(LocalDispatchQuery.java:132)
    at io.prestosql.$gen.Presto_330____20200221_121842_2.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

Source

Svti

Most helpful comment

https://github.com/prestosql/presto/issues/3154#issuecomment-601546809

@Praveen2112 here it is.

Svti on 24 Mar 2020

👍2

All 21 comments

SELECT 1
,CASE WHEN a.grouping_id > 0 THEN '-' ELSE CAST(a.dt AS varchar) END AS dt
,a.grouping_id AS debug
,a.dnd AS dnd
,a.dnu AS dnu
,a.dau AS dau
,a.dar AS dar
FROM (
SELECT COUNT(DISTINCT(newdid)) AS dnd
,COUNT(DISTINCT(newuid)) AS dnu
,COUNT(DISTINCT(uid)) AS dau
,COUNT(DISTINCT(rid)) AS dar
,dt
,GROUPING(dt) AS grouping_id
FROM (
SELECT dt
,'' AS newdid -- TODO
,'' AS newuid -- TODO
,'' AS newrid
,browser_did AS did
,uid AS uid
,rid AS rid
FROM hive.ltgame_db.dwd_d_role_status a
) GROUP BY GROUPING SETS (
()
,(dt)
) -- end of GROUP BY
) a
-- ORDER
ORDER BY grouping_id DESC, a.dt DESC
LIMIT 201

Svti on 19 Mar 2020

Can you post the same query in #2716.

Praveen2112 on 19 Mar 2020

Is the table partitioned based on dt ?

Praveen2112 on 19 Mar 2020

The sql are different , but #2716 have already been solved ( left join -> inner join).

the problems are same ?

Svti on 19 Mar 2020

Is the table partitioned based on dt ?

@Praveen2112 yes

Svti on 19 Mar 2020

Actually both are same

Praveen2112 on 19 Mar 2020

How long will it take to fix it ？
@Praveen2112

Svti on 19 Mar 2020

👍1

@Svti can u share the query json from ui?

tooptoop4 on 19 Mar 2020

I'm working on it...I was able to recreate the same issue with this tpch query

select count(a.orderkey) from orders a left join orders b on a.custkey=b.custkey where b.orderkey in (1) group by grouping sets ((a.custkey), ())

The actual issue is when we try to have a grouping set based on a partitioned column, the AddExchange optimizer tries to skip adding the ExchangeNode which doesn't distribute the data for empty grouping set thus failing.. The workaround is to force add the ExchangeNode for it.. Working on that part

can u share the query json from ui?

I guess it won't give us much info as the query is failed during planning itself I guess

Praveen2112 on 19 Mar 2020

@Svti Can you share the plan you got when we run it without the empty grouping set ?

Praveen2112 on 19 Mar 2020

@tooptoop4

{
  "queryId" : "20200320_014821_00109_zsbg8",
  "session" : {
    "queryId" : "20200320_014821_00109_zsbg8",
    "clientTransactionSupport" : true,
    "user" : "presto",
    "source" : "DataGrip 2019.2.5",
    "catalog" : "hive",
    "path" : { },
    "timeZoneKey" : 0,
    "locale" : "zh_CN_#Hans",
    "remoteUserAddress" : "...",
    "userAgent" : "Presto JDBC Driver/319",
    "clientTags" : [ ],
    "clientCapabilities" : [ "PATH" ],
    "resourceEstimates" : { },
    "startTime" : 1584668901568,
    "systemProperties" : { },
    "catalogProperties" : { },
    "unprocessedCatalogProperties" : { },
    "roles" : { },
    "preparedStatements" : { }
  },
  "state" : "FAILED",
  "memoryPool" : "general",
  "scheduled" : false,
  "self" : "http://host/v1/query/20200320_014821_00109_zsbg8",
  "fieldNames" : [ ],
  "query" : "SELECT 1\n,CASE WHEN a.grouping_id \u003E 0 THEN \u0027-\u0027 ELSE CAST(a.dt AS varchar) END AS dt\n,a.grouping_id AS debug\n,a.dnd AS dnd\n,a.dnu AS dnu\n,a.dau AS dau\n,a.dar AS dar\nFROM (\nSELECT COUNT(DISTINCT(newdid)) AS dnd\n,COUNT(DISTINCT(newuid)) AS dnu\n,COUNT(DISTINCT(uid)) AS dau\n,COUNT(DISTINCT(rid)) AS dar\n,dt\n,GROUPING() AS grouping_id\nFROM (\nSELECT dt\n,\u0027\u0027 AS newdid -- TODO\n,\u0027\u0027 AS newuid -- TODO\n,\u0027\u0027 AS newrid\n,browser_did AS did\n,uid AS uid\n,rid AS rid\nFROM hive.ltgame_db.dwd_d_role_status a\n) GROUP BY GROUPING SETS (\n()\n-- ,(dt)\n) -- end of GROUP BY\n) a\n-- ORDER\nORDER BY grouping_id DESC, a.dt DESC\nLIMIT 201",
  "queryStats" : {
    "createTime" : "2020-03-20T01:48:21.570Z",
    "executionStartTime" : "2020-03-20T01:48:21.570Z",
    "lastHeartbeat" : "2020-03-20T01:48:21.570Z",
    "endTime" : "2020-03-20T01:48:21.570Z",
    "elapsedTime" : "0.00ms",
    "queuedTime" : "0.00ms",
    "resourceWaitingTime" : "0.00ms",
    "dispatchingTime" : "0.00ms",
    "executionTime" : "0.00ms",
    "analysisTime" : "0.00ms",
    "planningTime" : "0.00ms",
    "finishingTime" : "0.00ms",
    "totalTasks" : 0,
    "runningTasks" : 0,
    "completedTasks" : 0,
    "totalDrivers" : 0,
    "queuedDrivers" : 0,
    "runningDrivers" : 0,
    "blockedDrivers" : 0,
    "completedDrivers" : 0,
    "cumulativeUserMemory" : 0.0,
    "userMemoryReservation" : "0B",
    "revocableMemoryReservation" : "0B",
    "totalMemoryReservation" : "0B",
    "peakUserMemoryReservation" : "0B",
    "peakRevocableMemoryReservation" : "0B",
    "peakTotalMemoryReservation" : "0B",
    "peakTaskUserMemory" : "0B",
    "peakTaskRevocableMemory" : "0B",
    "peakTaskTotalMemory" : "0B",
    "scheduled" : false,
    "totalScheduledTime" : "0.00ms",
    "totalCpuTime" : "0.00ms",
    "totalBlockedTime" : "0.00ms",
    "fullyBlocked" : false,
    "blockedReasons" : [ ],
    "physicalInputDataSize" : "0B",
    "physicalInputPositions" : 0,
    "physicalInputReadTime" : "0.00ms",
    "internalNetworkInputDataSize" : "0B",
    "internalNetworkInputPositions" : 0,
    "rawInputDataSize" : "0B",
    "rawInputPositions" : 0,
    "processedInputDataSize" : "0B",
    "processedInputPositions" : 0,
    "outputDataSize" : "0B",
    "outputPositions" : 0,
    "physicalWrittenDataSize" : "0B",
    "stageGcStatistics" : [ ],
    "operatorSummaries" : [ ],
    "logicalWrittenDataSize" : "0B",
    "writtenPositions" : 0,
    "spilledDataSize" : "0B"
  },
  "setSessionProperties" : { },
  "resetSessionProperties" : [ ],
  "setRoles" : { },
  "addedPreparedStatements" : { },
  "deallocatedPreparedStatements" : [ ],
  "clearTransactionId" : false,
  "failureInfo" : {
    "type" : "java.lang.IllegalArgumentException",
    "message" : "grouping operation columns cannot be empty",
    "suppressed" : [ ],
    "stack" : [ "com.google.common.base.Preconditions.checkArgument(Preconditions.java:141)", "io.prestosql.sql.tree.GroupingOperation.\u003Cinit\u003E(GroupingOperation.java:35)", "io.prestosql.sql.parser.AstBuilder.visitGroupingOperation(AstBuilder.java:1745)", "io.prestosql.sql.parser.AstBuilder.visitGroupingOperation(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$GroupingOperationContext.accept(SqlBaseParser.java:8239)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitValueExpressionDefault(SqlBaseBaseVisitor.java:769)", "io.prestosql.sql.parser.SqlBaseParser$ValueExpressionDefaultContext.accept(SqlBaseParser.java:7318)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitPredicated(AstBuilder.java:1191)", "io.prestosql.sql.parser.AstBuilder.visitPredicated(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$PredicatedContext.accept(SqlBaseParser.java:6576)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitExpression(SqlBaseBaseVisitor.java:685)", "io.prestosql.sql.parser.SqlBaseParser$ExpressionContext.accept(SqlBaseParser.java:6500)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitSelectSingle(AstBuilder.java:769)", "io.prestosql.sql.parser.AstBuilder.visitSelectSingle(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$SelectSingleContext.accept(SqlBaseParser.java:5524)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)", "java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1382)", "java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)", "java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)", "java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)", "java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)", "java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)", "io.prestosql.sql.parser.AstBuilder.visit(AstBuilder.java:2145)", "io.prestosql.sql.parser.AstBuilder.visitQuerySpecification(AstBuilder.java:671)", "io.prestosql.sql.parser.AstBuilder.visitQuerySpecification(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QuerySpecificationContext.accept(SqlBaseParser.java:4816)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitQueryPrimaryDefault(SqlBaseBaseVisitor.java:482)", "io.prestosql.sql.parser.SqlBaseParser$QueryPrimaryDefaultContext.accept(SqlBaseParser.java:4548)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitQueryTermDefault(SqlBaseBaseVisitor.java:468)", "io.prestosql.sql.parser.SqlBaseParser$QueryTermDefaultContext.accept(SqlBaseParser.java:4356)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitQueryNoWith(AstBuilder.java:611)", "io.prestosql.sql.parser.AstBuilder.visitQueryNoWith(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QueryNoWithContext.accept(SqlBaseParser.java:4156)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitQuery(AstBuilder.java:576)", "io.prestosql.sql.parser.AstBuilder.visitQuery(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QueryContext.accept(SqlBaseParser.java:3509)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitSubqueryRelation(AstBuilder.java:1161)", "io.prestosql.sql.parser.AstBuilder.visitSubqueryRelation(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$SubqueryRelationContext.accept(SqlBaseParser.java:6285)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitAliasedRelation(AstBuilder.java:1138)", "io.prestosql.sql.parser.AstBuilder.visitAliasedRelation(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$AliasedRelationContext.accept(SqlBaseParser.java:6134)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitSampledRelation(AstBuilder.java:1122)", "io.prestosql.sql.parser.AstBuilder.visitSampledRelation(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$SampledRelationContext.accept(SqlBaseParser.java:6013)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitRelationDefault(SqlBaseBaseVisitor.java:594)", "io.prestosql.sql.parser.SqlBaseParser$RelationDefaultContext.accept(SqlBaseParser.java:5636)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)", "java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1382)", "java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)", "java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)", "java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)", "java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)", "java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)", "io.prestosql.sql.parser.AstBuilder.visit(AstBuilder.java:2145)", "io.prestosql.sql.parser.AstBuilder.visitQuerySpecification(AstBuilder.java:673)", "io.prestosql.sql.parser.AstBuilder.visitQuerySpecification(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QuerySpecificationContext.accept(SqlBaseParser.java:4816)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitQueryPrimaryDefault(SqlBaseBaseVisitor.java:482)", "io.prestosql.sql.parser.SqlBaseParser$QueryPrimaryDefaultContext.accept(SqlBaseParser.java:4548)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitQueryTermDefault(SqlBaseBaseVisitor.java:468)", "io.prestosql.sql.parser.SqlBaseParser$QueryTermDefaultContext.accept(SqlBaseParser.java:4356)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitQueryNoWith(AstBuilder.java:611)", "io.prestosql.sql.parser.AstBuilder.visitQueryNoWith(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QueryNoWithContext.accept(SqlBaseParser.java:4156)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitQuery(AstBuilder.java:576)", "io.prestosql.sql.parser.AstBuilder.visitQuery(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$QueryContext.accept(SqlBaseParser.java:3509)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visitChildren(AbstractParseTreeVisitor.java:46)", "io.prestosql.sql.parser.SqlBaseBaseVisitor.visitStatementDefault(SqlBaseBaseVisitor.java:48)", "io.prestosql.sql.parser.SqlBaseParser$StatementDefaultContext.accept(SqlBaseParser.java:1432)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.AstBuilder.visitSingleStatement(AstBuilder.java:220)", "io.prestosql.sql.parser.AstBuilder.visitSingleStatement(AstBuilder.java:206)", "io.prestosql.sql.parser.SqlBaseParser$SingleStatementContext.accept(SqlBaseParser.java:232)", "org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)", "io.prestosql.sql.parser.SqlParser.invokeParser(SqlParser.java:157)", "io.prestosql.sql.parser.SqlParser.createStatement(SqlParser.java:86)", "io.prestosql.execution.QueryPreparer.prepareQuery(QueryPreparer.java:55)", "io.prestosql.dispatcher.DispatchManager.createQueryInternal(DispatchManager.java:174)", "io.prestosql.dispatcher.DispatchManager.lambda$createQuery$0(DispatchManager.java:146)", "io.prestosql.$gen.Presto_330____20200221_121842_2.run(Unknown Source)", "java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)", "java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)", "java.lang.Thread.run(Thread.java:748)" ],
    "errorCode" : {
      "code" : 65536,
      "name" : "GENERIC_INTERNAL_ERROR",
      "type" : "INTERNAL_ERROR"
    }
  },
  "errorCode" : {
    "code" : 65536,
    "name" : "GENERIC_INTERNAL_ERROR",
    "type" : "INTERNAL_ERROR"
  },
  "warnings" : [ ],
  "inputs" : [ ],
  "errorType" : "INTERNAL_ERROR",
  "finalQueryInfo" : true
}

Svti on 20 Mar 2020

@Praveen2112

yes , if i run it without the empty grouping set , it works ~

Svti on 20 Mar 2020

@Praveen2112
we also found , when we query with ' COUNT(DISTINCT(..)) ' , then this issue happened.

when we query with ' COUNT() ' , 'SUM() ' , then it works.

Svti on 20 Mar 2020

Can you share the query plan( using explain ) for both the working cases?

Praveen2112 on 20 Mar 2020

@Praveen2112 the presto ui don't have query plan

Svti on 20 Mar 2020

Output[_col0, dt, debug, dnd, dnu, dnr, dau, dar]
│ Layout: [expr_118:integer, expr_119:varchar, expr_90:integer, count:bigint, count_86:bigint, count_87:bigint, count_88:bigint, count_89:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ _col0 := expr_118
│ dt := expr_119
│ debug := expr_90
│ dnd := count
│ dnu := count_86
│ dnr := count_87
│ dau := count_88
│ dar := count_89
└─ Project[]
│ Layout: [expr_118:integer, expr_119:varchar, expr_90:integer, count:bigint, count_86:bigint, count_87:bigint, count_88:bigint, count_89:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ TopN[201 by (expr_90 DESC_NULLS_LAST, dt DESC_NULLS_LAST)]
│ Layout: [dt:integer, count_89:bigint, count_88:bigint, count:bigint, expr_90:integer, count_87:bigint, expr_119:varchar, count_86:bigint, expr_118:integer]
└─ LocalExchange[SINGLE] ()
│ Layout: [dt:integer, count_89:bigint, count_88:bigint, count:bigint, expr_90:integer, count_87:bigint, expr_119:varchar, count_86:bigint, expr_118:integer]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ RemoteExchange[GATHER]
│ Layout: [dt:integer, count_89:bigint, count_88:bigint, count:bigint, expr_90:integer, count_87:bigint, expr_119:varchar, count_86:bigint, expr_118:integer]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ TopNPartial[201 by (expr_90 DESC_NULLS_LAST, dt DESC_NULLS_LAST)]
│ Layout: [dt:integer, count_89:bigint, count_88:bigint, count:bigint, expr_90:integer, count_87:bigint, expr_119:varchar, count_86:bigint, expr_118:integer]
└─ Project[]
│ Layout: [dt:integer, count_89:bigint, count_88:bigint, count:bigint, expr_90:integer, count_87:bigint, expr_119:varchar, count_86:bigint, expr_118:integer]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ expr_90 := 0
│ expr_119 := CAST("dt" AS varchar)
│ expr_118 := 1
└─ Aggregate[dt][$hashvalue]
│ Layout: [dt:integer, $hashvalue:bigint, count_89:bigint, count_88:bigint, count:bigint, count_87:bigint, count_86:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ count_89 := count("rid") (mask = expr$distinct_146)
│ count_88 := count("uid") (mask = expr$distinct_145)
│ count := count("expr_54") (mask = expr$distinct)
│ count_87 := count("rid_30") (mask = expr$distinct_144)
│ count_86 := count("expr_54") (mask = expr$distinct_143)
└─ MarkDistinct[distinct=dt:integer, rid:varchar marker=expr$distinct_146][$hashvalue_147]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint, expr$distinct:boolean, expr$distinct_143:boolean, expr$distinct_144:boolean, expr$distinct_145:boolean, expr$distinct_146:boolean]
└─ MarkDistinct[distinct=dt:integer, uid:varchar marker=expr$distinct_145][$hashvalue_148]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint, expr$distinct:boolean, expr$distinct_143:boolean, expr$distinct_144:boolean, expr$distinct_145:boolean]
└─ MarkDistinct[distinct=dt:integer, rid_30:varchar marker=expr$distinct_144][$hashvalue_149]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint, expr$distinct:boolean, expr$distinct_143:boolean, expr$distinct_144:boolean]
└─ MarkDistinct[distinct=dt:integer marker=expr$distinct_143][$hashvalue]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint, expr$distinct:boolean, expr$distinct_143:boolean]
└─ MarkDistinct[distinct=dt:integer marker=expr$distinct][$hashvalue]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint, expr$distinct:boolean]
└─ LocalExchange[HASH][$hashvalue] ("dt")
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue:bigint, $hashvalue_147:bigint, $hashvalue_148:bigint, $hashvalue_149:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ Project[]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue_150:bigint, $hashvalue_151:bigint, $hashvalue_152:bigint, $hashvalue_153:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ RemoteExchange[REPARTITION][$hashvalue_154]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue_150:bigint, $hashvalue_151:bigint, $hashvalue_152:bigint, $hashvalue_153:bigint, $hashvalue_154:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
└─ Project[]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue_160:bigint, $hashvalue_161:bigint, $hashvalue_162:bigint, $hashvalue_163:bigint, $hashvalue_164:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ $hashvalue_164 := combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0)), COALESCE("$operator$hash_code"("expr_54"), 0))
└─ Project[]
│ Layout: [dt:integer, expr_54:varchar(0), rid_30:varchar, uid:varchar, rid:varchar, $hashvalue_160:bigint, $hashvalue_161:bigint, $hashvalue_162:bigint, $hashvalue_163:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ expr_54 := ''
│ $hashvalue_160 := combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0))
│ $hashvalue_161 := combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0)), COALESCE("$operator$hash_code"("rid"), 0))
│ $hashvalue_162 := combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0)), COALESCE("$operator$hash_code"("uid"), 0))
│ $hashvalue_163 := combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0)), COALESCE("$operator$hash_code"("rid_30"), 0))
└─ LeftJoin[("dt" = "dt_41") AND ("gameid" = "gameid_28") AND ("uid" = "uid_20") AND ("rid" = "rid_30")][$hashvalue_155, $hashvalue_157]
│ Layout: [uid:varchar, rid:varchar, dt:integer, rid_30:varchar]
│ Estimates: {rows: ? (?), cpu: ?, memory: ?, network: ?}
│ Distribution: PARTITIONED
├─ RemoteExchange[REPARTITION][$hashvalue_155]
│ │ Layout: [uid:varchar, gameid:varchar, rid:varchar, dt:integer, $hashvalue_155:bigint]
│ │ Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
│ └─ ScanFilterProject[table = hive:ltgame_db:dwd_d_role_status, filterPredicate = ("gameid" = CAST('574' AS varchar))]
│ Layout: [uid:varchar, gameid:varchar, rid:varchar, dt:integer, $hashvalue_156:bigint]
│ Estimates: {rows: 3 (537B), cpu: 510, memory: 0B, network: 0B}/{rows: ? (?), cpu: 1020, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}
│ $hashvalue_156 := combine_hash(combine_hash(combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt"), 0)), COALESCE("$operator$hash_code"("gameid"), 0)), COALESCE("$operator$hash_code"("uid"), 0)), COALESCE("$operator$hash_code"("rid"), 0))
│ dt := dt:int:-1:PARTITION_KEY
│ :: [[20200312], [20200313], [20200314], [20200315], [20200316], [20200317], [20200318]]
│ gameid := gameid:string:28:REGULAR
│ uid := uid:string:20:REGULAR
│ rid := rid:string:30:REGULAR
└─ LocalExchange[HASH][$hashvalue_157] ("dt_41", "gameid_28", "uid_20", "rid_30")
│ Layout: [uid_20:varchar, gameid_28:varchar, rid_30:varchar, dt_41:integer, $hashvalue_157:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
└─ RemoteExchange[REPARTITION][$hashvalue_158]
│ Layout: [uid_20:varchar, gameid_28:varchar, rid_30:varchar, dt_41:integer, $hashvalue_158:bigint]
│ Estimates: {rows: ? (?), cpu: ?, memory: 0B, network: ?}
└─ ScanFilterProject[table = hive:ltgame_db:dwd_d_role_reg, filterPredicate = ("gameid_28" = CAST('574' AS varchar))]
Layout: [uid_20:varchar, gameid_28:varchar, rid_30:varchar, dt_41:integer, $hashvalue_159:bigint]
Estimates: {rows: 1 (179B), cpu: 170, memory: 0B, network: 0B}/{rows: ? (?), cpu: 340, memory: 0B, network: 0B}/{rows: ? (?), cpu: ?, memory: 0B, network: 0B}
$hashvalue_159 := combine_hash(combine_hash(combine_hash(combine_hash(bigint '0', COALESCE("$operator$hash_code"("dt_41"), 0)), COALESCE("$operator$hash_code"("gameid_28"), 0)), COALESCE("$operator$hash_code"("uid_20"), 0)), COALESCE("$operator$hash_code"("rid_30"), 0))
uid_20 := uid:string:21:REGULAR
rid_30 := rid:string:31:REGULAR
gameid_28 := gameid:string:29:REGULAR
dt_41 := dt:int:-1:PARTITION_KEY
:: [[20200313], [20200316], [20200317], [20200318]]

this above are using grouping set( (dt) )

Svti on 20 Mar 2020

How about sharing the plan created by running explain query in CLI ?

Praveen2112 on 20 Mar 2020

@Praveen2112 the error query (using grouping set( () (dt) ) don't have no explain message in CLI

Svti on 20 Mar 2020

No I meant the query plan for query which doesn’t throw any error. The query without empty grouping set and the query which doesn’t have count(distinct())

Praveen2112 on 20 Mar 2020

https://github.com/prestosql/presto/issues/3154#issuecomment-601546809

@Praveen2112 here it is.

Svti on 24 Mar 2020

👍2

@Svti Feel free to re-open this issue. If the issue still exists

Praveen2112 on 15 May 2020

Was this page helpful?

0 / 5 - 0 ratings