UDFs with Mutations Testing Notes

102773 (mutation enablement) follow up

CREATE TABLE a (i INT PRIMARY KEY, j INT, INDEX (j));      

INSERT INTO a VALUES (0, 0);      

-- UPSERT from https://github.com/cockroachdb/cockroach/issues/70731
CREATE FUNCTION f(m INT, n INT) RETURNS INT AS                             
$$                                                                         
   UPSERT INTO a VALUES (m,n) RETURNING j;                                  
$$ LANGUAGE SQL;                                                           

-- The example from https://github.com/cockroachdb/cockroach/issues/70731 but implemented with UDFs.
WITH x AS (SELECT f(0,1)), y AS (SELECT f(0,2)) SELECT * FROM x;           
ERROR: multiple modification subqueries of the same table "a" are not supported unless they all use INSERT without ON CONFLICT; this is to prevent data corruption, see documentation of sql.multiple_modifications_of_table.enabled
SQLSTATE: 0A000

SELECT f(x,y) FROM (VALUES(0,1),(0,2)) v(x,y);                             
  f
-----
  1
  2

select i,j from a@primary;                                                 
  i | j
----+----
  0 | 2

select i,j from a@a_j_idx;                                                 
  i | j
----+----
  0 | 2

-- Similar to udf_upsert tests's f_ocdn
CREATE FUNCTION g(m INT, n INT) RETURNS RECORD AS                          
$$                                                                         
  INSERT INTO a VALUES (m,n) ON CONFLICT DO NOTHING RETURNING *;           
$$ LANGUAGE SQL;  

SELECT g(x,y) FROM (VALUES (0,13), (1,13), (1,15)) v(x,y);                 
    g
----------
  NULL
  (1,13)
  NULL

select i,j from a@primary;                                                 
  i | j
----+-----
  0 |  2
  1 | 13
  
  select i,j from a@a_j_idx;                                                 
  i | j
----+-----
  0 |  2
  1 | 13

-- similar to udf_update test's f2
CREATE FUNCTION h(m INT, n INT) RETURNS INT AS                             
$$                                                                         
   UPDATE a SET j = n WHERE i = m RETURNING j;                              
$$ LANGUAGE SQL; 

SELECT h(x,y) FROM (VALUES(0,3),(0,4)) v(x,y);                             
  h
-----
  3
  4

select i,j from a@primary;                                                 
  i | j
----+-----
  0 |  4
  1 | 13
  
select i,j from a@a_j_idx;                                                 
  i | j
----+-----
  0 |  4
  1 | 13