You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Filters fail to work correctly against calculated numeric columns returned by SQL views because type affinity rules do not apply #1671
I found a strange behavior, and I'm not sure if it's related to views and boolean values perhaps, or if there's something else weird going on here, but I'll provide an example that may help show what I'm seeing happen.
#!/bin/bashecho"\"id\",\"expiration_date\"0,2018-01-041,2019-01-052,2020-01-063,2021-01-074,2022-01-085,2023-01-096,2024-01-107,2025-01-118,2026-01-129,2027-01-13"> test.csv
csvs-to-sqlite test.csv test.db
sqlite-utils create-view --replace test.db test_view "select id, expiration_date, case when julianday('NOW') >= julianday(expiration_date) then 1 else 0 end as has_expired FROM test"
datasette test.db
Thanks again and let me know if you want me to provide anything else!
changed the title [-]Error with facets or parameterized filters on Boolean values ...[/-][+]Filters fail to work correctly against calculated numeric columns returned by SQL views because type affinity rules do not apply[/+]on Mar 21, 2022
When an expression is a simple reference to a column of a real table (not a VIEW or subquery) then the expression has the same affinity as the table column.
In your example, has_expired is no longer a simple reference to a column of a real table, hence the bug.
SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. Whether or not any conversions are attempted before the comparison takes place depends on the type affinity of the operands.
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 https://www.sqlite.org/stricttables.html says nothing about their impact on comparison operations. I should test this to make absolutely sure though.
Generated columns have a type, so my hunch is they will continue to work fine too.
If someone clicks on "View and edit SQL" from a filtered table page I don't want them to have to wonder why that cast is there.
But... for querying views, the cast turns out to be necessary.
So one fix would be to get the SQL generating logic to use casts like this any time it is operating against a view.
An even better fix would be to detect which columns in a view come from a table and which ones might not, and only use casts for the columns that aren't definitely from a table.
The trick I was exploring here might be able to help with that:
Though... it looks like there's a bug in that? It doesn't account for float values - "3.5".isdigit() return False - probably for the best, because int(3.5) would break that value anyway.
Activity
simonw commentedon Mar 21, 2022
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 = 1returns different results fromwhere has_expired = '1'This doesn't happen against tables because of SQLite's type affinity mechanism, which handles the type conversion automatically.
[-]Error with facets or parameterized filters on Boolean values ...[/-][+]Filters fail to work correctly against calculated numeric columns returned by SQL views because type affinity rules do not apply[/+]simonw commentedon Mar 21, 2022
Relevant section of the SQLite documentation: 3.2. Affinity Of Expressions:
In your example,
has_expiredis no longer a simple reference to a column of a real table, hence the bug.Then 4.2. Type Conversions Prior To Comparison fills in the rest:
simonw commentedon Mar 21, 2022
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 https://www.sqlite.org/stricttables.html says nothing about their impact on comparison operations. I should test this to make absolutely sure though.
Generated columns have a type, so my hunch is they will continue to work fine too.
simonw commentedon Mar 21, 2022
Thinking about options for fixing this...
The following query works fine:
I don't want to start using this for every query, because one of the goals of Datasette is to help people who are learning SQL:
If someone clicks on "View and edit SQL" from a filtered table page I don't want them to have to wonder why that
castis there.But... for querying views, the
castturns out to be necessary.So one fix would be to get the SQL generating logic to use casts like this any time it is operating against a view.
An even better fix would be to detect which columns in a view come from a table and which ones might not, and only use casts for the columns that aren't definitely from a table.
The trick I was exploring here might be able to help with that:
simonw commentedon Mar 22, 2022
The alternative to using
casthere would be for Datasette to convert the"1"to a1in Python code before passing it as a param.This feels a bit neater to me, but I still then need to solve the problem of how to identify the "type" of a column that I want to use in a query.
simonw commentedon Mar 22, 2022
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 from it?"Even the details from the
explaintrick explored in #1293 don't seem to come back with column type information: https://latest.datasette.io/fixtures?sql=explain+select+pk%2C+text1%2C+text2%2C+[name+with+.+and+spaces]+from+searchable_view+where+%22pk%22+%3D+%3Ap0&p0=1simonw commentedon Mar 22, 2022
Note that Datasette does already have special logic to convert parameters to integers for numeric comparisons like
>:datasette/datasette/filters.py
Lines 203 to 212 in c4c9dbd
Though... it looks like there's a bug in that? It doesn't account for
floatvalues -"3.5".isdigit()returnFalse- probably for the best, becauseint(3.5)would break that value anyway.simonw commentedon Mar 22, 2022
Also made me realize that this query:
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