[TAJO-1762] Cannot get right answer about query SUBQUERY and JOIN mixed

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 0.11.0
• Component/s: Benchmark
• Labels: sql
• Assignee: Unassigned
• Reporter: Dongkyu Hwangbo
• Created: 15/Aug/15 08:37
• Updated: 15/Aug/15 08:41

Description

In TPC-DS q69, I can get right answer about below query with tajo.
(seperate 'table a' and 'inner join)

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;

but, Tajo gives me 0 row when i enter below query.
('table a' and 'inner join' are mixed)

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