Abhishek Girish created DRILL-3307:
--------------------------------------
Summary: Query with window function runs out of memory
Key: DRILL-3307
URL: https://issues.apache.org/jira/browse/DRILL-3307
Project: Apache Drill
Issue Type: Bug
Components: Query Planning & Optimization
Affects Versions: 1.1.0
Environment: Data set: TPC-DS SF 100 Parquet
Number of Nodes: 4
Reporter: Abhishek Girish
Assignee: Deneche A. Hakim
Attachments: drillbit.log.txt
Query with window function runs out of memory:
{code:sql}
SELECT SUM(ss.ss_net_paid_inc_tax) OVER (PARTITION BY ss.ss_store_sk) AS
TotalSpend FROM store_sales ss ORDER BY 1 LIMIT 20;
java.lang.RuntimeException: java.sql.SQLException: RESOURCE ERROR: One or more
nodes ran out of memory while executing the query.
Fragment 3:0
[Error Id: 9af19064-9175-46a4-b557-714d1c77cd76 on abhi6.qa.lab:31010]
at sqlline.IncrementalRows.hasNext(IncrementalRows.java:73)
at
sqlline.TableOutputFormat$ResizingRowsProvider.next(TableOutputFormat.java:85)
at sqlline.TableOutputFormat.print(TableOutputFormat.java:116)
at sqlline.SqlLine.print(SqlLine.java:1583)
at sqlline.Commands.execute(Commands.java:852)
at sqlline.Commands.sql(Commands.java:751)
at sqlline.SqlLine.dispatch(SqlLine.java:738)
at sqlline.SqlLine.begin(SqlLine.java:612)
at sqlline.SqlLine.start(SqlLine.java:366)
at sqlline.SqlLine.main(SqlLine.java:259)
{code}
Plan:
{code}
00-00 Screen : rowType = RecordType(ANY TotalSpend): rowcount =
2.87997024E8, cumulative cost = {4.3487550824E9 rows, 5.7539970079068695E10
cpu, 0.0 io, 7.077814861824E12 network, 4.607952384E9 memory}, id = 142297
00-01 SelectionVectorRemover : rowType = RecordType(ANY TotalSpend):
rowcount = 2.87997024E8, cumulative cost = {4.31995538E9 rows,
5.751117037666869E10 cpu, 0.0 io, 7.077814861824E12 network, 4.607952384E9
memory}, id = 142296
00-02 Limit(fetch=[20]) : rowType = RecordType(ANY TotalSpend): rowcount
= 2.87997024E8, cumulative cost = {4.031958356E9 rows, 5.722317335266869E10
cpu, 0.0 io, 7.077814861824E12 network, 4.607952384E9 memory}, id = 142295
00-03 SingleMergeExchange(sort0=[0 ASC]) : rowType = RecordType(ANY
TotalSpend): rowcount = 2.87997024E8, cumulative cost = {4.031958336E9 rows,
5.722317327266869E10 cpu, 0.0 io, 7.077814861824E12 network, 4.607952384E9
memory}, id = 142294
01-01 SelectionVectorRemover : rowType = RecordType(ANY TotalSpend):
rowcount = 2.87997024E8, cumulative cost = {3.743961312E9 rows,
5.261522088866869E10 cpu, 0.0 io, 5.89817905152E12 network, 4.607952384E9
memory}, id = 142293
01-02 TopN(limit=[20]) : rowType = RecordType(ANY TotalSpend):
rowcount = 2.87997024E8, cumulative cost = {3.455964288E9 rows,
5.232722386466869E10 cpu, 0.0 io, 5.89817905152E12 network, 4.607952384E9
memory}, id = 142292
01-03 Project(TotalSpend=[$0]) : rowType = RecordType(ANY
TotalSpend): rowcount = 2.87997024E8, cumulative cost = {3.167967264E9 rows,
4.734841414759049E10 cpu, 0.0 io, 5.89817905152E12 network, 4.607952384E9
memory}, id = 142291
01-04 HashToRandomExchange(dist0=[[$0]]) : rowType =
RecordType(ANY TotalSpend, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount =
2.87997024E8, cumulative cost = {3.167967264E9 rows, 4.734841414759049E10 cpu,
0.0 io, 5.89817905152E12 network, 4.607952384E9 memory}, id = 142290
02-01 UnorderedMuxExchange : rowType = RecordType(ANY
TotalSpend, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative
cost = {2.87997024E9 rows, 4.274046176359049E10 cpu, 0.0 io, 3.538907430912E12
network, 4.607952384E9 memory}, id = 142289
03-01 Project(TotalSpend=[$0],
E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($0))]) : rowType =
RecordType(ANY TotalSpend, ANY E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount =
2.87997024E8, cumulative cost = {2.591973216E9 rows, 4.245246473959049E10 cpu,
0.0 io, 3.538907430912E12 network, 4.607952384E9 memory}, id = 142288
03-02 Project(TotalSpend=[CASE(>($2, 0), CAST($3):ANY,
null)]) : rowType = RecordType(ANY TotalSpend): rowcount = 2.87997024E8,
cumulative cost = {2.303976192E9 rows, 4.130047664359049E10 cpu, 0.0 io,
3.538907430912E12 network, 4.607952384E9 memory}, id = 142287
03-03 Window(window#0=[window(partition {1} order by
[] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT($0),
$SUM0($0)])]) : rowType = RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk,
BIGINT w0$o0, ANY w0$o1): rowcount = 2.87997024E8, cumulative cost =
{2.015979168E9 rows, 4.014848854759049E10 cpu, 0.0 io, 3.538907430912E12
network, 4.607952384E9 memory}, id = 142286
03-04 SelectionVectorRemover : rowType =
RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk): rowcount = 2.87997024E8,
cumulative cost = {1.727982144E9 rows, 3.928449747559049E10 cpu, 0.0 io,
3.538907430912E12 network, 4.607952384E9 memory}, id = 142285
03-05 Sort(sort0=[$1], dir0=[ASC]) : rowType =
RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk): rowcount = 2.87997024E8,
cumulative cost = {1.43998512E9 rows, 3.899650045159049E10 cpu, 0.0 io,
3.538907430912E12 network, 4.607952384E9 memory}, id = 142284
03-06 Project(ss_net_paid_inc_tax=[$0],
ss_store_sk=[$1]) : rowType = RecordType(ANY ss_net_paid_inc_tax, ANY
ss_store_sk): rowcount = 2.87997024E8, cumulative cost = {1.151988096E9 rows,
6.623931552E9 cpu, 0.0 io, 3.538907430912E12 network, 0.0 memory}, id = 142283
03-07 HashToRandomExchange(dist0=[[$1]]) :
rowType = RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk, ANY
E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost =
{1.151988096E9 rows, 6.623931552E9 cpu, 0.0 io, 3.538907430912E12 network, 0.0
memory}, id = 142282
04-01 UnorderedMuxExchange : rowType =
RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk, ANY
E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost =
{8.63991072E8 rows, 2.015979168E9 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
142281
05-01 Project(ss_net_paid_inc_tax=[$0],
ss_store_sk=[$1], E_X_P_R_H_A_S_H_F_I_E_L_D=[castInt(hash64AsDouble($1))]) :
rowType = RecordType(ANY ss_net_paid_inc_tax, ANY ss_store_sk, ANY
E_X_P_R_H_A_S_H_F_I_E_L_D): rowcount = 2.87997024E8, cumulative cost =
{5.75994048E8 rows, 1.727982144E9 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
142280
05-02 Scan(groupscan=[ParquetGroupScan
[entries=[ReadEntryWithPath
[path=maprfs:///drill/testdata/tpcds_sf100/parquet/store_sales]],
selectionRoot=/drill/testdata/tpcds_sf100/parquet/store_sales, numFiles=1,
columns=[`ss_net_paid_inc_tax`, `ss_store_sk`]]]) : rowType = RecordType(ANY
ss_net_paid_inc_tax, ANY ss_store_sk): rowcount = 2.87997024E8, cumulative cost
= {2.87997024E8 rows, 5.75994048E8 cpu, 0.0 io, 0.0 network, 0.0 memory}, id =
142279
{code}
Log attached.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
