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

WHERE search condition ignored when also using row value constructor in view

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.3.0, 4.3.1
    • Fix Version/s: 4.5.0, 4.4.1
    • Labels:
      None

      Description

      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;

        Attachments

        1. PHOENIX-2014_v2.patch
          7 kB
          James Taylor
        2. PHOENIX-2014.patch
          4 kB
          James Taylor

          Activity

            People

            • Assignee:
              jamestaylor James Taylor
              Reporter:
              rangent Brian Esserlieu
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: