On 18 Jan 2017, at 11:36pm, Peter Haworth <p...@lcsql.com> wrote: > 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.
This was a known bug in old — very old — versions of MySQL. You will see it reported as ignoring ORDER BY when using subviews, or ignoring ORDER BY when using GROUP BY, or ignoring ORDER BY when using WHERE. You will also see occasional reports of MySQL ignoring an index which would make the query run faster. They’re all caused by the same underlying problem. I thought it had been fixed years ago. And by years, I mean more than 5. I can’t believe such a serious bug lasted this long. Are you, perhaps, using an old version of MySQL ? Perhaps the version that was in use when the original code was written ? As you write in your post, SQLite’s behaviour is correct. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users