[
https://issues.apache.org/jira/browse/DRILL-867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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',
. . . . . . . . . . .