bytea, index and like operator again and detailed report

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

Hi,

while changing a column from base255 encoded text (all except null byte) to
bytea, I found the following bug in Postgresql's LIKE operator with indexes
(it follows a more detailed description then my old mails in -bugs and
- -general, including the proof of the bug):

The index condition in the query plan for "where bytea_column like 'a%'" is:

Index Cond: (bytea_col >= 'a'::bytea) AND (bytea_col < 'b'::bytea))
Filter: (bcol ~~ 'a%'::bytea)

This is correct.

The index condition in the query plan for "bytea_column like '\\141%'" ("a"
in octal is 141) is exaclty the same, including filter condition.

Index Cond: ((bcol >= 'a'::bytea) AND (bcol < 'b'::bytea))
Filter: (bcol ~~ 'a%'::bytea)

This is also correct.

The index condition in the query plan for "bytea_column like '\\001%'" is:

Index Cond: (bcol = '0'::bytea)
Filter: (bcol ~~ '\\001%'::bytea)

THIS IS WRONG! Isn't it?

If the byte is displayable in ASCII, then all is OK. If not, it seems that
Postgres takes the first character of the octal number and uses this as
comparison parameter.
With "ä" (344) it takes "3" ...

When index scan is disabled or from other reasons seqscan is used, the
query plan and the result is correct.

The result differs, if index is used or not used.

I guess there is too much conversion between different character sets etc.

A piece of test SQL and the results are attached.

My Version is:
PostgreSQL 7.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4

The same was with 7.3.4

Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/ -- http://odem.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/z6YbOndlH63J86wRAr+qAKCo6yi3/0HGO13IkKP2KbyH147kMACeKq7T
WEKPu3dNKnesLqQUd9puyh0=
=Sivh
-----END PGP SIGNATURE-----