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 per row. 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. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
