Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3301

Row Value Constructors Against Indexes Don't Work Correctly

    XMLWordPrintableJSON

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

          Activity

            People

              Unassigned Unassigned
              rangent Brian Esserlieu
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: