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

Brian Esserlieu commented on PHOENIX-3301:
------------------------------------------

[~jamestaylor] I updated the description to include proper repro steps. Please 
let me know if anything there is unclear. CC [~samarthjain]

> Row Value Constructors Against Indexes Don't Work Correctly
> -----------------------------------------------------------
>
>                 Key: PHOENIX-3301
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-3301
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.8.0
>            Reporter: Brian Esserlieu
>
> Why does adding an ORDER BY change the number of records returned when there 
> is a secondary index? 
> Why without including an ORDER by are no records returned (seems to be 
> hitting the base table and not the index)?
> I've included repro steps below:
> *Repro*
> {code:title=repro.sql|borderStyle=solid}
> DROP INDEX IF EXISTS TEST_INDEX ON TEST_TABLE;
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE IF NOT EXISTS TEST_TABLE (
>     PK1 CHAR(15) NOT NULL,
>     PK2 DATE NOT NULL
>     CONSTRAINT PK PRIMARY KEY 
>     ( 
>         PK1,
>         PK2 DESC
>     )
> );
> CREATE INDEX IF NOT EXISTS TEST_INDEX ON TEST_TABLE (PK2, PK1);
> UPSERT INTO TEST_TABLE (PK1, PK2) 
> VALUES ('abc123',TO_DATE('2010-01-01T00:00:01Z'));
> UPSERT INTO TEST_TABLE (PK1, PK2) 
> VALUES ('abc123',TO_DATE('2010-01-01T00:00:02Z'));
> UPSERT INTO TEST_TABLE (PK1, PK2) 
> VALUES ('abc123',TO_DATE('2010-01-01T00:00:03Z'));
> -- Selects 
> --This select statement returns 0 rows, though it should be returning the 3 
> upserted rows from above
> SELECT PK1, PK2
> FROM TEST_TABLE 
> WHERE (PK2, PK1) >= (TO_DATE('1970-01-01'), null)
> -- the EXPLAIN shows the base table is being hit. Why is the base table hit 
> with this RVC?
> explain SELECT PK1, PK2
> FROM TEST_TABLE 
> WHERE (PK2, PK1) >= (TO_DATE('1970-01-01'), null)
> --Note: by adding an ORDER BY statement, the query returns all 3 rows
> SELECT PK1, PK2
> FROM TEST_TABLE 
> WHERE (PK2, PK1) >= (TO_DATE('1970-01-01'), null)
> ORDER BY PK2, PK1;
> -- the EXPLAIN shows the secondary index is now being used by this query
> explain SELECT PK1, PK2
> FROM TEST_TABLE 
> WHERE (PK2, PK1) >= (TO_DATE('1970-01-01'), null)
> ORDER BY PK2, PK1;
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to