A BUG IN FULLTEXT SEARCH IN BOLEAN MODE WITH JOIN

Description: The BOOLEAN FULLTEXT search return wrong record (non matching
record) if used with fields from different table.

How-To-Repeat: I report you the QUERY with the 13 WRONG result; I used
REGEXP for drop the correct rows returned.
NOTE: if you do the same with only ONE field
(without join), no wrong rows are returned.
============================START OF CUT===============================
[root@dl320a /root]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 184773 to server version: 4.0.9-gamma

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT C.nomelungo,C.nome,S.sonstr
-> FROM Categorie as C, Sottocateg as S , Categorie as C1
-> WHERE C.fileid=S.parent AND S.son = C1.fileid AND
-> MATCH (C.nomelungo,C.nome,S.sonstr) AGAINST ('+video' IN BOOLEAN
MODE) AND
-> NOT (C.nomelungo REGEXP '[[:<:]]video[[:>:]]' OR
-> C.nome REGEXP '[[:<:]]video[[:>:]]' OR
-> S.sonstr REGEXP '[[:<:]]video[[:>:]]'
-> );
+------------------------------------------+---------------- ---+------------
---------+
| nomelungo | nome | sonstr
|
+------------------------------------------+---------------- ---+------------
---------+
| Varie + ESTATE | OSSERVA | WEBCAM
RAGAZZE |
| Musica + Artisti + Rock | Alanis Morissette | Foto
|
| Musica + Artisti + Pop | Gabrielle | Testi delle
Canzoni |
| Musica + Artisti + CANTANTI ITALIANI | Nek | Foto
|
| Musica + Artisti + CANTANTI ITALIANI | Nek | Spartiti
|
| Varie + Festivita' e Ricorrenze + NATALE | Regalare | Computer e
hardware |
| Varie + Festivita' e Ricorrenze + NATALE | Regalare | Viaggi
|
| Varie + Festivita' e Ricorrenze + NATALE | Regalare |
Videocassette |
| Varie + Festivita' e Ricorrenze + NATALE | Regalare | DVD
|
| Varie + Festivita' e Ricorrenze + NATALE | Regalare |
Collezionismo |
| Varie + Festivita' e Ricorrenze + NATALE | Regalare | Riviste
|
| Varie + Festivita' e Ricorrenze + NATALE | Regalare | Altre idee
regalo |
| Musica + Artisti + Punk e Alternativa | P.O.D. | Testi delle
Canzoni |
+------------------------------------------+---------------- ---+------------
---------+
13 rows in set (0.18 sec)

============================END OF CUT===============================

Fix: I don't know how to FIX :-(

Originator: luca.maghini@tuttogratis.com
Organization: Tuttogratis S.p.A.
MySQL support: none
Synopsis: FULLTEXT SEARCH IN BOLEAN MODE WITH JOINS RETURN
STRINGS THAT DOESN'T MATCH THE SEARCH
Severity: serious
Priority: high
Category: mysql
Class: sw-bug
Release: mysql 4.0.9-gamma (binary distribution)
Server: /usr/local/nusphere/mysql/bin/mysqld
Server version 4.0.9-gamma
Protocol version 10
Connection mysql.privnet.tuttogratis.it via TCP/IP
TCP port 3306
Uptime: 1 hour 42 min 52 sec

Threads: 5 Questions: 796914 Slow queries: 3 Opens: 202 Flush tables: 1
Open tables: 196 Queries per second avg: 129.118

Environment: COMPAQ dl320 , Red Hat Linux 7.1 2.96-85
System: Linux dl320a.tuttogratis.it 2.2.19 #1 Sat Sep 22 18:44:32 GMT+1 2001
i686 unknown


------------------------------------------------------------ ---------
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 bugs-thread13472@lists.mysql.com
To unsubscribe, e-mail

Re: A BUG IN FULLTEXT SEARCH IN BOLEAN MODE WITH JOIN

Hi!

On Jan 13, Luca Maghini wrote:
> Description: The BOOLEAN FULLTEXT search return wrong record (non matching
> record) if used with fields from different table.
>
> How-To-Repeat: I report you the QUERY with the 13 WRONG result; I used
> REGEXP for drop the correct rows returned.

Unfortunately, the query was not enough. I failed to reproduce the bug.
Can you provide me with the table data ?

Or - better - create a dedicated test case for it. That is sql script,
that starts from CREATE TABLE, then populates tables with INSERT's,
and at the end runs SELECT that shows wrong results.
Something I could run as 'mysql < bug.sql'

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

------------------------------------------------------------ ---------
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 bugs-thread13487@lists.mysql.com
To unsubscribe, e-mail