phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aman Jha (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions
Date Fri, 16 Jun 2017 12:50:00 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-3952?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Aman Jha updated PHOENIX-3952:
------------------------------
    Description: 
If I'm joining two tables, say inner join, on an OR based condition, then the following exception
is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition specified. Consider
using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables and entries : 
{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
	COM_CODE VARCHAR NOT NULL , 
	BU_CODE VARCHAR NOT NULL , 
	LOC_CODE VARCHAR NOT NULL 
	CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);

upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 'Devcast', 'C3', 'B4',
'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 'Thought', 'C2', 'B1',
'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 'Jabber', 'C3', 'B4',
'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 'Yakijo', 'C3', 'B8',
'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 'Youfeed', 'C2', 'B3',
'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 'C2', 'B1',
'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 'Trilia', 'C1', 'B10',
'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 'Podcat', 'C1', 'B3',
'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 'Twitter', 'C1', 'B5',
'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 'C1', 'B8',
'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 'C1', 'B1',
'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
{code}

+*Run the following query :*+

{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

+*Expected O/P :*+

{noformat}

1	Devcast	C3	B4	L5	C3	*	*
3	Jabber	C3	B4	L1	C3	*	*
4	Yakijo	C3	B8	L9	C3	*	*
11	DabZ	C1	B1	L10	C1	B1	*
{noformat}





  was:
If I'm joining two tables, say inner join, on an OR based condition, then the following exception
is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition specified. Consider
using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables : 
{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
	COM_CODE VARCHAR NOT NULL , 
	BU_CODE VARCHAR NOT NULL , 
	LOC_CODE VARCHAR NOT NULL 
	CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);
{code}
Make following entries : 
{code:java}
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 'Devcast', 'C3', 'B4',
'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 'Thought', 'C2', 'B1',
'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 'Jabber', 'C3', 'B4',
'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 'Yakijo', 'C3', 'B8',
'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 'Youfeed', 'C2', 'B3',
'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 'C2', 'B1',
'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 'Trilia', 'C1', 'B10',
'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 'Podcat', 'C1', 'B3',
'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 'Twitter', 'C1', 'B5',
'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 'C1', 'B8',
'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 'C1', 'B1',
'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
{code}

+*Run the following query :*+

{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

+*Expected O/P :*+

{noformat}

1	Devcast	C3	B4	L5	C3	*	*
3	Jabber	C3	B4	L1	C3	*	*
4	Yakijo	C3	B8	L9	C3	*	*
11	DabZ	C1	B1	L10	C1	B1	*
{noformat}






> "Ambiguous or non-equi join condition specified" Exception thrown for usage of  OR expression
in join conditions
> ----------------------------------------------------------------------------------------------------------------
>
>                 Key: PHOENIX-3952
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3952
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.8.1
>         Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, Squirrel v-3.7
running on Windows 10
>            Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the following
exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition specified.
Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
> 	COM_CODE VARCHAR NOT NULL , 
> 	BU_CODE VARCHAR NOT NULL , 
> 	LOC_CODE VARCHAR NOT NULL 
> 	CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>    PO_ID VARCHAR PRIMARY KEY,
>    BU_ID VARCHAR ,
>    PO_NAME VARCHAR,
>    C_ID VARCHAR,
>    LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 'Devcast', 'C3',
'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 'Thought', 'C2',
'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 'Jabber', 'C3',
'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 'Yakijo', 'C3',
'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 'Youfeed', 'C2',
'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 'C2',
'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 'Trilia', 'C1',
'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 'Podcat', 'C1',
'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 'Twitter', 'C1',
'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 'C1',
'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 'C1',
'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>    ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>    AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>    AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1	Devcast	C3	B4	L5	C3	*	*
> 3	Jabber	C3	B4	L1	C3	*	*
> 4	Yakijo	C3	B8	L9	C3	*	*
> 11	DabZ	C1	B1	L10	C1	B1	*
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message