phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vivek K T (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-2480) SQL Query with multiple projection selections over multiple tables having LEFT OUTER JOINS returns completely null for random columns even when data is present
Date Fri, 04 Dec 2015 14:48:10 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-2480?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15041617#comment-15041617
] 

Vivek K T commented on PHOENIX-2480:
------------------------------------

[~maryannxue] I think i will go with option 1 : But I'm confused about the artifact downloaded.
There are many Jars in them and I'm not sure which one to use as the phoenix "client" jar
and phoenix "server" jar. could you help me out?

> 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
>            Assignee: Maryann Xue
>            Priority: Minor
>             Fix For: 4.7.0
>
>         Attachments: PHOENIX-2480.patch
>
>
> 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)

Mime
View raw message