bytea, index and like operator - Mailing list pgsql-bugs

-----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-----