Brian Esserlieu created PHOENIX-2014: ----------------------------------------
Summary: WHERE search condition ignored when also using row value constructor in view Key: PHOENIX-2014 URL: https://issues.apache.org/jira/browse/PHOENIX-2014 Project: Phoenix Issue Type: Bug Affects Versions: 4.3.0 Reporter: Brian Esserlieu Running a SELECT statement against a view with both a regular condition and a row value constructor in the WHERE clause has bad behavior. Here are the repro steps: --REPRO: Create table, create view, insert records, run SELECT query containing the row value constructor. I've included a few extra statements for your convenience. CREATE TABLE IF NOT EXISTS TEST_TABLE.TEST1 ( PK1 CHAR(3) NOT NULL, PK2 CHAR(3) NOT NULL, DATA1 CHAR(10) CONSTRAINT PK PRIMARY KEY ( PK1, PK2 ) ); CREATE VIEW IF NOT EXISTS TEST_TABLE."FOO" AS SELECT * FROM TEST_TABLE.TEST1 WHERE PK1 = 'FOO'; -- Create data UPSERT INTO TEST_TABLE.TEST1 VALUES('FOO','001','SOMEDATA'); UPSERT INTO TEST_TABLE.TEST1 VALUES('FOO','002','SOMEDATA'); UPSERT INTO TEST_TABLE.TEST1 VALUES('FOO','003','SOMEDATA'); UPSERT INTO TEST_TABLE.TEST1 VALUES('FOO','004','SOMEDATA'); UPSERT INTO TEST_TABLE.TEST1 VALUES('FOO','005','SOMEDATA'); --You can verify the data was created correctly if needed: SELECT * FROM TEST_TABLE."FOO"; -- As you can see, this query returns the first 2 rows correctly SELECT * FROM TEST_TABLE."FOO" WHERE PK2 < '004' LIMIT 2 -- For paging through the data, we use a row value constructor: SELECT * FROM TEST_TABLE."FOO" WHERE PK2 < '004' AND ("PK1","PK2") > ('FOO','002') LIMIT 2 -- You would expect this to only return the ('FOO','003','SOMEDATA') row, but it actually returns both the ('FOO','003','SOMEDATA') and ('FOO','004','SOMEDATA') row as well. -- As demonstrated, the condition (PK2 < '004') is completely ignored --Running this statement with no limit better demonstrates that the row value constructor is the only condition processed: SELECT * FROM TEST_TABLE."FOO" WHERE PK2 < '004' AND ("PK1","PK2") > ('FOO','002') -- Clean up if you need: DROP VIEW TEST_TABLE."FOO"; DROP TABLE TEST_TABLE.TEST1; -- This message was sent by Atlassian JIRA (v6.3.4#6332)