Re: [sqlite] [OT] mySQL ORDER BY clause in Views

[Peter Haworth]

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

[John McKown]

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

[James K. Lowden]

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

[Scott Hess]

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

[R Smith]

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

[Simon Slavin]

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

[Scott Hess]

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

[Simon Slavin]

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

[Peter Haworth]

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 +
> From: Peter Haworth 
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] [OT] mySQL ORDER BY clause in Views
> Message-ID:
> 

Re: [sqlite] [OT] mySQL ORDER BY clause in Views

[Simon Slavin]

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