Found funny bug in O8i with LIKE

Hello out there,

today I found a funny bug in Oracle 8i (8.1.5) when using the like operator. Have a look at the following transcript:

------------------------> cut <-------------------------------

SQL*Plus: Release 8.1.5.0.0 - Production on Mo Nov 29 20:43:45 1999

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Verbunden mit:
Oracle8i Enterprise Edition Release 8.1.5.0.1 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> create table mytest (mycol varchar2(2) primary key);

Tabelle wurde angelegt.

SQL> insert into mytest values ('AA');

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> select * from mytest where mycol like 'AA%';

Es wurden keine Zeilen ausgewählt

SQL> select * from mytest where mycol like 'A%';

MY
--

AA

SQL> select * from mytest where mycol like 'AA';

MY
--

AA

SQL> select /*+ FULL(mytest) */ * from mytest where mycol like 'AA%';

MY
--

AA

SQL>
----------------------> cut <--------------------------------

Sorry for the german anwers of my db but the behaviour should be clear nevertheless.
When doing a LIKE select on an indexed column using the full width of the column with an additional '%' I don't get any resulting rows. When I force a full table scan or use a shorter pattern ther are resulting rows.
I reproduced this on a Linux box and on an NT box.

This was also sent to Oracle support but I didn't get a response yet. I just wanted you to have someting to think about. ;-)

Lothar

--

Lothar Armbrüster       | lothar.armbruester_at_rheingau.netsurf.de
Hauptstr. 26            | lothar.armbruester_at_t-online.de
D-65346 Eltville        |