Multiple match() against() in a MySQL query

Posted: - Source : stackoverflow

Same question asked on here, with no answers.

I'm using MySQL 5.7.17 on Windows 10. I got this table:

CREATE TABLE `tbl1`(
  `id` int(10) unsigned NOT NULL,
  `col1` varchar(128) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `col1_idx` (`col1`) WITH PARSER `ngram`,
  FULLTEXT KEY `col2_idx` (`col2`) WITH PARSER `ngram`,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I would like to make a query that col1 MUST have str1 and col2 MUST NOT have str2, I run multiple MATCH() AGAINST() in a query,like this

SELECT * FROM tbl1
 WHERE MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

But this query returns nothing.

When I use EXPLAIN, like:

EXPLAIN SELECT * FROM tbl1 WHERE 
 MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE) 
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

it returns:

+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table     | partitions | type     | possible_keys           | key           | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | tbl1      | NULL       | fulltext |    col1_idx, col2_idx   | col1_idx      | 0       | const |    1 |    11.11 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+

How do i make the query works? Any advice would be appreciated.