On 2017/01/25 3:18 PM, Dominique Devienne wrote:
On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp <[email protected]> wrote:
On 1/25/17, Richard Hipp <[email protected]> wrote:
On 1/25/17, [email protected] <[email protected]> wrote:
I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t where
id<=5
SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.
Further information:
The query optimizer is transforming your nested query into a single
query. You wrote:
SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;
Evaluated directly, this would require two separate queries. For
improved performance, SQLite "flattens" the inner query into the
second, like this:
SELECT func(name), name FROM example WHERE func(name)<5;
Hi Richard,
Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?
Not only would it avoid calling it twice, the QP might even cache it for
future iterations with the same parameter... The immediate problem here
is that his function is specifically NOT deterministic, it returns an
ever-growing result upon each call (from the looks of it - untested).
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
