SQLite 3.19 doesn’t seem to coalesce identical calls to a deterministic
function. For example, in this query, where `fl_value` is a function I’ve
registered as SQLITE_DETERMINISTIC:
SELECT key FROM kv_default
WHERE fl_value(body, 'contact.address.state') = 'CA'
OR fl_value(body, 'contact.address.state') = 'WA'
fl_value gets called twice per row in the table, with the same inputs both
times of course. As fl_value is not a cheap function — it’s similar to
json_value — it would be a noticeable speedup if it were evaluated only once
Is there a way I can restructure these (automatically generated) queries to do
the refactoring explicitly? Sort of like assigning to a temporary variable in
an imperative language? It looks like a WITH clause lets me do this
syntactically, but I'm not sure if it'll make a difference at runtime.
sqlite-users mailing list