Re: [sqlite] Why this query plan?

On Thu, Jan 12, 2017 at 5:33 PM, Richard Hipp wrote: > On 1/12/17, Luca Ferrari wrote: > >> One thing I was not expecting was SQLite to use the index at all: >> since the query does not apply any filter (where clause), it simply >> states that the user

Re: [sqlite] Why this query plan?

> Anyone asking why the order is what it is is not a valid question Well, I think it was as I know the answer now and that was useful to know. RBS On Thu, Jan 12, 2017 at 11:17 PM, Darko Volaric wrote: > Your example is entirely wrong. Spreadsheet apps explicitly define the >

Re: [sqlite] Why this query plan?

Actually all that would happen is a massive number of hidden bugs would be revealed. He would be doing the world a favour. On Thu, Jan 12, 2017 at 5:13 PM, Jean-Christophe Deschamps wrote: > At 15:13 12/01/2017, you wrote: > >> Re: "I read this as a provocative joke." >> >>

Re: [sqlite] Why this query plan?

Your example is entirely wrong. Spreadsheet apps explicitly define the behavior, and provide functionality, for defaulting the attributes for unused cells. A better example is this: looking at your paper mail and asking "why didn't mail posted on the same day from the same sender arrive on the

Re: [sqlite] Why this query plan?

On 1/12/17, David Raymond wrote: > Well, then you're handcuffing it when an index would be better but is in > nowhere near rowid order. No, it just scans the index in reverse order. The idea of reverse_unordered_selects is that it makes DESC the default scan order

Re: [sqlite] Why this query plan?

ssage- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Bart Smissaert > Sent: Thursday, January 12, 2017 3:53 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Why this query plan? > > How about a > pragma_sort_as

Re: [sqlite] Why this query plan?

of SQLite Database Subject: Re: [sqlite] Why this query plan? How about a pragma_sort_asc_on_rowid_or_primary_integer_key_for_unordered_selects ? RBS On 12 Jan 2017 20:35, "Richard Hipp" <d...@sqlite.org> wrote: > On 1/12/17, David Raymond <david.raym...@tomtom.com> wrote:

Re: [sqlite] Why this query plan?

...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, January 12, 2017 3:35 PM To: SQLite mailing list Subject: Re: [sqlite] Why this query plan? On 1/12/17, David Raymond <david.raym...@tomtom.com> wrote: > > In the same vane I assume DRH's random ordering would be only ra

Re: [sqlite] Why this query plan?

How about a pragma_sort_asc_on_rowid_or_primary_integer_key_for_unordered_selects ? RBS On 12 Jan 2017 20:35, "Richard Hipp" wrote: > On 1/12/17, David Raymond wrote: > > > > In the same vane I assume DRH's random ordering would be only random by >

Re: [sqlite] Why this query plan?

On 1/12/17, David Raymond wrote: > > In the same vane I assume DRH's random ordering would be only random by page > of results. If you have 100+ million records in a table then keeping track > of which ones you've randomly picked so far would cripple systems with the >

Re: [sqlite] Why this query plan?

sqlite.org] On Behalf Of Bart Smissaert Sent: Thursday, January 12, 2017 12:03 PM To: SQLite mailing list Subject: Re: [sqlite] Why this query plan? > Because the index is smaller than the main table. Less disk I/O. Yes and that is the one (and only one) interesting thing I found from this t

Re: [sqlite] Why this query plan?

> So you could benefit from an index for reasons other than the usual reasons > eg assisting the where clause. Yes. Using a "covering index" (that contains all fields in the SELECT clause) is often suggested as a _potential_ optimisation step, so the main row-data does not need to be accessed

Re: [sqlite] Why this query plan?

On 11/01/17 16:49, Richard Hipp wrote: > For years I have threatened to make it a feature of SQLite that it > really does output the rows in some random order if you omit the ORDER > BY clause - specifically to expose the common bug of omitting the > ORDER BY clause when the order matters. And

Re: [sqlite] Why this query plan?

