Fulltext search can not find word which contains punctuation marks

[Sveta Smirnova]

Originally reported at https://bugs.mysql.com/bug.php?id=86164

Description:
Fulltext search can not find word which contains punctuation marks like "!?,.{}/" in boolean mode BUT it does in natural language mode.

There is one case reported for "." and "," but according our tests it includes much more punctuation marks.

The reported case is https://bugs.mysql.com/bug.php?id=85876

How to repeat:
The problem can be reproduced as follows:

=================================
CREATE TABLE `test` (
`seq` int(11) NOT NULL AUTO_INCREMENT,
`cont` text COLLATE utf8mb4_bin,
PRIMARY KEY (`seq`),
FULLTEXT KEY `fx_txts` (`cont`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into test (cont) values ('aBc!efg');
insert into test (cont) values ('aBc@efg');
insert into test (cont) values ('aBc#efg');
insert into test (cont) values ('aBc$efg');
insert into test (cont) values ('aBc%efg');
insert into test (cont) values ('abc^efg');
insert into test (cont) values ('abc&efg');
insert into test (cont) values ('abc*efg');
insert into test (cont) values ('abc(efg');
insert into test (cont) values ('abc)efg');

Then try to select whatever value using boolean mode:
mysql [localhost] {msandbox} (test) > select * from test where match(cont) against ('"c!e"' in boolean mode);
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test where match(cont) against ('"c#e"' in boolean mode);
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test where match(cont) against ('"c$e"' in boolean mode);
Empty set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from test where match(cont) against ('"c!e"' in boolean mode);
Empty set (0.00 sec)

If change to natural language mode it works:
mysql [localhost] {msandbox} (test) > select * from test where match(cont) against ('"c!e"' in natural language mode);
+-----+---------+
| seq | cont |
+-----+---------+
| 1 | aBc!efg |
| 11 | abc!efg |
+-----+---------+
2 rows in set (0.00 sec)

The index entries are created as can be seen in INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE:

mysql [localhost] {msandbox} (test) > SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; +------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+------+--------------+-------------+-----------+--------+----------+
| !e | 2 | 12 | 2 | 2 | 3 |
| !e | 2 | 12 | 2 | 12 | 3 |
| #e | 4 | 4 | 1 | 4 | 3 |
| $e | 5 | 5 | 1 | 5 | 3 |
| %e | 6 | 6 | 1 | 6 | 3 |
| &e | 8 | 8 | 1 | 8 | 3 |
| (e | 10 | 10 | 1 | 10 | 3 |
| )e | 11 | 11 | 1 | 11 | 3 |
| *e | 9 | 9 | 1 | 9 | 3 |
| @e | 3 | 3 | 1 | 3 | 3 |
| ^e | 7 | 7 | 1 | 7 | 3 |
<...>
| c! | 2 | 12 | 2 | 2 | 2 |
| c! | 2 | 12 | 2 | 12 | 2 |
| c# | 4 | 4 | 1 | 4 | 2 |
| c$ | 5 | 5 | 1 | 5 | 2 |
| c% | 6 | 6 | 1 | 6 | 2 |
| c& | 8 | 8 | 1 | 8 | 2 |
| c( | 10 | 10 | 1 | 10 | 2 |
| c) | 11 | 11 | 1 | 11 | 2 |
| c* | 9 | 9 | 1 | 9 | 2 |
| c@ | 3 | 3 | 1 | 3 | 2 |
| c^ | 7 | 7 | 1 | 7 | 2 |
<...>
+------+--------------+-------------+-----------+--------+----------+
55 rows in set (0.00 sec)

[Sveta Smirnova (svetasmirnova)]

If we decide to let user pass symbols they want to search via option it would be good not to limit them to ASCII, but make Uft8mb4 compatible

[Laurynas Biveinis (laurynas-biveinis)]

Sveta -

On the upstream bug you write "ngram parser is not required to repeat this bug. It is repeatable with MyISAM as well." Could you please provide an example without the ngram parser?

[Sveta Smirnova (svetasmirnova)]

This is exactly same case, but with skipped /*!50100 WITH PARSER `ngram` */ part:

mysql> CREATE TABLE `test` (
    -> `seq` int(11) NOT NULL AUTO_INCREMENT,
    -> `cont` text COLLATE utf8mb4_bin,
    -> PRIMARY KEY (`seq`),
    -> FULLTEXT KEY `fx_txts` (`cont`)
    -> ) ENGINE=myisam DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.06 sec)

mysql> set names utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test (cont) values ('aBc!efg');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test (cont) values ('aBc@efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('aBc#efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('aBc$efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('aBc%efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('abc^efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('abc&efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('abc*efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('abc(efg');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (cont) values ('abc)efg');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where match(cont) against ('"c!e"' in boolean mode);
Empty set (0.13 sec)

mysql> select * from test where match(cont) against ('"c#e"' in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(cont) against ('"c$e"' in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(cont) against ('"c!e"' in boolean mode);
Empty set (0.00 sec)

mysql> select * from test where match(cont) against ('"c!e"' in natural language mode);
Empty set (0.00 sec)

[Laurynas Biveinis (laurynas-biveinis)]

Introducing a per-session variable for extra word characters. On setting, it will be checked not to intersect with the ft_boolean_syntax variable value.

[Laurynas Biveinis (laurynas-biveinis)]

A change that would affect tokenizing on writing the FTS is not safe enough. Since the ngram parser tokenizer already does what we want here, and the use case involves that parser, only introduce a new variable that affects tokenizing only for FTS queries.

[Sveta Smirnova (svetasmirnova)]

Would be great if fix will work for both ngram and mecab fulltext indexes.

[Laurynas Biveinis (laurynas-biveinis)]

Ack, I will update on the mecab status later

[Laurynas Biveinis (laurynas-biveinis)]

https://github.com/percona/percona-server/pull/2078

Changes done for MeCab as well, but only tested not to have caused regressions in their existing tests.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2501