>Description:
The included log
>How-To-Repeat:
>Fix:
>Submitter-Id: Devan Goodwin
>Originator: Devan Goodwin
>Organization:
National Research Council Canada
>MySQL support: none
>Synopsis: Problems with MySQL full text search
>Severity: serious
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.32 (Source distribution)
>Environment:
System: Linux flipper.cisti.nrc.ca 2.2.14-6.1.1smp #1 SMP Thu Apr 13 19:55:55 EDT 2000
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Dec 15 11:49 /lib/libc.so.6 -> libc-2.1.3.so
-rwxr-xr-x 1 root root 4101324 Feb 29 2000 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20272704 Feb 29 2000 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 29 2000 /usr/lib/libc.so
Configure command: ./configure --prefix=/home/mysql/mySQL --with-mysqld-user=mysql
Perl: This is perl, version 5.005_03 built for i386-linux
==================================
We were attempting some simple tests on MySQL fulltext searching, using the
documentation provided on www.mysql.com as a starting point. Our session was
as follows:
mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES
-> ('MySQL has now support', 'for full-text search'),
-> ('Full-text indexes', 'are called collections'),
-> ('Function MATCH ... AGAINST()','is used to do a search')
-> ;
Query OK, 3 rows affected (0.03 sec)
mysql> select * from t;
+------------------------------+------------------------+
| a | b |
+------------------------------+------------------------+
| MySQL has now support | for full-text search |
| Full-text indexes | are called collections |
| Function MATCH ... AGAINST() | is used to do a search |
+------------------------------+------------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
+-----------------------+----------------------+
| a | b |
+-----------------------+----------------------+
| MySQL has now support | for full-text search |
+-----------------------+----------------------+
1 row in set (0.04 sec)
mysql> SELECT MATCH a,b AGAINST ('collections support') as x FROM t;
+------------------------------+------------------------+------------------+
| a | b | x |
+------------------------------+------------------------+------------------+
| MySQL has now support | for full-text search | 0.65545834044456 |
| Full-text indexes | are called collections | 0.65545834044456 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
+------------------------------+------------------------+------------------+
3 rows in set (0.00 sec)
mysql> SELECT MATCH a,b AGAINST ('collections support') as x FROM t;
+------------------+
| x |
+------------------+
| 0.65545834044456 |
| 0.65545834044456 |
| 0 |
+------------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO t VALUES
-> ('mysql has now support', 'for full-text search');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+-----------------+
| x |
+-----------------+
| 0 |
| 1.0388768903895 |
| 0 |
| 0 |
+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t order by x;
+------------------------------+------------------------+-----------------+
| a | b | x |
+------------------------------+------------------------+-----------------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 1.0388768903895 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
+------------------------------+------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t order by x,
descending;
+------------------------------+------------------------+-----------------+
| a | b | x |
+------------------------------+------------------------+-----------------+
| MySQL has now support | for full-text search | 0 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 1.0388768903895 |
+------------------------------+------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t order by x desc;
+------------------------------+------------------------+-----------------+
| a | b | x |
+------------------------------+------------------------+-----------------+
| Full-text indexes | are called collections | 1.0388768903895 |
| MySQL has now support | for full-text search | 0 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
+------------------------------+------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('MySQL') as x FROM t order by x desc;
+------------------------------+------------------------+------+
| a | b | x |
+------------------------------+------------------------+------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 0 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
+------------------------------+------------------------+------+
4 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('MySQL') as x FROM t order by x;
+------------------------------+------------------------+------+
| a | b | x |
+------------------------------+------------------------+------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 0 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
+------------------------------+------------------------+------+
4 rows in set (0.00 sec)
mysql> describe t;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a | varchar(200) | YES | MUL | NULL | |
| b | text | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
Empty set (0.00 sec)
mysql> SELECT * FROM t;
+------------------------------+------------------------+
| a | b |
+------------------------------+------------------------+
| MySQL has now support | for full-text search |
| Full-text indexes | are called collections |
| Function MATCH ... AGAINST() | is used to do a search |
| mysql has now support | for full-text search |
+------------------------------+------------------------+
4 rows in set (0.00 sec)
mysql> select * from t where a like 'MySQL%';
+-----------------------+----------------------+
| a | b |
+-----------------------+----------------------+
| MySQL has now support | for full-text search |
| mysql has now support | for full-text search |
+-----------------------+----------------------+
2 rows in set (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+------------------------------+------------------------+-----------------+
| a | b | x |
+------------------------------+------------------------+-----------------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 1.0388768903895 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
+------------------------------+------------------------+-----------------+
4 rows in set (0.00 sec)
mysql> INSERT INTO t VALUES
-> ('collections',' support');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+------------------------------+------------------------+------------------+
| a | b | x |
+------------------------------+------------------------+------------------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 0.38341854994499 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
| collections support | foo | 0.39634908797834 |
+------------------------------+------------------------+------------------+
5 rows in set (0.00 sec)
mysql> INSERT INTO t VALUES
-> ('collections',' support');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+------------------------------+------------------------+------+
| a | b | x |
+------------------------------+------------------------+------+
| MySQL has now support | for full-text search | 0 |
| Full-text indexes | are called collections | 0 |
| Function MATCH ... AGAINST() | is used to do a search | 0 |
| mysql has now support | for full-text search | 0 |
| collections support | foo | 0 |
| collections | support | 0 |
+------------------------------+------------------------+------+
6 rows in set (0.00 sec)
******************************************************************************
Briefly, it seems to us that the x column values should have remained the same,
or atleast > 0 after our addition of the last entry to the table. It seems
MySQL detected matches beforehand, but not after.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
