>Description:
This isn't a very critical issue, but I discovered today that if a
table has only one record in it, then a query with MATCH in the WHERE
clause will succeed for that record regardless of whether or not it
should. NOTE: I have read the manual with regards to fulltext
searching, the problem is NOT related to the relevancy score of words
in that appear in a high percentage of records -- this problem is with
words that do not appear at all.
>How-To-Repeat:
Here is the SQL that can be used to demonstrate the problem. Note the
result set from the second Select, which includes the sole record even
though "Bogity" appears no where, and the MATCH relevancy value is '0'.
(I've included the output from my system at the end of this bug report)
CREATE TABLE mysql_bug_table (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)
;
INSERT INTO mysql_bug_table VALUES
(0,'MySQL Tutorial', 'DBMS stands for DataBase ...')
;
SELECT title, MATCH (title,body) AGAINST ('Bogity')
FROM mysql_bug_table
;
SELECT title, MATCH (title,body) AGAINST ('Bogity')
FROM mysql_bug_table
WHERE MATCH (title,body) AGAINST ('Bogity')
;
INSERT INTO mysql_bug_table VALUES
(0,'How To Use MySQL Efficiently', 'After you went through a ...')
;
SELECT title, MATCH (title,body) AGAINST ('Bogity')
FROM mysql_bug_table
WHERE MATCH (title,body) AGAINST ('Bogity')
;
DROP TABLE mysql_bug_table
;
>Fix: unknown
>Submitter-Id:
>Originator: root
>Organization:
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis: MATCH succeeds for any word when table has only one record
>Severity: non-critical
>Priority: low
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.49 (Source distribution)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686
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 3.23.49-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 5 days 20 hours 21 min 27 sec
Threads: 9 Questions: 103853 Slow queries: 0 Opens: 72 Flush tables: 1 Open
tables: 58 Queries per second avg: 0.206
>Environment:
System: Linux slash 2.2.19 #1 Sat Jun 9 13:04:06 EST 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Mar 28 05:18 /lib/libc.so.6 -> libc-2.2.5.so
-rwxr-xr-x 1 root root 1170812 Feb 3 23:03 /lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2667532 Feb 3 23:03 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Feb 3 23:03 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr --exec-prefix=/usr
--libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql
--localstatedir=/var/lib/mysql --includedir=/usr/include --infodir=/usr/share/info
--mandir=/usr/share/man --enable-shared --with-libwrap --enable-assembler
--with-berkeley-db --with-innodb --enable-static --enable-shared --enable-local-infile
--with-raid --enable-thread-safe-client --without-readline
--with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql
--without-bench --with-client-ldflags=-lstdc++ --with-extra-charsets=all
--OUTPUT--
Logging to file 'mysql.bug.out'
mysql> CREATE TABLE mysql_bug_table (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> )
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO mysql_bug_table VALUES
-> (0,'MySQL Tutorial', 'DBMS stands for DataBase ...')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT title, MATCH (title,body) AGAINST ('Bogity')
-> FROM mysql_bug_table
-> ;
+----------------+---------------------------------------+
| title | MATCH (title,body) AGAINST ('Bogity') |
+----------------+---------------------------------------+
| MySQL Tutorial | 0 |
+----------------+---------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT title, MATCH (title,body) AGAINST ('Bogity')
-> FROM mysql_bug_table
-> WHERE MATCH (title,body) AGAINST ('Bogity')
-> ;
+----------------+---------------------------------------+
| title | MATCH (title,body) AGAINST ('Bogity') |
+----------------+---------------------------------------+
| MySQL Tutorial | 0 |
+----------------+---------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> INSERT INTO mysql_bug_table VALUES
-> (0,'How To Use MySQL Efficiently', 'After you went through a ...')
-> ;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> SELECT title, MATCH (title,body) AGAINST ('Bogity')
-> FROM mysql_bug_table
-> WHERE MATCH (title,body) AGAINST ('Bogity')
-> ;
Empty set (0.00 sec)
mysql>
mysql> DROP TABLE mysql_bug_table
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
---------------------------------------------------------------------
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
