You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Victoria Markman (JIRA)" <ji...@apache.org> on 2015/11/16 22:14:11 UTC
[jira] [Commented] (DRILL-867) tpcds queries 6, 9 and 10 fail to
plan
[ https://issues.apache.org/jira/browse/DRILL-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15007330#comment-15007330 ]
Victoria Markman commented on DRILL-867:
----------------------------------------
All these queries throw proper unsupported errors now and have enhancement requests associated with the unsupported features:
query 6 (DRILL-3801)
{code}
0: jdbc:drill:schema=dfs> 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;
Error: UNSUPPORTED_OPERATION ERROR: Non-scalar sub-query used in an expression
See Apache Drill JIRA: DRILL-1937
[Error Id: 841dae77-8b74-484e-a0f0-a01a2c07fbd8 on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}
query 9 ( DRILL-4092)
{code}
0: jdbc:drill:schema=dfs> select * from (select store.s_store_name
. . . . . . . . . . . . > ,sum(ss.ss_net_profit)
. . . . . . . . . . . . > from store_sales ss
. . . . . . . . . . . . > ,date_dim dd
. . . . . . . . . . . . > ,store,
. . . . . . . . . . . . > (select ca_zip
. . . . . . . . . . . . > from (
. . . . . . . . . . . . > (SELECT substr(ca.ca_zip,1,5) ca_zip
. . . . . . . . . . . . > FROM customer_address ca
. . . . . . . . . . . . > WHERE substr(ca.ca_zip,1,5) IN (
. . . . . . . . . . . . > '16733','50732','51878','16933','33177','55974',
. . . . . . . . . . . . > '21338','90455','63106','78712','45114',
. . . . . . . . . . . . > '51090','44881','35526','91360','34986',
. . . . . . . . . . . . > '31893','28853','84061','25483','84541',
. . . . . . . . . . . . > '39275','56211','51199','85189','24292',
. . . . . . . . . . . . > '27477','46388','77218','21137','43660',
. . . . . . . . . . . . > '36509','77925','11691','26790','35256',
. . . . . . . . . . . . > '59221','42491','39214','35273','27293',
. . . . . . . . . . . . > '74258','68798','50936','19136','25240',
. . . . . . . . . . . . > '89163','21667','30941','61680','10425',
. . . . . . . . . . . . > '96787','84569','37596','84291','44843',
. . . . . . . . . . . . > '31487','24949','31269','62115','79494',
. . . . . . . . . . . . > '32194','62531','61655','40724','29091',
. . . . . . . . . . . . > '81608','77126','32704','79045','19008',
. . . . . . . . . . . . > '81581','59693','24689','79355','19635',
. . . . . . . . . . . . > '52025','83585','56103','80150','26203',
. . . . . . . . . . . . > '81571','85657','39672','62868','33498',
. . . . . . . . . . . . > '69453','25748','44145','35695','57860',
. . . . . . . . . . . . > '59532','76967','81235','22004','34487',
. . . . . . . . . . . . > '48499','47318','63039','77728','89774',
. . . . . . . . . . . . > '91640','76501','70137','37512','48507',
. . . . . . . . . . . . > '51980','34851','54884','30905','12745',
. . . . . . . . . . . . > '60630','42798','39923','47591','82518',
. . . . . . . . . . . . > '32982','14233','56444','79278','57791',
. . . . . . . . . . . . > '37395','93812','14062','21556','58923',
. . . . . . . . . . . . > '13595','87261','79484','24492','10389',
. . . . . . . . . . . . > '89526','21733','85078','35187','68025',
. . . . . . . . . . . . > '45624','25243','42027','50749','13870',
. . . . . . . . . . . . > '47072','17847','46413','11259','20221',
. . . . . . . . . . . . > '32961','14173','96788','77001','65695',
. . . . . . . . . . . . > '52542','39550','21651','68063','48779',
. . . . . . . . . . . . > '55702','16612','15953','22707','83997',
. . . . . . . . . . . . > '61460','18919','27616','55164','54421',
. . . . . . . . . . . . > '47268','66355','86846','74968','95883',
. . . . . . . . . . . . > '92832','37009','66903','38063','95421',
. . . . . . . . . . . . > '45640','55118','22721','54787','29772',
. . . . . . . . . . . . > '79121','85462','28380','34680','85831',
. . . . . . . . . . . . > '60615','60763','87605','10096','69252',
. . . . . . . . . . . . > '28329','68812','47734','36851','24290',
. . . . . . . . . . . . > '39067','32242','97396','45999','37157',
. . . . . . . . . . . . > '88891','71571','17941','12910','28800',
. . . . . . . . . . . . > '47548','11514','49224','50161','27249',
. . . . . . . . . . . . > '29522','50098','20810','23683','51862',
. . . . . . . . . . . . > '57007','43224','98002','65238','30719',
. . . . . . . . . . . . > '15735','70127','33927','96245','56649',
. . . . . . . . . . . . > '44640','34914','18833','72797','18380',
. . . . . . . . . . . . > '17256','75124','36114','44696','35472',
. . . . . . . . . . . . > '76579','52537','82424','44424','32748',
. . . . . . . . . . . . > '77516','17985','57725','34893','42886',
. . . . . . . . . . . . > '98097','62869','24984','80539','19716',
. . . . . . . . . . . . > '87183','63517','60342','42577','88040',
. . . . . . . . . . . . > '46167','75108','41469','73674','13253',
. . . . . . . . . . . . > '66716','36315','16812','85084','70345',
. . . . . . . . . . . . > '16291','84204','38177','41416','75043',
. . . . . . . . . . . . > '85969','52544','13572','21899','22356',
. . . . . . . . . . . . > '16473','25488','46385','18400','17159',
. . . . . . . . . . . . > '74763','34719','18588','39471','47156',
. . . . . . . . . . . . > '28837','84535','69380','54019','57251',
. . . . . . . . . . . . > '51378','43170','51671','40569','81767',
. . . . . . . . . . . . > '59720','68739','28324','24144','96283',
. . . . . . . . . . . . > '53359','11880','52839','13744','21434',
. . . . . . . . . . . . > '24927','99581','87926','93557','34275',
. . . . . . . . . . . . > '12144','82294','39717','28926','89184',
. . . . . . . . . . . . > '29862','38378','91135','17811','57160',
. . . . . . . . . . . . > '74994','34074','51040','69828','65826',
. . . . . . . . . . . . > '84570','24660','15444','62133','83549',
. . . . . . . . . . . . > '15555','80929','27543','86821','98908',
. . . . . . . . . . . . > '89602','68316','69972','40191','97204',
. . . . . . . . . . . . > '42699','56262','69604','44040','48466',
. . . . . . . . . . . . > '55692','14302','38041','33734','47513',
. . . . . . . . . . . . > '46513','16039','81050','34048','30741',
. . . . . . . . . . . . > '18213','99574','27215','60005','47953',
. . . . . . . . . . . . > '29145','14682','50833','74174','86506',
. . . . . . . . . . . . > '57452','92971','70344','66483','99501',
. . . . . . . . . . . . > '78134','79445','82179','44114','19591',
. . . . . . . . . . . . > '20096','85999','52672','47030','74464',
. . . . . . . . . . . . > '30215','59015','42068','25463','26536',
. . . . . . . . . . . . > '53394','43508','41140','29335','37130',
. . . . . . . . . . . . > '43967','22686','78500','70281','20148',
. . . . . . . . . . . . > '54335','31575','79592','16787'))
. . . . . . . . . . . . > intersect
. . . . . . . . . . . . > (select ca_zip
. . . . . . . . . . . . > from (SELECT substr(ca.ca_zip,1,5) ca_zip,count(*) cnt
. . . . . . . . . . . . > FROM customer_address ca, customer
. . . . . . . . . . . . > WHERE ca.ca_address_sk = customer.c_current_addr_sk and
. . . . . . . . . . . . > customer.c_preferred_cust_flag='Y'
. . . . . . . . . . . . > group by ca.ca_zip
. . . . . . . . . . . . > having count(*) > 10)A1))A2) V1
. . . . . . . . . . . . > where ss.ss_store_sk = store.s_store_sk
. . . . . . . . . . . . > and ss.ss_sold_date_sk = dd.d_date_sk
. . . . . . . . . . . . > and dd.d_qoy = 1 and dd.d_year = 2001
. . . . . . . . . . . . > and (substr(store.s_zip,1,2) = substr(V1.ca_zip,1,2))
. . . . . . . . . . . . > group by store.s_store_name
. . . . . . . . . . . . > order by store.s_store_name
. . . . . . . . . . . . > ) limit 100;
Error: UNSUPPORTED_OPERATION ERROR: INTERSECT is not supported
See Apache Drill JIRA: DRILL-1921
[Error Id: 08c3c08e-3168-4d13-b3a0-888f5fc469ca on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}
query 10
{code}
0: jdbc:drill:schema=dfs> 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;
Error: UNSUPPORTED_OPERATION ERROR: This query cannot be planned possibly due to either a cartesian join or an inequality join
[Error Id: 7152437e-4d0a-4b83-b582-9b24608d9d8a on atsqa4-133.qa.lab:31010] (state=,code=0)
{code}
> tpcds queries 6, 9 and 10 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
> Priority: Minor
> Fix For: Future
>
>
> 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.3.4#6332)