Re: [sqlite] [OT] mySQL ORDER BY clause in Views
Thanks to all for the input. To clarify, yes the ORDER BY clause is in the view definition and the SELECT statement that addresses the view does not have an ORDER BY clause. I think the penny has finally dropped for me on this issue. The key was the description by a responder that views are not macros, they are tables and you can't specify an ORDER BY clause when creating a table. I still don't fully understand why all this appeared to work OK in SQLite, but not in mySQL Maybe some difference in the way the query planner works in each implementation I do think that If ORDER BY is not allowed in views, or has no effect, an error should be flagged when creating a view that includes it. Once again, thanks for all the input, I will proceed accordingly. Pete ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
Loved that explanation. I could easily understand it. On Jan 19, 2017 17:14, "James K. Lowden" wrote: > On Wed, 18 Jan 2017 23:36:14 + > Peter Haworth 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
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On Wed, 18 Jan 2017 23:36:14 + Peter Haworth 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
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On Thu, Jan 19, 2017 at 1:03 PM, R Smith wrote: > On 2017/01/19 9:01 PM, Simon Slavin wrote: >> On 19 Jan 2017, at 6:54pm, Scott Hess wrote: >>> 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. >> >> Scott wins. I should have read more carefully. I thought you were >> referring to a VIEW with both WHERE and ORDER BY. >> >> If you perform a SELECT on a VIEW and your SELECT does not specify an >> order, the results can be in any order. To enforce an order on the SELECT, >> you have to specify the order in the SELECT. > > Very correct - just to add a little bit as to WHY this is (since the OP > seems to be on new territory) - A view, just like a table, is regarded by > SQL as a set and it has no inherent order, nor can it have order explicitly > per definition (meant here as "as it was defined"). The fact that SQLite > allows ordering in a view is simply 'cause it is nice, in the same way that > it will order output from a table when using an ordered index, but this > behaviour is not required by the standard, nor guaranteed by SQLite, plus, > it might change in future. You simply /must/ include an ORDER BY in the > final SELECT if you wish to see ordered output.Yes... every time. > > It boils down to: If you do not add the ORDER BY clause explicitly to your > final SELECT, then you have no right to expect an ordered outcome. Note that your engine should do the right thing if you specify "too many" ORDER BY clauses. If you have an index which orders the data the same way your ORDER BY clause does, then SQLite can happily compile to the same code with or without the ORDER BY clause. SQLite implements a VIEW by kind of inlining the VIEW's SELECT statement. There is no data storage associated with a SQLite VIEW. So defining the VIEW without an ORDER BY and defining the SELECT with the ORDER BY should result in identical performance, but with improved correctness. [Yes, I can see how it may be annoying to some to have to sprinkle ORDER BY clauses all over the place, rather than having them centralized. But note that ORDER BY is specifying the order that the code calling this current statement expects, so it is appropriate to specify the ORDER BY at that point, rather than having it baked into the schema somehow, where it can easily be misplaced. Basically, if your code wants the data in a particular order for a particular statement, you should consider the act of specifying that ordering as part of your API contract.] -scott ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On 2017/01/19 9:01 PM, Simon Slavin wrote: On 19 Jan 2017, at 6:54pm, Scott Hess wrote: 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. Scott wins. I should have read more carefully. I thought you were referring to a VIEW with both WHERE and ORDER BY. If you perform a SELECT on a VIEW and your SELECT does not specify an order, the results can be in any order. To enforce an order on the SELECT, you have to specify the order in the SELECT. Very correct - just to add a little bit as to WHY this is (since the OP seems to be on new territory) - A view, just like a table, is regarded by SQL as a set and it has no inherent order, nor can it have order explicitly per definition (meant here as "as it was defined"). The fact that SQLite allows ordering in a view is simply 'cause it is nice, in the same way that it will order output from a table when using an ordered index, but this behaviour is not required by the standard, nor guaranteed by SQLite, plus, it might change in future. You simply /must/ include an ORDER BY in the final SELECT if you wish to see ordered output.Yes... every time. It boils down to: If you do not add the ORDER BY clause explicitly to your final SELECT, then you have no right to expect an ordered outcome. MySQL will ignore the order by in a view as it sees fit and there is nothing in the documentation I know of that claims otherwise, and MSSQL doesn't even allow you to order a view. I have not tested this on other engines, but I am sure it ends up "undefined" or "not allowed" in most cases. ORDER BY is really an "output" function, and I know Views blur the lines a bit, but the final SELECT is still the boss. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On 19 Jan 2017, at 6:54pm, Scott Hess wrote: > 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. Scott wins. I should have read more carefully. I thought you were referring to a VIEW with both WHERE and ORDER BY. If you perform a SELECT on a VIEW and your SELECT does not specify an order, the results can be in any order. To enforce an order on the SELECT, you have to specify the order in the SELECT. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On Wed, Jan 18, 2017 at 3:36 PM, Peter Haworth 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
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On 19 Jan 2017, at 5:58pm, Peter Haworth wrote: > 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. Those are far more recent than the versions I know had those related problems. Given how well-known it is, I’m very surprised that the fault you reported has not yet been fixed. Maybe someone made the decision that for backward compatibility reasons it won’t get fixed. Anyway, your original post is correct. SQLite is doing the right thing. MySQL is doing the wrong thing. On this list we can help with SQLite but not MySQL. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
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 > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] [OT] mySQL ORDER BY clause in Views > Message-ID: > 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 + > From: Simon Slavin > To: SQLite mailing list > 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 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
Re: [sqlite] [OT] mySQL ORDER BY clause in Views
On 18 Jan 2017, at 11:36pm, Peter Haworth 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
[sqlite] [OT] mySQL ORDER BY clause in Views
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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users