Escaping of special characters in ARRAYs is broken - Mailing list pgsql-bugs

============================================================================                       POSTGRESQL BUG
REPORTTEMPLATE 
============================================================================


Your name        :    Gerhard Häring
Your email address    :       gerhard@bigfoot.de


System Configuration
--------------------- Architecture (example: Intel Pentium)      :   Intel Pentium
 Operating System (example: Linux 2.0.26 ELF)     :   FreeBSD 4.5-STABLE
 PostgreSQL version (example: PostgreSQL-7.1.3):   PostgreSQL-7.2.1
 Compiler used (example:  gcc 2.95.2)        :   gcc 2.95.3


Please enter a FULL description of your problem:
------------------------------------------------

Escaping of special characters in ARRAYs is broken.

I'm not sure if the problem occurs when inserting these special characters or
when fetching them from the database, but I realized inconsistent bahaviour.
You can see the inconsistency yourself when you try to insert a special
character like \005 into a varchar array. IMO This should work with inserting
'{"\\005"}', but this just inserts the three letters 005.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Use this dump:

----- SNIP -----
drop table arraytable;

CREATE TABLE "arraytable" ("v" character varying[],"i" integer[],"m" money[],"f" double precision[]
);

COPY "arraytable" FROM stdin;
{@,"\\\\005"}    \N    \N    \N
\.
----- SNIP -----



Then look at what happens - this is a typescript from me:



----- SNIP -----
Script started on Sun Apr 21 10:31:53 2002
bash-2.05$ psql < escape_bug_testcase.sql
DROP
CREATE
bash-2.05$ psql
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms      \h for help with SQL commands      \? for help on internal slash commands
    \g or terminate with semicolon to execute query      \q to quit 

gerhard=# select v from arraytable;     v
-------------{@,"\\005"}
(1 row)

gerhard=# select v[1] from arraytable where v[1] = '\100';v
---@
(1 row)

gerhard=# select v[2] from arraytable where v[2] = '\005';v
---
(0 rows)

gerhard=# select v[2] from arraytable where v[2] = '\\005'; v
------\005
(1 row)

gerhard=# \q
bash-2.05$ exit

Script done on Sun Apr 21 10:32:48 2002
----- SNIP -----

The finding the second array entry with '\005' does not work, but it does work
for the first entry with '\100'. Thi shows that special chars are not escaped
properly. It may also be that the syntax of the dump is wrong, but just try to
insert a special character like \005 into PostgreSQL and get it back again with
a SELECT. You'll see that this will not work consistently.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


--
mail:   gerhard <at> bigfoot <dot> de       registered Linux user #64239
web:    http://www.cs.fhm.edu/~ifw00065/    OpenPGP public key id AD24C930
public key fingerprint: 3FCC 8700 3012 0A9E B0C9  3667 814B 9CAA AD24 C930
reduce(lambda x,y:x+y,map(lambda x:chr(ord(x)^42),tuple('zS^BED\nX_FOY\x0b')))