-----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-----
Thu, 04 Dec 2003 22:24:42 +0100
Alvar Freude <alvar@a-blast.org>
bytea, index and like operator again and detailed report
Alvar Freude wrote:
> 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):
> 

Apparently you never read my reply to you all the way to the bottom. I said:

Joe Conway wrote:
 > Alvar Freude wrote:
 >>   select * from test where b like '\001%';
 > This is weird. I'm sure it worked at one time -- will research.
 >
 > Joe

I'm actively working on your issue. Please quit spamming all the lists 
with it.

Joe



Thu, 04 Dec 2003 14:09:34 -0800
Joe Conway <mail@joeconway.com>
Re: bytea, index and like operator again and detailed report
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail@joeconway.com> wrote:

> Apparently you never read my reply to you all the way to the bottom. I
> said:

oh, sorry, I understand your mail wrong!
I understand it in this way, that you are not sure that there is something
buggy, so I looked more deeply in this issue. 

 
> I'm actively working on your issue. Please quit spamming all the lists
> with it.

I'm sorry, but after reading the desciption of "bugs" again I realised that
according to the list desciption "bugs" is the wrong one und "hackers" the
right; so, after debugging my code two days I'm now sure that there is
something wrong with Postgres. Normally such Problems are in my Application
;-)

If there is no answer like "Ah, it really seems that there is a bug, thanks
for the pointer, I'll look for it" I every time guess that the issue
depends on a mistake by myself or it is forgotten ... ;-)


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/z7Q5OndlH63J86wRAvrHAKDLAEz7X8ZeGah0CvL9QmglVMZrfwCdHAGr
H17Kp6qy65jj32lBvsC/9zY=
=b1l7
-----END PGP SIGNATURE-----


Thu, 04 Dec 2003 23:24:57 +0100
Alvar Freude <alvar@a-blast.org>
Re: bytea, index and like operator again and detailed
Alvar Freude wrote:
> I'm sorry, but after reading the desciption of "bugs" again I realised that
> according to the list desciption "bugs" is the wrong one und "hackers" the
> right; so, after debugging my code two days I'm now sure that there is
> something wrong with Postgres. Normally such Problems are in my Application
> ;-)

Actually "bugs" is the best place to post bug reports if you think they 
might be in Postgres and not your app.

> If there is no answer like "Ah, it really seems that there is a bug, thanks
> for the pointer, I'll look for it" I every time guess that the issue
> depends on a mistake by myself or it is forgotten ... ;-)

OK -- I guess I could have been more clear myself. Anyway I've been 
working on this on-and-off for a day now. I understand the root cause, 
but am still trying to figure out what the "right" solution is. It may 
take another day or so due to other things I have going on (like my job 
;-)).

Joe



Thu, 04 Dec 2003 14:36:27 -0800
Joe Conway <mail@joeconway.com>
Re: bytea, index and like operator again and detailed report
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail@joeconway.com> wrote:

> I understand the root cause,
> but am still trying to figure out what the "right" solution is. It may
> take another day or so due to other things I have going on (like my job
> ;-)).

:-)

If you need some testing data, I can give you a dump ...


> Actually "bugs" is the best place to post bug reports if you think they
> might be in Postgres and not your app.

perhaps someone should change the list descriptions at

  http://www.postgresql.org/lists.html


  pgsql-bugs
  If you a find a bug, please fill out the form typically located 
  in the PostgreSQL source code at src/pgsql/doc/bug.template and 
  mail it to this mailing list.

  [...]

  pgsql-hackers (Developers List)
  [...]
  This list is for the discussion of current development issues, 
  problems and bugs and the discussion of proposed new features.

  [...]

;-)


So, my idea was: Uups, it was the wrong group, therefore no real answer ;)
 

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/z7giOndlH63J86wRAmBwAJ46KjKlahE8vYcVG33lOsmi2sGqiACgluPn
8CE/HqK+OqYuGQONXnBszP0=
=5+rU
-----END PGP SIGNATURE-----


Thu, 04 Dec 2003 23:41:38 +0100
Alvar Freude <alvar@a-blast.org>
Re: bytea, index and like operator again and detailed
Alvar Freude wrote:
> 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):

Please try the attached patch and let me know how it works for you. It 
is against cvs HEAD, but should apply OK to 7.4.

Thanks,

Joe
Fri, 05 Dec 2003 14:42:28 -0800
Joe Conway <mail@joeconway.com>
Re: bytea, index and like operator again and detailed report
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Joe,

