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

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

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 (view raw or flat)
Thread:
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

Responses

pgsql-bugs by date

Next:From: MattDate: 2008-09-04 12:10:56
Subject: BUG #4399: List of Servers Cleared
Previous:From: Rudolf LeitgebDate: 2008-09-04 09:59:46
Subject: Re: BUG #4397: crash in tab-complete.c

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