Re: BUG #16368: Incorrect function inlining in the presence of a window function

On Wed, Apr 15, 2020 at 11:07 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 16368
> Logged by: Elvis Pranskevichus
> Email address: elprans(at)gmail(dot)com
> PostgreSQL version: 12.2
> Operating system: Gentoo Linux
> Description:
>
> Consider the following function:
>
> CREATE OR REPLACE FUNCTION intfmt(input text, fmt text)
> [...]
> SELECT
> CASE WHEN fmt IS NULL
> THEN input::bigint
> ELSE to_number(input, fmt)::bigint
> END;
> [...]
> SELECT
> [...]

intfmt('123,456', q.fmt) AS "out"
>

>
> The expected result is the integer 123456, but the query fails with:
>
> ERROR: invalid input syntax for type bigint: "123,456"
> CONTEXT: SQL function "intfmt" during inlining
>
> Which means that somehow during inlining of "intfmt" Postgres incorrectly
> takes the first branch in the `CASE` expression.

During inlining the case expression becomes:

CASE WHEN q.fmt IS NULL
THEN '123,456'::bigint
ELSE to_number('123,456', q.fmt)
END;

It doesn't "take" a branch - it turns variables into constants and, as
written, some of those constants are invalid for the types they are being
assigned to.

> This only happens in the
> presence of the "first_value" window call in the nested query.
>

The ability to optimize, and how, depends on the whole query.

I don't actually know whether this is a bug or just an expected downside to
using inline-able functions and case statements to avoid malformed data
parsing.

Writing the function in pl/pgsql prevents the inlining and stabilizes the
query.

David J.