Full Text Search and Unit Tests

[gtalarico]

Summary

Manually implementation of FULLTEXT search support returns empty queryset when using Django TestCase.

---

My apologies in advance as this is likely not a bug, but I have been stuck on this for a while and was not able to get help on StackOverflow, Reddit, and Django Mailing list.
You are my last hope 🙏

My Stackoverflow question has all the details, but here is a summary:

I added a __search lookup and FULLTEXT index manually to allow full text search:

# models.py

class Search(models.Lookup):
    lookup_name = "search"

    def as_mysql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return f"MATCH (%s) AGAINST (%s IN BOOLEAN MODE)" % (lhs, rhs), params


models.TextField.register_lookup(Search)

# Manually Modified to add FULLTEXT INDEX
# Generated by Django 2.2 on 2020-04-28 03:41

from django.db import migrations, models

# Table details
table_name = "by_api_casesnapshot"
field_name = "text"
index_name = f"{table_name}_{field_name}_index"


class Migration(migrations.Migration):

    dependencies = [("by_api", "0033_add_tag_color")]

    operations = [
        migrations.CreateModel(...), # As auto-generated
        migrations.RunSQL(
            f"CREATE FULLTEXT INDEX {index_name} ON {table_name} ({field_name})",
            f"DROP INDEX {index_name} ON {table_name}",
        ),
    ]

The code above works perfectly - the index is created, and search mysql fulltext boolean actually works as expected.

# Tested a few searches and they all returned expected results
>>> CaseSnapshot.objects.filter(text__search="+X")
<QuerySet [<CaseSnapshot pk=1>]>
>>> CaseSnapshot.objects.filter(text__search="zzzz")

The issue is testing. When I tried to write tests, __search always returns an empty queryset.
I actually noticed if I use unittest.TestCase instead of django.test.TestCase the test passes - could this be related to how django's test case wraps db transactions?

Once again, thank you for any help and apologies for asking a question here.
Hopefully this could help others in the future trying to implement FULLTEXT search themselves!

class TestModels(TestCase):
    def test_search(self):
        CaseSnapshot.objects.create(text="XXX")

        # Passes - using `__contains`
        rv1 = CaseSnapshot.objects.filter(text__contains="XXX")
        self.assertEqual(rv1.count(), 1)

        # Fails! - Should return 1, returns empty queryset
        rv2 = CaseSnapshot.objects.filter(text__search="XXX")
        self.assertEqual(rv2.count(), 1)

[adamchainz]

could this be related to how django's test case wraps db transactions?

Yes, InnoDB doesn't make changes to full text indexes until commits. You can use TransactionTestCase to get around this.

[adamchainz]

Also, this issue tracker is not really a support service. Your issue does not relate to the library. Please constrain your support questions to support forums like Stack Overflow in future.

[gtalarico]

Thank you @adamchainz
I wasn't sure if the issue was related to this library and the non-supported addition of FULLTEXT index. But I understand the issue now, so I apologize.

I found what you are referring to in the innodb docs fulltext docs here.

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. The following example demonstrates this behavior. The FULLTEXT search only returns a result after the inserted lines are committed.

Do you have any plans to support FULLTEXT index and __search lookup?
I would be happy help to work on this if it's something you would consider

[adamchainz]

Full text search yes, but not via the __search lookup (which Django removed). It'd be better to use one or more database functions to support all the options that searching allows. See #31 and #314 .