sphinx search: querying using implicit AND not returning the result

Question

Using sphinx search v2.2.9, this query returns a particular record:

((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) )

this query also returns that same record:

(@(issued) 2007)

but this query (which I believe is an implied "AND" combination of the above two queries does not return the record:

(@(issued) 2007) ((@(author) fstaed) | (@(authsurname) fstaed) | (@(authori) fstaed) ) 

why?

Update 1:

I can reproduce this problem using the mysql command line, below shows running each of the above 3 tests. Note that id: 187 is in both individual result sets but not the combined result set.

$ mysql -h0 -P9306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.2.9-id64-release (rel22-r5006)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT id, weight() FROM `work` WHERE MATCH('(@(issued) 2007)') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
+------+----------+
| id   | weight() |
+------+----------+
|  187 |     1604 |
|  200 |     1604 |
|  215 |     1604 |
..i cutoff these results as irrelevant.
+------+----------+
40 rows in set, 1 warning (0.01 sec)

mysql> SELECT id, weight() FROM `work` WHERE MATCH('((@(author) fstaed)  | (@(authsurname) fstaed)  | (@(authori) fstaed) )') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
+------+----------+
| id   | weight() |
+------+----------+
|  187 |     1560 |
|  383 |     1560 |
+------+----------+
2 rows in set, 1 warning (0.01 sec)

mysql> SELECT id, weight() FROM `work` WHERE MATCH('(@(issued) 2007) ((@(author) fstaed)  | (@(authsurname) fstaed)  | (@(authori) fstaed) )') AND `sphinx_deleted` = 0 LIMIT 0, 1000 OPTION ranker=proximity_bm25
-> ;
Empty set, 1 warning (0.01 sec)

mysql> 

Update 2:

I should also mention that the "work" index is a distributed index between a number of indexes that includes "iresrevi1_core" and "iresrevi2_core". The "issued" field is null in the iresrevi2_core index (not null in the iresrevi1_core index) and the authori field being searched is null in the opposite index iresrevi1_core (not null in the iresrevi2_core index).

I think it might have something to do with this? I can confirm that if i query the 2 indexes directly the iresrevi1_core index will return the issued search data but the iresrevi2_core index will not return the issued search data. Vice versa, the iresrevi2_core will return the author search data but the iresrevi1_core will not.

I have multiple indexes distributed such that I can index all the fields that I want to search using "non infix" methods (forcing on the "infix" fields to be blank in this index) and all the fields that I want to search using "infix" methods are sitting on the other index with all the "non-infix" fields blanked out. The 2 sources/indexes look like this:

source srcresrevi1 : srcresrev
{
    sql_query       = \
    select SQL_NO_CACHE `work`.`ID` AS `ID`, '' as authori \
     from work \
     WHERE (`work`.`ID` BETWEEN $start AND $end) \
     and `work`.`ID` <= (select max_id from sphinx_deltas where id = 1)

sql_joined_field = authsurname from ranged-query; \
    SELECT SQL_NO_CACHE wa.work_id AS ID, s.surname \
    from `work_authors` wa, \
         `author_surnames` s \
    where wa.author_surname_id = s.id \
    and wa.work_id >= $start and wa.work_id <= $end \
    and `wa`.`work_ID` <= (select max_id from sphinx_deltas where id = 1) \
    order by wa.work_id ASC; \
    select min(work_id), max(work_id) from `work_authors` \
    where work_id <= (select max_id from sphinx_deltas where id = 1)

sql_joined_field = author from ranged-query; \
    SELECT SQL_NO_CACHE wa.work_id AS ID, CONCAT(f.given,' ',s.surname) \
    from `work_authors` wa, `author_surnames` s, `author_fnames` f \
    where wa.author_surname_id = s.id \
    and wa.author_fname_id = f.id \
    and wa.work_id >= $start and wa.work_id <= $end \
    and `wa`.`work_ID` <= (select max_id from sphinx_deltas where id = 1) \
    order by wa.work_id ASC; \
    select min(work_id), max(work_id) from `work_authors` \
    where work_id <= (select max_id from sphinx_deltas where id = 1)

sql_joined_field = issued from ranged-query; \
    SELECT SQL_NO_CACHE work_id AS ID, `year` \
    from issued \
    where work_id >= $start and work_id <= $end \
    and work_ID <= (select max_id from sphinx_deltas where id = 1) \
    order by work_id ASC; \
    select min(work_id), max(work_id) from `issued` \
    where work_id <= (select max_id from sphinx_deltas where id = 1)
}

source srcresrevi2 : srcresrev
{
    sql_query       = \
    select SQL_NO_CACHE `work`.`ID` AS `ID`, '' as authsurname, '' as author, '' as issued \
            from work \
            WHERE (`work`.`ID` BETWEEN $start AND $end) \
            and `work`.`ID` <= (select max_id from sphinx_deltas where id = 1)

sql_joined_field = authori from ranged-query; \
    SELECT SQL_NO_CACHE wa.work_id, CONCAT(f.given,' ',s.surname) \
    from `work_authors` wa, `author_surnames` s, `author_fnames` f \
    where wa.author_surname_id = s.id \
    and wa.author_fname_id = f.id \
    and work_id >= $start and work_id <= $end \
    and work_ID <= (select max_id from sphinx_deltas where id = 1) \
    order by wa.work_id ASC; \
    select min(work_id), max(work_id) from `work_authors` \
    where work_id <= (select max_id from sphinx_deltas where id = 1)
}

index iresrevi1_core
{
source          = srcresrevi1
path = /home/resrev/pubrevit/db/sphinx/development/iresrevi1
docinfo         = extern
dict            = keywords
mlock           = 0
morphology      = stem_en
charset_table = 0..9, A..Z->a..z, _, a..z, \
        U+410..U+42F->U+430..U+44F, U+430..U+44F
min_word_len        = 3
expand_keywords     = 0
ngram_len       = 1
ngram_chars     = U+3000..U+2FA1F
html_strip      = 1
html_remove_elements    = style, script, head, DOCTYPE, !DOCTYPE
inplace_enable      = 1
index_exact_words   = 0
index_sp        = 0
index_field_lengths = 1
}

index iresrevi2_core
{
source          = srcresrevi2
path = /home/resrev/pubrevit/db/sphinx/development/iresrevi2
docinfo         = extern
dict            = keywords
mlock           = 0
morphology      = stem_en
charset_table = 0..9, A..Z->a..z, _, a..z, \
        U+410..U+42F->U+430..U+44F, U+430..U+44F
min_word_len        = 3
min_infix_len       = 3
expand_keywords     = 1
ngram_len       = 1
ngram_chars     = U+3000..U+2FA1F
html_strip      = 1
html_remove_elements    = style, script, head, DOCTYPE, !DOCTYPE
inplace_enable      = 1
index_exact_words   = 0
index_sp        = 1
index_field_lengths = 1
}

Answer 1

So yes, its the multiple distinct indexes that is the problem. UNION not JOIN.

As well as the thread you found, there is a more recent thread here too, which mentions possibly using @@relaxed to work around it. It will probably sill work for a distributed index.

http://sphinxsearch.com/forum/view.html?id=13581