Re: BUG #19487: Error while executing SQL query involving XML parsing

ср, 20 мая 2026 г. в 04:30, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19487
> Logged by: Ilya Portnov
> Email address: i(dot)portnov(at)compassplus(dot)com
> PostgreSQL version: 18.3
> Operating system: Ubuntu 24.04 LTS
> Description:
>
> Hello.
>
> While testing our software product for compatibility with PostgreSQL 18, we
> found a problem which appears when executing particular query. Initial
> query
> we
> were executing was quite complex and involved our custom SQL functions; we
> tried to simplify it as far as we could, but it's still not very simple.
>
> Environment which we used to reproduce this:
>
> OS: Ubuntu 24.04 LTS
> Architecture: x86_64
> PostgreSQL version() output:
> PostgreSQL 18.3 (Ubuntu 18.3-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit
>
> We also tried on several different Linux distributions and PostgreSQL
> builds, the problem still reproduces.
>
> So, steps to reproduce are:
>
> 1. Create function:
>
> create or replace function xml_to_text(pXml xml) returns text
> as $$
> select
> case when pXml is document
> then (xpath('/*/text()', pXml))[1] ::text
> else pXml::text
> end;
> $$ language sql immutable;
>
> 2. Execute query:
>
> select xml_to_text( (xpath('ns:rq/@Day', case
> when tbl.strcolumn != '' then XMLParse(document tbl.strcolumn)
> when tbl.clobcolumn != '' then XMLParse(DOCUMENT
> replace(replace(replace(replace(replace(tbl.clobcolumn, '&#x07;',
> '?'),'&#x10;', '?'),'&#x13;', '?'),'&#x1C;', '?'), '&#x1D;', '?'))
> else XMLParse(DOCUMENT '<rq></rq>')
> end,
> array[array['ns', 'http://example.com/schema.xsd']]
> ))[1] )
> from
> (
> (select '<ns:rq xmlns:ns="http://example.com/schema.xsd"
> Day="2019-12-16T00:00:00.000"/>' as strcolumn, null as clobcolumn)
> union all
> (select '<ns:rq xmlns:ns="http://example.com/schema.xsd"
> Day="2019-12-16T00:00:00.000"></ns:rq>' as strcolumn, null as clobcolumn)
> ) tbl;
>
> Expected result, which is what we get on PostgreSQL 17.9:
>
> xml_to_text |
> -----------------------+
> 2019-12-16T00:00:00.000|
> 2019-12-16T00:00:00.000|
>
> Actual result on PostgreSQL 18.3:
>
> SQL Error [2200M]: ERROR: could not parse XML document
> Detail: line 1: Start tag expected, '<' not found
> 2019-12-16T00:00:00.000
> ^
> Where: SQL function "xpath" statement 1
> SQL function "xml_to_text" statement 1
>
> It appears that all parts of problematic query are important for this
> problem:
> if I try to simplify it, for example if I try to remove one of five nested
> replace() calls, the problem goes away.
>
> This looks like a problem which was introduced somewhere between PostgreSQL
> 17 and 18. Any help in fixing it will be appreciated.
>
> Best regards,
> Ilya V. Portnov.
>
>
>
>
> Hi, Ilya!

Thanks for the report.
I'm sorry for the long wait with the answer.

# Guilty commit

0dca5d68d7b — Change SQL-language functions to use the plan cache.

- parent `e9e7b66044c`: query OK
- `0dca5d68d7b`: same ERROR as on master

Custom plans substitute Const arguments into the SQL function body.
`eval_const_expressions` then simplifies CASE WHEN results even when the
WHEN condition is a non-Const XmlExpr (`IS DOCUMENT`). Immutable
`xpath()` on the Const non-document content is executed at plan time and
throws.

# Proposal Fix

Fold constant `XmlExpr` in `eval_const_expressions` (same path as
`MinMaxExpr` / `ArrayExpr`), so `IS DOCUMENT` becomes a boolean Const and
CASE drops the unused arm before simplifying `xpath()`.

--
Regards,
Rachitskiy Andrey