[PHOENIX-6961] Using a covered field in hinted non-covered indexed query fails

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 5.2.0, 5.1.3
• Fix Version/s: 5.2.0, 5.1.4
• Component/s: core
• Assignee: Istvan Toth
• Reporter: Istvan Toth
• Created: 22/May/23 15:04
• Updated: 21/Jul/23 03:42
• Resolved: 21/Jul/23 03:42

Description

When covered fields are to be used in a hinted uncovered index lookup, Phoenix fails with a bogus error message.

0: jdbc:phoenix:localhost:59972> create table d (v1 integer, k integer primary key, v2 integer, v3 integer, v4 integer);
No rows affected (2.15 seconds)
0: jdbc:phoenix:localhost:59972> create index i on d(v2) include (v3);
No rows affected (7.177 seconds)
0: jdbc:phoenix:localhost:59972> select /*+ index(d i) */ * from d where v2=1 and v3=1;
Error: ERROR 514 (42892): A duplicate column name was detected in the object definition or ALTER TABLE/VIEW statement. columnName=I.V1 (state=42892,code=514)
org.apache.phoenix.schema.ColumnAlreadyExistsException: ERROR 514 (42892): A duplicate column name was detected in the object definition or ALTER TABLE/VIEW statement. columnName=I.V1
at org.apache.phoenix.schema.PTableImpl$Builder.initDerivedAttributes(PTableImpl.java:694)
at org.apache.phoenix.schema.PTableImpl$Builder.build(PTableImpl.java:802)
at org.apache.phoenix.compile.TupleProjectionCompiler.createProjectedTable(TupleProjectionCompiler.java:179)
at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:701)
at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:667)
at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:249)
at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:178)
at org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:348)
at org.apache.phoenix.optimize.QueryOptimizer.getHintedQueryPlan(QueryOptimizer.java:302)
at org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlansForSingleFlatQuery(QueryOptimizer.java:230)
at org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:138)
at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:116)
at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:102)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:319)
at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:301)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:300)
at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:293)
at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:2038)
at sqlline.Commands.executeSingleQuery(Commands.java:1130)
at sqlline.Commands.execute(Commands.java:1079)
at sqlline.Commands.sql(Commands.java:1033)
at sqlline.SqlLine.dispatch(SqlLine.java:822)
at sqlline.SqlLine.begin(SqlLine.java:596)
at sqlline.SqlLine.start(SqlLine.java:269)
at sqlline.SqlLine.main(SqlLine.java:208)

I thought that this was caused by the PK field not being the first, but the same happens with:

create table d (k integer primary key, v1 integer, v2 integer, v3 integer, v4 integer);

 

Issue Links

• links to: GitHub Pull Request #1639
• links to: GitHub Pull Request #1641