| Bug #36384 | Full-Text required (+) operator bug | ||
|---|---|---|---|
| Submitted: | 28 Apr 2008 20:15 | Modified: | 29 Apr 2008 3:41 |
| Reporter: | Thomas Chin | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S1 (Critical) |
| Version: | 5.0.44-r1, 5.0.58 | OS: | Linux (5.0.44-log Gentoo Linux mysql-5.0.44-r1) |
| Assigned to: | CPU Architecture: | Any | |
[29 Apr 2008 3:41]
Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 5.0.58:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE test (data TEXT, FULLTEXT(data)) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test VALUES('apple brazil beach');
Query OK, 1 row affected (0.03 sec)
mysql> INSERT INTO test VALUES('orange brazil beach');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES('orange pear beach');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test VALUES('orange banana');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil beach)'
IN
-> BOOLEAN MODE);
+---------------------+
| data |
+---------------------+
| orange brazil beach |
| orange pear beach |
+---------------------+
2 rows in set (0.05 sec)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil +beach)
' IN
-> BOOLEAN MODE);
+---------------------+
| data |
+---------------------+
| orange brazil beach |
| orange pear beach |
+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil +pear beach)' IN
-> BOOLEAN MODE);
+-------------------+
| data |
+-------------------+
| orange pear beach |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(+brazil beach)
' IN
-> BOOLEAN MODE);
+---------------------+
| data |
+---------------------+
| apple brazil beach |
| orange brazil beach |
+---------------------+
2 rows in set (0.00 sec)
mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(beach +brazil)
' IN
-> BOOLEAN MODE);
+---------------------+
| data |
+---------------------+
| orange brazil beach |
+---------------------+
1 row in set (0.00 sec)
Description: To whom it may concern: I am able to reproduce a case in which the required (+) operator has no affect on a full-text search in boolean mode. The sample code provided will illustrate a case where the "+orange" is being ignored and results not containing the word "orange" are being returned. Other sample cases are provided to indicate that it seems to work for other cases. Thank you in advance for any help you can provide. How to repeat: mysql> CREATE TABLE test (data TEXT, FULLTEXT(data)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO test VALUES('apple brazil beach'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test VALUES('orange brazil beach'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test VALUES('orange pear beach'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test VALUES('orange banana'); Query OK, 1 row affected (0.00 sec) mysql> -- expect 2 rows mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil beach)' IN BOOLEAN MODE); +---------------------+ | data | +---------------------+ | orange brazil beach | | orange pear beach | +---------------------+ 2 rows in set (0.00 sec) mysql> -- expect 2 rows mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil +beach)' IN BOOLEAN MODE); +---------------------+ | data | +---------------------+ | orange brazil beach | | orange pear beach | +---------------------+ 2 rows in set (0.00 sec) mysql> -- expect 1 row mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(brazil +pear beach)' IN BOOLEAN MODE); +-------------------+ | data | +-------------------+ | orange pear beach | +-------------------+ 1 row in set (0.00 sec) mysql> -- expect 1 row (** unexpected result **) mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(+brazil beach)' IN BOOLEAN MODE); +---------------------+ | data | +---------------------+ | apple brazil beach | | orange brazil beach | +---------------------+ 2 rows in set (0.00 sec) mysql> -- expect 1 row mysql> SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(beach +brazil)' IN BOOLEAN MODE); +---------------------+ | data | +---------------------+ | orange brazil beach | +---------------------+ 1 row in set (0.00 sec) Suggested fix: I believe the third query should still require the word "orange" in the result set: SELECT data FROM test WHERE MATCH(data) AGAINST('+orange +(+brazil beach)' IN BOOLEAN MODE); It should return the same result set as the fourth query.