Vivek K T created PHOENIX-2480:
----------------------------------
Summary: SQL Query with multiple projection selections over
multiple tables having LEFT OUTER JOINS returns completely null for random
columns even when data is present
Key: PHOENIX-2480
URL: https://issues.apache.org/jira/browse/PHOENIX-2480
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.4.0
Environment: Linux CentOS release 6.6/ Hadoop 2.2.0 / Hbase 0.98 / JDK
1.7.0_55 / Apache Phoenix 4.4.0
Reporter: Vivek K T
Priority: Blocker
Please do the following to reproduce the issue.
***********************************************************************************
Create following test tables :
***********************************************************************************
CREATE TABLE master_businessunit (
code varchar(255) PRIMARY KEY,
name varchar(255)
);
CREATE TABLE master_company (
code varchar(255) PRIMARY KEY,
name varchar(255)
);
CREATE TABLE master_costcenter (
code varchar(255) PRIMARY KEY,
name varchar(255)
);
CREATE TABLE master_location (
code varchar(255) PRIMARY KEY,
name varchar(255)
);
CREATE TABLE master_product (
id int(11) PRIMARY KEY,
product_name varchar(255)
);
CREATE TABLE master_purchaseorder (
purchaseOrderNumber varchar(255),
companyCode varchar(255) ,
businessUnitCode varchar(255),
locationCode varchar(255) ,
purchaseOrderId varchar(255) PRIMARY KEY,
releasedOn date ,
name varchar(255)
);
CREATE TABLE trans_purchaseorderitem (
purchaseOrderItemId varchar(255) PRIMARY KEY,
purchaseOrderId varchar(255),
lineNo varchar(255),
name varchar(255)
);
CREATE TABLE trans_purchaseorderitem_costing (
purchaseorderItem_costing_id varchar(255) primary key,
purchaseorderItemId varchar(255) ,
purchaseorderId varchar(255) ,
costcenterCode varchar(255)
);
*************************************************************************************
Upsert following test values :
**************************************************************************************
upsert into master_businessunit(code,name) values ('1','BU1');
upsert into master_businessunit(code,name) values ('2','BU2');
upsert into master_company(code,name) values ('1','Company1');
upsert into master_company(code,name) values ('2','Company2');
upsert into master_costcenter(code,name) values ('1','CC1');
upsert into master_costcenter(code,name) values ('2','CC2');
upsert into master_location(code,name) values ('1','Location1');
upsert into master_location(code,name) values ('2','Location2');
upsert into master_product(id,product_name) values (1,'ProductName1');
upsert into master_product(id,product_name) values (2,'Product2');
upsert into
master_purchaseorder(purchaseOrderNumber,companyCode,businessUnitCode,locationCode,purchaseOrderId,releasedOn,name)
values ('1','1','1','1','1','2015-12-01','1');
upsert into
master_purchaseorder(purchaseOrderNumber,companyCode,businessUnitCode,locationCode,purchaseOrderId,releasedOn,name)
values ('2','2','2','2','2','2015-12-02','2');
upsert into
trans_purchaseorderitem(purchaseOrderItemId,purchaseOrderId,lineNo,name) values
('1','1','1','1');
upsert into
trans_purchaseorderitem(purchaseOrderItemId,purchaseOrderId,lineNo,name) values
('2','2','2','2');
upsert into
trans_purchaseorderitem_costing(purchaseorderItem_costing_id,purchaseorderItemId,purchaseorderId,costcenterCode)
values ('1','1','1','1');
upsert into
trans_purchaseorderitem_costing(purchaseorderItem_costing_id,purchaseorderItemId,purchaseorderId,costcenterCode)
values ('2','2','2','2');
********************************************************************************************
Now execute the following query :
SELECT
DISTINCT
COALESCE( a1.name, 'N.A.'),
COALESCE( a2.name, 'N.A.'),
COALESCE( a3.name, 'N.A.'),
COALESCE( a4.purchaseOrderNumber, 'N.A.'),
COALESCE( a1.name, 'N.A.'),
COALESCE( a4.name, 'N.A.'),
COALESCE( a5.lineNo, 'N.A.'),
COALESCE( a5.name, 'N.A.'),
COALESCE( a7.name,'N.A.')
FROM
(
master_purchaseorder a4 LEFT OUTER
JOIN master_company a1 ON a4.companyCode = a1.code LEFT OUTER
JOIN master_businessunit a2 ON a4.businessUnitCode = a2.code
LEFT OUTER
JOIN master_location a3 ON a4.locationCode = a3.code LEFT OUTER
JOIN trans_purchaseorderitem a5 ON a5.purchaseOrderId =
a4.purchaseOrderId LEFT OUTER
JOIN trans_purchaseorderitem_costing a6 ON
a6.purchaseOrderItemId = a5.purchaseOrderItemId
AND a6.purchaseOrderId = a5.purchaseOrderId LEFT OUTER
JOIN master_costcenter a7 ON a6.costCenterCode = a7.code
)
************************************************************************************
The first three columns are displays 'N.A' in all the rows even though data is
present.
surprisingly they return the actual values when the last three projections are
commented out in the select clause.
For e.g. the following query (same as above, just reduced number of
projections) returns proper data
SELECT
DISTINCT
COALESCE( a1.name, 'N.A.'),
COALESCE( a2.name, 'N.A.'),
COALESCE( a3.name, 'N.A.'),
COALESCE( a4.purchaseOrderNumber, 'N.A.'),
COALESCE( a1.name, 'N.A.'),
COALESCE( a4.name, 'N.A.')
FROM
(
master_purchaseorder a4 LEFT OUTER
JOIN master_company a1 ON a4.companyCode = a1.code LEFT OUTER
JOIN master_businessunit a2 ON a4.businessUnitCode = a2.code
LEFT OUTER
JOIN master_location a3 ON a4.locationCode = a3.code LEFT OUTER
JOIN trans_purchaseorderitem a5 ON a5.purchaseOrderId =
a4.purchaseOrderId LEFT OUTER
JOIN trans_purchaseorderitem_costing a6 ON
a6.purchaseOrderItemId = a5.purchaseOrderItemId
AND a6.purchaseOrderId = a5.purchaseOrderId LEFT OUTER
JOIN master_costcenter a7 ON a6.costCenterCode = a7.code
)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
