sql: test ON UPDATE interactions

[pawalt]

Upon completion of #69057, we will need to test ON UPDATE interactions with other types of constraints. Of interest are:

• Interaction with foreign key updates
• Interaction with DEFAULT expressions
• Interaction with backfills

Epic CRDB-9148

[vy-ton]

@rafiss Do we need to do this for 21.2?

[otan]

In particular:

• Interaction with foreign key updates
  Ensure we can't create FKs with an ON UPDATE on the same column. Thinks that's done.
  When another table does an update on a FK that e.g. sets the value to NULL, do we want ON UPDATE to trigger? I'm guessing no?

• Interaction with DEFAULT expressions
  ? checking having both populated works ?
  this already seems tested, but idk what peyton had in mind

• Interaction with backfills
  Backfills over the row (e.g. ADD COLUMN) do not trigger an ON UPDATE change.

[rafiss]

thanks @otan !

@vy-ton for 21.2 would you be able to include the above in your acceptance testing? we will also keep this issue open and work on adding these tests during this milestone

[vy-ton]

I can check these scenarios

Ensure we can't create FKs with an ON UPDATE on the same column.
Is this a FK referencing the ON UPDATE column?

[otan]

Ensure we can't create FKs with an ON UPDATE on the same column.
Is this a FK referencing the ON UPDATE column?

You can't have id INT ON UPDATE 2 REFERENCES(other-table.int) ON UPDATE SET NULL

[vy-ton]

• Interaction with DEFAULT expressions

CREATE TABLE t1 (
  i int,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  d DATE DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
insert into t1(i) values (1), (2), (3);
update t1 set i = 10 where i = 1;
update t1 set (i, ts, d) = (select i, ts, d from t1 where i = 2) where i = 2;

• Interaction with backfills: Backfills over the row (e.g. ADD COLUMN) do not trigger an ON UPDATE change.

vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> CREATE TABLE inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT DEFAULT 100 ON UPDATE 50,
    PRIMARY KEY (product_id, warehouse_id)
  );
INSERT INTO inventories (product_id, warehouse_id, quantity_on_hand) VALUES (1, 1, 1);
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> insert into inventories (product_id, warehouse_id) values (2, 2);
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> select * from inventories;
  product_id | warehouse_id | quantity_on_hand
-------------+--------------+-------------------
           1 |            1 |                1
           2 |            2 |              100
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> ALTER TABLE inventories ADD COLUMN new_col INT DEFAULT 13;
vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> select * from inventories;
  product_id | warehouse_id | quantity_on_hand | new_col
-------------+--------------+------------------+----------
           1 |            1 |                1 |      13
           2 |            2 |              100 |      13
         

• Interaction with foreign key updates

vy@free-tier9.gcp-asia-southeast1.crdb.io:26257/defaultdb> CREATE TABLE orders_2 (
    id INT PRIMARY KEY,
    customer_id INT ON UPDATE 10 REFERENCES customers_2(id) ON UPDATE CASCADE ON DELETE CASCADE
  );
ERROR: cannot specify both ON UPDATE expression and a foreign key ON UPDATE action for column with ID 2
SQLSTATE: 42P16

[rafiss]

We found one buggy interaction where ALTER TYPE (experimental) deletes the ON UPDATE clause: #71571

[ZhouXing19]

• Interaction with foreign key updates (already covered)
• Interaction with DEFAULT expressions (already covered)
• PR for Interaction with backfills over row