[Bug report] invalid filter generated for postgres

This report is specifically for Postgres.

When you enter a text in the "create multi column filter" edit box, a filter query is automatically generated, like this:

"fieldname1" LIKE '%sometext%' OR 
"fieldname2" LIKE '%sometext%' OR 
"fieldname3" LIKE '%sometext%'

It neatly escapes field names the postgres-way. However, when a table contains a non-textual field (e.g. Integer), the above query is not valid.

Postgres reports:

ERROR:  operator does not exist: integer ~~ unknown
LINE 1: ...tus" WHERE "fieldname1" LIKE '%some%' OR "fieldname2" LIKE '%SOM...

Solution1:

"fieldname1"::text LIKE '%sometext%' OR "fieldname2"::text LIKE '%sometext%' OR "fieldname3"::text LIKE '%sometext%'

Advantage: should be easy to implement. Disadvantage: probably extremely slow on large tables, because it'll do a conversion for all fields of all rows.

Solution 2:

If the generated query depends on the input, things could run faster. So, when a valid numeric value is given, this gets created:

"textfield1" LIKE '%sometext%' OR 
"integerfield2" = 'sometext'::integer OR 
"integerfield3" = 'sometext'::integer

When a text is entered, don't attempt to match non-text fields, so we'd get something like this:

"textfield1" LIKE '%sometext%'

It'll leave non-text fields out of the where-clause. There are not going to be matches anyway, so we don't have to bother doing conversions either.