Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Text query

Re: Oracle Text query

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Fri, 17 Mar 2006 12:14:53 +0100
Message-ID: <124001c649b4$0463ad20$3c02a8c0@JARAWIN>


Hi Joe,

which version?

> select * from table_name_tab
> where contains(column_name_metadata, 'the\_world\_is\_bi%')>1
>
> the query suddenly starts matching the following strings:
> the_world_is_big
> theZworldZisZbig
> the3world2is1b

I can reproduce this "feature" in 9.2.0.5 In 10.2.0.1 it works perfectly well, therefore it is probably a "bug". Compare the two runs below ...

Regards,

Jaromir

Table dropped.

SQL> create table texttable (textid number, text varchar2(40) ) ;

Table created.

SQL> --
SQL> insert into texttable values(1, 'this_string_contains_underscores');

1 row created.

SQL> insert into texttable values(2, 'this_string_contains underscores');

1 row created.

SQL> insert into texttable values(3, 'this_string_containsXunderscores');

1 row created.

SQL> insert into texttable values(4, 'this_string_containsXXunderscores');

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> create index text_ix1 on texttable(text)   2 indextype is CTXSYS.CONTEXT
  3 parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER mylex10')   4 ;

Index created.

SQL> --
SQL> -- check preferences
SQL> select prv_preference, prv_attribute, prv_value from 
CTX_PREFERENCE_VALUES
  2 where prv_preference in ( 'MYLEX10')   3 order by prv_preference, prv_attribute;
PRV_PREFERENCE                 PRV_ATTRIBUTE
------------------------------ ------------------------------
PRV_VALUE
MYLEX10                        NUMGROUP

.
MYLEX10                        NUMJOIN

.
MYLEX10                        PRINTJOINS
_$%&
PRV_PREFERENCE                 PRV_ATTRIBUTE
------------------------------ ------------------------------
PRV_VALUE
MYLEX10                        WHITESPACE
,=
SQL> --
SQL> --- check underscores
SQL> --
SQL> select textid, text,score(1) from texttable
  2 where contains (text,'this_string_contains_underscores',1)>1;
    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         1 this_string_contains_underscores                  5
         3 this_string_containsXunderscores                  5

SQL> --
SQL> select textid, text,score(1) from texttable   2 where contains (text,'this\_string\_contains\_underscores',1)>1;

    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         1 this_string_contains_underscores                  5

SQL> --
SQL> select textid, text,score(1) from texttable   2 where contains (text,'this\_string\_contains\_underscores%',1)>1;

    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         1 this_string_contains_underscores                  5

SQL> --
SQL> select * from v$version where banner like '%Database%';

BANNER



Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL>

Table dropped.

SQL> create table texttable (textid number, text varchar2(40) ) ;

Table created.

SQL> --
SQL> insert into texttable values(1, 'this_string_contains_underscores');

1 row created.

SQL> insert into texttable values(2, 'this_string_contains underscores');

1 row created.

SQL> insert into texttable values(3, 'this_string_containsXunderscores');

1 row created.

SQL> insert into texttable values(4, 'this_string_containsXXunderscores');

1 row created.

SQL> commit;

Commit complete.

SQL> --
SQL> create index text_ix1 on texttable(text)   2 indextype is CTXSYS.CONTEXT
  3 parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER mylex10')   4 ;

Index created.

SQL> --
SQL> -- check preferences
SQL> select prv_preference, prv_attribute, prv_value from 
CTX_PREFERENCE_VALUES
  2 where prv_preference in ( 'MYLEX10')   3 order by prv_preference, prv_attribute;
PRV_PREFERENCE                 PRV_ATTRIBUTE
------------------------------ ------------------------------
PRV_VALUE
MYLEX10                        NUMGROUP

.
MYLEX10                        NUMJOIN

.
MYLEX10                        PRINTJOINS
_$%&
PRV_PREFERENCE                 PRV_ATTRIBUTE
------------------------------ ------------------------------
PRV_VALUE
MYLEX10                        WHITESPACE
,=
SQL> --
SQL> --- check underscores
SQL> --
SQL> select textid, text,score(1) from texttable
  2 where contains (text,'this_string_contains_underscores',1)>1;
    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         3 this_string_containsXunderscores                  5
         1 this_string_contains_underscores                  5

SQL> --
SQL> select textid, text,score(1) from texttable   2 where contains (text,'this\_string\_contains\_underscores',1)>1;

    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         1 this_string_contains_underscores                  5

SQL> --
SQL> select textid, text,score(1) from texttable   2 where contains (text,'this\_string\_contains\_underscores%',1)>1;

    TEXTID TEXT                                       SCORE(1)
---------- ---------------------------------------- ----------
         3 this_string_containsXunderscores                  5  <<<<<<<<<< 
probable a bug here <<<<<<
         1 this_string_contains_underscores                  5


SQL> select * from v$version;

BANNER



Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 17 2006 - 05:14:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US