public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PetSerAl <petseral@gmail.com>
To: a.prototype7@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
Date: Thu, 21 May 2026 21:41:51 +0300
Message-ID: <CAKygsHTkCSHzR82G5ePAJ3wQ7BP4Z=qaUymLEZYo8Pvo4+dYSw@mail.gmail.com> (raw)
In-Reply-To: <19486-f1cbfe2bd1c9c3d9@postgresql.org>
References: <19486-f1cbfe2bd1c9c3d9@postgresql.org>

>   CREATE OR REPLACE FUNCTION xml_to_text_no_inline(pXml xml) RETURNS text
>   LANGUAGE sql
>   IMMUTABLE
>   SET search_path = pg_catalog
>   AS $$
>   SELECT CASE WHEN pXml IS DOCUMENT
>               THEN (xpath('/*/text()', pXml))[1]::text
>               ELSE pXml::text
>          END;
>   $$;

There is bug in that function. Expectation, that `xpath('/*/text()',
pXml)` will be evaluate only after successful `pXml IS DOCUMENT`
check, is not supported by documentation.
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-EXPRESS-EVAL

The order of evaluation of subexpressions is not defined. In
particular, the inputs of an operator or function are not necessarily
evaluated left-to-right or in any other fixed order.
...
When it is essential to force evaluation order, a `CASE` construct
(see Section 9.18) can be used.
...

`CASE` is not a cure-all for such issues, however. One limitation of
the technique illustrated above is that it does not prevent early
evaluation of constant subexpressions. As described in Section 36.7,
functions and operators marked `IMMUTABLE` can be evaluated when the
query is planned rather than when it is executed. Thus for example
```
SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;
```
is likely to result in a division-by-zero failure due to the planner
trying to simplify the constant subexpression, even if every row in
the table has `x > 0` so that the `ELSE` arm would never be entered at
run time.

While that particular example might seem silly, related cases that
don't obviously involve constants can occur in queries executed within
functions, since the values of function arguments and local variables
can be inserted into queries as constants for planning purposes.
Within PL/pgSQL functions, for example, using an `IF`-`THEN`-`ELSE`
statement to protect a risky computation is much safer than just
nesting it in a `CASE` expression.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: petseral@gmail.com, a.prototype7@gmail.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
  In-Reply-To: <CAKygsHTkCSHzR82G5ePAJ3wQ7BP4Z=qaUymLEZYo8Pvo4+dYSw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox