[
https://issues.apache.org/jira/browse/PHOENIX-3042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15381476#comment-15381476
]
Thomas D'Silva commented on PHOENIX-3042:
-----------------------------------------
It looks like the select query that is constructed using
getSubqueryForOptimizedPlan() is only used to build the data table to index
table replacement map. The issue is when we use the prefilter column refs to
generate the select statement and then call optimize the function index cannot
be used since the index only contains the function expression and not the
individual columns. If the replacement map doesn't contain the index then
IndexExpressionParseNodeRewriter won't be called.
> Using functional index expression in where statement for join query fails.
> ---------------------------------------------------------------------------
>
> Key: PHOENIX-3042
> URL: https://issues.apache.org/jira/browse/PHOENIX-3042
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.8.0
> Reporter: Sergey Soldatov
> Assignee: Thomas D'Silva
> Fix For: 4.8.1
>
> Attachments: PHOENIX-3042-V2.patch, PHOENIX-3042-v3.patch,
> PHOENIX-3042.patch
>
>
> A simple scenario:
> {noformat}
> CREATE TABLE customer_phx ( c_customer_sk varchar primary key, c_first_name
> varchar, c_last_name varchar );
> UPSERT INTO customer_phx values ( '1', 'David', 'Smith');
> CREATE LOCAL INDEX CUSTINDEX ON customer_phx (c_customer_sk || c_first_name
> asc) include (c_customer_sk);
> select c.c_customer_sk from customer_phx c left outer join customer_phx c2
> on c.c_customer_sk = c2.c_customer_sk where c.c_customer_sk || c.c_first_name
> = '1David';
> {noformat}
> It fails with an Exception :
> {noformat}
> Error: ERROR 504 (42703): Undefined column. columnName=C_FIRST_NAME
> (state=42703,code=504)
> org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703):
> Undefined column. columnName=C_FIRST_NAME
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.resolveColumn(WhereCompiler.java:190)
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:169)
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:156)
> at
> org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:56)
> at
> org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
> at
> org.apache.phoenix.parse.StringConcatParseNode.accept(StringConcatParseNode.java:46)
> at
> org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
> at
> org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:45)
> at
> org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:130)
> at
> org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:100)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:556)
> at
> org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:324)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:200)
> at
> org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
> 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:807)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> {noformat}
> Meanwhile using the same where statement without join works just fine.
> Any ideas [~jamestaylor], [~ramkrishna.s.vasude...@gmail.com] ?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
