I have a bunch of data that I want to group up and look at the counts. In
order to get a row number for plotting, I tried a window function.

The data consists of about 7,2 million rows accessed via a view[1]. The
columns are pretty much all untyped[2].

This query works great:


*with *
*t0 as (*
*  select count(1) cnt*
*  from  dfs.flt.`flights-2018-01.csv` *
*  group by columns[5]*
*  order by cnt desc)*
*select cnt, row_number() over (order by cnt desc) r*
*from t0*


But if I change the order of the columns like this:

*with *
*t0 as (*
*  select count(1) cnt*
*  from  dfs.flt.`flights-2018-01.csv` *
*  group by columns[5]*
*  order by cnt desc)*
*select row_number() over (order by cnt desc) r, cnt*
*from t0*


I get the error below in query planning. That seems so very wrong.

Any ideas? I know I can just avoid the issue, but I was hoping for some
insight.

For extra oddness points, if I use a common table expression and invert the
field order, I get what I want with no error:


*with *
*t0 as (*
*  select count(1) cnt*
*  from  dfs.flt.`flights-2018-01.csv` *
*  group by columns[5]*
*  order by cnt desc),*
*t1 as (*
*  select cnt, row_number() over (order by cnt desc) r *
*  from t0)*
*select r, cnt*
*from t1*


This means that the planner is not rewriting this to eliminate the common
table before planning.


