sql: PL/pgSQL routine prematurely exits when SELECT ... INTO statements produce zero rows

[mgartner]

See the example below.

CREATE TABLE t (a INT);

CREATE FUNCTION f() RETURNS INT AS $$
  DECLARE
    found INT;
  BEGIN
    RAISE NOTICE 'HERE 1';
    SELECT a INTO found FROM t WHERE a = 3;

    -- The function exits here and returns NULL when the SELECT
    -- above does not find any rows.

    RAISE NOTICE 'HERE 2';
    RETURN 10;
  END
$$ LANGUAGE PLpgSQL;

-- ‼️ The function incorrectly exits early when no rows match the
-- SELECT ... INTO query.
SELECT f();
-- NOTICE: HERE 1
--    f
-- --------
--   NULL

INSERT INTO t VALUES (3);

-- After inserting a matching row, the function executes correctly.
SELECT f();
-- NOTICE: HERE 1
-- NOTICE: HERE 2
--   f
-- ------
--   10

Jira issue: CRDB-33702

[mgartner]

I think this may be a GA blocker. I've added the label.

[blathers-crl]

Hi @mgartner, please add branch-* labels to identify which branch(es) this release-blocker affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}