Re: [sqlite] Custom window functions vs builtin
> On 12 Nov 2019, at 14:58, Dan Kennedy wrote: > > No, they don't need to handle that. The rows will always be added/removed in > ORDER BY order for all window functions (and will be removed in the same > order in which they were added if the ORDER BY order is ambiguous). Ah, excellent! This dramatically simplifies my life :) Could I suggest adding a note to this extent to https://www.sqlite.org/windowfunctions.html#udfwinfunc for the next paranoid person? Kind regards, Merijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Custom window functions vs builtin
On 12/11/62 18:50, Merijn Verstraaten wrote: I already asked this question without an answer, but as it hidden somewhere nested deeply in another thread I think it may have simply gone under the radar. Apologies for the duplication if you already saw it! How is the behaviour of (custom) window functions defined? Specifically, in the presence of an ORDER BY on the window. The functionality of row_number/rank/dense_rank seems to require that xStep and xInverse are called on rows in the order specified by ORDER BY. And, indeed, the implementation of row_number() in the sqlite source seems to rely on being called in the same order as ORDER BY, but at the same time the documentation states: "the built-in window functions, however, require special-case handling in the query planner and hence new window functions that exhibit the exceptional properties found in the built-in window functions cannot be added by the application." So does this indeed mean that these builtin ones are handled specially and other windows functions have to accept/deal with having their window arguments added/removed in an arbitrary order? No, they don't need to handle that. The rows will always be added/removed in ORDER BY order for all window functions (and will be removed in the same order in which they were added if the ORDER BY order is ambiguous). There is some special handling for the various built-in window functions though. For example, most of them ignore the window type. You can't implement percent_rank(), cume_dist() or ntile() without knowing the number of rows in the partition before returning any values, so that requires special handling as well. There are probably other things too... Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Custom window functions vs builtin
I already asked this question without an answer, but as it hidden somewhere nested deeply in another thread I think it may have simply gone under the radar. Apologies for the duplication if you already saw it! How is the behaviour of (custom) window functions defined? Specifically, in the presence of an ORDER BY on the window. The functionality of row_number/rank/dense_rank seems to require that xStep and xInverse are called on rows in the order specified by ORDER BY. And, indeed, the implementation of row_number() in the sqlite source seems to rely on being called in the same order as ORDER BY, but at the same time the documentation states: "the built-in window functions, however, require special-case handling in the query planner and hence new window functions that exhibit the exceptional properties found in the built-in window functions cannot be added by the application." So does this indeed mean that these builtin ones are handled specially and other windows functions have to accept/deal with having their window arguments added/removed in an arbitrary order? Kind regards, Merijn ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
