On Wed, Jan 18, 2017 at 3:36 PM, Peter Haworth <p...@lcsql.com> wrote: > I am in the process of converting an SQLite database to mySQL. The SQLIte > db includes several views with ORDER BY clauses that have always returned > qualifying rows in the correct order. > > I am discovering that in mySQL issuing a SELECT statement against these > same views works fine in terms of the order in which the rows are returned > if the SELECT does not include a WHERE clause but 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 and it > seems to me that the SQLite behavior is correct. > > Does anyone know what the official SQL specification has to say on this > topic, if anything?
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. If you have: CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT); INSERT INTO t VALUES (1, 'x'), (2, 'a'), (3, 'h'); CREATE VIEW tv (id, v) AS SELECT id, v FROM t ORDER BY v; Then: SELECT * FROM tv ORDER BY v; should always return rows as ordered by column v, but: SELECT * FROM tv; can return rows in an implementation-defined order. That order may happen to be the order defined by CREATE VIEW, depending on implementation. I don't think the standard is likely to address this, because the code which constructs the result set from the VIEW might use various optimization tricks (such as temporary tables or indices) based on the interactions of the various WHERE clauses. I don't think a VIEW or a TABLE would matter for this. In fact, I'd expect it to be more likely to forbid ORDER BY in a VIEW definition, which happens: https://msdn.microsoft.com/en-us/library/ms188385.aspx "The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless either the TOP or OFFSET and FETCH clauses are also specified. When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself." -scott _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
