500 error caused by faceting if a column called n exists

[Kabouik]

I recently discovered datasette thanks to your great talk at FOSDEM and would like to use it for some projects. However, when trying to use it on databases created from some csv ot tsv files, I am sometimes getting this issue when going to http://127.0.0.1:8001/databasetest/databasetest and I don't exactly understand what it refers to.

So far, I couldn't find anything relevant when reviewing the raw text files that could explain this issue, nor could I find something obvious between the files that generate this issue and those that don't. Does the error ring a bell and, if so, could you please point me to the right direction?

$ datasette databasetest.db 
INFO:     Started server process [1408482]
INFO:     Waiting for application startup.
INFO:     Application startup complete.
INFO:     Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit)
INFO:     127.0.0.1:56394 - "GET / HTTP/1.1" 200 OK
INFO:     127.0.0.1:56394 - "GET /-/static/app.css?4e362c HTTP/1.1" 200 OK
INFO:     127.0.0.1:56396 - "GET /-/static-plugins/datasette_vega/main.2acbb312.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56398 - "GET /-/static-plugins/datasette_vega/main.08f5d3d8.js HTTP/1.1" 200 OK
Traceback (most recent call last):
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/app.py", line 1099, in route_path
    response = await view(request, send)
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/views/base.py", line 147, in view
    request, **request.scope["url_route"]["kwargs"]
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/views/base.py", line 121, in dispatch_request
    return await handler(request, *args, **kwargs)
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/views/base.py", line 260, in get
    request, database, hash, correct_hash_provided, **kwargs
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/views/base.py", line 434, in view_get
    request, database, hash, **kwargs
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/views/table.py", line 782, in data
    suggested_facets.extend(await facet.suggest())
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/facets.py", line 168, in suggest
    and any(r["n"] > 1 for r in distinct_values)
  File "/home/kabouik/.local/lib/python3.7/site-packages/datasette/facets.py", line 168, in <genexpr>
    and any(r["n"] > 1 for r in distinct_values)
TypeError: '>' not supported between instances of 'str' and 'int'
INFO:     127.0.0.1:56402 - "GET /databasetest/databasetest HTTP/1.1" 500 Internal Server Error
INFO:     127.0.0.1:56402 - "GET /-/static/app.css?4e362c HTTP/1.1" 200 OK
INFO:     127.0.0.1:56404 - "GET / HTTP/1.1" 200 OK
INFO:     127.0.0.1:56404 - "GET /-/static/app.css?4e362c HTTP/1.1" 200 OK
INFO:     127.0.0.1:56406 - "GET /-/static-plugins/datasette_vega/main.2acbb312.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /-/static-plugins/datasette_vega/main.08f5d3d8.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /databasetest HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /-/static/app.css?4e362c HTTP/1.1" 200 OK
INFO:     127.0.0.1:56404 - "GET /-/static-plugins/datasette_vega/main.2acbb312.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56406 - "GET /-/static/codemirror-5.57.0.min.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56410 - "GET /-/static-plugins/datasette_vega/main.08f5d3d8.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56414 - "GET /-/static/codemirror-5.57.0-sql.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56412 - "GET /-/static/codemirror-5.57.0.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /-/static/sql-formatter-2.3.3.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /databasetest?sql=select+*+from+databasetest HTTP/1.1" 200 OK
INFO:     127.0.0.1:56410 - "GET /-/static/app.css?4e362c HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /-/static-plugins/datasette_vega/main.2acbb312.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56412 - "GET /-/static/codemirror-5.57.0.min.css HTTP/1.1" 200 OK
INFO:     127.0.0.1:56404 - "GET /-/static/sql-formatter-2.3.3.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56406 - "GET /-/static/codemirror-5.57.0.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56414 - "GET /-/static-plugins/datasette_vega/main.08f5d3d8.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56408 - "GET /-/static/codemirror-5.57.0-sql.min.js HTTP/1.1" 200 OK
INFO:     127.0.0.1:56410 - "GET /databasetest.json?sql=select+*+from+databasetest&_shape=array&_shape=array HTTP/1.1" 200 OK
^CINFO:     Shutting down
INFO:     Waiting for application shutdown.
INFO:     Application shutdown complete.
INFO:     Finished server process [1408482]

Note that there is no error if I go to http://127.0.0.1:8001/databasetest and then click on Run SQL.

[simonw]

It looks to me like something is causing the faceting query here to return a string when it was expected to return a number:

datasette/datasette/facets.py

Lines 153 to 170 in 3296301

	suggested_facet_sql = """
	select {column}, count(*) as n from (
	{sql}
	) where {column} is not null
	group by {column}
	limit {limit}
	""".format(
	column=escape_sqlite(column), sql=self.sql, limit=facet_size + 1
	)
	distinct_values = None
	try:
	distinct_values = await self.ds.execute(
	self.database,
	suggested_facet_sql,
	self.params,
	truncate=False,
	custom_time_limit=self.ds.setting("facet_suggest_time_limit_ms"),
	)

I can't think of any way that a count(*) as n would turn into a string though!

[simonw]

... unless your data had a column called n?

[simonw]

Yes! That's the problem. I was able to replicate it like so:

echo '[{                    
    "n": "one",
    "abc": 1
}, {
    "n": "one",
    "abc": 2
}, {
    "n": "two",
    "abc": 3
}]' | sqlite-utils insert column-called-n.db t -

[simonw]

Demo: https://latest.datasette.io/fixtures/facetable - which now has a column called n.

[Kabouik]

... unless your data had a column called n?

Exactly, that's highly likely even though I can't double check from this computer just now. Thanks!