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.

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

2 Answers 2

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

2
  • Thanks for confirming it's a bug, and raising it @dbdemon Oct 10, 2019 at 11:49
  • For this test, be sure to have more rows without O'Brien than with.
    – Rick James
    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.)

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". Oct 15, 2019 at 11:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.