On Wed, 18 Jan 2017 23:36:14 +0000 Peter Haworth <p...@lcsql.com> wrote:
> if I include a WHERE claus, the view's ORDER BY clause is ignored and > the rows are returned in seemingly random order. > > Searching around the web suggests that this behavior is accepted as > correct in mySQL although I haven't been able to find a justification > for it I'd like to amplify Ryan's correct answer that a view, like a table, has no defined order. Regarding justification: That's what the SQL standard says. Why is that the standard? A view is not merely stored SQL; it is a *definition*. Since a data definition in SQL has no order, it's no surprise that a view has no order. To impose order on a view (as part of its definition) is to impose meaning on the order, and order is not part of the data (what relational folks call the relation's "extension"). In SQL, data exists only when expressed explicitly, usually as a column. It's tempting to think of a view as a macro. But SQL has no macro system. When we drop a view into a query, it's convenient and correct to think of it as a "all that view's SQL here, as if a table", and to a large extent that's also how most SQL engines process it. That conceptualization falls down when ORDER BY and LIMIT are included, because the view is no longer "like a table". I'm sure it's too late for the 2017 wish list, but ISTM this is an opportunity to suggest a "standard" mode (or perhaps "pedantic") that would warn about or refuse to process SQL constructs that are in exception to the SQL standard. Then projects like your that have big-boy aspirations could guard against inadvertent reliance on nonstandard features. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
