Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

I think people are missing the point, probably becuase it's not a great example. Consider the following statement: SELECT funca(slow(10)), funkb(slow(10)) and lets say slow(10) takes an hour to compute, and funka and funkb take almost no time to execute. With common subexpression optimization

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 9/14/17, Darko Volaric wrote: > I think people are missing the point, probably becuase it's not a great > example. Consider the following statement: > > SELECT funca(slow(10)), funkb(slow(10)) > > and lets say slow(10) takes an hour to compute, and funka and funkb take >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > On 9/14/17, Darko Volaric wrote: > > I think people are missing the point, probably becuase it's not a great > > example. Consider the following statement: > > > > SELECT funca(slow(10)), funkb(slow(10)) >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 3:08pm, Darko Volaric wrote: > I think people are missing the point, probably becuase it's not a great > example. Consider the following statement: > > SELECT funca(slow(10)), funkb(slow(10)) > > and lets say slow(10) takes an hour to compute, and funka

Re: [sqlite] SQL prepare statement - help

On 9/13/2017 9:20 PM, Papa wrote: void write(const std::string& table_name, const int pos, const int64_t data) {     ...     std::string apstr = "INSERT INTO (";     apstr += table_name.data();     apstr += ", ";     apstr += data; I'm pretty sure this calls string::operator+=(char),

Re: [sqlite] Table-valued functions as PIVOT

On 9/14/17, Gary Briggs wrote: > It's been a while since anyone has brought up PIVOT, and none that I > can see since table-valued functions were added [in 3.9.0, I think?] > > Has anyone successfully implemented PIVOT using table-valued functions? > > I'm in the situation

Re: [sqlite] SQL prepare statement - help

> On Sep 14, 2017, at 8:19 AM, Igor Tandetnik wrote: > > I'm pretty sure this calls string::operator+=(char), appending to the string > a character whose ASCII code happens to be char(data). Which is unlikely to > be what you had in mind. I suspect you get a warning from

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring that THAT gets calculated only once? SELECT calc_once(slow(10))   FROM xxx Note that if the same function

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

OK, in trying to clear up one misunderstanding I've created another, so let me be clear: I don't support automatic CSE, I think it should be done manually. But to support that Richard, can you answer this question: is there a way of ensuring that a particular expression (or just function call)

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Sep 14, 2017, at 9:36 AM, Darko Volaric wrote: > > I don't support automatic CSE, I think it should be done manually. So…you want a SQL profiler, then? Like EXPLAIN? I suppose there might be a market for a tool that takes a query string and spends a ridiculous amount of

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 9/14/17, Darko Volaric wrote: > is > there a way of ensuring that a particular expression (or just function call) > will be guaranteed to be evaluated before any other in a particular > statement? No, not in general. There are special cases were the order of evaluation is

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Sep 14, 2017, at 8:49 AM, Dominique Devienne wrote: > > On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > >> the amount of extra time spent inside of sqlite3_prepare() in order to >> deal with them is not worth the effort. > > But why not let the

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 5:38 PM, Warren Young wrote: > On Sep 14, 2017, at 8:49 AM, Dominique Devienne > wrote: > > > > On Thu, Sep 14, 2017 at 4:13 PM, Richard Hipp wrote: > > > >> the amount of extra time spent inside of

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 8:38 AM, Warren Young wrote: > > All the examples I’ve seen attempting to support the value of this feature > are simple enough that even a naive text compression algorithm could find the > similarities and “hoist” the copies so the value is computed

[sqlite] Table-valued functions as PIVOT

It's been a while since anyone has brought up PIVOT, and none that I can see since table-valued functions were added [in 3.9.0, I think?] Has anyone successfully implemented PIVOT using table-valued functions? I'm in the situation again where I'd like a better way to pivot, where knowledge of

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 1:23 PM, Keith Medcalf wrote: > > You merely need to ONCE it either for each input row or for each result row. > So for example: > > select slow(a.x), slow(a.x)*slow(b.y), slow(b.y) from a, b where a.this == > b.that > > when computing the result

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

No, you do not have to keep track of all the possibly seen results to only compute them once in order to achieve significant benefits. You merely need to ONCE it either for each input row or for each result row. So for example: select slow(a.x), slow(a.x)*slow(b.y), slow(b.y) from a, b

Re: [sqlite] sqlite-autoconf-3200100 : Where is the test suite for sqlite ?

On 9/14/17, Dennis Clarke wrote: > RE : sqlite-autoconf-3200100 > > Dear maillist : > > After doing a typical configure and compile on a 64-bit PPC arch > linux server I was very very surprised to see : > > ppc64$ /usr/local/bin/gmake installcheck > gmake: Nothing to

Re: [sqlite] SQL prepare statement - help

On Sep 14, 2017, at 10:27 AM, Jens Alfke wrote: > > s << "INSERT INTO " << table_name << " (" << data; You probably just wrote a SQL injection vulnerability. Use prepared statements, [named] parameters, and the “bind” functions to build the query string instead.

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

> On Sep 14, 2017, at 10:31 AM, R Smith wrote: > > I think that's actually one of the things one of the posters tried and found > not to work. The problem is the WITH syntax in theory is just-like > constructing a temporary table and using the values from there, but in >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

If you're looking for a simple/easy/clean way of doing it, there isn't one. You have to modify the library to do it properly. But I still find it an interesting design challenge. Maybe instead of going the eager route you can go lazy and just cache subexpressions which might be called again.

Re: [sqlite] sqlite-autoconf-3200100 : Where is the test suite for sqlite ?

On 09/14/2017 02:00 PM, Richard Hipp wrote: On 9/14/17, Dennis Clarke wrote: RE : sqlite-autoconf-3200100 Dear maillist : After doing a typical configure and compile on a 64-bit PPC arch linux server I was very very surprised to see : ppc64$ /usr/local/bin/gmake

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Jens Alfke wrote: > can someone please tell me how to hoist/factor out the subexpression manually > then? Move the subexpression into a subquery, then prevent subquery flattening (http://www.sqlite.org/optoverview.html#flattening) by violating one of the listed constraints. (These rules might

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 8:02pm, Clemens Ladisch wrote: > Jens Alfke wrote: >> can someone please tell me how to hoist/factor out the subexpression >> manually then? > > Move the subexpression into a subquery, then prevent subquery flattening >

[sqlite] sqlite-autoconf-3200100 : Where is the test suite for sqlite ?

RE : sqlite-autoconf-3200100 Dear maillist : After doing a typical configure and compile on a 64-bit PPC arch linux server I was very very surprised to see : ppc64$ /usr/local/bin/gmake installcheck gmake: Nothing to be done for `installcheck'. ppc64$ /usr/local/bin/gmake check gmake:

Re: [sqlite] SQL prepare statement - help

> On Sep 14, 2017, at 11:10 AM, Warren Young wrote: > > You probably just wrote a SQL injection vulnerability. > Use prepared statements, [named] parameters, and the “bind” functions to > build the query string instead. Yeah, you're right. I was trying to keep the example

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 5:55pm, R Smith wrote: > Richard, wouldn't it be possible to supply a wrapping function (perhaps a > hint function, like the likelihood() function), that takes another function > as a parameter and then ensuring that THAT gets calculated only once? Can

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 2017/09/14 7:10 PM, Simon Slavin wrote: On 14 Sep 2017, at 5:55pm, R Smith wrote: Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin wrote: > > > On 14 Sep 2017, at 5:55pm, R Smith wrote: > > > Richard, wouldn't it be possible to supply a wrapping function (perhaps > a hint function, like the likelihood() function), that takes another >

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 14 Sep 2017, at 10:43pm, Nico Williams wrote: > On Thu, Sep 14, 2017 at 1:10 PM Simon Slavin > wrote: > >> Can you not do it with WITH ? I don’t really understand how WITH works >> but it would seem to evaluate its

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

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 wrote: > Try fl_value(...) IN () > > -Ursprüngliche Nachricht- > Von: sqlite-users