[
https://issues.apache.org/jira/browse/PHOENIX-2014?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14591111#comment-14591111
]
Hudson commented on PHOENIX-2014:
---------------------------------
FAILURE: Integrated in Phoenix-master #789 (See
[https://builds.apache.org/job/Phoenix-master/789/])
PHOENIX-2014 WHERE search condition ignored when also using row value
constructor in view (jamestaylor: rev 14d11b130ca0b3726e7724a1f4a9770bc1cb2453)
* phoenix-core/src/test/java/org/apache/phoenix/compile/WhereOptimizerTest.java
* phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
* phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorIT.java
> 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
> Fix For: 5.0.0, 4.5.0, 4.4.1
>
> Attachments: PHOENIX-2014.patch, PHOENIX-2014_v2.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)
