Re: Possible bug (or at least unexpected behavior)

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) = 0 THEN output
>         ELSE runs(input[2:],
>                   array_append(output, CASE
>                     WHEN input[1] = 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 number 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) = 0 THEN output
        ELSE runs_p(input[2:],
                  array_append(output, CASE
                    WHEN input[1] = 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