Explain shows that secondary index is not used in version 5.1.3

[Shuai,Ben]

I am experimenting phoenix and see how secondary index is used. I found out 
that global secondary index was not guaranteed to be used even if hint is used. 
 I wondered if I missed any steps.

I used hbase version 2.2.7 with phoenix 5.1.3 (the client and server jar used 
are phoenix-client-embedded-hbase-2.2-5.1.3.jar and 
phoenix-server-hbase-2.2-5.1.3.jar
). Based on https://issues.apache.org/jira/browse/PHOENIX-6458, the hint should 
force index to be used even if the column is not a covered column, but it did 
not.


here is how table and its index were created, and the plan showed full table 
scan:

        CREATE TABLE IF NOT EXISTS  DATATABLE_CF0_NATURE_CEB2 (rowkey VARCHAR 
PRIMARY KEY, C1 VARCHAR, C2 VARCHAR );

        create index DATATABLE_CF0_NATURE_CEB2_IDX1 on 
DATATABLE_CF0_NATURE_CEB2 (C1);

     Plan is also Full scan:
       explain select /*+ INDEX ( DATATABLE_CF0_NATURE_CEB2  
DATATABLE_CF0_NATURE_CEB2_IDX1 ) */ c2 from DATATABLE_CF0_NATURE_CEB2 where 
c1='me';
       
+------------------------------------------------------------------------------------+----------------+---------------+-------------+
       |                                        PLAN                            
            | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
       
+------------------------------------------------------------------------------------+----------------+---------------+-------------+
       | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER 
DATATABLE_CF0_NATURE_CEB2 | null           | null          | null        |
       |     SERVER FILTER BY C1 = 'me'                                         
            | null           | null          | null        |
       
+------------------------------------------------------------------------------------+----------------+---------------+-------------+


In another table created using the same way , the plan did show the secondary 
index was used:

CREATE TABLE pure_phoenix_poc_table ( id INTEGER not null primary key desc, 
name VARCHAR (30), date DATE ,  m.db_utilization DECIMAL, i.db_utilization 
DECIMAL ) m.DATA_BLOCK_ENCODING='DIFF';

create index PURE_PHOENIX_POC_TABLE_IDX_NAME on PURE_PHOENIX_POC_TABLE (name);

explain select /*+ INDEX(PURE_PHOENIX_POC_TABLE 
PURE_PHOENIX_POC_TABLE_IDX_NAME) */ i.DB_UTILIZATION from 
PURE_PHOENIX_POC_TABLE where name = '111';
+---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
|                                               PLAN                            
                    | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER 
PURE_PHOENIX_POC_TABLE_IDX_NAME ['111'] | null           | null          | null 
       |
|     SERVER MERGE [I.DB_UTILIZATION]                                           
                    | null           | null          | null        |
|     SERVER FILTER BY FIRST KEY ONLY                                           
                    | null           | null          | null        |
+---------------------------------------------------------------------------------------------------+----------------+---------------+-------------+

Any help is appreciated. Thanks