[
https://issues.apache.org/jira/browse/PHOENIX-2782?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Simon Lee updated PHOENIX-2782:
-------------------------------
Description:
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,
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
{code}
a1 a2 a3 a4
-- ----- ----- -----
1 Small Red USA
1 Small Yellow UK
1 Small Green China
1 Small Green Australia
{code}
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}
was:
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}
> 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,
> 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
> {code}
> a1 a2 a3 a4
> -- ----- ----- -----
> 1 Small Red USA
> 1 Small Yellow UK
> 1 Small Green China
> 1 Small Green Australia
> {code}
> 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)
