| 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: | |
| 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 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]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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.
[30 Oct 2019 17:31]
Test Test
Can confirm that this is still an issue on MySQL 8.0.18.
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;