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.
MySQL will ignore the order by in a view as it sees fit and there is
nothing in the documentation I know of that claims otherwise, and MSSQL
doesn't even allow you to order a view.
I have not tested this on other engines, but I am sure it ends up
"undefined" or "not allowed" in most cases. ORDER BY is really an
"output" function, and I know Views blur the lines a bit, but the final
SELECT is still the boss.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
