[
https://issues.apache.org/jira/browse/PHOENIX-3780?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16190429#comment-16190429
]
James Taylor commented on PHOENIX-3780:
---------------------------------------
I can't repro this exception in master (which will be 4.12 release) with the
following test. I did, however find that the index wasn't being used when the
query was hinted if a column from the view is referenced, even when hinted. I
filed PHOENIX-4274 for that.
{code}
@Test
public void testHintForIndexOnViewWithInclude() throws Exception {
testHintForIndexOnView(true);
}
@Ignore("PHOENIX-4274 Hint query for index on view does not use include")
@Test
public void testHintForIndexOnViewWithoutInclude() throws Exception {
testHintForIndexOnView(false);
}
private void testHintForIndexOnView(boolean includeColumns) throws
Exception {
Properties props = new Properties();
Connection conn1 = DriverManager.getConnection(getUrl(), props);
conn1.setAutoCommit(true);
String tableName=generateUniqueName();
String viewName=generateUniqueName();
String indexName=generateUniqueName();
conn1.createStatement().execute(
"CREATE TABLE "+tableName+" (k VARCHAR PRIMARY KEY, v1 VARCHAR, v2
VARCHAR) UPDATE_CACHE_FREQUENCY=1000000");
conn1.createStatement().execute("upsert into "+tableName+" values
('row1', 'value1', 'key1')");
conn1.createStatement().execute(
"CREATE VIEW "+viewName+" (v3 VARCHAR, v4 VARCHAR) AS SELECT * FROM
"+tableName+" WHERE v1 = 'value1'");
conn1.createStatement().execute("CREATE INDEX " + indexName + " ON " +
viewName + "(v3)" + (includeColumns ? " INCLUDE(v4)" : ""));
PhoenixStatement stmt =
conn1.createStatement().unwrap(PhoenixStatement.class);
ResultSet rs = stmt.executeQuery("SELECT /*+ INDEX(" + viewName + " " +
indexName + ") */ v1 FROM " + viewName + " WHERE v3 = 'foo' ORDER BY v4");
assertFalse(rs.next());
assertEquals(indexName,
stmt.getQueryPlan().getContext().getCurrentTable().getTable().getName().getString());
}
{code}
> Undefined Column Error while Using index hint
> ----------------------------------------------
>
> Key: PHOENIX-3780
> URL: https://issues.apache.org/jira/browse/PHOENIX-3780
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.10.0
> Reporter: Manoj Patrick
> Assignee: Loknath Priyatham Teja Singamsetty
> Attachments: Analytics_reports_scenario.xml,
> create_pherf_REPORT_INSTANCE_DATA_DETAIL.sql
>
>
> When I force an index with the index hint , I am getting an error. Is this a
> known error ?
> Error: ERROR 504 (42703): Undefined column. columnName=:REPORT_INSTANCE_ID
> (state=42703,code=504)
>
> Table Schema:
> 3 columns: ORGANIZATION_ID,REPORT_INSTANCE_ID,ROWNUM
>
> Tenant Specific View Schema: dynamic columns including ACCOUNTNAME
>
> index script: create index my_index_filter_1 on
> pherf.rrs_custom_view1(ACCOUNTNAME) include(ROWNUM);
>
> Query: select /*+ INDEX(pherf.rrs_custom_view1 my_index_filter_1) */ ROWNUM
> from PHERF.RRS_CUSTOM_VIEW1 where ACCOUNTNAME = 'Account_7' ORDER BY
> CUSTOMFIELD2;
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
