[PHOENIX-5109] Allow local indexes to work for queries with uncovered columns.

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.14.1
• Fix Version/s: 4.15.0, 5.1.0
• Assignee: Lars Hofhansl
• Reporter: Lars Hofhansl
• Created: 23/Jan/19 18:31
• Updated: 11/Mar/21 10:19
• Resolved: 25/Jan/19 06:06

Description

This change allows local indexes to be used for a wide set of queries where not all columns are covered.
See scenarios in the test case attached with the patch.

This is important for various reasons:

1. Unlike global indexes, local indexes can support a wide range case where the queries are not covered.
2. Indexes that cover only the indexed columns are much friendlier to schema changes. (changes to any extra included column cause a need to rebuild these indexes)
3. A lot of storage is saved. The same is true for read and write IO.

(Of course uncovered columns have disadvantages too, Phoenix needs to reassemble the complete row in those cases.)

—

Was:

Example:
create table test (pk integer primary key, v1 float, v2 float, v3 integer);
create local index l1 on test(v1);
select /*+ INDEX(test l1) */ * from test where v2 = 1;

0: jdbc:phoenix:localhost> select /*+ INDEX(test l1) */ * from test where v2 = 1;
Error: ERROR 504 (42703): Undefined column. columnName=TEST.V2 (state=42703,code=504)
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=TEST.V2
	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.resolveColumn(WhereCompiler.java:202)
	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:177)
	at org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:164)
	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.CastParseNode.accept(CastParseNode.java:60)
	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:138)
	at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:108)
	at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:559)
	at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:510)
	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:195)
	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:155)
	at org.apache.phoenix.compile.QueryCompiler.compileSubquery(QueryCompiler.java:499)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:221)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:295)
	at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:230)
	at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:193)
	at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:155)
	at org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:399)
	at org.apache.phoenix.optimize.QueryOptimizer.getHintedQueryPlan(QueryOptimizer.java:279)
	at org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlansForSingleFlatQuery(QueryOptimizer.java:220)
	at org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:133)
	at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:111)
	at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:97)
	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)

Issue Links

• causes: PHOENIX-6365 Bogus AmbiguousTableException in query with aliases on local indexed tables
• relates to: PHOENIX-5112 Simplify QueryPlan selection in Phoenix
• relates to: PHOENIX-5113 Reason through the correct plans for salted tables and local indexes