Mehdi Salarkia created PHOENIX-5950: ---------------------------------------
Summary: View With Where Clause On A Table With Composite Key Should Be Able To Optimize Queries Key: PHOENIX-5950 URL: https://issues.apache.org/jira/browse/PHOENIX-5950 Project: Phoenix Issue Type: Bug Reporter: Mehdi Salarkia For a table with a composite primary {code:java} CREATE TABLE MY_TABLE (K1 INTEGER NOT NULL, K2 VARCHAR NOT NULL,K3 INTEGER NOT NULL, V1 DECIMAL, CONSTRAINT pk PRIMARY KEY (K1, K2, K3)) {code} when a view is created that has some (and not all) of primary key columns {code:java} CREATE VIEW MY_VIEW(v2 VARCHAR, V3 VARCHAR ) AS SELECT * FROM MY_TABLE WHERE K2 = 'A' {code} if you run a query on the view without providing all the primary columns {code:java} EXPLAIN SELECT K1, K2, K3, V1 FROM MY_VIEW WHERE (K1,K3) IN ((1,2),(3,4)); +------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | +------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN SKIP SCAN ON 2 KEYS OVER MY_TABLE [1,'A'] - [3,'A'] | null | null | null | | SERVER FILTER BY (K1, K3) IN ([128,0,0,1,128,0,0,2],[128,0,0,3,128,0,0,4]) | null | null | null | +------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ 2 rows selected (0.047 seconds) {code} the query generated is scan rather than a point look up same query on the parent table looks like this {code:java} EXPLAIN SELECT K1, K2, K3, V1 FROM MY_TABLE WHERE (K1,K2,K3) IN ((1,'A',2),(3,'A',4)); +--------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | +--------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ | CLIENT 1-CHUNK 2 ROWS 268 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 2 KEYS OVER MY_TABLE | 268 | 2 | 0 | +--------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+ 1 row selected (0.025 seconds) {code} The issue is view condition is always added as `AND` to user provided where clause and in this case query optimizer is failing to optimize this query to a point look up. -- This message was sent by Atlassian Jira (v8.3.4#803005)