[
https://issues.apache.org/jira/browse/DRILL-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14014034#comment-14014034
]
Krystal commented on DRILL-867:
-------------------------------
Query 9:
{code}
select case when (select count(*)
from store_sales ss
where ss.ss_quantity between 1 and 20) > 30992
then (select avg(ss.ss_ext_sales_price)
from store_sales ss
where ss.ss_quantity between 1 and 20)
else (select avg(ss.ss_net_paid)
from store_sales ss
where ss.ss_quantity between 1 and 20) end bucket1 ,
case when (select count(*)
from store_sales ss
where ss.ss_quantity between 21 and 40) > 25740
then (select avg(ss.ss_ext_sales_price)
from store_sales ss
where ss.ss_quantity between 21 and 40)
else (select avg(ss.ss_net_paid)
from store_sales ss
where ss.ss_quantity between 21 and 40) end bucket2,
case when (select count(*)
from store_sales ss
where ss.ss_quantity between 41 and 60) > 20311
then (select avg(ss.ss_ext_sales_price)
from store_sales ss
where ss.ss_quantity between 41 and 60)
else (select avg(ss.ss_net_paid)
from store_sales ss
where ss.ss_quantity between 41 and 60) end bucket3,
case when (select count(*)
from store_sales ss
where ss.ss_quantity between 61 and 80) > 21635
then (select avg(ss.ss_ext_sales_price)
from store_sales ss
where ss.ss_quantity between 61 and 80)
else (select avg(ss.ss_net_paid)
from store_sales ss
where ss.ss_quantity between 61 and 80) end bucket4,
case when (select count(*)
from store_sales ss
where ss.ss_quantity between 81 and 100) > 20532
then (select avg(ss.ss_ext_sales_price)
from store_sales ss
where ss.ss_quantity between 81 and 100)
else (select avg(ss.ss_net_paid)
from store_sales ss
where ss.ss_quantity between 81 and 100) end bucket5
from reason
where reason.r_reason_sk = 1
;
"message: ""Failure while parsing sql. < CannotPlanException:[ Node
[rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[]] could not be implemented;
planner state:
Root: rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[]
Original rel:
AbstractConverter(subset=[rel#40624:Subset#192.PHYSICAL.SINGLETON([]).[]],
convention=[PHYSICAL], DrillDistributionTraitDef=[SINGLETON([])], sort=[[]]):
rowcount = 3.811189240726212E73, cumulative cost = {inf}, id = 40626
DrillScreenRel(subset=[rel#40623:Subset#192.LOGICAL.ANY([]).[]]): rowcount =
3.811189240726212E73, cumulative cost = {3.811189240726212E72 rows,
3.811189240726212E72 cpu, 0.0 io, 0.0 network}, id = 40622
DrillProjectRel(subset=[rel#40621:Subset#191.LOGICAL.ANY([]).[]],
bucket1=[CASE(>($2, 30992), $3, $4)], bucket2=[CASE(>($5, 25740), $6, $7)],
bucket3=[CASE(>($8, 20311), $9, $10)], bucket4=[CASE(>($11, 21635), $12, $13)],
bucket5=[CASE(>($14, 20532), $15, $16)]): rowcount = 3.811189240726212E73,
cumulative cost = {3.811189240726212E73 rows, 20.0 cpu, 0.0 io, 0.0 network},
id = 40620
DrillJoinRel(subset=[rel#40619:Subset#190.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 3.811189240726212E73, cumulative
cost = {3.811189240726212E73 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40618
DrillJoinRel(subset=[rel#40612:Subset#187.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 5.292575959103254E68, cumulative
cost = {5.292575959103254E68 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40611
DrillJoinRel(subset=[rel#40605:Subset#184.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 7.349768933945729E63, cumulative
cost = {7.349768933945729E63 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40604
DrillJoinRel(subset=[rel#40597:Subset#180.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.020658065180541E59, cumulative
cost = {1.020658065180541E59 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40596
DrillJoinRel(subset=[rel#40590:Subset#177.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.417381818912265E54, cumulative
cost = {1.417381818912265E54 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40589
DrillJoinRel(subset=[rel#40583:Subset#174.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.9683097494827322E49,
cumulative cost = {1.9683097494827322E49 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 40582
DrillJoinRel(subset=[rel#40575:Subset#170.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 2.733380108460802E44, cumulative
cost = {2.733380108460802E44 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40574
DrillJoinRel(subset=[rel#40568:Subset#167.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 3.7958287913234415E39,
cumulative cost = {3.7958287913234415E39 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 40567
DrillJoinRel(subset=[rel#40561:Subset#164.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 5.271244993859808E34, cumulative
cost = {5.271244993859808E34 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40560
DrillJoinRel(subset=[rel#40553:Subset#160.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 7.320146748664156E29, cumulative
cost = {7.320146748664156E29 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40552
DrillJoinRel(subset=[rel#40546:Subset#157.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.0165444498291428E25,
cumulative cost = {1.0165444498291428E25 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 40545
DrillJoinRel(subset=[rel#40539:Subset#154.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.411669265601829E20, cumulative
cost = {1.411669265601829E20 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40538
DrillJoinRel(subset=[rel#40531:Subset#150.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 1.9603767604847498E15,
cumulative cost = {1.9603767604847498E15 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 40530
DrillJoinRel(subset=[rel#40524:Subset#147.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 2.7223636135552505E10,
cumulative cost = {2.7223636135552505E10 rows, 0.0 cpu, 0.0 io, 0.0 network},
id = 40523
DrillJoinRel(subset=[rel#40517:Subset#144.LOGICAL.ANY([]).[]],
condition=[true], joinType=[left]): rowcount = 378053.025, cumulative cost =
{378053.025 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40516
DrillFilterRel(subset=[rel#40508:Subset#139.LOGICAL.ANY([]).[]],
condition=[=(CAST($1):INTEGER, 1)]): rowcount = 5.25, cumulative cost = {35.0
rows, 140.0 cpu, 0.0 io, 0.0 network}, id = 40507
DrillScanRel(subset=[rel#40506:Subset#138.LOGICAL.ANY([]).[]], table=[[dfs,
tpcds, reason]]): rowcount = 35.0, cumulative cost = {35.0 rows, 70.0 cpu, 0.0
io, 0.0 network}, id = 39732
DrillAggregateRel(subset=[rel#40515:Subset#143.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40514
DrillProjectRel(subset=[rel#40513:Subset#142.LOGICAL.ANY([]).[]], $f0=[0]):
rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0
network}, id = 40512
DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost =
{2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40522:Subset#146.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40521
DrillProjectRel(subset=[rel#40520:Subset#145.LOGICAL.ANY([]).[]],
ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0
rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40519
DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost =
{2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40529:Subset#149.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40528
DrillProjectRel(subset=[rel#40527:Subset#148.LOGICAL.ANY([]).[]],
ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0
cpu, 0.0 io, 0.0 network}, id = 40526
DrillFilterRel(subset=[rel#40511:Subset#141.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 1), <=($1, 20))]): rowcount = 720101.0, cumulative cost =
{2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40510
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40537:Subset#153.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40536
DrillProjectRel(subset=[rel#40535:Subset#152.LOGICAL.ANY([]).[]], $f0=[0]):
rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0
network}, id = 40534
DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40544:Subset#156.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40543
DrillProjectRel(subset=[rel#40542:Subset#155.LOGICAL.ANY([]).[]],
ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0
rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40541
DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40551:Subset#159.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40550
DrillProjectRel(subset=[rel#40549:Subset#158.LOGICAL.ANY([]).[]],
ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0
cpu, 0.0 io, 0.0 network}, id = 40548
DrillFilterRel(subset=[rel#40533:Subset#151.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 21), <=($1, 40))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40532
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40559:Subset#163.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40558
DrillProjectRel(subset=[rel#40557:Subset#162.LOGICAL.ANY([]).[]], $f0=[0]):
rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0
network}, id = 40556
DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40566:Subset#166.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40565
DrillProjectRel(subset=[rel#40564:Subset#165.LOGICAL.ANY([]).[]],
ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0
rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40563
DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40573:Subset#169.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40572
DrillProjectRel(subset=[rel#40571:Subset#168.LOGICAL.ANY([]).[]],
ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0
cpu, 0.0 io, 0.0 network}, id = 40570
DrillFilterRel(subset=[rel#40555:Subset#161.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 41), <=($1, 60))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40554
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40581:Subset#173.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40580
DrillProjectRel(subset=[rel#40579:Subset#172.LOGICAL.ANY([]).[]], $f0=[0]):
rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0 io, 0.0
network}, id = 40578
DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40588:Subset#176.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40587
DrillProjectRel(subset=[rel#40586:Subset#175.LOGICAL.ANY([]).[]],
ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0
rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40585
DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40595:Subset#179.LOGICAL.ANY([]).[]], group=[{}],
EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1 rows, 0.0
cpu, 0.0 io, 0.0 network}, id = 40594
DrillProjectRel(subset=[rel#40593:Subset#178.LOGICAL.ANY([]).[]],
ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0
cpu, 0.0 io, 0.0 network}, id = 40592
DrillFilterRel(subset=[rel#40577:Subset#171.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 61), <=($1, 80))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40576
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40603:Subset#183.LOGICAL.ANY([]).[]],
group=[{}], EXPR$0=[COUNT()]): rowcount = 72010.1, cumulative cost = {72010.1
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40602
DrillProjectRel(subset=[rel#40601:Subset#182.LOGICAL.ANY([]).[]],
$f0=[0]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0 cpu, 0.0
io, 0.0 network}, id = 40600
DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40610:Subset#186.LOGICAL.ANY([]).[]],
group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40609
DrillProjectRel(subset=[rel#40608:Subset#185.LOGICAL.ANY([]).[]],
ss_ext_sales_price=[$2]): rowcount = 720101.0, cumulative cost = {720101.0
rows, 4.0 cpu, 0.0 io, 0.0 network}, id = 40607
DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
DrillAggregateRel(subset=[rel#40617:Subset#189.LOGICAL.ANY([]).[]],
group=[{}], EXPR$0=[AVG($0)]): rowcount = 72010.1, cumulative cost = {72010.1
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 40616
DrillProjectRel(subset=[rel#40615:Subset#188.LOGICAL.ANY([]).[]],
ss_net_paid=[$3]): rowcount = 720101.0, cumulative cost = {720101.0 rows, 4.0
cpu, 0.0 io, 0.0 network}, id = 40614
DrillFilterRel(subset=[rel#40599:Subset#181.LOGICAL.ANY([]).[]],
condition=[AND(>=($1, 81), <=($1, 100))]): rowcount = 720101.0, cumulative cost
= {2880404.0 rows, 2.3043232E7 cpu, 0.0 io, 0.0 network}, id = 40598
DrillScanRel(subset=[rel#40509:Subset#140.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 = 38829
"
{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)
