[PHOENIX-7046] Query results return different values when PKs of view have DESC order

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 5.2.0, 5.1.4
• Assignee: Viraj Jasani
• Reporter: Jacob Isaac
• Created: 21/Sep/23 17:55
• Updated: 11/Oct/23 04:59
• Resolved: 11/Oct/23 04:59

Description

To reproduce -

CREATE TABLE IF NOT EXISTS TEST_ENTITY.T1(OID CHAR(15) NOT NULL,KP CHAR(3) NOT NULL, COL1 VARCHAR CONSTRAINT pk PRIMARY KEY (OID,KP)) MULTI_TENANT=true,COLUMN_ENCODED_BYTES=0;

CREATE VIEW IF NOT EXISTS TEST_ENTITY.G1_P01(ID1 INTEGER not null, COL4 VARCHAR, CONSTRAINT pk PRIMARY KEY (ID1 DESC)) AS SELECT * FROM TEST_ENTITY.T1 WHERE KP = 'P01';

CREATE VIEW IF NOT EXISTS TEST_ENTITY.TV_P01(ROW_ID CHAR(15) NOT NULL,COLA VARCHAR CONSTRAINT pk PRIMARY KEY (ROW_ID)) AS SELECT * FROM TEST_ENTITY.G1_P01 WHERE ID1 = 42724;

UPSERT INTO TEST_ENTITY.TV_P01(OID, ROW_ID, COL4, COLA) VALUES('00D0y0000000001', '00Z0y0000000001','d07223','a05493');

SELECT ID1, COL4 FROM TEST_ENTITY.TV_P01;
SELECT ID1, COL4 FROM TEST_ENTITY.G1_P01;

 

 

Issue Links

• breaks: PHOENIX-6978 Redesign Phoenix TTL for Views
• links to: GitHub Pull Request #1689