Loved that explanation. I could easily understand it. On Jan 19, 2017 17:14, "James K. Lowden" <jklow...@schemamania.org> wrote:
> 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users