phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Samarth Jain (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-2014) WHERE search condition ignored when also using row value constructor in view
Date Wed, 27 May 2015 23:19:18 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-2014?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14561966#comment-14561966
] 

Samarth Jain commented on PHOENIX-2014:
---------------------------------------

[~rangent] - would you mind trying with our 4.3.1 release. I want to confirm if this has already
been fixed as part of https://issues.apache.org/jira/browse/PHOENIX-1753. Thanks!

> 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)

Mime
View raw message