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)
