cubicweb #3590300 Broken python<->sql type conversion in sqlite [open]

Consider the following RQL query:

Any MIN(CD), MAX(CD) WHERE X is Application, X creation_date CD

rql will turn this into the following SQL query:

SELECT MIN(_X.cw_creation_date), MAX(_X.cw_creation_date)
FROM cw_Application AS _X

min and max in Postgres are explicitly specified as having the same return type as the argument type - timestamp in the above queries - which psycopg2 will happily convert to datetime objects.

SQLite's type system is vastly different and aggregate functions such as min and max will return whatever type the arguments were cast to (usually either floating numbers or strings) before actually running the function.

TL;DR: sqlite3 returns a Unicode string instead of the expected datetime for the original query, breaking unsuspecting code such as web.facet.DateRangeFacet which is built around this broken assumption.

The proper fix is to modify the generated SQL to look like this instead

SELECT MIN(_X.cw_creation_date) AS "[timestamp]", MAX(_X.cw_creation_date) AS "[timestamp]"
FROM cw_Application AS _X

and to set sqlite3.connect()'s detect_types argument to sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES (the latter part needs to be done in logilab-database).

done in<not specified>
closed by<not specified>