[
https://issues.apache.org/jira/browse/PHOENIX-2014?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14562009#comment-14562009
]
Brian Esserlieu commented on PHOENIX-2014:
------------------------------------------
Sure, no problem. :)
> 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)
