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

Reply via email to