drill-issues mailing list archives

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

    [ 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)

Mime
View raw message