On 18 May 2016, at 2:05am, dandl <david at andl.org> wrote: >> The "problem" is to produce 3 rows where, relationally, the only answers > have >> 2 or 4 rows. There is no right answer to the problem because there is no >> answer to the problem. > > Which is what I said. The solution with 3 rows is unambiguous. You either > resolve this the way the standard does by making it "implementation defined" > or by forcing the query to be unambiguous by adding all the ORDER BY columns > to the SELECT list.
Or we'll answer my original question by breaking down one of the above two options. The documentation for the implementation may simply say that the order will be consistent in any one database connection, without ever saying what the order will be. Numerous users of SQLite have assumed this over the years, since if you cannot make this assumption you cannot implement cursors or scrolling windows the way they want to, by changing an OFFSET (or remembering the key values for the first and last lines) as the user presses line-up, line-down, page-up or page-down. It's a natural use of SQLite inside any device with a small display and I'm sure programmers would be very annoyed if it was difficult to program. SQLite does what they want even though there's no documentation that says it'll work. SQLite does break the consistency rule under one circumstance, though it's very unlikely that a programmer would trigger it by accident. It happens when you change the setting for PRAGMA reverse_unordered_selects = boolean Simon.
