sql: the updated values on ON CONFLICT do not get constraint-checked properly

[knz]

Consider the following table:

CREATE TABLE ab(a INT PRIMARY KEY, b INT, CHECK (b < 1))

A simple INSERT statement fails, as it should:

> INSERT INTO ab(a,b) VALUES(1, 12312);
pq: failed to satisfy CHECK constraint (b < 1)

Now, the same with INSERT ... ON CONFLICT:

> INSERT INTO ab(a, b) VALUES (1,0); -- create some initial valid value
> INSERT INTO ab(a, b) VALUES (1,0) ON CONFLICT(a) DO UPDATE SET b = 123132;

This succeeds!
And results in a row that fails constraint validation:

> SELECT * FROM ab;
+---+--------+
| a |   b    |
+---+--------+
| 1 | 123132 |
+---+--------+
(1 row)

This may or may not need to be fixed for 2.0, but certainly needs a note in docs as known limitation.

[knz]

Found while working on #23698 / #23373.

[knz]

cc @justinj @dt.

[dt]

cc @BramGruneir should see if this is an issue with FKs too.

[knz]

I checked - FKs do get checked properly, they use a different code path (in the RowInserter/RowUpdater from sqlbase) which is properly run in this case.

[dt]

Thanks!. Let's make sure we get test coverage though all the same, as this is a really easy mutation path to overlook.

[jseldess]

Documented as a known limitation in cockroachdb/docs#2823.

[knz]

@emsal1863 when you're done with #6639 / #26465 you can have a look at this. It's a natural followup now that you understand how this works.