I believe this is the same bug we encountered with local indexes where Phoenix 4.13.1 failed to join back to the main table if the local index is not covering for all columns referenced in the WHERE.
Please file a JIRA and include the Phoenix and HBase version. Sounds like you’ve found a bug.
I’ve done what you’re looking for by selecting the pk from the index in a nested query and filtering the other column separately.
> On Feb 27, 2018, at 6:39 AM, Alexey Karpov <email@example.com> wrote:
> Thanks for quick answer, but my case is a slightly different. I've seen these
> links and already use local index. All cases, described in faq, index_usage
> and any other, I've found in this user list, are about SELECT clause. In
> WHERE clause there is always field from the index.
> In my case in WHERE clause I have one field from the index and one not from
> the index, combined with AND operator:
> SELECT * from test WHERE name = 'a' *AND description = 'b'*
> name - from the index
> description - not from the index
> Without filter on description (only on name) Phoenix uses index, as expected
> for local index. But with additional filter Phoenix decides to do a full
> scan. And my question is: Is there any way to make Phoenix use index in such
> types of queries, without include all fields in index ?
> Hint does not help:
> SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
> description= 'b'
> ColumnNotFoundException: ERROR 504 (42703): Undefined column.
> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/