Subrat Mishra created PHOENIX-4859:
--------------------------------------

             Summary: Using local index in where statement for join (only rhs 
table) query fails
                 Key: PHOENIX-4859
                 URL: https://issues.apache.org/jira/browse/PHOENIX-4859
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 5.0.0, 4.7.0
            Reporter: Subrat Mishra


Consider a simple scenario:
{code:java}
CREATE TABLE cust_data (customer_id integer primary key, postal_code varchar, 
country_code varchar); 
UPSERT INTO cust_data values(1,'560103','IN'); 
CREATE LOCAL INDEX ZIP_INDEX ON cust_data(postal_code); 
SELECT * from cust_data c1, cust_data c2 where c1.customer_id=c2.customer_id 
and c2.postal_code='560103'; {code}
Query fails with an exception:

(For version- 4.7.0)

 
{code:java}
Error: ERROR 504 (42703): Undefined column. columnName=0:COUNTRY_CODE 
(state=42703,code=504) 
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined 
column. columnName=0:COUNTRY_CODE 
at org.apache.phoenix.schema.PTableImpl.getColumn(PTableImpl.java:665) 
at 
org.apache.phoenix.compile.FromCompiler$MultiTableColumnResolver.resolveColumn(FromCompiler.java:874)
 
at 
org.apache.phoenix.compile.FromCompiler$ProjectedTableColumnResolver.resolveColumn(FromCompiler.java:941)
 
at 
org.apache.phoenix.compile.ProjectionCompiler.projectAllIndexColumns(ProjectionCompiler.java:239)
 
at 
org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:393)
 
at 
org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:557)
 
at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:247)
 
at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:294)
 
at 
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:197) 
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:154) 
at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:404)
 
at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:378)
 
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:271) 
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266) 
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) 
at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265)
 
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1444) 
at sqlline.Commands.execute(Commands.java:822) 
at sqlline.Commands.sql(Commands.java:732) 
at sqlline.SqlLine.dispatch(SqlLine.java:808) 
at sqlline.SqlLine.begin(SqlLine.java:681) 
at sqlline.SqlLine.start(SqlLine.java:398) 
at sqlline.SqlLine.main(SqlLine.java:292) {code}
(For version- 5.0.0)
{code:java}
java.lang.NullPointerException

at 
org.apache.phoenix.schema.LocalIndexDataColumnRef.<init>(LocalIndexDataColumnRef.java:40)

at 
org.apache.phoenix.compile.ProjectionCompiler.projectAllIndexColumns(ProjectionCompiler.java:221)

at 
org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:389)

at 
org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561)

at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:320)

at 
org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:228)

at 
org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:191)

at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:153)

at 
org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:190)

at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:112)

at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:98)

at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:309)

at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:291)

at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)

at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:290)

at 
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283)

at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1830)

at sqlline.Commands.execute(Commands.java:822)

at sqlline.Commands.sql(Commands.java:732)

at sqlline.SqlLine.dispatch(SqlLine.java:813)

at sqlline.SqlLine.begin(SqlLine.java:686)

at sqlline.SqlLine.start(SqlLine.java:398)

at sqlline.SqlLine.main(SqlLine.java:291){code}
Interestingly if we change c2.postal_code to c1.postal_code in where clause 
like shown below then the query runs fine. 
{code:java}
SELECT * from cust_data c1, cust_data c2 where c1.customer_id=c2.customer_id 
and c1.postal_code='560103'; {code}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to