[ https://issues.apache.org/jira/browse/PHOENIX-4859?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Josh Elser resolved PHOENIX-4859. --------------------------------- Resolution: Invalid This is against an HDP release, not an Apache release. More work needs to be done in-house at Hortonworks first. > 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: 4.7.0, 5.0.0 > Reporter: Subrat Mishra > Priority: Major > > 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)