Isn't that what cross join is for? Do a select on a virtual table to calculate the value and then use that value in the real where clause?
On Wed, Sep 13, 2017, 9:10 AM Hick Gunter <h...@scigames.at> wrote: > Try fl_value(...) IN (<list>) > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Jens Alfke > Gesendet: Dienstag, 12. September 2017 19:26 > An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] [sqlite] Common subexpression optimization of > deterministic functions > > 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 > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
