should not be prompted for repeated typing

[jd-zhang]

Issue migrated from trac ticket # 555

component: computing nodes | priority: major

2022-04-06 15:21:27: vito@zettadb.com created the issue

---

sql code: CREATE TABLE document ( did int primary key, cid int, dlevel int not null, dauthor name, dtitle text ); GRANT ALL ON document TO public; INSERT INTO document VALUES ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'), ( 4, 44, 1, 'regress_rls_bob', 'my first manga'), ( 5, 44, 2, 'regress_rls_bob', 'my second manga'), ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'), ( 7, 33, 2, 'regress_rls_carol', 'great technology book'), ( 8, 44, 1, 'regress_rls_carol', 'great manga'), ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'), (10, 33, 2, 'regress_rls_dave', 'awesome technology book');

CREATE TABLE category ( cid int primary key, cname text ); GRANT ALL ON category TO public; INSERT INTO category VALUES (11, 'novel'), (22, 'science fiction'), (33, 'technology'), (44, 'manga');

-- alternative UPDATE path happens to be taken): INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') ON CONFLICT (did) DO UPDATE SET dtitle # EXCLUDED.dtitle, dauthorEXCLUDED.dauthor;

psql:sql/rowsecurity.sql:564: ERROR:  Kunlun-db: MySQL storage node (2, 2) returned error: 1062, Duplicate entry '33' for key 'document.PRIMARY'.

expect: INSERT 0 1

INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;

psql:sql/rowsecurity.sql:568: ERROR:  Kunlun-db: MySQL storage node (2, 2) returned error: 1062, Duplicate entry '2' for key 'document.PRIMARY'.

expect:

 did | cid | dlevel |      dauthor      |     dtitle     
-----+-----+--------+-------------------+----------------
   2 |  11 |      2 | regress_rls_carol | my first novel
(1 row)

INSERT 0 1

[jd-zhang]

2022-04-06 15:21:44: vito@zettadb.com

[jd-zhang]

2022-04-06 17:38:22: smith

[jd-zhang]

2022-05-13 18:20:09: vito@zettadb.com commented

---

在版本0.9.2 将不会提示错误,他们的insert的值不同

INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') ON CONFLICT (did) DO UPDATE SET dtitle # EXCLUDED.dtitle, dauthorEXCLUDED.dauthor;

INSERT 0 2

pg的显示只有:INSERT 0 1

INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;

did | cid | dlevel |     dauthor     |     dtitle
-----+-----+--------+-----------------+----------------
   2 |  11 |      1 | regress_rls_bob | my first novel
(1 row)

pg的输出是:

 did | cid | dlevel |      dauthor      |     dtitle
-----+-----+--------+-------------------+----------------
   2 |  11 |      2 | regress_rls_carol | my first novel
(1 row)

[jd-zhang]

2022-05-13 18:24:54: vito@zettadb.com commented

---

-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the -- case in respect of existing tuple):

INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') ON CONFLICT (did) DO UPDATE SET dtitle # EXCLUDED.dtitle, cid33 RETURNING *;

 did | cid | dlevel |     dauthor     |        dtitle
-----+-----+--------+-----------------+-----------------------
  78 |  11 |      1 | regress_rls_bob | some technology novel
(1 row)

pg 的输出是:

 did | cid | dlevel |     dauthor     |        dtitle
-----+-----+--------+-----------------+-----------------------
  78 |  33 |      1 | regress_rls_bob | some technology novel
(1 row)

kunlun这里的cid没有变化