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:13:01 GMT

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

Krystal commented on DRILL-867:
-------------------------------

Query 10:
{code}
select * from (select  
  cd.cd_gender,
  cd.cd_marital_status,
  cd.cd_education_status,
  count(*) cnt1,
  cd.cd_purchase_estimate,
  count(*) cnt2,
  cd.cd_credit_rating,
  count(*) cnt3,
  cd.cd_dep_count,
  count(*) cnt4,
  cd.cd_dep_employed_count,
  count(*) cnt5,
  cd.cd_dep_college_count,
  count(*) cnt6
 from
  customer c,customer_address ca,customer_demographics cd
 where
  c.c_current_addr_sk = ca.ca_address_sk and
  ca.ca_county in ('Yellowstone County','Montgomery County','Divide County','Cedar County','Manassas
Park city') and
  cd.cd_demo_sk = c.c_current_cdemo_sk and 
  exists (select *
          from store_sales ss,date_dim dd
          where c.c_customer_sk = ss.ss_customer_sk and
                ss.ss_sold_date_sk = dd.d_date_sk and
                dd.d_year = 2000 and
                dd.d_moy between 2 and 2+3) and
   (exists (select *
            from web_sales ws,date_dim dd
            where c.c_customer_sk = ws.ws_bill_customer_sk and
                  ws.ws_sold_date_sk = dd.d_date_sk and
                  dd.d_year = 2000 and
                  dd.d_moy between 2 ANd 2+3) or 
    exists (select * 
            from catalog_sales cs,date_dim dd
            where c.c_customer_sk = cs.cs_ship_customer_sk and
                  cs.cs_sold_date_sk = dd.d_date_sk and
                  dd.d_year = 2000 and
                  dd.d_moy between 2 and 2+3))
 group by cd.cd_gender,
          cd.cd_marital_status,
          cd.cd_education_status,
          cd.cd_purchase_estimate,
          cd.cd_credit_rating,
          cd.cd_dep_count,
          cd.cd_dep_employed_count,
          cd.cd_dep_college_count
 order by cd.cd_gender,
          cd.cd_marital_status,
          cd.cd_education_status,
          cd.cd_purchase_estimate,
          cd.cd_credit_rating,
          cd.cd_dep_count,
          cd.cd_dep_employed_count,
          cd.cd_dep_college_count
) limit 100;


"message: ""Failure while parsing sql. < CannotPlanException:[ Node [rel#41767:Subset#37.LOGICAL.ANY([]).[]]
could not be implemented; planner state:

Root: rel#41767:Subset#37.LOGICAL.ANY([]).[]
Original rel:
AbstractConverter(subset=[rel#41767:Subset#37.LOGICAL.ANY([]).[]], convention=[LOGICAL], DrillDistributionTraitDef=[ANY([])],
sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost = {inf}, id = 41768
  SortRel(subset=[rel#41766:Subset#37.NONE.ANY([]).[]], fetch=[100]): rowcount = 1.7976931348623157E308,
cumulative cost = {Infinity rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network}, id = 41765
    ProjectRel(subset=[rel#41764:Subset#36.NONE.ANY([]).[0, 1, 2, 4, 6, 8, 10, 12]], cd_gender=[$0],
cd_marital_status=[$1], cd_education_status=[$2], cnt1=[$3], cd_purchase_estimate=[$4], cnt2=[$5],
cd_credit_rating=[$6], cnt3=[$7], cd_dep_count=[$8], cnt4=[$9], cd_dep_employed_count=[$10],
cnt5=[$11], cd_dep_college_count=[$12], cnt6=[$13]): rowcount = 1.7976931348623157E308, cumulative
cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41763
      SortRel(subset=[rel#41762:Subset#35.NONE.ANY([]).[0, 1, 2, 4, 6, 8, 10, 12]], sort0=[$0],
sort1=[$1], sort2=[$2], sort3=[$4], sort4=[$6], sort5=[$8], sort6=[$10], sort7=[$12], dir0=[ASC],
dir1=[ASC], dir2=[ASC], dir3=[ASC], dir4=[ASC], dir5=[ASC], dir6=[ASC], dir7=[ASC]): rowcount
= 1.7976931348623157E308, cumulative cost = {Infinity rows, 1.7976931348623157E308 cpu, 0.0
io, 0.0 network}, id = 41761
        ProjectRel(subset=[rel#41760:Subset#34.NONE.ANY([]).[]], cd_gender=[$0], cd_marital_status=[$1],
cd_education_status=[$2], cnt1=[$8], cd_purchase_estimate=[$3], cnt2=[$8], cd_credit_rating=[$4],
cnt3=[$8], cd_dep_count=[$5], cnt4=[$8], cd_dep_employed_count=[$6], cnt5=[$8], cd_dep_college_count=[$7],
cnt6=[$8]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows,
Infinity cpu, 0.0 io, 0.0 network}, id = 41759
          AggregateRel(subset=[rel#41758:Subset#33.NONE.ANY([]).[]], group=[{0, 1, 2, 3, 4,
5, 6, 7}], cnt6=[COUNT()]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41757
            ProjectRel(subset=[rel#41756:Subset#32.NONE.ANY([]).[]], cd_gender=[$8], cd_marital_status=[$9],
cd_education_status=[$10], cd_purchase_estimate=[$11], cd_credit_rating=[$12], cd_dep_count=[$13],
cd_dep_employed_count=[$14], cd_dep_college_count=[$15]): rowcount = 1.7976931348623157E308,
cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41755
              FilterRel(subset=[rel#41754:Subset#31.NONE.ANY([]).[]], condition=[AND(=($1,
$6), OR(=($5, 'Yellowstone County'), =($5, 'Montgomery County'), =($5, 'Divide County'), =($5,
'Cedar County'), =($5, 'Manassas Park city')), =($16, $3), IS TRUE($17), OR(IS TRUE($18),
IS TRUE($19)))]): rowcount = 6.320014927250328E304, cumulative cost = {6.320014927250328E304
rows, 1.7976931348623157E308 cpu, 0.0 io, 0.0 network}, id = 41753
                ProjectRel(subset=[rel#41752:Subset#30.NONE.ANY([]).[]], *=[$0], c_current_addr_sk=[$1],
c_customer_sk=[$2], c_current_cdemo_sk=[$3], *0=[$4], ca_county=[$5], ca_address_sk=[$6],
*1=[$7], cd_gender=[$8], cd_marital_status=[$9], cd_education_status=[$10], cd_purchase_estimate=[$11],
cd_credit_rating=[$12], cd_dep_count=[$13], cd_dep_employed_count=[$14], cd_dep_college_count=[$15],
cd_demo_sk=[$16], $f0=[$17], $f00=[$18], $f019=[$19]): rowcount = 1.7976931348623157E308,
cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41751
                  CorrelatorRel(subset=[rel#41750:Subset#29.NONE.ANY([]).[]], condition=[true],
joinType=[left], correlations=[[var2=offset2]]): rowcount = 1.7976931348623157E308, cumulative
cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41749
                    ProjectRel(subset=[rel#41736:Subset#22.NONE.ANY([]).[]], *=[$0], c_current_addr_sk=[$1],
c_customer_sk=[$2], c_current_cdemo_sk=[$3], *0=[$4], ca_county=[$5], ca_address_sk=[$6],
*1=[$7], cd_gender=[$8], cd_marital_status=[$9], cd_education_status=[$10], cd_purchase_estimate=[$11],
cd_credit_rating=[$12], cd_dep_count=[$13], cd_dep_employed_count=[$14], cd_dep_college_count=[$15],
cd_demo_sk=[$16], $f0=[$17], $f018=[$18]): rowcount = 1.7976931348623157E308, cumulative cost
= {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41735
                      CorrelatorRel(subset=[rel#41734:Subset#21.NONE.ANY([]).[]], condition=[true],
joinType=[left], correlations=[[var1=offset2]]): rowcount = 1.7976931348623157E308, cumulative
cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41733
                        ProjectRel(subset=[rel#41720:Subset#14.NONE.ANY([]).[]], *=[$0], c_current_addr_sk=[$1],
c_customer_sk=[$2], c_current_cdemo_sk=[$3], *0=[$4], ca_county=[$5], ca_address_sk=[$6],
*1=[$7], cd_gender=[$8], cd_marital_status=[$9], cd_education_status=[$10], cd_purchase_estimate=[$11],
cd_credit_rating=[$12], cd_dep_count=[$13], cd_dep_employed_count=[$14], cd_dep_college_count=[$15],
cd_demo_sk=[$16], $f0=[$17]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308
rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41719
                          CorrelatorRel(subset=[rel#41718:Subset#13.NONE.ANY([]).[]], condition=[true],
joinType=[left], correlations=[[var0=offset2]]): rowcount = 1.7976931348623157E308, cumulative
cost = {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41717
                            JoinRel(subset=[rel#41702:Subset#4.NONE.ANY([]).[]], condition=[true],
joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41701
                              JoinRel(subset=[rel#41699:Subset#2.NONE.ANY([]).[]], condition=[true],
joinType=[inner]): rowcount = 10000.0, cumulative cost = {10000.0 rows, 0.0 cpu, 0.0 io, 0.0
network}, id = 41698
                                EnumerableTableAccessRel(subset=[rel#41696:Subset#0.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, customer]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network}, id = 41442
                                EnumerableTableAccessRel(subset=[rel#41697:Subset#1.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, customer_address]]): rowcount = 100.0, cumulative cost = {100.0 rows,
101.0 cpu, 0.0 io, 0.0 network}, id = 41443
                              EnumerableTableAccessRel(subset=[rel#41700:Subset#3.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, customer_demographics]]): rowcount = 100.0, cumulative cost = {100.0 rows,
101.0 cpu, 0.0 io, 0.0 network}, id = 41445
                            AggregateRel(subset=[rel#41716:Subset#12.NONE.ANY([]).[]], group=[{}],
agg#0=[MIN($0)]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41715
                              ProjectRel(subset=[rel#41714:Subset#11.NONE.ANY([]).[]], $f0=[true]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308
cpu, 0.0 io, 0.0 network}, id = 41713
                                ProjectRel(subset=[rel#41712:Subset#10.NONE.ANY([]).[]], *=[$0],
ss_sold_date_sk=[$1], ss_customer_sk=[$2], *0=[$3], d_year=[$4], d_date_sk=[$5], d_moy=[$6]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, Infinity
cpu, 0.0 io, 0.0 network}, id = 41711
                                  JoinRel(subset=[rel#41710:Subset#9.NONE.ANY([]).[]], condition=[=($1,
$5)], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41709
                                    FilterRel(subset=[rel#41705:Subset#6.NONE.ANY([]).[]],
condition=[=($cor0.c_customer_sk, $2)]): rowcount = 15.0, cumulative cost = {15.0 rows, 100.0
cpu, 0.0 io, 0.0 network}, id = 41704
                                      EnumerableTableAccessRel(subset=[rel#41703:Subset#5.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, store_sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0
cpu, 0.0 io, 0.0 network}, id = 41447
                                    FilterRel(subset=[rel#41708:Subset#8.NONE.ANY([]).[]],
condition=[AND(=(CAST($1):INTEGER, 2000), >=($3, 2), <=($3, +(2, 3)))]): rowcount =
3.75, cumulative cost = {3.75 rows, 100.0 cpu, 0.0 io, 0.0 network}, id = 41707
                                      EnumerableTableAccessRel(subset=[rel#41706:Subset#7.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network}, id = 41448
                        AggregateRel(subset=[rel#41732:Subset#20.NONE.ANY([]).[]], group=[{}],
agg#0=[MIN($0)]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41731
                          ProjectRel(subset=[rel#41730:Subset#19.NONE.ANY([]).[]], $f0=[true]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308
cpu, 0.0 io, 0.0 network}, id = 41729
                            ProjectRel(subset=[rel#41728:Subset#18.NONE.ANY([]).[]], *=[$0],
ws_sold_date_sk=[$1], ws_bill_customer_sk=[$2], *0=[$3], d_year=[$4], d_date_sk=[$5], d_moy=[$6]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, Infinity
cpu, 0.0 io, 0.0 network}, id = 41727
                              JoinRel(subset=[rel#41726:Subset#17.NONE.ANY([]).[]], condition=[=($1,
$5)], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41725
                                FilterRel(subset=[rel#41723:Subset#16.NONE.ANY([]).[]], condition=[=($cor1.c_customer_sk,
$2)]): rowcount = 15.0, cumulative cost = {15.0 rows, 100.0 cpu, 0.0 io, 0.0 network}, id
= 41722
                                  EnumerableTableAccessRel(subset=[rel#41721:Subset#15.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, web_sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network}, id = 41455
                                FilterRel(subset=[rel#41708:Subset#8.NONE.ANY([]).[]], condition=[AND(=(CAST($1):INTEGER,
2000), >=($3, 2), <=($3, +(2, 3)))]): rowcount = 3.75, cumulative cost = {3.75 rows,
100.0 cpu, 0.0 io, 0.0 network}, id = 41707
                                  EnumerableTableAccessRel(subset=[rel#41706:Subset#7.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network}, id = 41448
                    AggregateRel(subset=[rel#41748:Subset#28.NONE.ANY([]).[]], group=[{}],
agg#0=[MIN($0)]): rowcount = 1.7976931348623158E307, cumulative cost = {1.7976931348623158E307
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41747
                      ProjectRel(subset=[rel#41746:Subset#27.NONE.ANY([]).[]], $f0=[true]):
rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308 rows, 1.7976931348623157E308
cpu, 0.0 io, 0.0 network}, id = 41745
                        ProjectRel(subset=[rel#41744:Subset#26.NONE.ANY([]).[]], *=[$0], cs_sold_date_sk=[$1],
cs_ship_customer_sk=[$2], *0=[$3], d_year=[$4], d_date_sk=[$5], d_moy=[$6]): rowcount = 1.7976931348623157E308,
cumulative cost = {1.7976931348623157E308 rows, Infinity cpu, 0.0 io, 0.0 network}, id = 41743
                          JoinRel(subset=[rel#41742:Subset#25.NONE.ANY([]).[]], condition=[=($1,
$5)], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost = {1.7976931348623157E308
rows, 0.0 cpu, 0.0 io, 0.0 network}, id = 41741
                            FilterRel(subset=[rel#41739:Subset#24.NONE.ANY([]).[]], condition=[=($cor2.c_customer_sk,
$2)]): rowcount = 15.0, cumulative cost = {15.0 rows, 100.0 cpu, 0.0 io, 0.0 network}, id
= 41738
                              EnumerableTableAccessRel(subset=[rel#41737:Subset#23.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, catalog_sales]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0
cpu, 0.0 io, 0.0 network}, id = 41463
                            FilterRel(subset=[rel#41708:Subset#8.NONE.ANY([]).[]], condition=[AND(=(CAST($1):INTEGER,
2000), >=($3, 2), <=($3, +(2, 3)))]): rowcount = 3.75, cumulative cost = {3.75 rows,
100.0 cpu, 0.0 io, 0.0 network}, id = 41707
                              EnumerableTableAccessRel(subset=[rel#41706:Subset#7.ENUMERABLE.ANY([]).[]],
table=[[dfs, tpcds, date_dim]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu,
0.0 io, 0.0 network}, id = 41448
"
{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