-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

there is a bug in handling bytea columns with index and the like-operator.
At least in 7.3.4. When the FreeBSD Port for 7.4 is ready, I'll test this
.... ;-)

When an index scan is active, a query dosn't give the correct result:


select version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4


begin;
create table test (b bytea);
create index tst_idx on test(b);
insert into test values ('\001abc\006');
insert into test values ('\001xabc\006');
insert into test values ('\001\002abc\006');
insert into test values ('\000\001\002abc\006');
insert into test values ('\002\003abc\006');

select * from test where b like '\001%';


Result:

b
---
(0 Zeilen) [0 rows]

explain analyze select * from test where b like '\001%';
QUERY PLAN
- ---------------------------------------------------------------------------
- -------------------
Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual
time=0.05..0.08 rows=3 loops=1)
Filter: (b ~~ '\\001%'::bytea)
Total runtime: 0.16 msec
(3 Zeilen)


explain analyze select * from test where b like '\001%';
QUERY PLAN
------------------------------------------------------------------------
Index Scan using tst_idx on test (cost=0.00..17.07 rows=5 width=32)
(actual time=0.04..0.04 rows=0 loops=1)
Index Cond: (b = '0'::bytea)
Filter: (b ~~ '\\001%'::bytea)
Total runtime: 0.14 msec


But with seq scan (after vacuuming, creating index later, ...) it works as
expected.

drop index tst_idx;
online_demo=> select * from test where b like '\001%';
b
-----------------
\001abc\006
\001xabc\006
\001\002abc\006
(3 Zeilen)


explain analyze select * from test where b like '\001%';
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on test (cost=0.00..22.50 rows=5 width=32) (actual
time=0.05..0.08 rows=3 loops=1)
Filter: (b ~~ '\\001%'::bytea)
Total runtime: 0.16 msec


hmmm ...

It seems, that bytea is no good idea for production use?


Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zhtwOndlH63J86wRAh9VAJ9tjx/MrvbMPjlqhQqvhbXLaIG5owCfRbAn
S65xELFQ6I9ObdzAXOTjIWM=
=7DuO
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Alvar Freude wrote:
Note that bytea input strings should be escaped with doubled
backslashes, because the string literal parser consumes 1 layer, and the
byteain function consumes another. See:
http://www.postgresql.org/docs/7.3/s...pe-binary.html

In the strings above, the string literal parser will turn, e.g., "\001"
into the single octet '\1' anyway, and byteain will accept it just fine.
However "\000" will become '\0', and since byteain requires a null byte
terminator, you are actually inserting an empty string into test.b for
that row:

regression=# select b, b = '' from test;
b | ?column?
-----------------+----------
\001abc\006 | f
\001xabc\006 | f
\001\002abc\006 | f
(5 rows)

This is weird. I'm sure it worked at one time -- will research.

Joe





---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail (AT) joeconway (DOT) com> wrote:

oh, shi.... -- Yes, I read this, but forgot it ... ;-(

So, yes, you are right.

Hmmm, there are a lot of pitfalls with bytea, *puh*!


Sorry for confusion!


Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zlUROndlH63J86wRArzBAKDC8hXj0GLZrIFlaaIQBt8pk4yi3g CcCDxy
kVnHzc3mklt6/8IcI6ZvD38=
=yUYU
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Alvar Freude <alvar (AT) a-blast (DOT) org> wrote:

sorry, shame on me: I have to put two \ in the query (and inserts) for
bytea.

But it's interesting that without and with index search the handling of
wrong data is different ...


Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/zmq2OndlH63J86wRAsUvAJ9pTFwNNQAsG5cNXGdUcTwmkpIjeQ CeNldH
aJuVsiY2juZqrG73VeSERdc=
=NJ6+
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail (AT) joeconway (DOT) com> wrote:

hmmm, but there remains some confusing stuff. Perhaps I made some other
mistake, but:


I've a filled table with bytea strings (converted from former text column),
and want to make a "like" comparison on it. So, now I'll make double
backslashes for each byte: like doesn't select the rows, when a index
exists. With "=" one row is selected. When the index is deleted, the
correct three rows are returned.

See below. Whats going wrong?

The same happens in my application, when the strings are given unescaped.



db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> SELECT bytea_col FROM table WHERE bytea_col = '\\001\\012\\010';
bytea_col
- --------------
\001\012\010
(1 row)

db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> begin;
BEGIN
db=> drop index table_bytea_col_idx;
DROP INDEX
db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- --------------------------
\001\012\010\001\001\001
\001\012\010
\001\012\010\001\001\002
(3 rows)

db=> rollback;
ROLLBACK
db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010';
bytea_col
- -----
(0 rows)



Ciao
Alvar


- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/znDNOndlH63J86wRAtAyAKDJYq/KPSH7W4rJvO+VJQGe0OQi3wCfQOfr
HmWZiQdc4MW5JecTG0dqwSg=
=h3ag
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster