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

10g run --- SQL> drop table texttable;

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>

Oracle 9i run --- SQL> drop table texttable;

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

Original Message ----- From: "Joe Smith" <joe_dba_at_hotmail.com> To: <oracle-l_at_freelists.org> Sent: Thursday, March 16, 2006 2:51 PM Subject: Oracle Text query

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 17 2006 - 05:14:53 CST