phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-3780) Undefined Column Error while Using index hint
Date Tue, 03 Oct 2017 22:00:04 GMT

    [ 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)

Mime
View raw message