On 03/23/2011 01:07 AM, Jay A. Kreibich wrote: > On Tue, Mar 22, 2011 at 06:25:04PM +0700, Dan Kennedy scratched on the wall: > >> SQLite assumes that the result of each expression in the WHERE >> clause depends only on its inputs. If the input arguments are >> the same, the output should be do. Since random() has no inputs, >> SQLite figures that it must always return the same value. > > To what degree? And expression like "...WHERE 20<= (random()%100)" > has no "inputs" other than constants, but is still evaluated once per > row. Or is it just raw functions and column references, and not the > expression as a whole?
I think once you are trying to predict how many times or exactly when a user function will be called for a given SQL statement you are technically into the realms of undefined behaviour. And again, technically, SQLite assumes that the value returned by a user-defined function are a function of its inputs. Once instance of where this assumption is used is with virtual tables. If you do: SELECT * FROM vtab WHERE col = userfunction(<constants>); and the xBestIndex() method says it can handle "col = ?" but does not set the corresponding "aConstraintUsage[x].omit" flag, SQLite will evaluate userfunction(<constants>) once to pass to the xFilter method, and then again for each row visited by the virtual table cursor. If the result of userfunction(<constants>) is not stable, the query could return difficult to explain results. I think there might be other such examples too. Left joins. Where clauses that include OR operators. That sort of thing. That said, we're aware of the way random() and user-functions with side-effects are often used. I don't think it's something that would get changed capriciously. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
