freelancing-god/thinking-sphinx — pull request #317 by mbarnett (2011-12-13 21:38:08+00:00)
Title: Resolve invalid SQL Generated for some indexes on Postgres

We've discovered that in some circumstances thinking-sphinx is generating SQL that PostgreSQL rejects for some indexes.

Specifically we have an index

define_index do
indexes locatable.tags(:id), :as => :tag_id, :type => :integer
end

Which causes the following error to appear when rake ts:index is run against a postgres database

ERROR: index 'giving_location_core': sql_range_query: ERROR: invalid input syntax for integer: ""
LINE 1: ...ing(array_agg(COALESCE(CAST(COALESCE("tags"."id", '') as var...

(with a caret pointing at the empty quotes in the second argument to COALESCE)

This commit resolves the issue by always casting the column to a character type prior to passing it in.