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

Revision: 123279
User: Mike Zhao
Date: 2023-02-07 15:10

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:**

<<style class="darkheader borders">>

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.

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

Products

Services

Pricing

Resources

About Us

Download MariaDB