Re: Found funny bug in O8i with LIKE

A copy of this was sent to "Lothar Armbrüster" <lothar.armbruester_at_rheingau.netsurf.de> (if that email address didn't require changing) On 29 Nov 99 20:56:22 +0100, you wrote:

>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

Clearly a bug. I filed bug 1093293 against this. You can call support and link them to that bug number.

It repro's in 8.0.5 but not 8.0.3 and before.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 29 1999 - 15:48:14 CST