[
https://issues.apache.org/jira/browse/PHOENIX-3301?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Brian Esserlieu reopened PHOENIX-3301:
--------------------------------------
> 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)
