Re: [sqlite] Common subexpression optimization of deterministic functions

Stop,,I'm not subscribed  Sent via the Samsung Galaxy S7, an AT&T 4G LTE smartphone Original message From: Warren Young Date: 9/12/17 6:19 PM (GMT-05:00) To: SQLite mailing list Subject: Re: [sqlite] Common subexpression optimization of deterministic   function

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 2:36 PM, Jens Alfke wrote: > > On Sep 12, 2017, at 12:58 PM, Warren Young wrote: >> >> Could it be *extended* to mean what you want? Of course, but that means >> you’re asking for a feature, not reporting a bug. > > I never claimed to be reporting a bug! I read your refe

Re: [sqlite] Common subexpression optimization of deterministic functions

Yeah that is a tricky bit, especially since the query optimizer might evaluate join expressions in an arbitrary order. A possible approach to this is to work out how to always get a particular expression evaluated first (that may well just be the left-most expression in the WHERE clause) then cr

Re: [sqlite] Common subexpression optimization of deterministic functions

lite] Common subexpression optimization of deterministic functions You can implement this by using user defined functions to implement row "local variables" or "registers". They're single assignment storage that keeps intermediate results, namely the common sube

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 1:41 PM, Darko Volaric wrote: > > You can implement this by using user defined functions to implement row > "local variables" or "registers". They're single assignment storage that > keeps intermediate results, namely the common subexpressions. Thanks! That's a very inte

Re: [sqlite] Common subexpression optimization of deterministic functions

You can implement this by using user defined functions to implement row "local variables" or "registers". They're single assignment storage that keeps intermediate results, namely the common subexpressions. You'd define two functions, something like Get(rowid, name) and Set(rowid, name, value).

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:58 PM, Warren Young wrote: > > Could it be *extended* to mean what you want? Of course, but that means > you’re asking for a feature, not reporting a bug. I never claimed to be reporting a bug! The subject line refers to this as an "optimization", and in the initial

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:55 PM, Richard Hipp wrote: > > But we deliberately omit common subexpression elimination (CSE). This > is because our research shows that out of the millions of queries that > SQLite compiles every second, only a very tiny fraction would actually > benefit from CSE OM

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 12:41 PM, Jens Alfke wrote: > >> On Sep 12, 2017, at 11:09 AM, Warren Young wrote: >> >> From my reading of the docs, I don’t see that that is the purpose of >> SQLITE_DETERMINISTIC: >> >> https://www.sqlite.org/deterministic.html >>

Re: [sqlite] Common subexpression optimization of deterministic functions

On 9/12/17, Jens Alfke wrote: > > I realize SQLite doesn't have the kind of industrial-strength query > optimizers that other SQL databases have, but (from an outside perspective) > this seems like a fairly straightforward optimization. SQLite is already > doing some similar tricks to recognize ma

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 12:02 PM, Darren Duncan wrote: > > Practically speaking any optimization to reduce actual calls to the > deterministic function would have to be at compile time to rewrite the query > to explicitly keep the result of the function and use it several times, Exactly. > wh

Re: [sqlite] Common subexpression optimization of deterministic functions

On 2017-09-12 11:41 AM, Jens Alfke wrote: On Sep 12, 2017, at 11:09 AM, Warren Young wrote: From my reading of the docs, I don’t see that that is the purpose of SQLITE_DETERMINISTIC: https://www.sqlite.org/deterministic.html Actually it is. "A

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 11:09 AM, Warren Young wrote: > > From my reading of the docs, I don’t see that that is the purpose of > SQLITE_DETERMINISTIC: > >https://www.sqlite.org/deterministic.html > Actually it is. "A deterministic function alway

Re: [sqlite] Common subexpression optimization of deterministic functions

On Sep 12, 2017, at 11:25 AM, Jens Alfke wrote: > > 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: From my reading of the docs, I don’t see that that is th

Re: [sqlite] Common subexpression optimization of deterministic functions

> On Sep 12, 2017, at 10:52 AM, Richard Hipp wrote: > > ... WHERE fl_value(body,'contact.address.state') IN ('CA','WA'); This was just a simple example query. In general it's possible to have arbitrary queries that use arbitrary properties. For example it might be comparing a property to a

Re: [sqlite] Common subexpression optimization of deterministic functions

On 9/12/17, Jens Alfke wrote: > 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') =