On 8 Jul 2013, at 5:51am, James K. Lowden <jklow...@schemamania.org> wrote:
> Your test doesn't show that the above *query* is inefficient, but that > SQLite executes it inefficiently. The query can be executed > efficiently; whether or not SQLite does so is a design choice. One problem with implementing a 'returned row number' function is that SQL is not consistent in the order of rows returned. In SQL it is perfectly allowable for two queries SELECT matchDate WHERE score > 100 to return the rows in different orders. The only times this is not allowed to happen are when < 2 rows are returned, or when an ORDER BY clause uses a key which happens to have only unique values. In practise, the way most SQL engines work means that unless underlying data is changed, the row numbers are consistent. But I have seen SQL engines where rows which had changed most recently were returned first even if the values that were changed weren’t mentioned in the SELECT. And I have seen SQL engines where the data was distributed over different nodes and the rows which were returned first corresponded to whichever datastore returned its results first, which depended on network traffic between the nodes. If a 'returned row number' function was defined it would encourage people to think that the row numbers were deterministic: in other words that they could rely on a fixed relationship between the row number and the data returned. Not doing that would lead to support calls and complaints about a broken API. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
