Potential bug in numeric handling where_clause for filters

[simonw]

Note that Datasette does already have special logic to convert parameters to integers for numeric comparisons like >:

datasette/datasette/filters.py

Lines 203 to 212 in c4c9dbd

	def where_clause(self, table, column, value, param_counter):
	converted = self.format.format(value)
	if self.numeric and converted.isdigit():
	converted = int(converted)
	if self.no_argument:
	kwargs = {"c": column}
	converted = None
	else:
	kwargs = {"c": column, "p": f"p{param_counter}", "t": table}
	return self.sql_template.format(**kwargs), converted

Though... it looks like there's a bug in that? It doesn't account for float values - "3.5".isdigit() return False - probably for the best, because int(3.5) would break that value anyway.

Originally posted by @simonw in #1671 (comment)

[simonw]

My hunch is that this mechanism doesn't actually do anything useful at all, because of the type conversion that automatically happens for data from tables based on the column type affinities, see:

• Filters fail to work correctly against calculated numeric columns returned by SQL views because type affinity rules do not apply #1671

So either remove the self.numeric type conversion bit entirely, or prove that it is necessary and upgrade it to be able to handle floating point values too.

[simonw]

I would expect this to break against SQL views that include calculated columns though - something like this:

create view this_will_break as select pk + 1 as pk_plus_one, 0.5 as score from searchable;

Confirmed: the filter interface for that view plain doesn't work for any comparison against that table - except for score > 0 since 0 is converted to an integer. 0.1 breaks though because it doesn't get converted as it doesn't match .isdigit().