java.sql.SQLException: [MapR][DrillJDBCDriver](500165) Query execution
error. Details: SYSTEM ERROR: CannotPlanException: Node
[rel#18615:Subset#8.LOGICAL.ANY([]).[1 DESC]] could not be implemented;
planner state: Root: rel#18615:Subset#8.LOGICAL.ANY([]).[1 DESC] Original
rel: LogicalProject(subset=[rel#18615:Subset#8.LOGICAL.ANY([]).[1 DESC]],
r=[$1], cnt=[$0]): rowcount = 10.0, cumulative cost = {10.0 rows, 20.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 18613
LogicalWindow(subset=[rel#18612:Subset#7.NONE.ANY([]).[1 DESC]],
window#0=[window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])]): rowcount = 10.0,
cumulative cost = {10.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 18611 LogicalSort(subset=[rel#18610:Subset#6.NONE.ANY([]).[0 DESC]],
sort0=[$0], dir0=[DESC]): rowcount = 10.0, cumulative cost = {10.0 rows,
92.10340371976184 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18609
LogicalProject(subset=[rel#18608:Subset#5.NONE.ANY([]).[]], cnt=[$1]):
rowcount = 10.0, cumulative cost = {10.0 rows, 10.0 cpu, 0.0 io, 0.0
network, 0.0 memory}, id = 18607
LogicalAggregate(subset=[rel#18606:Subset#4.NONE.ANY([]).[]], group=[{0}],
cnt=[COUNT($1)]): rowcount = 10.0, cumulative cost = {11.25 rows, 0.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 18605
LogicalProject(subset=[rel#18604:Subset#3.NONE.ANY([]).[]],
dest_airport_id=[$5], $f1=[1]): rowcount = 100.0, cumulative cost = {100.0
rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18603
LogicalProject(subset=[rel#18602:Subset#2.NONE.ANY([]).[]], fl_date=[$0],
op_unique_carrier=[$1], tail_num=[$2], op_carrier_fl_num=[$3],
origin_airport_id=[$4], dest_airport_id=[$5], crs_dep_time=[$6],
dep_time=[$7], dep_delay=[$8], taxi_out=[$9], wheels_off=[$10],
wheels_on=[$11], taxi_in=[$12], arr_time=[$13], arr_delay=[$14],
air_time=[$15], distance=[$16]): rowcount = 100.0, cumulative cost = {100.0
rows, 1700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18601
LogicalProject(subset=[rel#18600:Subset#1.NONE.ANY([]).[]],
EXPR$0=[ITEM($1, 0)], EXPR$1=[ITEM($1, 1)], EXPR$2=[ITEM($1, 2)],
EXPR$3=[ITEM($1, 3)], EXPR$4=[ITEM($1, 4)], EXPR$5=[ITEM($1, 5)],
EXPR$6=[ITEM($1, 6)], EXPR$7=[ITEM($1, 7)], EXPR$8=[ITEM($1, 8)],
EXPR$9=[ITEM($1, 9)], EXPR$10=[ITEM($1, 10)], EXPR$11=[ITEM($1, 11)],
EXPR$12=[ITEM($1, 12)], EXPR$13=[ITEM($1, 13)], EXPR$14=[ITEM($1, 14)],
EXPR$15=[ITEM($1, 15)], EXPR$16=[ITEM($1, 16)]): rowcount = 100.0,
cumulative cost = {100.0 rows, 1700.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 18599
EnumerableTableScan(subset=[rel#18598:Subset#0.ENUMERABLE.ANY([]).[]],
table=[[dfs, flt, flights-2018-*.csv]]): rowcount = 100.0, cumulative cost
= {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18507
Sets: Set#0, type: RecordType(DYNAMIC_STAR **, ANY columns)
rel#18598:Subset#0.ENUMERABLE.ANY([]).[], best=rel#18507,
importance=0.4304672100000001
rel#18507:EnumerableTableScan.ENUMERABLE.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv]), rowcount=100.0, cumulative cost={100.0 rows, 101.0
cpu, 0.0 io, 0.0 network, 0.0 memory}
rel#18644:Subset#0.LOGICAL.ANY([]).[], best=rel#18653,
importance=0.28936962450000003
rel#18653:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12, columns=[`**`,
`columns`], files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8.370632E10 cpu, 0.0 io, 0.0 network, 0.0
memory} Set#1, type: RecordType(ANY EXPR$0, ANY EXPR$1, ANY EXPR$2, ANY
EXPR$3, ANY EXPR$4, ANY EXPR$5, ANY EXPR$6, ANY EXPR$7, ANY EXPR$8, ANY
EXPR$9, ANY EXPR$10, ANY EXPR$11, ANY EXPR$12, ANY EXPR$13, ANY EXPR$14,
ANY EXPR$15, ANY EXPR$16) rel#18600:Subset#1.NONE.ANY([]).[], best=null,
importance=0.4782969000000001
rel#18599:LogicalProject.NONE.ANY([]).[](input=rel#18598:Subset#0.ENUMERABLE.ANY([]).[],EXPR$0=ITEM($1,
0),EXPR$1=ITEM($1, 1),EXPR$2=ITEM($1, 2),EXPR$3=ITEM($1, 3),EXPR$4=ITEM($1,
4),EXPR$5=ITEM($1, 5),EXPR$6=ITEM($1, 6),EXPR$7=ITEM($1, 7),EXPR$8=ITEM($1,
8),EXPR$9=ITEM($1, 9),EXPR$10=ITEM($1, 10),EXPR$11=ITEM($1,
11),EXPR$12=ITEM($1, 12),EXPR$13=ITEM($1, 13),EXPR$14=ITEM($1,
14),EXPR$15=ITEM($1, 15),EXPR$16=ITEM($1, 16)), rowcount=100.0, cumulative
cost={inf}
rel#18601:LogicalProject.NONE.ANY([]).[](input=rel#18600:Subset#1.NONE.ANY([]).[],fl_date=$0,op_unique_carrier=$1,tail_num=$2,op_carrier_fl_num=$3,origin_airport_id=$4,dest_airport_id=$5,crs_dep_time=$6,dep_time=$7,dep_delay=$8,taxi_out=$9,wheels_off=$10,wheels_on=$11,taxi_in=$12,arr_time=$13,arr_delay=$14,air_time=$15,distance=$16),
rowcount=100.0, cumulative cost={inf}
rel#18636:Subset#1.LOGICAL.ANY([]).[], best=rel#18649,
importance=0.28936962450000003
rel#18649:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18648:Subset#10.LOGICAL.ANY([]).[],EXPR$0=ITEM($0,
0),EXPR$1=ITEM($0, 1),EXPR$2=ITEM($0, 2),EXPR$3=ITEM($0, 3),EXPR$4=ITEM($0,
4),EXPR$5=ITEM($0, 5),EXPR$6=ITEM($0, 6),EXPR$7=ITEM($0, 7),EXPR$8=ITEM($0,
8),EXPR$9=ITEM($0, 9),EXPR$10=ITEM($0, 10),EXPR$11=ITEM($0,
11),EXPR$12=ITEM($0, 12),EXPR$13=ITEM($0, 13),EXPR$14=ITEM($0,
14),EXPR$15=ITEM($0, 15),EXPR$16=ITEM($0, 16)), rowcount=8370632.0,
cumulative cost={1.6741264E7 rows, 5.77573608E8 cpu, 0.0 io, 0.0 network,
0.0 memory}
rel#18650:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18644:Subset#0.LOGICAL.ANY([]).[],EXPR$0=ITEM($1,
0),EXPR$1=ITEM($1, 1),EXPR$2=ITEM($1, 2),EXPR$3=ITEM($1, 3),EXPR$4=ITEM($1,
4),EXPR$5=ITEM($1, 5),EXPR$6=ITEM($1, 6),EXPR$7=ITEM($1, 7),EXPR$8=ITEM($1,
8),EXPR$9=ITEM($1, 9),EXPR$10=ITEM($1, 10),EXPR$11=ITEM($1,
11),EXPR$12=ITEM($1, 12),EXPR$13=ITEM($1, 13),EXPR$14=ITEM($1,
14),EXPR$15=ITEM($1, 15),EXPR$16=ITEM($1, 16)), rowcount=8370632.0,
cumulative cost={1.6741264E7 rows, 8.4275522976E10 cpu, 0.0 io, 0.0
network, 0.0 memory}
rel#18651:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18636:Subset#1.LOGICAL.ANY([]).[],fl_date=$0,op_unique_carrier=$1,tail_num=$2,op_carrier_fl_num=$3,origin_airport_id=$4,dest_airport_id=$5,crs_dep_time=$6,dep_time=$7,dep_delay=$8,taxi_out=$9,wheels_off=$10,wheels_on=$11,taxi_in=$12,arr_time=$13,arr_delay=$14,air_time=$15,distance=$16),
rowcount=8370632.0, cumulative cost={2.5111896E7 rows, 7.19874352E8 cpu,
0.0 io, 0.0 network, 0.0 memory} Set#3, type: RecordType(ANY
dest_airport_id, INTEGER $f1) rel#18604:Subset#3.NONE.ANY([]).[],
best=null, importance=0.531441
rel#18634:LogicalProject.NONE.ANY([]).[](input=rel#18600:Subset#1.NONE.ANY([]).[],dest_airport_id=$5,$f1=1),
rowcount=100.0, cumulative cost={inf}
rel#18638:LogicalProject.NONE.ANY([]).[](input=rel#18598:Subset#0.ENUMERABLE.ANY([]).[],dest_airport_id=ITEM($1,
5),$f1=1), rowcount=100.0, cumulative cost={inf}
rel#18630:Subset#3.LOGICAL.ANY([]).[], best=rel#18643, importance=0.2657205
rel#18637:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18636:Subset#1.LOGICAL.ANY([]).[],dest_airport_id=$5,$f1=1),
rowcount=8370632.0, cumulative cost={2.5111896E7 rows, 6.19426768E8 cpu,
0.0 io, 0.0 network, 0.0 memory}
rel#18643:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18642:Subset#9.LOGICAL.ANY([]).[],dest_airport_id=ITEM($0,
5),$f1=1), rowcount=8370632.0, cumulative cost={1.6741264E7 rows,
7.5335688E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
rel#18645:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18644:Subset#0.LOGICAL.ANY([]).[],dest_airport_id=ITEM($1,
5),$f1=1), rowcount=8370632.0, cumulative cost={1.6741264E7 rows,
8.3773285056E10 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#4, type:
RecordType(ANY dest_airport_id, BIGINT cnt)
rel#18606:Subset#4.NONE.ANY([]).[], best=null,
importance=0.5904900000000001
rel#18605:LogicalAggregate.NONE.ANY([]).[](input=rel#18604:Subset#3.NONE.ANY([]).[],group={0},cnt=COUNT($1)),
rowcount=10.0, cumulative cost={inf} rel#18628:Subset#4.LOGICAL.ANY([]).[],
best=rel#18631, importance=0.29524500000000004
rel#18631:DrillAggregateRel.LOGICAL.ANY([]).[](input=rel#18630:Subset#3.LOGICAL.ANY([]).[],group={0},cnt=COUNT($1)),
rowcount=8370581.997901573, cumulative cost={2.5111896E7 rows, 2.42748328E8
cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory} Set#5, type:
RecordType(BIGINT cnt) rel#18608:Subset#5.NONE.ANY([]).[], best=null,
importance=0.6561
rel#18607:LogicalProject.NONE.ANY([]).[](input=rel#18606:Subset#4.NONE.ANY([]).[],cnt=$1),
rowcount=10.0, cumulative cost={inf} rel#18609:LogicalSort.NONE.ANY([]).[0
DESC](input=rel#18608:Subset#5.NONE.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=10.0, cumulative cost={inf} rel#18621:Subset#5.LOGICAL.ANY([]).[],
best=rel#18629, importance=0.405 rel#18622:DrillSortRel.LOGICAL.ANY([]).[0
DESC](input=rel#18621:Subset#5.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=8370581.997901573, cumulative cost={4.185305999580315E7 rows,
7.848350521732183E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
rel#18629:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18628:Subset#4.LOGICAL.ANY([]).[],cnt=$1),
rowcount=8370581.997901573, cumulative cost={3.3482477997901574E7 rows,
2.5111890999790156E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
rel#18624:Subset#5.NONE.ANY([]).[0 DESC], best=null,
importance=0.7290000000000001 rel#18609:LogicalSort.NONE.ANY([]).[0
DESC](input=rel#18608:Subset#5.NONE.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=10.0, cumulative cost={inf} rel#18625:Subset#5.LOGICAL.ANY([]).[1
DESC], best=null, importance=0.81 rel#18626:Subset#5.LOGICAL.ANY([]).[0
DESC], best=rel#18622, importance=0.405
rel#18622:DrillSortRel.LOGICAL.ANY([]).[0
DESC](input=rel#18621:Subset#5.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=8370581.997901573, cumulative cost={4.185305999580315E7 rows,
7.848350521732183E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
Set#7, type: RecordType(BIGINT cnt, BIGINT w0$o0)
rel#18612:Subset#7.NONE.ANY([]).[1 DESC], best=null, importance=0.81
rel#18611:LogicalWindow.NONE.ANY([]).[[1
DESC]](input=rel#18624:Subset#5.NONE.ANY([]).[0
DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=10.0, cumulative
cost={inf} rel#18617:Subset#7.LOGICAL.ANY([]).[1 DESC], best=null,
importance=0.9 rel#18620:DrillWindowRel.LOGICAL.ANY([]).[1
DESC](input=rel#18625:Subset#5.LOGICAL.ANY([]).[1
DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=10.0, cumulative
cost={inf} Set#8, type: RecordType(BIGINT r, BIGINT cnt)
rel#18614:Subset#8.NONE.ANY([]).[1 DESC], best=null, importance=0.9
rel#18613:LogicalProject.NONE.ANY([]).[[1
DESC]](input=rel#18612:Subset#7.NONE.ANY([]).[1 DESC],r=$1,cnt=$0),
rowcount=10.0, cumulative cost={inf} rel#18615:Subset#8.LOGICAL.ANY([]).[1
DESC], best=null, importance=1.0
rel#18616:AbstractConverter.LOGICAL.ANY([]).[1
DESC](input=rel#18614:Subset#8.NONE.ANY([]).[1
DESC],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[1 DESC]),
rowcount=10.0, cumulative cost={inf}
rel#18618:DrillProjectRel.LOGICAL.ANY([]).[[1
DESC]](input=rel#18617:Subset#7.LOGICAL.ANY([]).[1 DESC],r=$1,cnt=$0),
rowcount=10.0, cumulative cost={inf} Set#9, type: RecordType(ANY columns)
rel#18642:Subset#9.LOGICAL.ANY([]).[], best=rel#18640,
importance=0.05314410000000001
rel#18640:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12,
columns=[`columns`[5]],
files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8370632.0 cpu, 0.0 io, 0.0 network, 0.0
memory} Set#10, type: RecordType(ANY columns)
rel#18648:Subset#10.LOGICAL.ANY([]).[], best=rel#18646,
importance=0.007410233661971832
rel#18646:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12,
columns=[`columns`[0], `columns`[1], `columns`[2], `columns`[3],
`columns`[4], `columns`[5], `columns`[6], `columns`[7], `columns`[8],
`columns`[9], `columns`[10], `columns`[11], `columns`[12], `columns`[13],
`columns`[14], `columns`[15], `columns`[16]],
files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8370632.0 cpu, 0.0 io, 0.0 network, 0.0
memory} [Error Id: e05bca25-7110-47aa-b872-60aa1b2fdc34 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#18615:Subset#8.LOGICAL.ANY([]).[1
DESC]] could not be implemented; planner state: Root:
rel#18615:Subset#8.LOGICAL.ANY([]).[1 DESC] Original rel:
LogicalProject(subset=[rel#18615:Subset#8.LOGICAL.ANY([]).[1 DESC]],
r=[$1], cnt=[$0]): rowcount = 10.0, cumulative cost = {10.0 rows, 20.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 18613
LogicalWindow(subset=[rel#18612:Subset#7.NONE.ANY([]).[1 DESC]],
window#0=[window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])]): rowcount = 10.0,
cumulative cost = {10.0 rows, 20.0 cpu, 0.0 io, 0.0 network, 0.0 memory},
id = 18611 LogicalSort(subset=[rel#18610:Subset#6.NONE.ANY([]).[0 DESC]],
sort0=[$0], dir0=[DESC]): rowcount = 10.0, cumulative cost = {10.0 rows,
92.10340371976184 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18609
LogicalProject(subset=[rel#18608:Subset#5.NONE.ANY([]).[]], cnt=[$1]):
rowcount = 10.0, cumulative cost = {10.0 rows, 10.0 cpu, 0.0 io, 0.0
network, 0.0 memory}, id = 18607
LogicalAggregate(subset=[rel#18606:Subset#4.NONE.ANY([]).[]], group=[{0}],
cnt=[COUNT($1)]): rowcount = 10.0, cumulative cost = {11.25 rows, 0.0 cpu,
0.0 io, 0.0 network, 0.0 memory}, id = 18605
LogicalProject(subset=[rel#18604:Subset#3.NONE.ANY([]).[]],
dest_airport_id=[$5], $f1=[1]): rowcount = 100.0, cumulative cost = {100.0
rows, 200.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18603
LogicalProject(subset=[rel#18602:Subset#2.NONE.ANY([]).[]], fl_date=[$0],
op_unique_carrier=[$1], tail_num=[$2], op_carrier_fl_num=[$3],
origin_airport_id=[$4], dest_airport_id=[$5], crs_dep_time=[$6],
dep_time=[$7], dep_delay=[$8], taxi_out=[$9], wheels_off=[$10],
wheels_on=[$11], taxi_in=[$12], arr_time=[$13], arr_delay=[$14],
air_time=[$15], distance=[$16]): rowcount = 100.0, cumulative cost = {100.0
rows, 1700.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18601
LogicalProject(subset=[rel#18600:Subset#1.NONE.ANY([]).[]],
EXPR$0=[ITEM($1, 0)], EXPR$1=[ITEM($1, 1)], EXPR$2=[ITEM($1, 2)],
EXPR$3=[ITEM($1, 3)], EXPR$4=[ITEM($1, 4)], EXPR$5=[ITEM($1, 5)],
EXPR$6=[ITEM($1, 6)], EXPR$7=[ITEM($1, 7)], EXPR$8=[ITEM($1, 8)],
EXPR$9=[ITEM($1, 9)], EXPR$10=[ITEM($1, 10)], EXPR$11=[ITEM($1, 11)],
EXPR$12=[ITEM($1, 12)], EXPR$13=[ITEM($1, 13)], EXPR$14=[ITEM($1, 14)],
EXPR$15=[ITEM($1, 15)], EXPR$16=[ITEM($1, 16)]): rowcount = 100.0,
cumulative cost = {100.0 rows, 1700.0 cpu, 0.0 io, 0.0 network, 0.0
memory}, id = 18599
EnumerableTableScan(subset=[rel#18598:Subset#0.ENUMERABLE.ANY([]).[]],
table=[[dfs, flt, flights-2018-*.csv]]): rowcount = 100.0, cumulative cost
= {100.0 rows, 101.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 18507
Sets: Set#0, type: RecordType(DYNAMIC_STAR **, ANY columns)
rel#18598:Subset#0.ENUMERABLE.ANY([]).[], best=rel#18507,
importance=0.4304672100000001
rel#18507:EnumerableTableScan.ENUMERABLE.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv]), rowcount=100.0, cumulative cost={100.0 rows, 101.0
cpu, 0.0 io, 0.0 network, 0.0 memory}
rel#18644:Subset#0.LOGICAL.ANY([]).[], best=rel#18653,
importance=0.28936962450000003
rel#18653:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12, columns=[`**`,
`columns`], files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8.370632E10 cpu, 0.0 io, 0.0 network, 0.0
memory} Set#1, type: RecordType(ANY EXPR$0, ANY EXPR$1, ANY EXPR$2, ANY
EXPR$3, ANY EXPR$4, ANY EXPR$5, ANY EXPR$6, ANY EXPR$7, ANY EXPR$8, ANY
EXPR$9, ANY EXPR$10, ANY EXPR$11, ANY EXPR$12, ANY EXPR$13, ANY EXPR$14,
ANY EXPR$15, ANY EXPR$16) rel#18600:Subset#1.NONE.ANY([]).[], best=null,
importance=0.4782969000000001
rel#18599:LogicalProject.NONE.ANY([]).[](input=rel#18598:Subset#0.ENUMERABLE.ANY([]).[],EXPR$0=ITEM($1,
0),EXPR$1=ITEM($1, 1),EXPR$2=ITEM($1, 2),EXPR$3=ITEM($1, 3),EXPR$4=ITEM($1,
4),EXPR$5=ITEM($1, 5),EXPR$6=ITEM($1, 6),EXPR$7=ITEM($1, 7),EXPR$8=ITEM($1,
8),EXPR$9=ITEM($1, 9),EXPR$10=ITEM($1, 10),EXPR$11=ITEM($1,
11),EXPR$12=ITEM($1, 12),EXPR$13=ITEM($1, 13),EXPR$14=ITEM($1,
14),EXPR$15=ITEM($1, 15),EXPR$16=ITEM($1, 16)), rowcount=100.0, cumulative
cost={inf}
rel#18601:LogicalProject.NONE.ANY([]).[](input=rel#18600:Subset#1.NONE.ANY([]).[],fl_date=$0,op_unique_carrier=$1,tail_num=$2,op_carrier_fl_num=$3,origin_airport_id=$4,dest_airport_id=$5,crs_dep_time=$6,dep_time=$7,dep_delay=$8,taxi_out=$9,wheels_off=$10,wheels_on=$11,taxi_in=$12,arr_time=$13,arr_delay=$14,air_time=$15,distance=$16),
rowcount=100.0, cumulative cost={inf}
rel#18636:Subset#1.LOGICAL.ANY([]).[], best=rel#18649,
importance=0.28936962450000003
rel#18649:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18648:Subset#10.LOGICAL.ANY([]).[],EXPR$0=ITEM($0,
0),EXPR$1=ITEM($0, 1),EXPR$2=ITEM($0, 2),EXPR$3=ITEM($0, 3),EXPR$4=ITEM($0,
4),EXPR$5=ITEM($0, 5),EXPR$6=ITEM($0, 6),EXPR$7=ITEM($0, 7),EXPR$8=ITEM($0,
8),EXPR$9=ITEM($0, 9),EXPR$10=ITEM($0, 10),EXPR$11=ITEM($0,
11),EXPR$12=ITEM($0, 12),EXPR$13=ITEM($0, 13),EXPR$14=ITEM($0,
14),EXPR$15=ITEM($0, 15),EXPR$16=ITEM($0, 16)), rowcount=8370632.0,
cumulative cost={1.6741264E7 rows, 5.77573608E8 cpu, 0.0 io, 0.0 network,
0.0 memory}
rel#18650:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18644:Subset#0.LOGICAL.ANY([]).[],EXPR$0=ITEM($1,
0),EXPR$1=ITEM($1, 1),EXPR$2=ITEM($1, 2),EXPR$3=ITEM($1, 3),EXPR$4=ITEM($1,
4),EXPR$5=ITEM($1, 5),EXPR$6=ITEM($1, 6),EXPR$7=ITEM($1, 7),EXPR$8=ITEM($1,
8),EXPR$9=ITEM($1, 9),EXPR$10=ITEM($1, 10),EXPR$11=ITEM($1,
11),EXPR$12=ITEM($1, 12),EXPR$13=ITEM($1, 13),EXPR$14=ITEM($1,
14),EXPR$15=ITEM($1, 15),EXPR$16=ITEM($1, 16)), rowcount=8370632.0,
cumulative cost={1.6741264E7 rows, 8.4275522976E10 cpu, 0.0 io, 0.0
network, 0.0 memory}
rel#18651:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18636:Subset#1.LOGICAL.ANY([]).[],fl_date=$0,op_unique_carrier=$1,tail_num=$2,op_carrier_fl_num=$3,origin_airport_id=$4,dest_airport_id=$5,crs_dep_time=$6,dep_time=$7,dep_delay=$8,taxi_out=$9,wheels_off=$10,wheels_on=$11,taxi_in=$12,arr_time=$13,arr_delay=$14,air_time=$15,distance=$16),
rowcount=8370632.0, cumulative cost={2.5111896E7 rows, 7.19874352E8 cpu,
0.0 io, 0.0 network, 0.0 memory} Set#3, type: RecordType(ANY
dest_airport_id, INTEGER $f1) rel#18604:Subset#3.NONE.ANY([]).[],
best=null, importance=0.531441
rel#18634:LogicalProject.NONE.ANY([]).[](input=rel#18600:Subset#1.NONE.ANY([]).[],dest_airport_id=$5,$f1=1),
rowcount=100.0, cumulative cost={inf}
rel#18638:LogicalProject.NONE.ANY([]).[](input=rel#18598:Subset#0.ENUMERABLE.ANY([]).[],dest_airport_id=ITEM($1,
5),$f1=1), rowcount=100.0, cumulative cost={inf}
rel#18630:Subset#3.LOGICAL.ANY([]).[], best=rel#18643, importance=0.2657205
rel#18637:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18636:Subset#1.LOGICAL.ANY([]).[],dest_airport_id=$5,$f1=1),
rowcount=8370632.0, cumulative cost={2.5111896E7 rows, 6.19426768E8 cpu,
0.0 io, 0.0 network, 0.0 memory}
rel#18643:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18642:Subset#9.LOGICAL.ANY([]).[],dest_airport_id=ITEM($0,
5),$f1=1), rowcount=8370632.0, cumulative cost={1.6741264E7 rows,
7.5335688E7 cpu, 0.0 io, 0.0 network, 0.0 memory}
rel#18645:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18644:Subset#0.LOGICAL.ANY([]).[],dest_airport_id=ITEM($1,
5),$f1=1), rowcount=8370632.0, cumulative cost={1.6741264E7 rows,
8.3773285056E10 cpu, 0.0 io, 0.0 network, 0.0 memory} Set#4, type:
RecordType(ANY dest_airport_id, BIGINT cnt)
rel#18606:Subset#4.NONE.ANY([]).[], best=null,
importance=0.5904900000000001
rel#18605:LogicalAggregate.NONE.ANY([]).[](input=rel#18604:Subset#3.NONE.ANY([]).[],group={0},cnt=COUNT($1)),
rowcount=10.0, cumulative cost={inf} rel#18628:Subset#4.LOGICAL.ANY([]).[],
best=rel#18631, importance=0.29524500000000004
rel#18631:DrillAggregateRel.LOGICAL.ANY([]).[](input=rel#18630:Subset#3.LOGICAL.ANY([]).[],group={0},cnt=COUNT($1)),
rowcount=8370581.997901573, cumulative cost={2.5111896E7 rows, 2.42748328E8
cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory} Set#5, type:
RecordType(BIGINT cnt) rel#18608:Subset#5.NONE.ANY([]).[], best=null,
importance=0.6561
rel#18607:LogicalProject.NONE.ANY([]).[](input=rel#18606:Subset#4.NONE.ANY([]).[],cnt=$1),
rowcount=10.0, cumulative cost={inf} rel#18609:LogicalSort.NONE.ANY([]).[0
DESC](input=rel#18608:Subset#5.NONE.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=10.0, cumulative cost={inf} rel#18621:Subset#5.LOGICAL.ANY([]).[],
best=rel#18629, importance=0.405 rel#18622:DrillSortRel.LOGICAL.ANY([]).[0
DESC](input=rel#18621:Subset#5.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=8370581.997901573, cumulative cost={4.185305999580315E7 rows,
7.848350521732183E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
rel#18629:DrillProjectRel.LOGICAL.ANY([]).[](input=rel#18628:Subset#4.LOGICAL.ANY([]).[],cnt=$1),
rowcount=8370581.997901573, cumulative cost={3.3482477997901574E7 rows,
2.5111890999790156E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
rel#18624:Subset#5.NONE.ANY([]).[0 DESC], best=null,
importance=0.7290000000000001 rel#18609:LogicalSort.NONE.ANY([]).[0
DESC](input=rel#18608:Subset#5.NONE.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=10.0, cumulative cost={inf} rel#18625:Subset#5.LOGICAL.ANY([]).[1
DESC], best=null, importance=0.81 rel#18626:Subset#5.LOGICAL.ANY([]).[0
DESC], best=rel#18622, importance=0.405
rel#18622:DrillSortRel.LOGICAL.ANY([]).[0
DESC](input=rel#18621:Subset#5.LOGICAL.ANY([]).[],sort0=$0,dir0=DESC),
rowcount=8370581.997901573, cumulative cost={4.185305999580315E7 rows,
7.848350521732183E8 cpu, 0.0 io, 0.0 network, 1.4732312320000002E8 memory}
Set#7, type: RecordType(BIGINT cnt, BIGINT w0$o0)
rel#18612:Subset#7.NONE.ANY([]).[1 DESC], best=null, importance=0.81
rel#18611:LogicalWindow.NONE.ANY([]).[[1
DESC]](input=rel#18624:Subset#5.NONE.ANY([]).[0
DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=10.0, cumulative
cost={inf} rel#18617:Subset#7.LOGICAL.ANY([]).[1 DESC], best=null,
importance=0.9 rel#18620:DrillWindowRel.LOGICAL.ANY([]).[1
DESC](input=rel#18625:Subset#5.LOGICAL.ANY([]).[1
DESC],window#0=window(partition {} order by [0 DESC] rows between UNBOUNDED
PRECEDING and CURRENT ROW aggs [ROW_NUMBER()])), rowcount=10.0, cumulative
cost={inf} Set#8, type: RecordType(BIGINT r, BIGINT cnt)
rel#18614:Subset#8.NONE.ANY([]).[1 DESC], best=null, importance=0.9
rel#18613:LogicalProject.NONE.ANY([]).[[1
DESC]](input=rel#18612:Subset#7.NONE.ANY([]).[1 DESC],r=$1,cnt=$0),
rowcount=10.0, cumulative cost={inf} rel#18615:Subset#8.LOGICAL.ANY([]).[1
DESC], best=null, importance=1.0
rel#18616:AbstractConverter.LOGICAL.ANY([]).[1
DESC](input=rel#18614:Subset#8.NONE.ANY([]).[1
DESC],convention=LOGICAL,DrillDistributionTraitDef=ANY([]),sort=[1 DESC]),
rowcount=10.0, cumulative cost={inf}
rel#18618:DrillProjectRel.LOGICAL.ANY([]).[[1
DESC]](input=rel#18617:Subset#7.LOGICAL.ANY([]).[1 DESC],r=$1,cnt=$0),
rowcount=10.0, cumulative cost={inf} Set#9, type: RecordType(ANY columns)
rel#18642:Subset#9.LOGICAL.ANY([]).[], best=rel#18640,
importance=0.05314410000000001
rel#18640:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12,
columns=[`columns`[5]],
files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8370632.0 cpu, 0.0 io, 0.0 network, 0.0
memory} Set#10, type: RecordType(ANY columns)
rel#18648:Subset#10.LOGICAL.ANY([]).[], best=rel#18646,
importance=0.007410233661971832
rel#18646:DrillScanRel.LOGICAL.ANY([]).[](table=[dfs, flt,
flights-2018-*.csv],groupscan=EasyGroupScan
[selectionRoot=maprfs:/mapr/61-demo/flights, numFiles=12,
columns=[`columns`[0], `columns`[1], `columns`[2], `columns`[3],
`columns`[4], `columns`[5], `columns`[6], `columns`[7], `columns`[8],
`columns`[9], `columns`[10], `columns`[11], `columns`[12], `columns`[13],
`columns`[14], `columns`[15], `columns`[16]],
files=[maprfs:/mapr/61-demo/flights/flights-2018-01.csv,
maprfs:/mapr/61-demo/flights/flights-2018-02.csv,
maprfs:/mapr/61-demo/flights/flights-2018-03.csv,
maprfs:/mapr/61-demo/flights/flights-2018-04.csv,
maprfs:/mapr/61-demo/flights/flights-2018-05.csv,
maprfs:/mapr/61-demo/flights/flights-2018-06.csv,
maprfs:/mapr/61-demo/flights/flights-2018-07.csv,
maprfs:/mapr/61-demo/flights/flights-2018-08.csv,
maprfs:/mapr/61-demo/flights/flights-2018-09.csv,
maprfs:/mapr/61-demo/flights/flights-2018-10.csv,
maprfs:/mapr/61-demo/flights/flights-2018-11.csv,
maprfs:/mapr/61-demo/flights/flights-2018-12.csv]]), rowcount=8370632.0,
cumulative cost={8370632.0 rows, 8370632.0 cpu, 0.0 io, 0.0 network, 0.0
memory} [Error Id: e05bca25-7110-47aa-b872-60aa1b2fdc34 on
mdn-1.mdn.tdunning-dsr-demo-z6io48.svc.cluster.local:31010]. ... 21 more

Reply via email to