> 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.
This is perfectly consistent with Sqlite behaviour in other comparable situations, and consistent with what the standard says about ORDER BY: "implementation defined". > 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. Absolutely! Application programmers depend heavily on SQL to do the heavy lifting in paging, and all the databases I use support it. Andl supports it in a way that is entirely consistent with relational theory, but many other purist relational projects have decided not to. [The application programmer is of course free to further sort the data locally if desired.] My only point for the original post was that when you are dealing with an SQL construct that is not defined by the standard, it's helpful if product maintainers make some attempt to informally implement a common subset;. In this case Sqlite is mostly compatible with Postgres, but not for LIMIT -1 vs LIMIT ALL. > 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 If you do that presumably you know to expect what you get! Regards David M Bennett FACS Andl - A New Database Language - andl.org
