Major 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;