public inbox for [email protected]
help / color / mirror / Atom feedBUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
3+ messages / 3 participants
[nested] [flat]
* BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
@ 2026-05-18 07:54 PG Bug reporting form <[email protected]>
2026-05-21 18:41 ` Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT PetSerAl <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2026-05-18 07:54 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19486
Logged by: Artem Zarubin
Email address: [email protected]
PostgreSQL version: 18.4
Operating system: Debian 13
Description:
Hello, I found a regression in SQL-language functions using XML values and
`IS DOCUMENT`.
Tested commits:
bad: 98dd6c2046965e51da015681e81c20109be46d71, PostgreSQL 18.4
bad: 5107398e6d5ecad96f3d1c0efcfc9aa02b9cdff9, PostgreSQL 19devel
good: e9e7b66044c9e3dfa76fd1599d5703acd3e4a3f5, parent of 0dca5d68
PostgreSQL 18devel before SQL-function plan cache changes
The server was configured with:
./configure --enable-tap-tests --enable-debug --enable-cassert
--with-libxml
SQL-script to reproduce:
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;
$$;
SELECT xml_to_text_no_inline(XMLPARSE(CONTENT '2019-12-16T00:00:00.000'));
Expected result:
2019-12-16T00:00:00.000
Actual result:
ERROR: could not parse XML document
DETAIL: line 1: Start tag expected, '<' not found
2019-12-16T00:00:00.000
^
CONTEXT: SQL function "xpath" statement 1
SQL function "xml_to_text_no_inline" statement 1
---
Best regards,
Artem Zarubin
Postgres Professional: https://postgrespro.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
2026-05-18 07:54 BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT PG Bug reporting form <[email protected]>
@ 2026-05-21 18:41 ` PetSerAl <[email protected]>
2026-05-25 21:23 ` Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT Tom Lane <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: PetSerAl @ 2026-05-21 18:41 UTC (permalink / raw)
To: [email protected]; [email protected]
> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT
2026-05-18 07:54 BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT PG Bug reporting form <[email protected]>
2026-05-21 18:41 ` Re: BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT PetSerAl <[email protected]>
@ 2026-05-25 21:23 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2026-05-25 21:23 UTC (permalink / raw)
To: PetSerAl <[email protected]>; +Cc: [email protected]; [email protected]
PetSerAl <[email protected]> writes:
>> 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.
Yeah, CASE is not strong enough to prevent constant-folding in this
context. You could try something like
create or replace function xml_to_text(pXml xml) returns text
as $$
select
coalesce(
(xpath('/*/text()',
case when pXml is document then pXml else null end))[1],
pXml
)::text;
$$ language sql immutable;
This works because xpath() is strict so it won't try to do anything
with a NULL input, just return NULL; and then the COALESCE() serves
the purpose of injecting pXml when that happens.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-05-25 21:23 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-18 07:54 BUG #19486: Regression in SQL-language functions using XML values and IS DOCUMENT PG Bug reporting form <[email protected]>
2026-05-21 18:41 ` PetSerAl <[email protected]>
2026-05-25 21:23 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox