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] Executing multiple statements at once

[Simon Slavin]

On 19 Jan 2017, at 5:55pm, Scott Hess  wrote:

> You could use sqlite3_exec(), which is internally just going to issue
> multiple single SQL statements.

Just to clarify, dealing with lists of commands is exactly what sqlite3_exec() 
is intended to do.  It should do exactly what you want.

It can be difficult to tell what went wrong if sqlite3_exec() results in an 
error.  However, with the statements you list there’s little scope for that.

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] Executing multiple statements at once

[Scott Hess]

On Thu, Jan 19, 2017 at 9:24 AM, Igor Korot  wrote:
> Is it possible to write something like this:
>
> sqlite3_prepare_v2( m_db, "BEGIN TRANSACTION; CREATE TEMP TABLE temp
> AS SELECT * FROM mytable; DROP TABLE mytable; CREATE TABLE mytable(id
> INTEGER PRIMARY KEY, name TEXT PRIMARY KEY, salary INTEGER); INSERT
> INTO mytable SELECT * FROM temp; DROP TABLE temp; COMMIT;", -1, ,
> NULL );
>
> or I will have to issue multiple single SQL statements?

You could use sqlite3_exec(), which is internally just going to issue
multiple single SQL statements.

-scott
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Executing multiple statements at once

[Clemens Ladisch]

Igor Korot wrote:
> sqlite3_prepare_v2( m_db, "BEGIN TRANSACTION; CREATE TEMP TABLE temp
> AS SELECT * FROM mytable; DROP TABLE mytable; CREATE TABLE mytable(id
> INTEGER PRIMARY KEY, name TEXT PRIMARY KEY, salary INTEGER); INSERT
> INTO mytable SELECT * FROM temp; DROP TABLE temp; COMMIT;", -1, ,
> NULL );
>
> or I will have to issue multiple single SQL statements?

To quote :
| If pzTail is not NULL then *pzTail is made to point to the first byte
| past the end of the first SQL statement in zSql. These routines only
| compile the first statement in zSql, so *pzTail is left pointing to
| what remains uncompiled.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Executing multiple statements at once

[Igor Korot]

 Hi, ALL,
Is it possible to write something like this:

sqlite3_prepare_v2( m_db, "BEGIN TRANSACTION; CREATE TEMP TABLE temp
AS SELECT * FROM mytable; DROP TABLE mytable; CREATE TABLE mytable(id
INTEGER PRIMARY KEY, name TEXT PRIMARY KEY, salary INTEGER); INSERT
INTO mytable SELECT * FROM temp; DROP TABLE temp; COMMIT;", -1, ,
NULL );

or I will have to issue multiple single SQL statements?

Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users