Re: Oracle Text query
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <joe_dba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Mar 2006 12:14:53 +0100
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@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, March 16, 2006 2:51 PM
Subject: Oracle Text query
--
//www.freelists.org/webpage/oracle-l
Other related posts:
