Thread: BUG #15756: Seemingly inconsistent behavior of SUBSTRING(string FROM pattern FOR escape) function

The following bug has been logged on the website:

Bug reference:      15756
Logged by:          Sergei Abramov
Email address:      auspex@rambler.ru
PostgreSQL version: 9.5.16
Operating system:   Linux, Windows
Description:

When statement SELECT SUBSTRING('+380481234567' FROM 5 FOR 9),
SUBSTRING('+380481234567' FROM '%#"[0-9]{9}#"' FOR '#') is issued in pgAdmin
III Query both functions give the same correct results which are expected
when psql command line tool is to be used:
psql --username=postgres --no-password --command="SELECT
SUBSTRING('+380481234567' FROM 5 FOR 9), SUBSTRING('+380481234567' FROM
'%#"[0-9]{9}#"' FOR '#')"
But here something strange occurs! The latter function invocation
permanently returns NULL value that can be easily seen when wrapped in
COALESCE:
psql --username=postgres --no-password --command="SELECT
COALESCE(SUBSTRING('+380481234567' FROM '%#"[0-9]{9}#"' FOR '#'), 'NULL')"
This behavior is reproduced in Windows XP 32-bit, Windows 7 32-bit (both
local PG-servers) and in Linux 64 bit (both local and remote PG-servers) for
PG versions 9.3.23, 9.4.9, and 9.5.16.

Thanks for your help!

> On 15 Apr 2019, at 12:01, PG Bug reporting form <noreply@postgresql.org> wrote:

> The latter function invocation
> permanently returns NULL value that can be easily seen when wrapped in
> COALESCE:
> psql --username=postgres --no-password --command="SELECT
> COALESCE(SUBSTRING('+380481234567' FROM '%#"[0-9]{9}#"' FOR '#'), 'NULL’)"

When executing this in a shell, I believe you need to escape the quotes in the
query.  The below commandline seems to work fine for me (tested in 9.4 and
current master):

$ ./bin/psql --command=“SELECT COALESCE(SUBSTRING('+380481234567' FROM '%#\"[0-9]{9}#\"' FOR '#'), 'NULL')" postgres
 coalesce
-----------
 481234567
(1 row)

cheers ./daniel

On Mon, Apr 15, 2019 at 12:02 PM PG Bug reporting form
<noreply@postgresql.org> wrote:

> when psql command line tool is to be used:
> psql --username=postgres --no-password --command="SELECT
> SUBSTRING('+380481234567' FROM 5 FOR 9), SUBSTRING('+380481234567' FROM
> '%#"[0-9]{9}#"' FOR '#')"
> But here something strange occurs! The latter function invocation

You are not using psql here to send the same query to the server. You
are using the shell to invoke sql with some arguments, and the shell
does quote parsing and similar things. To make the correct example you
should do the same thing you do with pgAdmin, start psql and then type
the query to it.

Windows /unix shells have some strange quoting behaviours which I'm
not completely familiar with, and you haven't told us which one you
use. If you are using the classic bash it is going to remove your
quotes, if you send "aaa'bbbb"cccc"dddd'eeee" to bash it is going to
produce <aaa'bbbcccddd'eee>, as quotes do not nest, and that is what
psql is going to see.

Start with the simple thing, test firing psql and typing the uery
inside it, then, if it still fails, report that result, else elaborate
from this ( try more elaborate quoting, sending the query in a file /
stdin, etc.. ).

Francisco Olarte.