[ 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)