Bug #78485 Fulltext search with char * produces a syntax error with InnoDB
Submitted: 19 Sep 2015 10:12 Modified: 19 Sep 2015 17:25
Reporter: Jose Ignacio Andrés Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.6/5.7 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: fulltext, innodb, syntax

[19 Sep 2015 10:12] Jose Ignacio Andrés
Description:
When I execute a query with "MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE)" in shows the next error:

ERROR 1064 (42000): syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

I've been trying to reproduce with other chars, but only appears to happen with "*" char

I've been googling and searching in the documentation and forums and I couldn't find any mention to this.

How to repeat:
DROP TABLE IF EXISTS `ft_test`;

CREATE TABLE IF NOT EXISTS `ft_test` (
  `uid` int(11) DEFAULT NULL,
  `strings` text COLLATE utf8_bin,
  FULLTEXT KEY `strings` (`strings`)
) ENGINE=InnoDB COLLATE=utf8_bin;

INSERT INTO `ft_test` (`uid`, `strings`) VALUES
(1, 'some words'),
(2, 'a random text');

SELECT uid
FROM ft_test
ORDER BY MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE) DESC;

Suggested fix:
If I use MyISAM as engine it works fine:

CREATE TABLE IF NOT EXISTS `ft_test` (
  `uid` int(11) DEFAULT NULL,
  `strings` text COLLATE utf8_bin,
  FULLTEXT KEY `strings` (`strings`)
) ENGINE=MyISAM COLLATE=utf8_bin;
[19 Sep 2015 13:02] Peter Laursen
More observations (InnoDB):

1)
SELECT uid FROM ft_test ORDER BY MATCH (strings) AGAINST ('\*' IN NATURAL LANGUAGE MODE) DESC;
/* same error */

2)
SELECT uid FROM ft_test ORDER BY MATCH (strings) AGAINST ('**' IN NATURAL LANGUAGE MODE) DESC;
/*   uid  
--------
       1
       2
*/
.. eerrrhhh?

Both 1 and 2 return an empty result with MyISAM.

-- Peter
-- not a MySQL/Oracle person
[19 Sep 2015 17:17] Godofredo Miguel Solorzano
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.27 Source distribution PULL: 2015/08/14

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > DROP TABLE IF EXISTS `ft_test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE IF NOT EXISTS `ft_test` (
    ->   `uid` int(11) DEFAULT NULL,
    ->   `strings` text COLLATE utf8_bin,
    ->   FULLTEXT KEY `strings` (`strings`)
    -> ) ENGINE=InnoDB COLLATE=utf8_bin;
Query OK, 0 rows affected (1.65 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `ft_test` (`uid`, `strings`) VALUES
    -> (1, 'some words'),
    -> (2, 'a random text');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 >
mysql 5.6 > SELECT uid
    -> FROM ft_test
    -> ORDER BY MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE) DESC;
ERROR 1064 (42000): syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
mysql 5.6 > alter table ft_test engine = MyISAM;
Query OK, 2 rows affected (0.45 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT uid
    -> FROM ft_test
    -> ORDER BY MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE) DESC;
+------+
| uid  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
[19 Sep 2015 17:21] Godofredo Miguel Solorzano
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 Source distribution PULL 2015/08/14

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > DROP TABLE IF EXISTS `ft_test`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql 5.7 >
mysql 5.7 > CREATE TABLE IF NOT EXISTS `ft_test` (
    ->   `uid` int(11) DEFAULT NULL,
    ->   `strings` text COLLATE utf8_bin,
    ->   FULLTEXT KEY `strings` (`strings`)
    -> ) ENGINE=InnoDB COLLATE=utf8_bin;
Query OK, 0 rows affected (1.50 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO `ft_test` (`uid`, `strings`) VALUES
    -> (1, 'some words'),
    -> (2, 'a random text');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 >
mysql 5.7 > SELECT uid
    -> FROM ft_test
    -> ORDER BY MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE) DESC;
ERROR 1064 (42000): syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
mysql 5.7 > alter table ft_test engine = MyISAM;
Query OK, 2 rows affected (0.88 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.7 > SELECT uid
    -> FROM ft_test
    -> ORDER BY MATCH (strings) AGAINST ('*' IN NATURAL LANGUAGE MODE) DESC;
+------+
| uid  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
[19 Sep 2015 17:25] Godofredo Miguel Solorzano
Thank you for the bug report.
[15 Nov 2015 22:44] Marek Lemanczyk
The same error affects boolean mode.
[26 Jul 2017 14:18] Ravi Gehlot
Any word on the progress of this bug? I cannot see a Progress History. It looks like that it remains an issue.