SPHINX field search operator issue

Question

I am using sphinx 2.0.4-release with SPH_MATCH_EXTENDED2 query syntax. When I have an "empty value" in my query i.e.:

blah & ''

sphinx ignores it and searches just "blah". It still works the same way when i use field search operator and an empty value comes last:

@field1 blah @field2 ''

But this query:

@field1 '' @field2 blah

causes error: syntax error, unexpected TOK_FIELDLIMIT near ' '' @field2 blah'. Of course i can trim empty values, but this behaviour seems illogical to me... Am i doing something wrong? Or is it actually a bug?

barryhunter barryhunter · Accepted Answer · 2013-07-02T10:56:51

Sphinx uses an inverted index. It breaks up the text into words and stores (hashes of) them.

As such it doesnt index 'nothing' (its not a word) - so you can't search an empty string.

All of those queries are strictly a syntax error - and nonsense. But in some cases sphinx will just dispose of invalid syntax silently (because it then falls back and thinks its word char, which are then not in charset_table and so go) - and in so doing come up with a 'valid' query (just not what you intended)

The solution is to simply turn an empty field into a 'word' at indexing time, then you can search for the empty string!

eg

sql_query = SELECT id, title, IF(field1 = '','EMPTY_STRING',field1) AS field1, ....

Then you can just query as

@field1 EMPTY_STRING @field2 blah

What you use as 'EMPTY_STRING' is completely arbitrary.

SPHINX field search operator issue

1 Answers