PostgreSQL 9.3.5 substring(text from pattern for escape) bug

From: Robert Schreiber <bobschreiber(at)charter(dot)net>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: PostgreSQL 9.3.5 substring(text from pattern for escape) bug
Date: 2019-05-11 04:02:57
Message-ID: 5bb27a41-350d-37bf-901e-9d26f5592dd0@charter.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I believe I have come across a bug in the substring(text from pattern
for escape) function.

What I am attempting to do is take a string like 'QMy NameQ' , strip off
leading/and/or/trailing Qs and return 'My Name'.

The substring() call that I have coded is substring(xxx from 'Q?#"%#"Q?'
FOR '#')

My understand of this is:
Q?     -- matches 0 or 1 occurrences of Q (the leading one, if present)
#"     -- starts data collection
%    -- matches any number of characters
#"   -- ends data collection
Q?  -- matches 0 or 1 occurrences of Q (the trialing one, if present)

What appears to be happening is that the _Q? on the tail_ is being
matched, but _the Q is also being included in the collected data.__
_
The attached PSQL test.sql file creates a table, populates it with 7
test cases with my expected results, and then executes substring()
against it.

There is an attached test_output.txt file is the PSQL output that
illustrates the problem. The column "Error" is true when the actual
value disagrees with what I expected.

Robert Schreiber
410-392-9553

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
Running under Windows 10 64-bit.

Attachment Content-Type Size
test.sql text/plain 909 bytes
test_output.txt text/plain 1.7 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Daniel Verite 2019-05-11 11:18:23 Re: PostgreSQL 9.3.5 substring(text from pattern for escape) bug
Previous Message Tom Lane 2019-05-10 21:05:09 Re: inconsistent results querying table partitioned by date