Lists: | pgsql-bugs |
---|
From: | "Rainer" <rainer(at)hamburg(dot)ccc(dot)de> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4398: Backslashes get escaped despite of backslash_quote=off |
Date: | 2008-09-04 11:56:56 |
Message-ID: | 200809041156.m84Buu0v067517@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4398
Logged by: Rainer
Email address: rainer(at)hamburg(dot)ccc(dot)de
PostgreSQL version: 8.3.3 /b1400
Operating system: Windows XP
Description: Backslashes get escaped despite of backslash_quote=off
Details:
Subject to: Backslashes get escaped in like operations despite of
backslash_quote=off
psql \version: PostgreSQL 8.3.3, compiled by Visual C++ build 1400
Goal to achieve: SQL compliant handling of escaping which means...
Expected result: ...backslashes are not to be escaped, single quotes are to
be escaped by itself
Current configuration is: backslash_quote=off and
standard_conforming_strings=on
Problem/Bug is: Operator "=" works fine, operator "like" does not (see test
case below)
Status is: I checked the three concerning manual chapters (4.1.2.1., 9.7.1.,
18.12.1.) which promise another result than actually received by the
following test case:
To see for yourself please feel free to use the following short script to
create a small test database with a table and a single test record:
-- --- BOF ---
create database dbs_test_escaping with encoding 'LATIN9';
\connect dbs_test_escaping
create table tbl_test_escaping
(
test_id int4 not null,
test_name varchar(100),
test_comment text,
constraint prk_test_id primary key (test_id)
);
-- this should make the comment actually to be like this: test "\te%s_t
<\'\>
insert into tbl_test_escaping
(test_id, test_name, test_comment)
values (1, 'Testname', 'test "\te%s_t <\''\>');
-- --- EOF ---
Now check the following statements whose results seem not to reflect the
above configuration:
1. Select the record by a single equal (works fine):
select * from tbl_test_escaping where test_comment = 'test "\te%s_t
<\''\>';
2. Select the record by a like-statement (won't return any result):
select * from tbl_test_escaping where test_comment like '%<\''\>%';
3. Select the record by a like-statement which is manually escaped and has
the escape flag reset (works fine):
select * from tbl_test_escaping where test_comment like '%<\''\>%' escape
'';
4. Select the record by a like-statement which is manually escaped (should
not match because of backslash_quote=off but it matches):
select * from tbl_test_escaping where test_comment like '%<\\''\\>%';
Two questions:
1. What I actually want: Shouldn't the second statement work by
documentation without the escape flag?
2. What I do not understand: Why does the fourth statement return a result
as backslash_quote is off?
Thanks sincerely,
Rainer
rainer(at)hamburg(dot)ccc(dot)de
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Rainer" <rainer(at)hamburg(dot)ccc(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off |
Date: | 2008-09-04 15:51:42 |
Message-ID: | 23710.1220543502@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Rainer" <rainer(at)hamburg(dot)ccc(dot)de> writes:
> Description: Backslashes get escaped despite of backslash_quote=off
Aren't you looking for standard_conforming_strings? backslash_quote is
something else entirely, and doesn't actually do anything at all when
backslash escaping is disabled.
> Two questions:
> 1. What I actually want: Shouldn't the second statement work by
> documentation without the escape flag?
No. standard_conforming_strings has nothing to do with the behavior of
LIKE (nor does backslash_quote). They just control the initial parsing
of SQL string literals.
> 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.
regards, tom lane
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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-----
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Rainer Glueck" <rainer(dot)glueck(at)netminds(dot)de> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4398: Backslashes get escaped despite of backslash_quote=off |
Date: | 2008-09-04 16:53:57 |
Message-ID: | 24915.1220547237@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Rainer Glueck" <rainer(dot)glueck(at)netminds(dot)de> writes:
> How can backslashes be stopped from escaping themselves in like-statements
Use ESCAPE ''. There isn't any other way provided. I suppose we might
consider inventing a separate GUC parameter to specify what the default
escape sequence is, but we wouldn't overload any existing parameter to
do it.
I think the reason the issue hasn't come up before is that it doesn't
seem real useful: if you have no escape character then you have no
way to quote % and _, and those are at least as likely to need to be
plain data characters as \ is.
regards, tom lane
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-05 08:22:54 |
Message-ID: | 48C1087E.10389.3CFE48D@rainer.glueck.netminds.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello Tom,
On 4 Sep 2008 at 12:53, Tom Lane wrote:
>
> "Rainer Glueck" <rainer(dot)glueck(at)netminds(dot)de> writes:
> > How can backslashes be stopped from escaping themselves in like-statements
>
> Use ESCAPE ''. There isn't any other way provided.
> ...
> if you have no escape character then you have no way to quote % and _
Yes, that's true indeed.
And since I learned that the escape clause is recommended to cope
with that matter not only by PostgreSQL, but also by Transact-SQL,
I'm going to adapt my database connector this way, withdraw this topic
and thank you for pushing me a little bit more to enlightenment. :)
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/AwUBSMDeV3YhZgYjHZbYEQKv3gCgx6UWMbVLsym6hb8/DG91JMNX3OAAoK9M
NrpA0F3AGjPeqacOks2AVtJe
=8SXN
-----END PGP SIGNATURE-----