search

simonw / datasette.io

The official project website for Datasette
https://datasette.io
92 stars 21 forks source link

500 error on `/-/beta?sort=oldest` #89

Closed simonw closed 2 years ago

simonw commented 2 years ago

https://datasette.io/-/beta?sort=oldest says:

fts5: syntax error near ""

simonw commented 2 years ago

The problem is this query, used for calculating the display snippet in the results: https://github.com/simonw/datasette.io/blob/9dffe361b0210b9d8b1f2fb820a3f2193f0f2fc7/templates/dogsheep-beta.yml#L68-L85

It throws an error if :q is an empty string.

So I needed to figure out a FTS query that will always return true. This was surprisingly hard! In the end this looks to work - since I know that the title of the row will always be included in the FTS index for that row:

             where
                id = :key
                and sections_fts match escape_fts(case when :q = '' then sections.title else :q end)