This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

MariaDB full-text searching skipped a stopword even in Boolean Mode

We are verifying MyISAM and InnoDB in full-text searching following [this post](https://stackoverflow.com/a/45674350).

The word `about` belongs to the [list of InnoDB stopwords](https://mariadb.com/kb/en/full-text-index-stopwords/#innodb-stopwords), so the query got an empty result when matching against it in Natural Language Mode.

In Boolean Mode, we expect to get matches on the relevant rows, but the results were still empty for the following queries for both InnoDB and MyISAM engines. Both engines showed the same system behavior. We need help understanding the testing results.

  • the query SELECT * FROM ft_1 WHERE MATCH(copy) AGAINST('about' in BOOLEAN MODE);, for InnoDB and
  • the query SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('about' in BOOLEAN MODE); for MyISAM

So, we wonder why and will highly appreciate hints and suggestions.

Technical Details:

  • SQL:
sql
-- Definition of the InnoDB table:

CREATE TABLE test.ft_1 (
  copy TEXT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
CREATE FULLTEXT INDEX ft_1_copy_IDX ON test.ft_1 (copy);


-- Definition of the MyISAM table:
CREATE TABLE `ft_myisam` (
  `copy` text DEFAULT NULL,
  FULLTEXT KEY `copy` (`copy`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Queries:

SELECT * FROM ft_1;
SELECT * FROM ft_myisam;

SELECT * FROM ft_1 WHERE MATCH(copy) AGAINST('about');
SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('about');
-- The default Natural Language Mode returns an empty set as expected.

SELECT * FROM ft_1 WHERE MATCH(copy) AGAINST('about' in BOOLEAN MODE);
SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('about' in BOOLEAN MODE);
-- The Boolean Mode still returns an empty set, and we wonder why.

SELECT * FROM ft_1 WHERE MATCH(copy) AGAINST('clock');
SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('clock');
-- Returns the row `It is about two o'clock` because 'clock' is not a stopword.

  • The data:
copy
Once upon a time
There was a wicked witch
Who ate everybody up
Once upon a wicked time
There was a wicked wicked witch
Who ate everybody wicked up
It is about two o'clock
About two
is two
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.