3

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.

navark
  • 163
  • 1
  • 1
  • 10
  • Possible duplicate of [Is it possible to INSERT and then SELECT the inserted row one after another?](http://stackoverflow.com/questions/14201947/is-it-possible-to-insert-and-then-select-the-inserted-row-one-after-another) – Tim Biegeleisen May 12 '16 at 23:53

1 Answers1

6

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I believe that he won't see _any_ data until he commits the transaction. – Tim Biegeleisen May 12 '16 at 23:52
  • 1
    Why do you think that? Only full-text indexes are deferred until the end of the transaction. A normal SELECT in the same transaction should see the inserted data, but a SELECT in a different transaction won't see it. – Barmar May 12 '16 at 23:55