Simon Willison

Maybe it's because `supports_table_xinfo()` creates a brand new in-memory SQLite connection every time you call it? https://github.com/simonw/datasette/blob/798f075ef9b98819fdb564f9f79c78975a0f71e8/datasette/utils/sqlite.py#L22-L35 Actually no, I'm caching that already: https://github.com/simonw/datasette/blob/798f075ef9b98819fdb564f9f79c78975a0f71e8/datasette/utils/sqlite.py#L12-L19

Oh this is fascinating! I replicated the bug (thanks for the steps to reproduce) and it looks like this is down to the following: Against views, `where has_expired = 1`...

Relevant section of the SQLite documentation: [3.2. Affinity Of Expressions](https://www.sqlite.org/datatype3.html#affinity_of_expressions): > When an expression is a simple reference to a column of a real table (not a [VIEW](https://www.sqlite.org/lang_createview.html) or subquery)...

I wonder if this will be a problem with generated columns, or with SQLite strict tables? My hunch is that strict tables will continue to work without any changes, because...

Thinking about options for fixing this... The following query works fine: ```sql select * from test_view where cast(has_expired as text) = '1' ``` I don't want to start using this...

The alternative to using `cast` here would be for Datasette to convert the `"1"` to a `1` in Python code before passing it as a param. This feels a bit...

No, I think I need to use `cast` - I can't think of any way to ask SQLite "for this query, what types are the columns that will come back...

Note that Datasette does already have special logic to convert parameters to integers for numeric comparisons like `>`: https://github.com/simonw/datasette/blob/c4c9dbd0386e46d2bf199f0ed34e4895c98cb78c/datasette/filters.py#L203-L212 Though... it looks like there's a bug in that? It doesn't...

Also made me realize that this query: ```sql select * from sortable where sortable > :p0 ``` Only works here thanks to the column affinity thing kicking in too: https://latest.datasette.io/fixtures?sql=select+*+from+sortable+where+sortable+%3E+%3Ap0&p0=70

I think the hook gets called any time any exception makes it to this function: https://github.com/simonw/datasette/blob/950cc7677f65aa2543067b3bbfc2b6acb98b62c8/datasette/app.py#L1374-L1440 Multiple plugins can register for the hook. If they return a `Response` then that's...