[PHOENIX-4632] Phoenix does not use local index if there is a field in where clause not included in index

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.7.0
• Assignee: Unassigned
• Reporter: Alexey Karpov
• Created: 28/Feb/18 09:26
• Updated: 02/Mar/18 19:57

Description

HDP 2.6.2, Phoenix 4.7.0, HBase 1.1.2.2.6.2.0-205

If in WHERE clause you have one field from the local index and one not from
the index, combined with AND operator, Phoenix decides to do a full scan.

Steps to reproduce:

CREATE TABLE test (id integer NOT NULL PRIMARY KEY, 
name VARCHAR, description VARCHAR)

CREATE LOCAL INDEX ix_test_name ON test(name)

explain  SELECT * FROM test WHERE name = 'a' AND description = 'b' 

CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER TEST
SERVER FILTER BY (NAME = 'a' AND DESCRIPTION = 'b')

After removing not indexed field in starts working as expected:

explain  SELECT * FROM test WHERE name = 'a' 

CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER TEST [1,'a']
SERVER FILTER BY FIRST KEY ONLY