Details
-
Bug
-
Status: Reopened
-
Major
-
Resolution: Unresolved
-
4.8.0
-
None
-
None
-
None
Description
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
repro.sql
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;
Attachments
Issue Links
- is related to
-
PHOENIX-4841 Filters that uses RVC with pk columns where with DESC sort order don't work correctly
- Closed