On 2017/01/25 3:18 PM, Dominique Devienne wrote:
On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp <d...@sqlite.org> wrote:
On 1/25/17, Richard Hipp <d...@sqlite.org> wrote:
On 1/25/17, dspub...@freemail.hu <dspub...@freemail.hu> 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).
Thanks for responses, I have an other problem
select * from (select row_number(name) as id,name from example order by name
desc) t order by name
In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because looks
like, the server ignores it.
_______________________________________________
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
