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-3042) Using local index on the expression in where statement for join query fails.
Date Mon, 11 Jul 2016 12:59:10 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-3042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15370703#comment-15370703
] 

James Taylor commented on PHOENIX-3042:
---------------------------------------

Looks like even with your patch the functional index isn't being used (though the exception
doesn't occur). Here's a QueryCompilerTest you can play with, [~tdsilva]:
{code}
    @Test
    public void testFuncIndexUsage() throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        try {
            conn.createStatement().execute("CREATE TABLE t1(k INTEGER PRIMARY KEY,"+
                    " col1 VARCHAR, col2 VARCHAR)");
            conn.createStatement().execute("CREATE TABLE t2(k INTEGER PRIMARY KEY," +
                    " col1 VARCHAR, col2 VARCHAR)");
            conn.createStatement().execute("CREATE TABLE t3(j INTEGER PRIMARY KEY," +
                    " col3 VARCHAR, col4 VARCHAR)");
            conn.createStatement().execute("CREATE INDEX idx ON t1 (col1 || col2)");
            String query = "SELECT a.k from t1 a where a.col1 || a.col2 = 'foobar'";
            ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+query);
            String explainPlan = QueryUtil.getExplainPlan(rs);
            assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + 
                    "    SERVER FILTER BY FIRST KEY ONLY",explainPlan);
            query = "SELECT k,j from t3 b join t1 a ON k = j where a.col1 || a.col2 = 'foobar'";
            rs = conn.createStatement().executeQuery("EXPLAIN "+query);
            explainPlan = QueryUtil.getExplainPlan(rs);
            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER T3\n" + 
                    "    SERVER FILTER BY FIRST KEY ONLY\n" + 
                    "    PARALLEL INNER-JOIN TABLE 0\n" + 
                    "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + 
                    "            SERVER FILTER BY FIRST KEY ONLY\n" + 
                    "    DYNAMIC SERVER FILTER BY B.J IN (A.K)",explainPlan);
            query = "SELECT a.k,b.k from t2 b join t1 a ON a.k = b.k where a.col1 || a.col2
= 'foobar'";
            rs = conn.createStatement().executeQuery("EXPLAIN "+query);
            explainPlan = QueryUtil.getExplainPlan(rs);
            assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER T2\n" + 
                    "    SERVER FILTER BY FIRST KEY ONLY\n" + 
                    "    PARALLEL INNER-JOIN TABLE 0\n" + 
                    "        CLIENT PARALLEL 1-WAY RANGE SCAN OVER IDX ['foobar']\n" + 
                    "            SERVER FILTER BY FIRST KEY ONLY\n" + 
                    "    DYNAMIC SERVER FILTER BY B.K IN (A.K)",explainPlan);
        } finally {
            conn.close();
        }
    }
{code}

> Using local index on the 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.9.0
>
>         Attachments: 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.vasudevan@gmail.com] ?



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message