- -- Joe Conway <mail@joeconway.com> wrote:

> Please try the attached patch and let me know how it works for you. It is
> against cvs HEAD, but should apply OK to 7.4.

has this about one week time? I travel on monday to Geneva (World Summit on
the Information Society) and have a lot to prepare ... :-(


I'll make a report when I have some minutes (or hours ;) ) ...


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/0RU8OndlH63J86wRAkKIAKDA6iRUyFg3qm7GRrUs9OnlhozVKwCfSVmk
jI5lGQ0vQLj22qsMCTLpn4I=
=au3Z
-----END PGP SIGNATURE-----


Sat, 06 Dec 2003 00:31:07 +0100
Alvar Freude <alvar@a-blast.org>
Re: bytea, index and like operator again and detailed
Alvar Freude wrote:
> has this about one week time? I travel on monday to Geneva (World Summit on
> the Information Society) and have a lot to prepare ... :-(
> 
> I'll make a report when I have some minutes (or hours ;) ) ...

Well, 7.4.1 will be bundled up for release on Sunday, so it would be 
ideal to get some feedback sooner if possible.

Joe


Fri, 05 Dec 2003 16:43:48 -0800
Joe Conway <mail@joeconway.com>
Re: bytea, index and like operator again and detailed report
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



- -- Joe Conway <mail@joeconway.com> wrote:

> Well, 7.4.1 will be bundled up for release on Sunday, so it would be
> ideal to get some feedback sooner if possible.

this is a good argument ... ;)

I'll try to check it ...


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/0S1jOndlH63J86wRAkMiAJ4xa9t4OR+Oif/IvUEMgZ5Yl52lpwCgnboU
bIyeU+IwfK3C34JltRmBuoU=
=zHKC
-----END PGP SIGNATURE-----


Sat, 06 Dec 2003 02:14:11 +0100
Alvar Freude <alvar@a-blast.org>
Re: bytea, index and like operator again and detailed
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail@joeconway.com> wrote:

> Please try the attached patch and let me know how it works for you. It is
> against cvs HEAD, but should apply OK to 7.4.

so, I checked it with my database. 
It looks good, all checks I made are OK.


I realised, that converting my text column to bytea is much faster then
before:

UPDATE forum_gtree SET gid2 = (decode(replace(gid, '\\', '\\\\'),
'escape'));


This may be because some other side effects (but it's the same machine and
same disc, same partition), but also perhaps some changes in the patch ...


MANY THANKS!


When I'm back from WSIS I make the next try to change my application from
Base255-text to bytea ;-)


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/0Tg6OndlH63J86wRAiLfAJ98wLJeeyEuFXG07Pa7ba/jM7LzxQCgzWa1
RGv3FtQ8JP/MZjCWuqbdnsY=
=U9Ng
-----END PGP SIGNATURE-----


Sat, 06 Dec 2003 03:00:25 +0100
Alvar Freude <alvar@a-blast.org>
Re: bytea, index and like operator again and detailed
Alvar Freude wrote:
> -- Joe Conway <mail@joeconway.com> wrote:
>>Please try the attached patch and let me know how it works for you. It is
>>against cvs HEAD, but should apply OK to 7.4.
> 
> so, I checked it with my database. 
> It looks good, all checks I made are OK.

The attached fixes the bytea-like bug found by Alvar -- as well as some 
others I found while working on it. I would like to apply this evening 
so that it can be in the 7.4.1 release. Any objections?

Thanks,

Joe
Sat, 06 Dec 2003 09:38:44 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] bytea,
 index and like operator again and detailed report
Joe Conway <mail@joeconway.com> writes:
> ! 		*prefix_const = string_to_bytea_const(match, match_pos);
> ! 		*rest_const = string_to_bytea_const(rest, pattlen - match_pos);

I think that should be pattlen - pos not pattlen - match_pos, no?

Otherwise it looks reasonable ...

			regards, tom lane

Sat, 06 Dec 2003 14:38:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] bytea,
	index and like operator again and detailed report
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>! 		*prefix_const = string_to_bytea_const(match, match_pos);
>>! 		*rest_const = string_to_bytea_const(rest, pattlen - match_pos);
> 
> I think that should be pattlen - pos not pattlen - match_pos, no?
> 

Yup -- you're right. Thanks for the review! Fixed and committed.

Joe


Sat, 06 Dec 2003 20:15:25 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] bytea, index and like operator again and