Refined search - multi select filter on numeric fields with empty values doesn't work

Scriptcase 9 Bugs
#1

This bug is present when using a refined search in a grid and the searched field (TINYINT SQL Type) contains numbers but in some records that field is empty.

Performing a multiselect by selecting one or more number on the left pane plus the “empty” value, when applying the refined search SC returns a “You have an error in your SQL syntax” error (as returned by MySQL)

For example, this is part of the query SC creates and tries to execute when in the refined search I select both “2015” and “Empty” for the order_date_year field:

… and ((order_date_year IN (2015,) OR …

As you can see above, the “Empty” selection in the refined search causes SC to put a comma and then nothing at the end of the IN list, i.e. there’s nothing after “2015,” and before the closing “)”:

#2

As a workaround I converted the field type in the db from TINYINT to CHAR, then regenerated the app in SC so the the SQL Type of the field for SC is now VARCHAR.

Now I have no more errors when applying a refined search with the conditions described in my previous post.

I didn’t investigate the SC generated query string when applying the refined search, but I bet that since “2015” is now a string, SC is escaping in double quotes all entries in the IN filter, including the “Empty” one, thus generating something like this:

… and ((order_date_year IN (“2015”,"") OR …

This workaround (changing the field type in the DB) is feasibile only because I don’t need that field to be an actual number, otherwise the issue remains!

@NetMake: please fix this bug