>Description:
We have three different unrelated tables, each with one field that
has a fulltext index. In each table, in the field with the fulltext
index, there are records with the word "needs" in them, and in each case no
records are found when doing this type of query (using a fulltext index):
select * from testdb where match(highergeog) against('needs' in boolean mode);
However, records are found when doing substring searches:
select * from testdb where highergeog like '%needs%';
>How-To-Repeat:
Sample table with a very small subset of the data I have in it:
CREATE TABLE testdb(
GeogID integer NOT NULL,
InstGeogID integer NOT NULL,
Institution varchar(255) NOT NULL,
CollectionCode varchar(15) NOT NULL,
HigherGeog varchar(255) NOT NULL,
ContinentOcean varchar(255),
Country varchar(255),
StateProv varchar(255),
County varchar(255),
Quad varchar(255),
Feature varchar(255),
Island varchar(255),
IslandGroup varchar(255),
Sea varchar(255),
StandardCountry varchar(255),
NAStandardState varchar(50)
);
CREATE FULLTEXT INDEX test_HigherGeog on testdb (HigherGeog);
insert into testdb values
(1, 1, 'Bishop Museum', 'BPBM',
'::::Australia:[possibly New South Wales]:unknown::', '',
'[probably Australia]', '[possibly New South Wales]', 'unknown',
'', '', '', '', '', 'Australia', ''),
(2, 2, 'Bishop Museum', 'BPBM', '::::unknown::::', '',
'<Unknown>: TO BE RECEIVED..', '', '',
'', '', '', '', '', 'unknown', ''),
(3, 3, 'Bishop Museum', 'BPBM',
'::::Africa:unknown:unknown::', '',
'Africa', 'unknown', 'unknown',
'', '', '', '', '', 'Africa', ''),
(4, 4,
'Bishop Museum', 'BPBM',
'::::American Samoa:--:Swains Island::', '',
'American Samoa', '--', 'Swains Island',
'', '', '', '', '', 'American Samoa', ''),
(5, 5,
'Bishop Museum', 'BPBM',
'::::American Samoa:--:Tutuila Island::', '',
'American Samoa', '--', 'Tutuila Island',
'', '', '', '', '', 'American Samoa', ''),
(6, 6, 'Bishop Museum', 'BPBM',
'::::Australia:New South Wales:::', '',
'Australia', 'New South Wales', '',
'', '', '', '', '', 'Australia', ''),
(7, 7, 'Bishop Museum', 'BPBM',
'::::Australia:New South Wales:Unknown::', '',
'Australia', 'New South Wales', 'Unknown',
'', '', '', '', '', 'Australia', ''),
(8, 8, 'Bishop Museum', 'BPBM',
'::::Australia:Northern Teritory:Sandy Island No. 2::', '',
'Australia', 'Northern Teritory', 'Sandy Island No. 2',
'', '', '', '', '', 'Australia', ''),
(9, 9, 'Bishop Museum', 'BPBM',
'::::Australia:Northern Territory:::', '',
'Australia', 'Northern Territory', '',
'', '', '', '', '', 'Australia', ''),
(10, 10, 'Bishop Museum', 'BPBM',
'::::Australia:Queensland:::', '',
'Australia', 'Queensland', '',
'', '', '', '', '', 'Australia', ''),
(20200, 311, 'Louisiana State University', 'LSU',
'::::Zambia:NEEDS EDITING:::', '',
'Zambia', 'NEEDS EDITING', '',
'', '', '', '', '', 'Zambia', ''),
(20202, 313, 'Louisiana State University', 'LSU',
'::::Congo:NEEDS EDITING:::', '',
'Congo', 'NEEDS EDITING', '', '', '', '', '', '', 'Congo', ''),
(20207, 318, 'Louisiana State University', 'LSU',
'::::United States:Virginia:NEEDS EDITING::', '',
'USA', 'Virginia', 'NEEDS EDITING',
'', '', '', '', '', 'United States', 'Virginia');
This brings up zero matches:
select * from testdb where match(highergeog) against('needs' in boolean mode);
This brings up three matches:
select * from testdb where highergeog like '%needs%';
Also I know someone running 4.0.15 on linux, and "needs" can't be found
in fulltext queries on his data also, even though the word exists in the data.
Perhaps this is all solved in 4.0.18?
>Fix:
Don't use fulltext index--use substring search.
But substring is slower than fulltext.
>Submitter-Id: Joyce Gross
>Originator:
>Organization:
UC Berkeley
>MySQL support: none
>Synopsis: fulltext bug
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.5-beta-standard (Official MySQL-standard binary)
>Server: /usr/local/mysql/bin/mysqladmin Ver 8.38 Distrib 4.0.5-beta, for
>sun-solaris2.7 on sparc
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.5-beta-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 15 days 3 hours 4 min 40 sec
Threads: 1 Questions: 2001092 Slow queries: 340 Opens: 138 Flush tables: 1 Open
tables: 64 Queries per second avg: 1.531
>Environment:
System: SunOS dlp.CS.Berkeley.EDU 5.7 Generic_106541-28 sun4u sparc SUNW,Ultra-4
Architecture: sun4
Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/sww/bin/gmake
/usr/sww/bin/gcc /usr/sww/bin/cc
GCC: Reading specs from
/usr/sww/pkg/gcc-2.95.2/lib/gcc-lib/sparc-sun-solaris2.7/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc'
CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti'
LDFLAGS='' ASFLAGS=''
LIBC:
-rw-r--r-- 1 bin 1712496 Oct 20 2003 /lib/libc.a
lrwxrwxrwx 1 root 11 Dec 8 1999 /lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 bin 1126200 Oct 20 2003 /lib/libc.so.1
-rw-r--r-- 1 bin 1712496 Oct 20 2003 /usr/lib/libc.a
lrwxrwxrwx 1 root 11 Dec 8 1999 /usr/lib/libc.so -> ./libc.so.1
-rwxr-xr-x 1 bin 1126200 Oct 20 2003 /usr/lib/libc.so.1
Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official
MySQL-standard binary' --with-extra-charsets=complex --with-server-suffix=-standard
--enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared
--with-innodb CC=gcc 'CFLAGS=-O3 -fno-omit-frame-pointer' 'CXXFLAGS=-O3
-fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' CXX=gcc
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
