Search truncates text fields silently

[ScotterC]

The existing search code when applied to text fields that are longer then 256 characters filters out results silently which is unexpected behavior.

Let's say you have an Post model with a content attribute that's a postgres text column. The queries below can return very different results depending on where in the content text field the query string resides.

SELECT COUNT(*) FROM "posts" WHERE "posts"."content" LIKE '%{query-string}%'
# => returns all results

SELECT COUNT(*) FROM "posts" WHERE CAST("posts"."content" AS CHAR(256)) LIKE '%{query-string}%'
# => returns only results where 'query-string' is in the first 256 characters

I would expect text fields to be as searchable as string fields.

[nickcharlton]

Ah yeah, that's annoying. Do you think you'd be able to contribute a fix?

[ScotterC]

Probably won't have the time but here's the questions that would pop up

• What's the purpose of the CHAR(256)? if the column is a text field is it necessary?
• Does this change per database type?
• would getting the column type and determining the solution based on string or text column be sufficient?

If the above is true then maybe something like this could work

def query_template
  search_attributes.map do |attr|
    table_name = query_table_name(attr)
    searchable_fields(attr).map do |field|
      column_name = column_to_query(field)
      column_type = model.columns_hash[column_name].type

      # Decide whether to use CHAR(256) based on column type
      cast_type = column_type == :text ? "TEXT" : "CHAR(256)"
      
      "LOWER(CAST(#{table_name}.#{column_name} AS #{cast_type})) LIKE ?"
    end.join(" OR ")
  end.join(" OR ")
end