Thanks Simon. According to SHOW VARIABLES LIKE "@version@" inno-db version is 5.6.25 and version is 5.6.25-log, which I think are pretty recent versions.
Seems like I'm pretty much stuck with the issue. On Thu, Jan 19, 2017 at 4:00 AM < sqlite-users-requ...@mailinglists.sqlite.org> wrote: > Send sqlite-users mailing list submissions to > sqlite-users@mailinglists.sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body 'help' to > sqlite-users-requ...@mailinglists.sqlite.org > > You can reach the person managing the list at > sqlite-users-ow...@mailinglists.sqlite.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqlite-users digest..." > > > Today's Topics: > > 1. [OT] mySQL ORDER BY clause in Views (Peter Haworth) > 2. Re: [OT] mySQL ORDER BY clause in Views (Simon Slavin) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Wed, 18 Jan 2017 23:36:14 +0000 > From: Peter Haworth <p...@lcsql.com> > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] [OT] mySQL ORDER BY clause in Views > Message-ID: > <CAGDT7eMF0V1XHeJjqEgfskdL4KUf= > wgsvwwapzkee45bhak...@mail.gmail.com> > Content-Type: text/plain; charset=UTF-8 > > 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? > > Thanks, > Pete > > > ------------------------------ > > Message: 2 > Date: Wed, 18 Jan 2017 23:57:54 +0000 > From: Simon Slavin <slav...@bigfraud.org> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] [OT] mySQL ORDER BY clause in Views > Message-ID: <04784eea-6f56-458b-b7f6-3b9fff3cf...@bigfraud.org> > Content-Type: text/plain; charset=utf-8 > > > 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. > > ------------------------------ > > Subject: Digest Footer > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ------------------------------ > > End of sqlite-users Digest, Vol 109, Issue 18 > ********************************************* > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
