On Thu, Jan 19, 2017 at 1:03 PM, R Smith <rsm...@rsweb.co.za> wrote: > On 2017/01/19 9:01 PM, Simon Slavin wrote: >> On 19 Jan 2017, at 6:54pm, Scott Hess <sh...@google.com> wrote: >>> Just to be clear, you're saying that the VIEW has an ORDER BY, but >>> when you SELECT from the VIEW you aren't using an ORDER BY? >>> >>> If your outer SELECT is using an ORDER BY and that is not respected, >>> that seems like an egregious bug. But if your outer SELECT has no >>> ORDER BY, then that seems like depending on implementation-defined >>> behavior. >> >> Scott wins. I should have read more carefully. I thought you were >> referring to a VIEW with both WHERE and ORDER BY. >> >> If you perform a SELECT on a VIEW and your SELECT does not specify an >> order, the results can be in any order. To enforce an order on the SELECT, >> you have to specify the order in the SELECT. > > Very correct - just to add a little bit as to WHY this is (since the OP > seems to be on new territory) - A view, just like a table, is regarded by > SQL as a set and it has no inherent order, nor can it have order explicitly > per definition (meant here as "as it was defined"). The fact that SQLite > allows ordering in a view is simply 'cause it is nice, in the same way that > it will order output from a table when using an ordered index, but this > behaviour is not required by the standard, nor guaranteed by SQLite, plus, > it might change in future. You simply /must/ include an ORDER BY in the > final SELECT if you wish to see ordered output. Yes... every time. > > It boils down to: If you do not add the ORDER BY clause explicitly to your > final SELECT, then you have no right to expect an ordered outcome.
Note that your engine should do the right thing if you specify "too many" ORDER BY clauses. If you have an index which orders the data the same way your ORDER BY clause does, then SQLite can happily compile to the same code with or without the ORDER BY clause. SQLite implements a VIEW by kind of inlining the VIEW's SELECT statement. There is no data storage associated with a SQLite VIEW. So defining the VIEW without an ORDER BY and defining the SELECT with the ORDER BY should result in identical performance, but with improved correctness. [Yes, I can see how it may be annoying to some to have to sprinkle ORDER BY clauses all over the place, rather than having them centralized. But note that ORDER BY is specifying the order that the code calling this current statement expects, so it is appropriate to specify the ORDER BY at that point, rather than having it baked into the schema somehow, where it can easily be misplaced. Basically, if your code wants the data in a particular order for a particular statement, you should consider the act of specifying that ordering as part of your API contract.] -scott _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
