Ban update statement which can modify the PK column

[st1page]

We can not handle it now, so we need to ban it or the result can make user confused
case 1:

dev=> create table t(k int primary key, cnt int);
CREATE_TABLE
dev=> insert into t values (1,1), (2,2), (3,3);
INSERT 0 3
dev=> UPDATE t SET k = 1;      
UPDATE 3
dev=> select * from t;
 k | cnt 
---+-----
 1 |   1
(1 row)

case 2:

dev=> create table t(k int primary key, cnt int);
CREATE_TABLE
dev=> insert into t values (1,1), (2,2), (3,3);
INSERT 0 3
dev=> UPDATE t SET k = k+1;
UPDATE 3
dev=> select * from t;
 k | cnt 
---+-----
 2 |   1
 3 |   3
 4 |   3
(3 rows)

[y-wei]

As for the second case it seems that the returns of update were fine but went wrong when being stored 🧐

dev=> UPDATE t SET k = k+1 returning *;
 k | cnt 
---+-----
 4 |   3
 2 |   1
 3 |   2
(3 rows)

UPDATE 3
dev=> select * from t;
 k | cnt 
---+-----
 3 |   2
 4 |   3
 2 |   2
(3 rows)

[st1page]

As for the second case it seems that the returns of update were fine but went wrong when being stored 🧐

Yes, this is because of RisingWave's DML implementation, and the RETURNING clause can not reflect what happens on storage exactly. There is the same issue with the return result of the DML statement which is expected to be the number of the result.

[y-wei]

close as completed #8569