[PHOENIX-4859] Using local index in where statement for join (only rhs table) query fails

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 5.0.0
• Fix Version/s: 4.15.0, 5.1.0
• Labels: localIndex
• Assignee: Rajeshbabu Chintaguntla
• Reporter: Subrat Mishra
• Created: 21/Aug/18 10:55
• Updated: 21/Dec/19 00:52
• Resolved: 09/Oct/18 10:36

Description

Consider a simple scenario:

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'; 

Query fails with an exception:

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)

Interestingly if we change c2.postal_code to c1.postal_code in where clause like shown below then the query runs fine. 

SELECT * from cust_data c1, cust_data c2 where c1.customer_id=c2.customer_id and c1.postal_code='560103';