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? Thanks, --DD _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
