[jira] [Created] (TAJO-1762) Cannot get right answer about query SUBQUERY and JOIN are mixed

["Dongkyu Hwangbo (JIRA)" <ji...@apache.org>]

Dongkyu Hwangbo created TAJO-1762:
-------------------------------------

             Summary: Cannot get right answer about query SUBQUERY and JOIN are mixed
                 Key: TAJO-1762
                 URL: https://issues.apache.org/jira/browse/TAJO-1762
             Project: Tajo
          Issue Type: Bug
          Components: Benchmark
    Affects Versions: 0.11.0
            Reporter: Dongkyu Hwangbo
            Priority: Minor


In TPC-DS q69, I can get right answer about below query with tajo.
(seperate 'table a' and 'inner join)
{code:SQL}
insert overwrite into a
select 
cd_gender,
cd_marital_status,
cd_education_status,
count(*) cnt1,
cd_purchase_estimate,
count(*) cnt2,
cd_credit_rating,
count(*) cnt3,
c.c_customer_sk
from
customer c,customer_address ca,customer_demographics
where
c.c_current_addr_sk = ca.ca_address_sk and
ca_state in ('KY','GA','NM') and
cd_demo_sk = c.c_current_cdemo_sk 
group by cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating,
  c_customer_sk
order by cd_gender,
  cd_marital_status,
  cd_education_status,
  cd_purchase_estimate,
  cd_credit_rating,
  c_customer_sk;

select a.cd_gender, a.cd_marital_status, a.cd_education_status, a.cnt1, a.cd_purchase_estimate, a.cnt2, a.cd_credit_rating, a.cnt3
from a
inner join tmp1
on a.c_customer_sk = tmp1.c_customer_sk;
{code}

but, Tajo gives me 0 row when i enter below query.
('table a' and 'inner join' are mixed)
{code:SQL}
select cd_gender, cd_marital_status, cd_education_status, cnt1, cd_purchase_estimate, cnt2, cd_credit_rating, cnt3
from (
	select 
	  cd_gender,
	  cd_marital_status,
	  cd_education_status,
	  count(*) cnt1,
	  cd_purchase_estimate,
	  count(*) cnt2,
	  cd_credit_rating,
	  count(*) cnt3,
	  c.c_customer_sk
	 from
	  customer c,customer_address ca,customer_demographics
	 where
	  c.c_current_addr_sk = ca.ca_address_sk and
	  ca_state in ('KY','GA','NM') and
	  cd_demo_sk = c.c_current_cdemo_sk 
	 group by cd_gender,
		  cd_marital_status,
		  cd_education_status,
		  cd_purchase_estimate,
		  cd_credit_rating,
		  c_customer_sk
	 order by cd_gender,
		  cd_marital_status,
		  cd_education_status,
		  cd_purchase_estimate,
		  cd_credit_rating,
		  c_customer_sk) a
inner join tmp2
on a.c_customer_sk = tmp2.c_customer_sk
{code}




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)