[ https://issues.apache.org/jira/browse/DRILL-7277?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16848071#comment-16848071 ]
Ted Dunning commented on DRILL-7277: ------------------------------------ This query: {{select row_number() over (order by department_id desc) r, department_id from (select department_id from cp.`employee.json` order by department_id desc) ;}} blows beets as below but putting department_id first in the output doesn't. {{java.sql.SQLException: [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM ERROR: CannotPlanException: Node [rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC]] could not be implemented; planner state: Root: rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC] Original rel: LogicalProject(subset=[rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC]], r=[$1], department_id=[$0]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26935 LogicalWindow(subset=[rel#26934:Subset#3.NONE.ANY([]).[1 DESC]], window#0=[window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26933 LogicalSort(subset=[rel#26932:Subset#2.NONE.ANY([]).[0 DESC]], sort0=[$0], dir0=[DESC]): rowcount = 100.0, cumulative cost = {100.0 rows, 1842.0680743952366 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26931 LogicalProject(subset=[rel#26930:Subset#1.NONE.ANY([]).[]], department_id=[$1]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26929 EnumerableTableScan(subset=[rel#26928:Subset#0.ENUMERABLE.ANY([]).[]], table=[[cp, employee.json]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26880 Sets: Set#0, type: RecordType(DYNAMIC_STAR **, ANY department_id) rel#26928:Subset#0.ENUMERABLE.ANY([]).[], best=rel#26880, importance=0.5904900000000001 rel#26880:EnumerableTableScan.ENUMERABLE.ANY([]).[](table=[cp, employee.json]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26952:Subset#0.LOGICAL.ANY([]).[], best=rel#26954, importance=0.3247695 rel#26954:DrillScanRel.LOGICAL.ANY([]).[](table=[cp, employee.json],groupscan=EasyGroupScan [selectionRoot=classpath:/employee.json, numFiles=1, columns=[`**`, `department_id`], files=[classpath:/employee.json]]), rowcount=463.0, cumulative cost={463.0 rows, 4630000.0 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#1, type: RecordType(ANY department_id) rel#26930:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561 rel#26929:LogicalProject.NONE.ANY([]).[](input=rel#26928:Subset#0.ENUMERABLE.ANY([]).[],department_id=$1), rowcount=100.0, cumulative cost={inf} rel#26931:LogicalSort.NONE.ANY([]).[0 DESC](input=rel#26930:Subset#1.NONE.ANY([]).[],sort0=$0,dir0=DESC), rowcount=100.0, cumulative cost={inf} rel#26943:Subset#1.LOGICAL.ANY([]).[], best=rel#26950, importance=0.405 rel#26944:DrillSortRel.LOGICAL.ANY([]).[0 DESC](input=rel#26943:Subset#1.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC), rowcount=463.0, cumulative cost={926.0 rows, 11830.070504167705 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26950:DrillScanRel.LOGICAL.ANY([]).[](table=[cp, employee.json],groupscan=EasyGroupScan [selectionRoot=classpath:/employee.json, numFiles=1, columns=[`department_id`], files=[classpath:/employee.json]]), rowcount=463.0, cumulative cost={463.0 rows, 463.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26953:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#26952:Subset#0.LOGICAL.ANY([]).[],department_id=$1), rowcount=463.0, cumulative cost={926.0 rows, 4630463.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26946:Subset#1.NONE.ANY([]).[0 DESC], best=null, importance=0.7290000000000001 rel#26931:LogicalSort.NONE.ANY([]).[0 DESC](input=rel#26930:Subset#1.NONE.ANY([]).[],sort0=$0,dir0=DESC), rowcount=100.0, cumulative cost={inf} rel#26947:Subset#1.LOGICAL.ANY([]).[1 DESC], best=null, importance=0.81 rel#26948:Subset#1.LOGICAL.ANY([]).[0 DESC], best=rel#26944, importance=0.405 rel#26944:DrillSortRel.LOGICAL.ANY([]).[0 DESC](input=rel#26943:Subset#1.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC), rowcount=463.0, cumulative cost={926.0 rows, 11830.070504167705 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#3, type: RecordType(ANY department_id, BIGINT w0$o0) rel#26934:Subset#3.NONE.ANY([]).[1 DESC], best=null, importance=0.81 rel#26933:LogicalWindow.NONE.ANY([]).[[1 DESC]](input=rel#26946:Subset#1.NONE.ANY([]).[0 DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=100.0, cumulative cost={inf} rel#26939:Subset#3.LOGICAL.ANY([]).[1 DESC], best=null, importance=0.9 rel#26942:DrillWindowRel.LOGICAL.ANY([]).[1 DESC](input=rel#26947:Subset#1.LOGICAL.ANY([]).[1 DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=100.0, cumulative cost={inf} Set#4, type: RecordType(BIGINT r, ANY department_id) rel#26936:Subset#4.NONE.ANY([]).[1 DESC], best=null, importance=0.9 rel#26935:LogicalProject.NONE.ANY([]).[[1 DESC]](input=rel#26934:Subset#3.NONE.ANY([]).[1 DESC],r=$1,department_id=$0), rowcount=100.0, cumulative cost={inf} rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC], best=null, importance=1.0 rel#26938:AbstractConverter.LOGICAL.ANY([]).[1 DESC](input=rel#26936:Subset#4.NONE.ANY([]).[1 DESC],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[1 DESC]), rowcount=100.0, cumulative cost={inf} rel#26940:DrillProjectRel.LOGICAL.ANY([]).[[1 DESC]](input=rel#26939:Subset#3.LOGICAL.ANY([]).[1 DESC],r=$1,department_id=$0), rowcount=100.0, cumulative cost={inf} [Error Id: e9bec34a-b9b6-4541-bfb7-942bf01f67ee on mdn-1.mdn.tdunning-dsr-demo-z6io48.svc.cluster.local:31010]. at com.mapr.drill.drill.dataengine.DRQryResultListener.checkAndThrowException(Unknown Source) at com.mapr.drill.drill.dataengine.DRQryResultListener.getNextBatch(Unknown Source) at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doLoadRecordBatchData(Unknown Source) at com.mapr.drill.drill.dataengine.DRJDBCResultSet.doMoveToNextRow(Unknown Source) at com.mapr.drill.drill.dataengine.DRJDBCQueryExecutor.execute(Unknown Source) at com.mapr.drill.jdbc.common.SStatement.executeNoParams(Unknown Source) at com.mapr.drill.jdbc.common.SStatement.execute(Unknown Source) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291) at org.apache.zeppelin.jdbc.JDBCInterpreter.executeSql(JDBCInterpreter.java:718) at org.apache.zeppelin.jdbc.JDBCInterpreter.interpret(JDBCInterpreter.java:801) at org.apache.zeppelin.interpreter.LazyOpenInterpreter.interpret(LazyOpenInterpreter.java:103) at org.apache.zeppelin.interpreter.remote.RemoteInterpreterServer$InterpretJob.jobRun(RemoteInterpreterServer.java:633) at org.apache.zeppelin.scheduler.Job.run(Job.java:188) at org.apache.zeppelin.scheduler.ParallelScheduler$JobRunner.run(ParallelScheduler.java:162) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) Caused by: com.mapr.drill.support.exceptions.GeneralException: [MapR][DrillJDBCDriver](500165) Query execution error. Details: SYSTEM ERROR: CannotPlanException: Node [rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC]] could not be implemented; planner state: Root: rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC] Original rel: LogicalProject(subset=[rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC]], r=[$1], department_id=[$0]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26935 LogicalWindow(subset=[rel#26934:Subset#3.NONE.ANY([]).[1 DESC]], window#0=[window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])]): rowcount = 100.0, cumulative cost = {100.0 rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26933 LogicalSort(subset=[rel#26932:Subset#2.NONE.ANY([]).[0 DESC]], sort0=[$0], dir0=[DESC]): rowcount = 100.0, cumulative cost = {100.0 rows, 1842.0680743952366 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26931 LogicalProject(subset=[rel#26930:Subset#1.NONE.ANY([]).[]], department_id=[$1]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26929 EnumerableTableScan(subset=[rel#26928:Subset#0.ENUMERABLE.ANY([]).[]], table=[[cp, employee.json]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 26880 Sets: Set#0, type: RecordType(DYNAMIC_STAR **, ANY department_id) rel#26928:Subset#0.ENUMERABLE.ANY([]).[], best=rel#26880, importance=0.5904900000000001 rel#26880:EnumerableTableScan.ENUMERABLE.ANY([]).[](table=[cp, employee.json]), rowcount=100.0, cumulative cost={100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26952:Subset#0.LOGICAL.ANY([]).[], best=rel#26954, importance=0.3247695 rel#26954:DrillScanRel.LOGICAL.ANY([]).[](table=[cp, employee.json],groupscan=EasyGroupScan [selectionRoot=classpath:/employee.json, numFiles=1, columns=[`**`, `department_id`], files=[classpath:/employee.json]]), rowcount=463.0, cumulative cost={463.0 rows, 4630000.0 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#1, type: RecordType(ANY department_id) rel#26930:Subset#1.NONE.ANY([]).[], best=null, importance=0.6561 rel#26929:LogicalProject.NONE.ANY([]).[](input=rel#26928:Subset#0.ENUMERABLE.ANY([]).[],department_id=$1), rowcount=100.0, cumulative cost={inf} rel#26931:LogicalSort.NONE.ANY([]).[0 DESC](input=rel#26930:Subset#1.NONE.ANY([]).[],sort0=$0,dir0=DESC), rowcount=100.0, cumulative cost={inf} rel#26943:Subset#1.LOGICAL.ANY([]).[], best=rel#26950, importance=0.405 rel#26944:DrillSortRel.LOGICAL.ANY([]).[0 DESC](input=rel#26943:Subset#1.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC), rowcount=463.0, cumulative cost={926.0 rows, 11830.070504167705 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26950:DrillScanRel.LOGICAL.ANY([]).[](table=[cp, employee.json],groupscan=EasyGroupScan [selectionRoot=classpath:/employee.json, numFiles=1, columns=[`department_id`], files=[classpath:/employee.json]]), rowcount=463.0, cumulative cost={463.0 rows, 463.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26953:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#26952:Subset#0.LOGICAL.ANY([]).[],department_id=$1), rowcount=463.0, cumulative cost={926.0 rows, 4630463.0 cpu, 0.0 io, 0.0 network, 0.0 memory} rel#26946:Subset#1.NONE.ANY([]).[0 DESC], best=null, importance=0.7290000000000001 rel#26931:LogicalSort.NONE.ANY([]).[0 DESC](input=rel#26930:Subset#1.NONE.ANY([]).[],sort0=$0,dir0=DESC), rowcount=100.0, cumulative cost={inf} rel#26947:Subset#1.LOGICAL.ANY([]).[1 DESC], best=null, importance=0.81 rel#26948:Subset#1.LOGICAL.ANY([]).[0 DESC], best=rel#26944, importance=0.405 rel#26944:DrillSortRel.LOGICAL.ANY([]).[0 DESC](input=rel#26943:Subset#1.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC), rowcount=463.0, cumulative cost={926.0 rows, 11830.070504167705 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#3, type: RecordType(ANY department_id, BIGINT w0$o0) rel#26934:Subset#3.NONE.ANY([]).[1 DESC], best=null, importance=0.81 rel#26933:LogicalWindow.NONE.ANY([]).[[1 DESC]](input=rel#26946:Subset#1.NONE.ANY([]).[0 DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=100.0, cumulative cost={inf} rel#26939:Subset#3.LOGICAL.ANY([]).[1 DESC], best=null, importance=0.9 rel#26942:DrillWindowRel.LOGICAL.ANY([]).[1 DESC](input=rel#26947:Subset#1.LOGICAL.ANY([]).[1 DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=100.0, cumulative cost={inf} Set#4, type: RecordType(BIGINT r, ANY department_id) rel#26936:Subset#4.NONE.ANY([]).[1 DESC], best=null, importance=0.9 rel#26935:LogicalProject.NONE.ANY([]).[[1 DESC]](input=rel#26934:Subset#3.NONE.ANY([]).[1 DESC],r=$1,department_id=$0), rowcount=100.0, cumulative cost={inf} rel#26937:Subset#4.LOGICAL.ANY([]).[1 DESC], best=null, importance=1.0 rel#26938:AbstractConverter.LOGICAL.ANY([]).[1 DESC](input=rel#26936:Subset#4.NONE.ANY([]).[1 DESC],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[1 DESC]), rowcount=100.0, cumulative cost={inf} rel#26940:DrillProjectRel.LOGICAL.ANY([]).[[1 DESC]](input=rel#26939:Subset#3.LOGICAL.ANY([]).[1 DESC],r=$1,department_id=$0), rowcount=100.0, cumulative cost={inf} [Error Id: e9bec34a-b9b6-4541-bfb7-942bf01f67ee on mdn-1.mdn.tdunning-dsr-demo-z6io48.svc.cluster.local:31010]. ... 21 more}} > Bug in planner with redundant order-by > -------------------------------------- > > Key: DRILL-7277 > URL: https://issues.apache.org/jira/browse/DRILL-7277 > Project: Apache Drill > Issue Type: Bug > Affects Versions: 1.14.0 > Reporter: Ted Dunning > Priority: Major > -- This message was sent by Atlassian JIRA (v7.6.3#76005)