Re: [sqlite] Default ordering of SELECT query

Andrii Motsok wrote: > I cannot force my users to add ORDER BY to all queries especially for > non UNIQUE indexes. Randomly flip this setting before each query; they'll learn: http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects Regards, Clemens

Re: [sqlite] Default ordering of SELECT query

On 6 Oct 2016, at 1:41pm, Andrii Motsok wrote: How do you mean "follow index ordering"? > This is only my observation that sqlite usually returns rows in the order in > which they are sorted in by index. So if I have two indexes and SELECT with > WHERE followed by AND choice of indexes ca

Re: [sqlite] Default ordering of SELECT query

> > > > Any DB update or insert or vacuum or analyze might alter the > > > > row order produced where the ordering isn't explicit. > I am interested only in the same ordering between two subsequent > query executions. The things which can happen in between are: > * checkpointing > * close/open i

Re: [sqlite] Default ordering of SELECT query

>On 2016/10/05 5:27 PM, Andrii Motsok wrote: >> Date: Mon, 3 Oct 2016 16:25:09 +0200 >> >> >> Hi, >> >> 1) In case of ORDER BY if we choose ordering which does not follow "index >> ordering" we will get performance degradation. >> 2) If we use ORDER BY can we be sure that order of rows is the sam

Re: [sqlite] Default ordering of SELECT query (Clemens Ladisch)

On 2016/10/05 5:27 PM, Andrii Motsok wrote: Date: Mon, 3 Oct 2016 16:25:09 +0200 Hi, 1) In case of ORDER BY if we choose ordering which does not follow "index ordering" we will get performance degradation. 2) If we use ORDER BY can we be sure that order of rows is the same for two subseque

Re: [sqlite] Default ordering of SELECT query (Clemens Ladisch)

Date: Mon, 3 Oct 2016 16:25:09 +0200 From: Clemens Ladisch To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Default ordering of SELECT query Message-ID: Content-Type: text/plain; charset=utf-8 >>Andrii Motsok wrote: >> "If a SELECT statement that returns more

Re: [sqlite] Default ordering of SELECT query

On 3 Oct 2016, at 3:18pm, Andrii Motsok wrote: > Can we assume that container1 == container2 ? Or in another words can we > assume that order of rows is same in this scenario? Even if this assumption holds true in the current version of SQLite, someone could always do PRAGMA reverse_unordere

Re: [sqlite] Default ordering of SELECT query

Andrii Motsok wrote: > "If a SELECT statement that returns more than one row does not have an ORDER > BY clause, the order in which the rows are returned is undefined" > > We have scenario: > * database is opened in WAL mode > * 10 rows have been read to container1 > * database has been checkpo

[sqlite] Default ordering of SELECT query

Hi, Sqlite man (https://www.sqlite.org/lang_select.html) [https://sqlite.org/images/syntax/select-stmt.gif] SQLite Query Language: SELECT www.sqlite.org The SELECT statement is used to query the database. The resu