drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Krystal (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DRILL-867) tpcds queries 6, 8 and 9 fail to plan
Date Fri, 30 May 2014 17:55:02 GMT

    [ https://issues.apache.org/jira/browse/DRILL-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14013897#comment-14013897
] 

Krystal edited comment on DRILL-867 at 5/30/14 5:53 PM:
--------------------------------------------------------

Query 6:
{code}
message: "Failure while parsing sql. < CannotPlanException:[ Node [rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]]
could not be implemented; planner state:

Root: rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]
Original rel:
AbstractConverter(subset=[rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]], convention=[PHYSICAL],
DrillDistributionTraitDef=[SINGLETON([])], sort=[[1]]): rowcount = 7.448439477042089E47, cumulative
cost = {inf}, id = 17370
  DrillScreenRel(subset=[rel#17367:Subset#237.LOGICAL.ANY([]).[1]]): rowcount = 7.448439477042089E47,
cumulative cost = {7.448439477042089E46 rows, 7.448439477042089E46 cpu, 0.0 io, 0.0 network},
id = 17366
    DrillLimitRel(subset=[rel#17365:Subset#236.LOGICAL.ANY([]).[1]], fetch=[100]): rowcount
= 7.448439477042089E47, cumulative cost = {100.0 rows, 400.0 cpu, 0.0 io, 0.0 network}, id
= 17364
      DrillSortRel(subset=[rel#17363:Subset#235.LOGICAL.ANY([]).[1]], sort0=[$1], dir0=[ASC]):
rowcount = 7.448439477042089E47, cumulative cost = {6.5683023079621116E50 rows, 7.448439477042089E47
cpu, 0.0 io, 0.0 network}, id = 17362
        DrillFilterRel(subset=[rel#17361:Subset#234.LOGICAL.ANY([]).[]], condition=[>=($1,
10)]): rowcount = 7.448439477042089E47, cumulative cost = {1.4896878954084178E48 rows, 5.958751581633671E48
cpu, 0.0 io, 0.0 network}, id = 17360
          DrillAggregateRel(subset=[rel#17359:Subset#233.LOGICAL.ANY([]).[]], group=[{0}],
cnt=[COUNT()]): rowcount = 1.4896878954084178E48, cumulative cost = {1.4896878954084178E48
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17358
            DrillProjectRel(subset=[rel#17357:Subset#232.LOGICAL.ANY([]).[]], state=[$0]):
rowcount = 1.4896878954084178E49, cumulative cost = {1.4896878954084178E49 rows, 4.0 cpu,
0.0 io, 0.0 network}, id = 17356
              DrillFilterRel(subset=[rel#17355:Subset#231.LOGICAL.ANY([]).[]], condition=[>($1,
*(1.2, $2))]): rowcount = 1.4896878954084178E49, cumulative cost = {2.9793757908168356E49
rows, 1.1917503163267342E50 cpu, 0.0 io, 0.0 network}, id = 17354
                DrillProjectRel(subset=[rel#17353:Subset#230.LOGICAL.ANY([]).[]], ca_state=[$0],
i_current_price=[$1], EXPR$0=[$4]): rowcount = 2.9793757908168356E49, cumulative cost = {2.9793757908168356E49
rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17352
                  DrillJoinRel(subset=[rel#17351:Subset#229.LOGICAL.ANY([]).[]], condition=[=($2,
$3)], joinType=[left]): rowcount = 2.9793757908168356E49, cumulative cost = {2.9793757908168356E49
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17350
                    DrillProjectRel(subset=[rel#17325:Subset#218.LOGICAL.ANY([]).[]], ca_state=[$0],
i_current_price=[$9], i_category=[$10]): rowcount = 2.363541750801446E22, cumulative cost
= {2.363541750801446E22 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17324
                      DrillFilterRel(subset=[rel#17323:Subset#217.LOGICAL.ANY([]).[]], condition=[=($7,
$12)]): rowcount = 2.363541750801446E22, cumulative cost = {1.5756945005342976E23 rows, 6.30277800213719E23
cpu, 0.0 io, 0.0 network}, id = 17322
                        DrillJoinRel(subset=[rel#17321:Subset#216.LOGICAL.ANY([]).[]], condition=[true],
joinType=[left]): rowcount = 1.5756945005342976E23, cumulative cost = {1.5756945005342976E23
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17320
                          DrillJoinRel(subset=[rel#17311:Subset#211.LOGICAL.ANY([]).[]], condition=[=($5,
$11)], joinType=[inner]): rowcount = 9.586834411205249E21, cumulative cost = {9.586834411205249E21
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17310
                            DrillJoinRel(subset=[rel#17306:Subset#208.LOGICAL.ANY([]).[]],
condition=[=($4, $8)], joinType=[inner]): rowcount = 3.5506794115574999E18, cumulative cost
= {3.5506794115574999E18 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17305
                              DrillJoinRel(subset=[rel#17301:Subset#205.LOGICAL.ANY([]).[]],
condition=[=($2, $6)], joinType=[inner]): rowcount = 3.2404545E14, cumulative cost = {3.2404545E14
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17300
                                DrillJoinRel(subset=[rel#17296:Subset#202.LOGICAL.ANY([]).[]],
condition=[=($1, $3)], joinType=[inner]): rowcount = 7.5E8, cumulative cost = {7.5E8 rows,
0.0 cpu, 0.0 io, 0.0 network}, id = 17295
                                  DrillProjectRel(subset=[rel#17291:Subset#199.LOGICAL.ANY([]).[]],
ca_state=[$1], ca_address_sk=[$2]): rowcount = 50000.0, cumulative cost = {50000.0 rows, 8.0
cpu, 0.0 io, 0.0 network}, id = 17290
                                    DrillScanRel(subset=[rel#17289:Subset#198.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer_address]]): rowcount = 50000.0, cumulative cost = {50000.0 rows,
150000.0 cpu, 0.0 io, 0.0 network}, id = 13326
                                  DrillProjectRel(subset=[rel#17294:Subset#201.LOGICAL.ANY([]).[]],
c_customer_sk=[$1], c_current_addr_sk=[$2]): rowcount = 100000.0, cumulative cost = {100000.0
rows, 8.0 cpu, 0.0 io, 0.0 network}, id = 17293
                                    DrillScanRel(subset=[rel#17292:Subset#200.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer]]): rowcount = 100000.0, cumulative cost = {100000.0 rows, 300000.0
cpu, 0.0 io, 0.0 network}, id = 13320
                                DrillProjectRel(subset=[rel#17299:Subset#204.LOGICAL.ANY([]).[]],
ss_sold_date_sk=[$1], ss_item_sk=[$2], ss_customer_sk=[$3]): rowcount = 2880404.0, cumulative
cost = {2880404.0 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17298
                                  DrillScanRel(subset=[rel#17297:Subset#203.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 13148
                              DrillProjectRel(subset=[rel#17304:Subset#207.LOGICAL.ANY([]).[]],
d_month_seq=[$1], d_date_sk=[$4]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 8.0
cpu, 0.0 io, 0.0 network}, id = 17303
                                DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                            DrillProjectRel(subset=[rel#17309:Subset#210.LOGICAL.ANY([]).[]],
i_current_price=[$1], i_category=[$2], i_item_sk=[$3]): rowcount = 18000.0, cumulative cost
= {18000.0 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17308
                              DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0
cpu, 0.0 io, 0.0 network}, id = 12746
                          DrillAggregateRel(subset=[rel#17319:Subset#215.LOGICAL.ANY([]).[]],
group=[{}], agg#0=[SINGLE_VALUE($0)]): rowcount = 16.436025, cumulative cost = {16.436025
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17318
                            DrillAggregateRel(subset=[rel#17317:Subset#214.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 164.36025, cumulative cost = {164.36025 rows, 0.0 cpu, 0.0 io, 0.0
network}, id = 17316
                              DrillProjectRel(subset=[rel#17315:Subset#213.LOGICAL.ANY([]).[]],
d_month_seq=[$1]): rowcount = 1643.6025, cumulative cost = {1643.6025 rows, 4.0 cpu, 0.0 io,
0.0 network}, id = 17314
                                DrillFilterRel(subset=[rel#17313:Subset#212.LOGICAL.ANY([]).[]],
condition=[AND(=(CAST($2):INTEGER, 1998), =(CAST($3):INTEGER, 5))]): rowcount = 1643.6025,
cumulative cost = {73049.0 rows, 584392.0 cpu, 0.0 io, 0.0 network}, id = 17312
                                  DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                    DrillAggregateRel(subset=[rel#17349:Subset#228.LOGICAL.ANY([]).[]], group=[{0}],
EXPR$0=[AVG($1)]): rowcount = 8.403704002849589E27, cumulative cost = {8.403704002849589E27
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17348
                      DrillProjectRel(subset=[rel#17347:Subset#227.LOGICAL.ANY([]).[]], $f0=[$4],
i_current_price=[$1]): rowcount = 8.403704002849588E28, cumulative cost = {8.403704002849588E28
rows, 8.0 cpu, 0.0 io, 0.0 network}, id = 17346
                        DrillJoinRel(subset=[rel#17345:Subset#226.LOGICAL.ANY([]).[]], condition=[=($2,
$4)], joinType=[inner]): rowcount = 8.403704002849588E28, cumulative cost = {8.403704002849588E28
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17344
                          DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]], table=[[dfs,
tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0 cpu, 0.0 io,
0.0 network}, id = 12746
                          DrillAggregateRel(subset=[rel#17343:Subset#225.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 3.1124829640183664E25, cumulative cost = {3.1124829640183664E25 rows,
0.0 cpu, 0.0 io, 0.0 network}, id = 17342
                            DrillProjectRel(subset=[rel#17341:Subset#224.LOGICAL.ANY([]).[]],
$f0=[$17]): rowcount = 3.112482964018366E26, cumulative cost = {3.112482964018366E26 rows,
4.0 cpu, 0.0 io, 0.0 network}, id = 17340
                              DrillJoinRel(subset=[rel#17339:Subset#223.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 3.112482964018366E26, cumulative cost = {3.112482964018366E26
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17338
                                DrillJoinRel(subset=[rel#17333:Subset#222.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.8936956861640001E25, cumulative cost = {1.8936956861640001E25
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17332
                                  DrillJoinRel(subset=[rel#17331:Subset#221.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.05205315898E21, cumulative cost = {1.05205315898E21
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17330
                                    DrillJoinRel(subset=[rel#17329:Subset#220.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.440202E16, cumulative cost = {1.440202E16
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17328
                                      DrillJoinRel(subset=[rel#17327:Subset#219.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 5.0E9, cumulative cost = {5.0E9 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 17326
                                        DrillScanRel(subset=[rel#17289:Subset#198.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer_address]]): rowcount = 50000.0, cumulative cost = {50000.0 rows,
150000.0 cpu, 0.0 io, 0.0 network}, id = 13326
                                        DrillScanRel(subset=[rel#17292:Subset#200.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer]]): rowcount = 100000.0, cumulative cost = {100000.0 rows, 300000.0
cpu, 0.0 io, 0.0 network}, id = 13320
                                      DrillScanRel(subset=[rel#17297:Subset#203.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 13148
                                    DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                                  DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0
cpu, 0.0 io, 0.0 network}, id = 12746
                                DrillAggregateRel(subset=[rel#17319:Subset#215.LOGICAL.ANY([]).[]],
group=[{}], agg#0=[SINGLE_VALUE($0)]): rowcount = 16.436025, cumulative cost = {16.436025
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17318
                                  DrillAggregateRel(subset=[rel#17317:Subset#214.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 164.36025, cumulative cost = {164.36025 rows, 0.0 cpu, 0.0 io, 0.0
network}, id = 17316
                                    DrillProjectRel(subset=[rel#17315:Subset#213.LOGICAL.ANY([]).[]],
d_month_seq=[$1]): rowcount = 1643.6025, cumulative cost = {1643.6025 rows, 4.0 cpu, 0.0 io,
0.0 network}, id = 17314
                                      DrillFilterRel(subset=[rel#17313:Subset#212.LOGICAL.ANY([]).[]],
condition=[AND(=(CAST($2):INTEGER, 1998), =(CAST($3):INTEGER, 5))]): rowcount = 1643.6025,
cumulative cost = {73049.0 rows, 584392.0 cpu, 0.0 io, 0.0 network}, id = 17312
                                        DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
{code}


was (Author: knguyen):
{code}
message: "Failure while parsing sql. < CannotPlanException:[ Node [rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]]
could not be implemented; planner state:

Root: rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]
Original rel:
AbstractConverter(subset=[rel#17368:Subset#237.PHYSICAL.SINGLETON([]).[1]], convention=[PHYSICAL],
DrillDistributionTraitDef=[SINGLETON([])], sort=[[1]]): rowcount = 7.448439477042089E47, cumulative
cost = {inf}, id = 17370
  DrillScreenRel(subset=[rel#17367:Subset#237.LOGICAL.ANY([]).[1]]): rowcount = 7.448439477042089E47,
cumulative cost = {7.448439477042089E46 rows, 7.448439477042089E46 cpu, 0.0 io, 0.0 network},
id = 17366
    DrillLimitRel(subset=[rel#17365:Subset#236.LOGICAL.ANY([]).[1]], fetch=[100]): rowcount
= 7.448439477042089E47, cumulative cost = {100.0 rows, 400.0 cpu, 0.0 io, 0.0 network}, id
= 17364
      DrillSortRel(subset=[rel#17363:Subset#235.LOGICAL.ANY([]).[1]], sort0=[$1], dir0=[ASC]):
rowcount = 7.448439477042089E47, cumulative cost = {6.5683023079621116E50 rows, 7.448439477042089E47
cpu, 0.0 io, 0.0 network}, id = 17362
        DrillFilterRel(subset=[rel#17361:Subset#234.LOGICAL.ANY([]).[]], condition=[>=($1,
10)]): rowcount = 7.448439477042089E47, cumulative cost = {1.4896878954084178E48 rows, 5.958751581633671E48
cpu, 0.0 io, 0.0 network}, id = 17360
          DrillAggregateRel(subset=[rel#17359:Subset#233.LOGICAL.ANY([]).[]], group=[{0}],
cnt=[COUNT()]): rowcount = 1.4896878954084178E48, cumulative cost = {1.4896878954084178E48
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17358
            DrillProjectRel(subset=[rel#17357:Subset#232.LOGICAL.ANY([]).[]], state=[$0]):
rowcount = 1.4896878954084178E49, cumulative cost = {1.4896878954084178E49 rows, 4.0 cpu,
0.0 io, 0.0 network}, id = 17356
              DrillFilterRel(subset=[rel#17355:Subset#231.LOGICAL.ANY([]).[]], condition=[>($1,
*(1.2, $2))]): rowcount = 1.4896878954084178E49, cumulative cost = {2.9793757908168356E49
rows, 1.1917503163267342E50 cpu, 0.0 io, 0.0 network}, id = 17354
                DrillProjectRel(subset=[rel#17353:Subset#230.LOGICAL.ANY([]).[]], ca_state=[$0],
i_current_price=[$1], EXPR$0=[$4]): rowcount = 2.9793757908168356E49, cumulative cost = {2.9793757908168356E49
rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17352
                  DrillJoinRel(subset=[rel#17351:Subset#229.LOGICAL.ANY([]).[]], condition=[=($2,
$3)], joinType=[left]): rowcount = 2.9793757908168356E49, cumulative cost = {2.9793757908168356E49
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17350
                    DrillProjectRel(subset=[rel#17325:Subset#218.LOGICAL.ANY([]).[]], ca_state=[$0],
i_current_price=[$9], i_category=[$10]): rowcount = 2.363541750801446E22, cumulative cost
= {2.363541750801446E22 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17324
                      DrillFilterRel(subset=[rel#17323:Subset#217.LOGICAL.ANY([]).[]], condition=[=($7,
$12)]): rowcount = 2.363541750801446E22, cumulative cost = {1.5756945005342976E23 rows, 6.30277800213719E23
cpu, 0.0 io, 0.0 network}, id = 17322
                        DrillJoinRel(subset=[rel#17321:Subset#216.LOGICAL.ANY([]).[]], condition=[true],
joinType=[left]): rowcount = 1.5756945005342976E23, cumulative cost = {1.5756945005342976E23
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17320
                          DrillJoinRel(subset=[rel#17311:Subset#211.LOGICAL.ANY([]).[]], condition=[=($5,
$11)], joinType=[inner]): rowcount = 9.586834411205249E21, cumulative cost = {9.586834411205249E21
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17310
                            DrillJoinRel(subset=[rel#17306:Subset#208.LOGICAL.ANY([]).[]],
condition=[=($4, $8)], joinType=[inner]): rowcount = 3.5506794115574999E18, cumulative cost
= {3.5506794115574999E18 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17305
                              DrillJoinRel(subset=[rel#17301:Subset#205.LOGICAL.ANY([]).[]],
condition=[=($2, $6)], joinType=[inner]): rowcount = 3.2404545E14, cumulative cost = {3.2404545E14
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17300
                                DrillJoinRel(subset=[rel#17296:Subset#202.LOGICAL.ANY([]).[]],
condition=[=($1, $3)], joinType=[inner]): rowcount = 7.5E8, cumulative cost = {7.5E8 rows,
0.0 cpu, 0.0 io, 0.0 network}, id = 17295
                                  DrillProjectRel(subset=[rel#17291:Subset#199.LOGICAL.ANY([]).[]],
ca_state=[$1], ca_address_sk=[$2]): rowcount = 50000.0, cumulative cost = {50000.0 rows, 8.0
cpu, 0.0 io, 0.0 network}, id = 17290
                                    DrillScanRel(subset=[rel#17289:Subset#198.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer_address]]): rowcount = 50000.0, cumulative cost = {50000.0 rows,
150000.0 cpu, 0.0 io, 0.0 network}, id = 13326
                                  DrillProjectRel(subset=[rel#17294:Subset#201.LOGICAL.ANY([]).[]],
c_customer_sk=[$1], c_current_addr_sk=[$2]): rowcount = 100000.0, cumulative cost = {100000.0
rows, 8.0 cpu, 0.0 io, 0.0 network}, id = 17293
                                    DrillScanRel(subset=[rel#17292:Subset#200.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer]]): rowcount = 100000.0, cumulative cost = {100000.0 rows, 300000.0
cpu, 0.0 io, 0.0 network}, id = 13320
                                DrillProjectRel(subset=[rel#17299:Subset#204.LOGICAL.ANY([]).[]],
ss_sold_date_sk=[$1], ss_item_sk=[$2], ss_customer_sk=[$3]): rowcount = 2880404.0, cumulative
cost = {2880404.0 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17298
                                  DrillScanRel(subset=[rel#17297:Subset#203.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 13148
                              DrillProjectRel(subset=[rel#17304:Subset#207.LOGICAL.ANY([]).[]],
d_month_seq=[$1], d_date_sk=[$4]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 8.0
cpu, 0.0 io, 0.0 network}, id = 17303
                                DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                            DrillProjectRel(subset=[rel#17309:Subset#210.LOGICAL.ANY([]).[]],
i_current_price=[$1], i_category=[$2], i_item_sk=[$3]): rowcount = 18000.0, cumulative cost
= {18000.0 rows, 12.0 cpu, 0.0 io, 0.0 network}, id = 17308
                              DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0
cpu, 0.0 io, 0.0 network}, id = 12746
                          DrillAggregateRel(subset=[rel#17319:Subset#215.LOGICAL.ANY([]).[]],
group=[{}], agg#0=[SINGLE_VALUE($0)]): rowcount = 16.436025, cumulative cost = {16.436025
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17318
                            DrillAggregateRel(subset=[rel#17317:Subset#214.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 164.36025, cumulative cost = {164.36025 rows, 0.0 cpu, 0.0 io, 0.0
network}, id = 17316
                              DrillProjectRel(subset=[rel#17315:Subset#213.LOGICAL.ANY([]).[]],
d_month_seq=[$1]): rowcount = 1643.6025, cumulative cost = {1643.6025 rows, 4.0 cpu, 0.0 io,
0.0 network}, id = 17314
                                DrillFilterRel(subset=[rel#17313:Subset#212.LOGICAL.ANY([]).[]],
condition=[AND(=(CAST($2):INTEGER, 1998), =(CAST($3):INTEGER, 5))]): rowcount = 1643.6025,
cumulative cost = {73049.0 rows, 584392.0 cpu, 0.0 io, 0.0 network}, id = 17312
                                  DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                    DrillAggregateRel(subset=[rel#17349:Subset#228.LOGICAL.ANY([]).[]], group=[{0}],
EXPR$0=[AVG($1)]): rowcount = 8.403704002849589E27, cumulative cost = {8.403704002849589E27
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17348
                      DrillProjectRel(subset=[rel#17347:Subset#227.LOGICAL.ANY([]).[]], $f0=[$4],
i_current_price=[$1]): rowcount = 8.403704002849588E28, cumulative cost = {8.403704002849588E28
rows, 8.0 cpu, 0.0 io, 0.0 network}, id = 17346
                        DrillJoinRel(subset=[rel#17345:Subset#226.LOGICAL.ANY([]).[]], condition=[=($2,
$4)], joinType=[inner]): rowcount = 8.403704002849588E28, cumulative cost = {8.403704002849588E28
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17344
                          DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]], table=[[dfs,
tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0 cpu, 0.0 io,
0.0 network}, id = 12746
                          DrillAggregateRel(subset=[rel#17343:Subset#225.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 3.1124829640183664E25, cumulative cost = {3.1124829640183664E25 rows,
0.0 cpu, 0.0 io, 0.0 network}, id = 17342
                            DrillProjectRel(subset=[rel#17341:Subset#224.LOGICAL.ANY([]).[]],
$f0=[$17]): rowcount = 3.112482964018366E26, cumulative cost = {3.112482964018366E26 rows,
4.0 cpu, 0.0 io, 0.0 network}, id = 17340
                              DrillJoinRel(subset=[rel#17339:Subset#223.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 3.112482964018366E26, cumulative cost = {3.112482964018366E26
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17338
                                DrillJoinRel(subset=[rel#17333:Subset#222.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.8936956861640001E25, cumulative cost = {1.8936956861640001E25
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17332
                                  DrillJoinRel(subset=[rel#17331:Subset#221.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.05205315898E21, cumulative cost = {1.05205315898E21
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17330
                                    DrillJoinRel(subset=[rel#17329:Subset#220.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 1.440202E16, cumulative cost = {1.440202E16
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17328
                                      DrillJoinRel(subset=[rel#17327:Subset#219.LOGICAL.ANY([]).[]],
condition=[true], joinType=[inner]): rowcount = 5.0E9, cumulative cost = {5.0E9 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 17326
                                        DrillScanRel(subset=[rel#17289:Subset#198.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer_address]]): rowcount = 50000.0, cumulative cost = {50000.0 rows,
150000.0 cpu, 0.0 io, 0.0 network}, id = 13326
                                        DrillScanRel(subset=[rel#17292:Subset#200.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, customer]]): rowcount = 100000.0, cumulative cost = {100000.0 rows, 300000.0
cpu, 0.0 io, 0.0 network}, id = 13320
                                      DrillScanRel(subset=[rel#17297:Subset#203.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, store_sales]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows,
1.1521616E7 cpu, 0.0 io, 0.0 network}, id = 13148
                                    DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
                                  DrillScanRel(subset=[rel#17307:Subset#209.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, item]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 72000.0
cpu, 0.0 io, 0.0 network}, id = 12746
                                DrillAggregateRel(subset=[rel#17319:Subset#215.LOGICAL.ANY([]).[]],
group=[{}], agg#0=[SINGLE_VALUE($0)]): rowcount = 16.436025, cumulative cost = {16.436025
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 17318
                                  DrillAggregateRel(subset=[rel#17317:Subset#214.LOGICAL.ANY([]).[]],
group=[{0}]): rowcount = 164.36025, cumulative cost = {164.36025 rows, 0.0 cpu, 0.0 io, 0.0
network}, id = 17316
                                    DrillProjectRel(subset=[rel#17315:Subset#213.LOGICAL.ANY([]).[]],
d_month_seq=[$1]): rowcount = 1643.6025, cumulative cost = {1643.6025 rows, 4.0 cpu, 0.0 io,
0.0 network}, id = 17314
                                      DrillFilterRel(subset=[rel#17313:Subset#212.LOGICAL.ANY([]).[]],
condition=[AND(=(CAST($2):INTEGER, 1998), =(CAST($3):INTEGER, 5))]): rowcount = 1643.6025,
cumulative cost = {73049.0 rows, 584392.0 cpu, 0.0 io, 0.0 network}, id = 17312
                                        DrillScanRel(subset=[rel#17302:Subset#206.LOGICAL.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 365245.0
cpu, 0.0 io, 0.0 network}, id = 12661
{code}

> tpcds queries 6, 8 and 9 fail to plan
> -------------------------------------
>
>                 Key: DRILL-867
>                 URL: https://issues.apache.org/jira/browse/DRILL-867
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: SQL Parser
>            Reporter: Krystal
>
> git.commit.id.abbrev=e1e5ea0
> git.commit.time=29.05.2014 @ 15\:32\:29 PDT
> query 6:
> {code}
> select * from (select  a.ca_state state, count(*) cnt
>  from customer_address a
>      ,customer c
>      ,store_sales s
>      ,date_dim d
>      ,item i
>  where  a.ca_address_sk = c.c_current_addr_sk
>   and c.c_customer_sk = s.ss_customer_sk
>   and s.ss_sold_date_sk = d.d_date_sk
>   and s.ss_item_sk = i.i_item_sk
>   and d.d_month_seq = 
>        (select distinct (d.d_month_seq)
>         from date_dim d
>                where d.d_year = 1998
>           and d.d_moy = 5 ) 
>   and i.i_current_price > 1.2 * 
>              (select avg(j.i_current_price)
>        from item j 
>        where j.i_category = i.i_category)
>  group by a.ca_state
>  having count(*) >= 10
>  order by cnt
>  ) limit 100;
> {code}
> query 7:



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message