[ https://issues.apache.org/jira/browse/PHOENIX-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Taylor updated PHOENIX-3370: ---------------------------------- Attachment: PHOENIX-3370_wip.patch Attaching unit test that repros the issue. Will work on a fix next. > VIEW derived from another VIEW with WHERE on a TABLE doesn't use parent VIEW > indexes > ------------------------------------------------------------------------------------ > > Key: PHOENIX-3370 > URL: https://issues.apache.org/jira/browse/PHOENIX-3370 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.8.1 > Reporter: Jiping Zhou > Attachments: PHOENIX-3370_wip.patch > > > 1. Create a global table > CREATE TABLE IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_TABLE ( > ORGANIZATION_ID CHAR(15) NOT NULL, > KEY_PREFIX CHAR(3) NOT NULL, > CREATED_DATE DATE, > CREATED_BY CHAR(15), > CONSTRAINT PK PRIMARY KEY ( > ORGANIZATION_ID, > KEY_PREFIX > ) > ) VERSIONS=1, IMMUTABLE_ROWS=true, MULTI_TENANT=true; > 2. Create a global view on base global table with where clause > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW ( > INT1 BIGINT NOT NULL, > DOUBLE1 DECIMAL(12, 3), > IS_BOOLEAN BOOLEAN, > TEXT1 VARCHAR, > CONSTRAINT PKVIEW PRIMARY KEY > ( > INT1 > ) > ) > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE WHERE KEY_PREFIX = '123'; > 3. Create gloabl index on the global view > CREATE INDEX IF NOT EXISTS GLOBAL_INDEX > ON PLATFORM_ENTITY.GLOBAL_VIEW (TEXT1 DESC, INT1) > INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE); > 4. Create tenant specific view on top of global view > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.TENANT_VIEW > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_VIEW; > 5. Query in global view > EXPLAIN SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW WHERE ORGANIZATION_ID > = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1; > The result is > CLIENT 1-CHUNK 1 ROWS 452 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 > KEY OVER PLATFORM_ENTITY.GLOBAL_TABLE > SERVER FILTER BY TEXT1 = 'Test' > It is not using the secondary index at all. The same thing happens on the > Tenant view. > However if we have a global view without where clause like > CREATE VIEW IF NOT EXISTS PLATFORM_ENTITY.GLOBAL_VIEW2 ( > INT1 BIGINT NOT NULL, > DOUBLE1 DECIMAL(12, 3), > IS_BOOLEAN BOOLEAN, > TEXT1 VARCHAR, > CONSTRAINT PKVIEW PRIMARY KEY > ( > INT1 > ) > ) > AS SELECT * FROM PLATFORM_ENTITY.GLOBAL_TABLE; > CREATE INDEX IF NOT EXISTS GLOBAL_INDEX2 > ON PLATFORM_ENTITY.GLOBAL_VIEW2 (TEXT1 DESC, INT1) > INCLUDE (CREATED_BY, DOUBLE1, IS_BOOLEAN, CREATED_DATE); > EXPLAIN SELECT DOUBLE1 FROM PLATFORM_ENTITY.GLOBAL_VIEW2 WHERE > ORGANIZATION_ID = '00Dxx0000002Col' AND TEXT1='Test' AND INT1=1 ORDER BY > TEXT1 DESC, INT1; > The secondary index will be correctly used. -- This message was sent by Atlassian JIRA (v6.3.4#6332)