A glaring bug in FULLTEXT search?

Here's a transcript of my chat with mysql:

----------------------
mysql> create table test (id int, name text, fulltext nameidx(name))
type=MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test values (1, 'some words');
Query OK, 1 row affected (0.06 sec)

mysql> insert into test values (2, 'other words');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+-------------+
| id | name |
+------+-------------+
| 1 | some words |
| 2 | other words |
+------+-------------+
2 rows in set (0.10 sec)

mysql> select * from test where match(name) against('word');
Empty set (0.02 sec)

mysql> select * from test where match(name) against('words');
Empty set (0.00 sec)

mysql> select * from test where match(name) against('some');
Empty set (0.00 sec)

mysql> select * from test where match(name) against('other');
Empty set (0.00 sec)
-------------------------------

Is this a bug or am I misunderstanding how FULLTEXT search is supposed to work?


--
Makefiles not war





--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: A glaring bug in FULLTEXT search?

Sorry, I forgot to specify: MySQL version is 4.0.15 (the Win32 binary release
from the MySQL website), the OS is WinXP SP1.


----- Original Message -----
From: "Ivan Todoroski"
To:
Sent: Friday, October 24, 2003 9:20 AM
Subject: A glaring bug in FULLTEXT search?


>
> Here's a transcript of my chat with mysql:
>
> ----------------------
> mysql> create table test (id int, name text, fulltext nameidx(name))
> type=MyISAM;
> Query OK, 0 rows affected (0.10 sec)
>
> mysql> insert into test values (1, 'some words');
> Query OK, 1 row affected (0.06 sec)
>
> mysql> insert into test values (2, 'other words');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test;
> +------+-------------+
> | id | name |
> +------+-------------+
> | 1 | some words |
> | 2 | other words |
> +------+-------------+
> 2 rows in set (0.10 sec)
>
> mysql> select * from test where match(name) against('word');
> Empty set (0.02 sec)
>
> mysql> select * from test where match(name) against('words');
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against('some');
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against('other');
> Empty set (0.00 sec)
> -------------------------------
>
> Is this a bug or am I misunderstanding how FULLTEXT search is supposed to
work?
>
>
> --
> Makefiles not war
>
>
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: A glaring bug in FULLTEXT search?

Hi Ivan,

No, definitely not a bug. Did you read this page from the manual,
http://www.mysql.com/doc/en/Fulltext_Search.html? Words that are present
in more than half the rows are ignored when not using IN BOOLEAN MODE.
You need at least 3 rows for a non-boolean search to work. Additionally,
"some" and "other" are stopwords, so they're also ignored.

This query would work as expected with your test data:

SELECT * FROM test WHERE MATCH (name) AGAINST ('words' IN BOOLEAN MODE);


Hope that helps.


Matt


----- Original Message -----
From: "Ivan Todoroski"
Sent: Friday, October 24, 2003 2:20 AM
Subject: A glaring bug in FULLTEXT search?


>
> Here's a transcript of my chat with mysql:
>
> ----------------------
> mysql> create table test (id int, name text, fulltext nameidx(name))
> type=MyISAM;
> Query OK, 0 rows affected (0.10 sec)
>
> mysql> insert into test values (1, 'some words');
> Query OK, 1 row affected (0.06 sec)
>
> mysql> insert into test values (2, 'other words');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test;
> +------+-------------+
> | id | name |
> +------+-------------+
> | 1 | some words |
> | 2 | other words |
> +------+-------------+
> 2 rows in set (0.10 sec)
>
> mysql> select * from test where match(name) against('word');
> Empty set (0.02 sec)
>
> mysql> select * from test where match(name) against('words');
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against('some');
> Empty set (0.00 sec)
>
> mysql> select * from test where match(name) against('other');
> Empty set (0.00 sec)
> -------------------------------
>
> Is this a bug or am I misunderstanding how FULLTEXT search is supposed
to work?


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: A glaring bug in FULLTEXT search?

Aaahh... a classic case of RTFM... too early in the morning I guess... :)

Thanks for the quick reponse!

I did read the FULLTEXT manual section, but only enough to figure out the
syntax, without looking at the finer details yet. Sorry for wasting your time.


----- Original Message -----
From: "Matt W"
To: "Ivan Todoroski" ;
Sent: Friday, October 24, 2003 9:57 AM
Subject: Re: A glaring bug in FULLTEXT search?


> Hi Ivan,
>
> No, definitely not a bug. Did you read this page from the manual,
> http://www.mysql.com/doc/en/Fulltext_Search.html? Words that are present
> in more than half the rows are ignored when not using IN BOOLEAN MODE.
> You need at least 3 rows for a non-boolean search to work. Additionally,
> "some" and "other" are stopwords, so they're also ignored.
>
> This query would work as expected with your test data:
>
> SELECT * FROM test WHERE MATCH (name) AGAINST ('words' IN BOOLEAN MODE);
>
>
> Hope that helps.
>
>
> Matt
>
>
> ----- Original Message -----
> From: "Ivan Todoroski"
> Sent: Friday, October 24, 2003 2:20 AM
> Subject: A glaring bug in FULLTEXT search?
>
>
> >
> > Here's a transcript of my chat with mysql:
> >
> > ----------------------
> > mysql> create table test (id int, name text, fulltext nameidx(name))
> > type=MyISAM;
> > Query OK, 0 rows affected (0.10 sec)
> >
> > mysql> insert into test values (1, 'some words');
> > Query OK, 1 row affected (0.06 sec)
> >
> > mysql> insert into test values (2, 'other words');
> > Query OK, 1 row affected (0.00 sec)
> >
> > mysql> select * from test;
> > +------+-------------+
> > | id | name |
> > +------+-------------+
> > | 1 | some words |
> > | 2 | other words |
> > +------+-------------+
> > 2 rows in set (0.10 sec)
> >
> > mysql> select * from test where match(name) against('word');
> > Empty set (0.02 sec)
> >
> > mysql> select * from test where match(name) against('words');
> > Empty set (0.00 sec)
> >
> > mysql> select * from test where match(name) against('some');
> > Empty set (0.00 sec)
> >
> > mysql> select * from test where match(name) against('other');
> > Empty set (0.00 sec)
> > -------------------------------
> >
> > Is this a bug or am I misunderstanding how FULLTEXT search is supposed
> to work?
>
>


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: A glaring bug in FULLTEXT search?

Hi,

thats an expected behavior. MySQL Fulltext search uses a 50% threshold. If you
only have 2 rows, one matching row is already 50%. Add an additional row and
everything should work fine.

Georg

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org