Suggest for ArrayFacet possibly confused by blank values

[simonw]

I sometimes don't get the suggestion for facet-by-array for columns that contain arrays. I think it may be because they have empty spaces in them - or perhaps it's because the null detection doesn't actually work.

[simonw]

Relevant code:

datasette/datasette/facets.py

Lines 271 to 295 in afed51b

	async def suggest(self):
	columns = await self.get_columns(self.sql, self.params)
	suggested_facets = []
	already_enabled = [c["config"]["simple"] for c in self.get_configs()]
	for column in columns:
	if column in already_enabled:
	continue
	# Is every value in this column either null or a JSON array?
	suggested_facet_sql = """
	select distinct json_type({column})
	from ({sql})
	""".format(
	column=escape_sqlite(column), sql=self.sql
	)
	try:
	results = await self.ds.execute(
	self.database,
	suggested_facet_sql,
	self.params,
	truncate=False,
	custom_time_limit=self.ds.setting("facet_suggest_time_limit_ms"),
	log_sql_errors=False,
	)
	types = tuple(r[0] for r in results.rows)
	if types in (("array",), ("array", None)):

[simonw]

Could I take this part:

             suggested_facet_sql = """ 
                 select distinct json_type({column}) 
                 from ({sql}) 
             """.format( 
                 column=escape_sqlite(column), sql=self.sql 
             ) 

And add where {column} is not null and {column} != '' perhaps?

[simonw]

I'm going to try a new pattern for testing this, enabled by #1151 - the test will create a new named in-memory database, write some records to it and then run some test facets against that. This will save me from having to add yet another fixtures table for this.