Skip site navigation (1) Skip section navigation (2)

Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off

From: "Rainer Glueck" <rainer(dot)glueck(at)netminds(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off
Date: 2008-09-04 16:41:42
Message-ID: 48C02BE6.10951.72326F@rainer.glueck.netminds.de (view raw or flat)
Thread:
Lists: pgsql-bugs
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Tom,
Hi list,

(I'm the same as rainer(at)hamburg(dot)ccc(dot)de who started this thread,
this is my regular address, you may compare the gpg-keys if in doubt :)

On 4 Sep 2008 at 11:51, Tom Lane wrote:
> 
> Aren't you looking for standard_conforming_strings?

Of course I did and I mentioned my configuration as:
backslash_quote=off and standard_conforming_strings=on

So it should be as close to the SQL standard as ever possible.


> backslash_quote is something else entirely, and doesn't actually
> do anything at all when backslash escaping is disabled.

Yes, this was my preferred behaviour at all. But I don't see a way:
How can backslashes be stopped from escaping themselves in like-statements
if not by setting both params as "backslash_quote=off" and
"standard_conforming_strings=on" and when not using the "escape" flag?


> > 2. What I do not understand: Why does the fourth statement return a result
> > as backslash_quote is off?
> 
> It looks like a perfectly good match to me.

Hmm, I really would have expected the same behaviour of backslashes
between an equal comparison and a like comparison as backslashes
according to your view and to my conviction are irrelevant in that case.

In short:
The following statements always should return the same
result in a table of just one row, but they do not.

this returns a row:
  select * from tbl_test_escaping
    where test_comment = 'test "\te%s_t<\''\>';

this does not return a row:
  select * from tbl_test_escaping
    where test_comment like '%<\''\>%';

Is this very different behaviour in string handling really intentional?

To have to escape backslashes in _another_ way depending on "=" or "like"
even if conforming strings is on and any special backslash handling is off,
sounds actually very strange and rather unwanted to me.

And what makes me wonder is that I can make it work by adding the escape
flag only in like statement, which I do not need in the case of an equal:

this works fine, too:
  select * from tbl_test_escaping where
    test_comment like '%<\''\>%' escape '';

I'm just looking for any way of configuration where I get this behaviour
but have not to add the escape '' -flag in every single like-statement.

Regards,
Rainer


-----BEGIN PGP SIGNATURE-----
Version: idw's PGP-Frontend 5.1.0.0 / 4-2006 + PGP 6.5.8
Comment: KEY: 0x231D96D8 - FP: 9E01C5D7 7041D0C4 804F4705 76216606 231D96D8

iQA/AwUBSMABv3YhZgYjHZbYEQLAQQCdGOXvt0LWMlzgUZTe1YxBUi3gy1wAnjOh
KHULNV/I/ssb6VNZUYtx/gCq
=w+9P
-----END PGP SIGNATURE-----

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-09-04 16:53:57
Subject: Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off
Previous:From: Tom LaneDate: 2008-09-04 15:51:42
Subject: Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group