plpgsql: SELECT INTO with composite-type variable is incorrect

[DrewKimball]

The SELECT INTO statement can assign the result of executing a single-row statement into a list of comma-separated variables, or a single composite variable (docs). Currently, we only allow the first, and incorrectly handle attempts to use the second form. Example:

CREATE TABLE test2 (x INT, y INT);
INSERT INTO test2 (SELECT t, t%6 FROM generate_series(1, 13) g(t));

root@localhost:26257/defaultdb> CREATE OR REPLACE PROCEDURE get_rows() LANGUAGE plpgsql AS $$
                             ->   DECLARE
                             ->     v test2;
                             ->     n INT;
                             ->     i INT := 0;
                             ->   BEGIN
                             ->     n := (SELECT count(*) FROM test2);
                             ->     WHILE i < n LOOP
                             ->       SELECT x, y INTO v FROM test2 ORDER BY y, x OFFSET i;
                             ->       RAISE NOTICE 'v: %', v;
                             ->       i := i + 1;
                             ->     END LOOP;
                             ->   END
                             -> $$;
                             ->
                             -> CALL get_rows();
CREATE PROCEDURE

Time: 170ms total (execution 169ms / network 0ms)

NOTICE: v: 6
NOTICE: v: 12
NOTICE: v: 1
NOTICE: v: 7
NOTICE: v: 13
NOTICE: v: 2
NOTICE: v: 8
NOTICE: v: 3
NOTICE: v: 9
NOTICE: v: 4
NOTICE: v: 10
NOTICE: v: 5
NOTICE: v: 11
CALL

Time: 18ms total (execution 18ms / network 0ms)

Notice how the v variable seems to store the first integer column returned by the query, rather than a tuple with both the x and y column values.

Jira issue: CRDB-33625

[DrewKimball]

Probably related:

CREATE OR REPLACE PROCEDURE get_rows() LANGUAGE plpgsql AS $$
  DECLARE
    v test2;
    n INT;
    i INT := 0;
  BEGIN
    n := (SELECT count(*) FROM test2);
    WHILE i < n LOOP
      SELECT ROW(x, y) INTO v FROM test2 ORDER BY y, x OFFSET i;
      RAISE NOTICE 'v: %', v;
      i := i + 1;
    END LOOP;
  END
$$;

CALL get_rows();

For the above, postgres fails with ERROR: 22P02: invalid input syntax for type integer: "(6,0)" during the CALL statement, but CRDB happily prints tuples filled out with x and y values.

[blathers-crl[bot]]

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

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