[OT] mySQL ORDER BY clause in Views

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[OT] mySQL ORDER BY clause in Views

phaworth
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

Simon Slavin-3

On 18 Jan 2017, at 11:36pm, Peter Haworth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

phaworth
In reply to this post by phaworth
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 <
[hidden email]> wrote:

> Send sqlite-users mailing list submissions to
>         [hidden email]
>
> 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
>         [hidden email]
>
> You can reach the person managing the list at
>         [hidden email]
>
> 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 <[hidden email]>
> To: [hidden email]
> Subject: [sqlite] [OT] mySQL ORDER BY clause in Views
> Message-ID:
>         <CAGDT7eMF0V1XHeJjqEgfskdL4KUf=
> [hidden email]>
> 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 <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] [OT] mySQL ORDER BY clause in Views
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=utf-8
>
>
> On 18 Jan 2017, at 11:36pm, Peter Haworth <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 109, Issue 18
> *********************************************
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

Simon Slavin-3

On 19 Jan 2017, at 5:58pm, Peter Haworth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

Scott Hess
In reply to this post by phaworth
On Wed, Jan 18, 2017 at 3:36 PM, Peter Haworth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

Simon Slavin-3

On 19 Jan 2017, at 6:54pm, Scott Hess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

R Smith


On 2017/01/19 9:01 PM, Simon Slavin wrote:

> On 19 Jan 2017, at 6:54pm, Scott Hess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

Scott Hess
On Thu, Jan 19, 2017 at 1:03 PM, R Smith <[hidden email]> wrote:

> On 2017/01/19 9:01 PM, Simon Slavin wrote:
>> On 19 Jan 2017, at 6:54pm, Scott Hess <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

James K. Lowden
In reply to this post by phaworth
On Wed, 18 Jan 2017 23:36:14 +0000
Peter Haworth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

John McKown
Loved that explanation. I could easily understand it.

On Jan 19, 2017 17:14, "James K. Lowden" <[hidden email]> wrote:

> On Wed, 18 Jan 2017 23:36:14 +0000
> Peter Haworth <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [OT] mySQL ORDER BY clause in Views

phaworth
In reply to this post by phaworth
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users