Faceting doesn't work against integer columns in views

[simonw]

Spotted this issue here: https://til.simonwillison.net/datasette/baseline

I had to do this workaround:

create view baseline as select
  _key,
  spec,
  '' || json_extract(status, '$.is_baseline') as is_baseline,
  json_extract(status, '$.since') as baseline_since,
  json_extract(status, '$.support.chrome') as baseline_chrome,
  json_extract(status, '$.support.edge') as baseline_edge,
  json_extract(status, '$.support.firefox') as baseline_firefox,
  json_extract(status, '$.support.safari') as baseline_safari,
  compat_features,
  caniuse,
  usage_stats,
  status
from
  [index]

I think the core issue here is that, against a table, select * from x where integer_column = '1' works correctly, due to some kind of column type conversion mechanism... but this mechanism doesn't work against views.

[simonw]

https://latest.datasette.io/fixtures doesn't currently have a view with any integer columns in it, making this bug harder to demonstrate there.

I can't replicate the bug using https://datasette.io/content/plugins?_facet=stargazers_count&stargazers_count=3 - I would expect that not to work correctly.

[simonw]

Here's a demo of this breaking in Datasette Lite:

https://lite.datasette.io/?sql=https://gist.github.com/simonw/261564c0ca01567df6eeb9b222b8be84&json=https%3A%2F%2Fcdn.jsdelivr.net%2Fnpm%2Fweb-features%2Findex.json#/data/baseline?_filter_column_1=is_baseline&_filter_op_1=exact&_filter_value_1=1&_filter_column_2=&_filter_op_2=notnull__1&_filter_value_2=1&_filter_column=&_filter_op=exact&_filter_value=&_sort=&_facet=is_baseline

Here's a SQL query that demonstrates the underlying issue:

select 'working', count(*) from baseline where is_baseline = 1
union all
select 'broken', count(*) from baseline where is_baseline = '1'

https://lite.datasette.io/?sql=https://gist.github.com/simonw/261564c0ca01567df6eeb9b222b8be84&json=https%3A%2F%2Fcdn.jsdelivr.net%2Fnpm%2Fweb-features%2Findex.json#/data?sql=select+%27working%27%2C+count%28*%29+from+baseline+where+is_baseline+%3D+1%0Aunion+all%0Aselect+%27broken%27%2C+count%28*%29+from+baseline+where+is_baseline+%3D+%271%27