Tested on Postgres 8.4

Steps to reproduce

  1. Create full text index on any field.
  2. Create view with exposed filter on this field. Enter 2 or more words.
  3. 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

taran2l’s picture

Patch attached, please review.

taran2l’s picture

drunken monkey’s picture

StatusFileSize
new3.35 KB

Regrettably, 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.)

drunken monkey’s picture

Status: Needs review » Fixed

Committed.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.