On 27 Nov 2015, at 9:08pm, T?r?k Edwin <edwin+sqlite3 at etorok.net> wrote:
> Thanks, non-deterministic was probably the wrong term to use. > I wanted to find situations where a query's result depends on an > implementation detail of SQLite, and the behaviour is not fully specified by > the query itself, > i.e. it could change from one version to the next, or even with same version > by slight changes to the DB internal structures. > So far I know of two possible situations like this: the 'arbitrary row > choice' in the question above, and order of results in an unordered select. Okay. That's a sensible thing to want. The bad news is that there are many of them. For instance, in your post you mention "and order of results in an unordered select". However, even in an ordered SELECT the order of results can change because two rows may have the same value. The order that SQLite returns those rows can depend on the order in which the rows were INSERTed or UPDATEd, and on which indexes are available. Another category of unpredictable things involve JOINs where the programmer of the JOIN assumed that only one row will satisfy the 'ON' clause. Again depending on indexes and data changes a row in one table might be matched with one row for one command but another for another command. Other unpredictable things are things involving random numbers (i.e. the random core function or external functions) and anything involving multiple threads or processes. And just as you write, all the above behaviours can change in different versions of SQLite so even if you do detailed detective work using the current version it might all be obsolete next week. Simon.
