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)
