[
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 :
{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}
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 :
> {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}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
