Thread

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

PG Bug reporting form <noreply@postgresql.org> — 2026-05-19T09:01:10Z

The following bug has been logged on the website:

Bug reference:      19487
Logged by:          Ilya Portnov
Email address:      i.portnov@compassplus.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, '',
'?'),'', '?'),'', '?'),'', '?'), '', '?'))
        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.