Tested on Postgres 8.4
Steps to reproduce
- Create full text index on any field.
- Create view with exposed filter on this field. Enter 2 or more words.
- You will get provided error.
More details
Module generates the following SQL:
SELECT COUNT(*) AS expression
FROM (
SELECT 1 AS expression
FROM search_api_db_entity_search_api_language t
WHERE t.item_id IN (
SELECT t.item_id AS item_id
FROM (
SELECT t.item_id AS item_id
FROM search_api_db_entity_field t
WHERE ( (word = 'first') OR (word = 'second') )
) t
GROUP BY t.item_id
HAVING (COUNT(DISTINCT t.word) >= '2')
)
) subquery
The problem is in HAVING (COUNT(DISTINCT t.word) >= '2') line, because of missing t.word field in the SELECT clause.
Proper SQL query:
SELECT COUNT(*) AS expression
FROM (
SELECT 1 AS expression
FROM search_api_db_entity_search_api_language t
WHERE t.item_id IN (
SELECT t.item_id AS item_id
FROM (
SELECT t.item_id AS item_id, t.word AS word
FROM search_api_db_entity_field t
WHERE ( (word = 'first') OR (word = 'second') )
) t
GROUP BY t.item_id
HAVING (COUNT(DISTINCT t.word) >= '2')
)
) subquery
Comments
Comment #1
taran2lPatch attached, please review.
Comment #2
taran2lThis issue probably is related to #1638142: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'value' in 'where clause' and #1694572: Column not found: 1054 Unknown column 'word' in 'field list. Patch might fix them too.
Comment #3
drunken monkeyRegrettably, your fix breaks several other use cases / tests. Please see the attached patch for my take on this. It even makes all tests included with the module finally pass (with #872912-5: Write tests applied to the Search API)!
Please test and confirm, whether this works for you, too! (Otherwise we should probably expand the tests.)
Comment #4
drunken monkeyCommitted.