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