> Because the index is smaller than the main table. Less disk I/O. Yes and that is the one (and only one) interesting thing I found from this thread. So you could benefit from an index for reasons other than the usual reasons eg assisting the where clause. RBS On Thu, Jan 12, 2017 at 4:33 PM,

Re: [sqlite] Why this query plan?

On 1/12/17, Luca Ferrari wrote: > One thing I was not expecting was SQLite to use the index at all: > since the query does not apply any filter (where clause), it simply > states that the user wants all the rows, and while it is true that the > order is something the

Re: [sqlite] Why this query plan?

On Thu, Jan 12, 2017 at 1:45 AM, Richard Hipp wrote: > If you omit the ORDER BY clause, then the SQL database engine (*any* > engine, not just SQLite) is free to return the rows in whatever random > order it chooses. And it does not need to explain itself when it > does. :-) >

Re: [sqlite] Why this query plan?

At 15:13 12/01/2017, you wrote: Re: "I read this as a provocative joke." I didn't read it as just a joke. The analogy with random fonts, etc. breaks down, I think, because randomizing the ordering would be an attempt to *improve* sqlite's usability -- not some pedantic punishment. I read

Re: [sqlite] Why this query plan?

On 12 Jan 2017, at 2:13pm, Donald Griggs wrote: > Further, Dr. Hipp and his team won't have to deal with howls of "it's > broken" when such a version is released. Just taking the Devil’s Advocate position here, a lot of programmers would argue that inconsistent operation

Re: [sqlite] Why this query plan?

Re: "I read this as a provocative joke." I didn't read it as just a joke. The analogy with random fonts, etc. breaks down, I think, because randomizing the ordering would be an attempt to *improve* sqlite's usability -- not some pedantic punishment. If a user has problems with her sqlite output

Re: [sqlite] Why this query plan?

2017 09:11 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Why this query plan? Richard, At 02:00 12/01/2017, you wrote: >The "PRAGMA reverse_unordered_selects=ON" statement has long been >available to do this. But it is an optional f

Re: [sqlite] Why this query plan?

Richard, At 02:00 12/01/2017, you wrote: The "PRAGMA reverse_unordered_selects=ON" statement has long been available to do this. But it is an optional feature that has to be turned on. And I don't think anybody ever turns it on. My proposal is to make it random. Maybe it would be

Re: [sqlite] Why this query plan?

Since you did not specify an ORDER BY clause, SQLite is free to return rows in *any* order. The order may even change if the underlying schema changes and SQLite finds a better way to compute the results. A "covering index" is one that contains all the fields required from a certain table to

Re: [sqlite] Why this query plan?

I am fully aware of this and I have no problem at all with this behaviour. I just wondered why it choose the particular plan in this situation. There is no criticism, just curiosity. RBS On Thu, Jan 12, 2017 at 12:45 AM, Richard Hipp wrote: > On 1/11/17, Bart Smissaert

Re: [sqlite] Why this query plan?

On 1/11/17, Simon Slavin wrote: > > On 12 Jan 2017, at 12:49am, Richard Hipp wrote: > >> For years I have threatened to make it a feature of SQLite that it >> really does output the rows in some random order if you omit the ORDER >> BY clause > >

Re: [sqlite] Why this query plan?

On 12 Jan 2017, at 12:45am, Richard Hipp wrote: > On 1/11/17, Bart Smissaert wrote: >> >> The result is that the output is descending on DOB. >> I expected and preferred if the output was ascending on rowid. > > If you omit the ORDER BY clause, then

Re: [sqlite] Why this query plan?

On 1/11/17, Richard Hipp wrote: > > If you omit the ORDER BY clause, then the SQL database engine (*any* > engine, not just SQLite) is free to return the rows in whatever random > order it chooses. And it does not need to explain itself when it > does. :-) > For years I have

Re: [sqlite] Why this query plan?

On 1/11/17, Bart Smissaert wrote: > > The result is that the output is descending on DOB. > I expected and preferred if the output was ascending on rowid. If you omit the ORDER BY clause, then the SQL database engine (*any* engine, not just SQLite) is free to return the