Match/Against and transactions

Question

When using match/against inside a transaction, it does not seem to query from the temporary uncommited data:

start transaction;

insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");

select feed_id, full_text
from feed_full_text
where feed_id = 5000008 and match(full_text) against("lorem" in boolean mode)
order by feed_id desc
limit 1;

commit

Returns no results, however:

start transaction;

insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");

select feed_id, full_text
from feed_full_text
where feed_id = 5000008
order by feed_id desc
limit 1;

commit

Returns the just inserted row, and:

insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");

select feed_id, full_text
from feed_full_text
where feed_id = 5000008 and match(full_text) against("lorem" in boolean mode)
order by feed_id desc
limit 1;

Returns the row as well. Is this a bug or am I missing something? I am using 5.7.11 where full-text indexes in InnoDB are supported.

Answer 1

This is the expected behavior. The documentation says:

InnoDB Full-Text Index Transaction Handling
InnoDB FULLTEXT indexes have special transaction handling characteristics due its caching and batch processing behavior. Specifically, updates and insertions on a FULLTEXT index are processed at transaction commit time, which means that a FULLTEXT search can only see committed data.