0

An apostrophe / single quote (') should be part of a word in a FULLTEXT index.

Admittedly I can't find this on the MariaDB website, but I'm assuming it should still behave like MySQL (version 5.5).

But when using "IN BOOLEAN MODE", and prefixing the word with a "+" (so it's mandatory in all rows returned), the record is not returned.

For example:

CREATE TABLE customer ( name TINYTEXT NOT NULL, FULLTEXT (name) ) ENGINE = InnoDB; INSERT INTO customer VALUES ('O''Brien'); INSERT INTO customer VALUES ('O Brien'); INSERT INTO customer VALUES ('X''Brien'); INSERT INTO customer VALUES ('Extra Amy'); INSERT INTO customer VALUES ('Extra Brian'); INSERT INTO customer VALUES ('Extra Cat'); INSERT INTO customer VALUES ('Extra Debbie');

I get these results:

SELECT * FROM customer WHERE MATCH (name) AGAINST ("O'Brien" IN BOOLEAN MODE); "O'Brien" "O Brien" "X'Brien" 3 rows in set (0.000 sec) SELECT * FROM customer WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE); Empty set (0.000 sec) SELECT * FROM customer WHERE MATCH (name) AGAINST ("+Brien" IN BOOLEAN MODE); "O'Brien" "O Brien" "X'Brien" 3 rows in set (0.000 sec) SELECT * FROM customer WHERE MATCH (name) AGAINST (+"O'Brien" IN BOOLEAN MODE); "O'Brien" "O Brien" "X'Brien" 3 rows in set (0.000 sec)

Am I missing something, like needing to quote the apostrophe?

Or is this an intentional difference between InnoDB and MyISAM?

Thanks to @rick-james, I've updated this question to include 3 "extra" records to avoid the 50% threshold limit, and I've included an example where the + is before the quoted word.

Improve this question
2
  • How many rows do you have? Remember the 50% rule. Commented Oct 15, 2019 at 2:18
  • Good point, but in the original case I have a table with 48,736 records. Commented Oct 15, 2019 at 11:16

2 Answers 2

Reset to default
1

If we create the table with the Aria or MyISAM storage engine, then the query succeeds:

CREATE TABLE customer2 ( name TINYTEXT NOT NULL, FULLTEXT (name) ) ENGINE = Aria; INSERT INTO customer2 VALUES ('O''Brien'); SELECT * FROM customer2 WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE); 1 row in set (0.001 sec) CREATE TABLE customer3 ( name TINYTEXT NOT NULL, FULLTEXT (name) ) ENGINE = MyISAM; INSERT INTO customer3 VALUES ('O''Brien'); SELECT * FROM customer3 WHERE MATCH (name) AGAINST ("+O'Brien" IN BOOLEAN MODE); 1 row in set (0.001 sec)

However, InnoDB doesn't seem to work for me either (MariaDB 10.4.8.)

So that leads me to think this is a bug. I reported the issue here: https://jira.mariadb.org/browse/MDEV-20797

Improve this answer
2
  • Thanks for confirming it's a bug, and raising it @dbdemon Commented Oct 10, 2019 at 11:49
  • For this test, be sure to have more rows without O'Brien than with. Commented Oct 15, 2019 at 2:19
2

Works for me in both MyISAM and InnoDB.

But note: out of 185,082 rows, 17 have "O'Brien". That's less than 50%, so the search term is not ignored. I verified the count with LIKE "%O'Brien%".

But, when adding the +, it needs to be outside the quotes for InnoDB. (Yet another difference between the implementations.)

(I have updated my blog to add this diff.)

Improve this answer
1
  • In my case (original question), I'm working with 48,736 records, and only 24 records with "O'Brien" (so less than 50%)... and moving the + outside of the quote marks seem to change the query so that the "O'" is ignored, and it returns any records that contain the name "Brien". Commented Oct 15, 2019 at 11:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.