Simon Lee created PHOENIX-2782:
----------------------------------
Summary: Index hint is ignored if table alias is used in the query
Key: PHOENIX-2782
URL: https://issues.apache.org/jira/browse/PHOENIX-2782
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.4.0
Environment: Linux
Reporter: Simon Lee
Priority: Minor
It is not clear to me whether this issue is a bug or an expected behavior.
Anyway, Index hint is ignored if table alias is used in the query
To reproduce the problem,
{code}
1. Create an HBase table with a column family. The column family has 4 columns
(a1, a2, a3, a4). Create an index on (a1, a2).
2. Populate the table with the following data
a1 a2 a3 a4
-- ----- ----- -----
1 Small Red USA
1 Small Yellow UK
1 Small Green China
1 Small Green Australia
3. Create a query with table alias, and use the alias in the select clause and
where clause. In this situation, the index hint is ignored. Phoenix uses full
range scan instead.
*query*
{code}
select /*+ INDEX(add_index_hint_here) */ t.a1, t.a2, t.a3, t.a4 from table t
where t.a1 = 1 and t.a2 = 'Small' and t.a3 = 'Green'
{code}
*explain plan*
{code}
+------------------------------------------+
| PLAN |
+------------------------------------------+
| CLIENT 126-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_table
[[53,54,49,45,72,89,71,45,57,51,55]] |
| SERVER FILTER BY (A.A1 = 1 AND A.A2 = 'Small' AND A.A3 = 'Green' |
+------------------------------------------+
{code}
4. The workaround is to remove the table alias from the query. After that, the
index hint takes effect
*query*
{code}
select /*+ INDEX(add_index_hint_here) */ a1, a2, a3, a4 from table where a1 =
1 and a2 = 'Small' and a3 = 'Green'
{code}
*explain plan*
{code}
+------------------------------------------+
| PLAN |
+------------------------------------------+
| CLIENT 394-CHUNK PARALLEL 1-WAY FULL SCAN OVER my_table |
| SERVER FILTER BY (...) |
| SKIP-SCAN-JOIN TABLE 0 |
| CLIENT 1-CHUNK PARALLEL 1-WAY RANGE SCAN OVER my_index
[[53,54,49,45,72,89,71,45,57,51,55], ...|
| SERVER FILTER BY FIRST KEY ONLY |
| DYNAMIC SERVER FILTER BY ("..."
+------------------------------------------+
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
