From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adam Mackler <adam@mackler.email>
cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: Possible bug (or at least unexpected behavior)
In-reply-to: 
 <WScDU5qfoZ7PB2gXwNqwGGgDPmWzz08VdydcPFLhOwUKZcdWbblbo-0Lku-qhuEiZoXJ82jpiQU4hOjOcrevYEDeoAvz6nR0IU4IHhXnaCA=@mackler.email>
References: 
 <WScDU5qfoZ7PB2gXwNqwGGgDPmWzz08VdydcPFLhOwUKZcdWbblbo-0Lku-qhuEiZoXJ82jpiQU4hOjOcrevYEDeoAvz6nR0IU4IHhXnaCA=@mackler.email>
Comments: In-reply-to Adam Mackler <adam@mackler.email>
	message dated "Sun, 07 Aug 2022 20:06:21 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <291121.1659915101.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 07 Aug 2022 19:31:41 -0400
Message-ID: <291122.1659915101@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/291122.1659915101%40sss.pgh.pa.us>
Precedence: bulk

Adam Mackler <adam@mackler.email> writes:
> Briefly, given the following function:

>     CREATE FUNCTION runs(input int[], output int[] DEFAULT '{}')
>     RETURNS int[] AS $$
>       SELECT
>         CASE WHEN cardinality(input) =3D 0 THEN output
>         ELSE runs(input[2:],
>                   array_append(output, CASE
>                     WHEN input[1] =3D 0 THEN 0
>                     ELSE output[cardinality(output)] + input[1]
>                   END)
>                  )
>         END
>     $$ LANGUAGE SQL;

> I expect the following invocation to return an array with the same numbe=
r of elements as the passed-in argument array:

>     # select runs('{0,1,1,1,1,0,-1,-1,-1,0}');
>                       runs
>     ----------------------------------------
>      {0,1,2,3,4,5,6,0,0,0,-1,-2,-3,-4,-5,0}
>     (1 row)

Yeah, there's a bug in here somewhere.  If you transpose the logic
into plpgsql, it behaves fine:

    CREATE FUNCTION runs_p(input int[], output int[] DEFAULT '{}')
    RETURNS int[] AS $$
    begin
      return
        CASE WHEN cardinality(input) =3D 0 THEN output
        ELSE runs_p(input[2:],
                  array_append(output, CASE
                    WHEN input[1] =3D 0 THEN 0
                    ELSE output[cardinality(output)] + input[1]
                  END)
                 )
        END;
    end
    $$ LANGUAGE plpgsql;

so that might do as a workaround.  It looks like memory management
in SQL functions is not coping well with expanded arrays, but I'm
not quite sure where it's going off the rails.

			regards, tom lane