[
https://issues.apache.org/jira/browse/PHOENIX-2014?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-2014:
----------------------------------
Attachment: PHOENIX-2014.patch
[~samarthjain] - not the perfect fix as we really want to intersect the
minMaxRange even when the key slot isn't the leading key. But this fixes the
issue and we can do more down the road (see PHOENIX-2044).
> 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, 4.3.1
> Reporter: Brian Esserlieu
> Assignee: James Taylor
> Attachments: PHOENIX-2014.patch
>
>
> 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)
