public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: i.portnov@compassplus.com
Subject: BUG #19487: Error while executing SQL query involving XML parsing
Date: Tue, 19 May 2026 09:01:10 +0000
Message-ID: <19487-367258bc497b923a@postgresql.org> (raw)

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, '&#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.

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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, i.portnov@compassplus.com
  Subject: Re: BUG #19487: Error while executing SQL query involving XML parsing
  In-Reply-To: <19487-367258bc497b923a@postgresql.org>

* 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