Re: [sqlite] Why this query plan?

[Luca Ferrari]

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 wants all the rows, and while it is true that the
>> order is something the engine can choose, why bother traversing an
>> index instead of a direct scan of the table?
>
> Because the index is smaller than the main table.  Less disk I/O.

Shame on me, it was selecting the columns of the index...
Thanks for the explaination.

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

Re: [sqlite] Why this query plan?

[Bart Smissaert]

> 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
> 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 same day?"
>
> The order of an unordered result is unspecified. There is a good reason for
> that: it's too complex to describe, or it's just not possible, for example
> when probabilistic optimisation is used. It's not an arbitrary restriction.
>
> Anyone asking why the order is what it is is not a valid question,
> regardless of their curiosity. Maybe if they really, really want to know
> they should read the code.
>
> On Thu, Jan 12, 2017 at 9:11 AM, Jean-Christophe Deschamps <
> j...@antichoc.net
> > wrote:
>
> > 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 sufficient to initialize the
> >> reverse_unordered_selects setting to a random value (on or off) inside
> >> of sqlite3_open().
> >>
> >
> > I read this as a provocative joke.
> >
> > While I agree with you that way too many users and applications blindly
> > (naively?) rely on the current behavior, willfully making the order more
> or
> > less random by default would be similar, say for a spreadsheet app, to
> > choose random font, size, centering, coloring and formatting of any cell
> > where those attributes have not been explicitely set.
> >
> > Ask yourself, but I for one wouldn't make much use of such a spreadsheet
> > app, even if some standard says it's legitimate behavior.
> >
> > If you ask somebody to enumerate strictly positive integers less than 6,
> > 99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 4,
> 1,
> > 3 is a perfectly valid answer, anyone would ask "Why this funny order?".
> >
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Darko Volaric]

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."
>>
>> 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 this, as well as Hick previous reply. I'm well aware of the issue,
> which is in no way specific to SQLite.
>
> Yet, providing some new SQLite build (source, amalgamation binaries)
> someday where the result order would be willingly random or different from
> the current behavior (call it natural or naively expectable or intuitive or
> whatelse) will break uncountable uses where the app isn't open to change.
> Remember that in many situations SQLite is being used as a loadable
> component either because the original code was designed so or because the
> language used can't statically link.
>
> So it could be an improvement for *-future-* SQLite apps, or rather a good
> reminder aimed towards developpers, but that would potentially break
> gazillions legacy uses or at the very least cause a huge lot of unnecessary
> inconveniences. Expect a tsunami of disapprovals.
>
> If a user has problems with her sqlite output early in the process, leading
>> to the discovery of a missing "ORDER BY" clause, the argument is that she
>> has been dealt a favor.  It's vastly worse for her to encounter a
>> mysterious bug when the sqlite version is updated years from now to one
>> which (perfectly correctly) returns a different ordering for that same
>> query.
>>
>> Further, Dr. Hipp and his team won't have to deal with howls of "it's
>> broken" when such a version is released.
>>
>
> I also have to repeatedly point out in the community where I offer support
> that SQL deals with unordered sets and to the consequence, that issuing the
> very same SELECT twice in a row could rightfully return results in
> different orders when no ORDER BY clause is specified.  But I bet such an
> uncalled change (as salutary as it may be from a rational point of view)
> would result in a long term continuous higher saturation of this list and
> other support channels with posts from questionning/angry/disappointed
> users.
>
>
>
> ___
> 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] Why this query plan?

[Darko Volaric]

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 same day?"

The order of an unordered result is unspecified. There is a good reason for
that: it's too complex to describe, or it's just not possible, for example
when probabilistic optimisation is used. It's not an arbitrary restriction.

Anyone asking why the order is what it is is not a valid question,
regardless of their curiosity. Maybe if they really, really want to know
they should read the code.

On Thu, Jan 12, 2017 at 9:11 AM, Jean-Christophe Deschamps  wrote:

> 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 sufficient to initialize the
>> reverse_unordered_selects setting to a random value (on or off) inside
>> of sqlite3_open().
>>
>
> I read this as a provocative joke.
>
> While I agree with you that way too many users and applications blindly
> (naively?) rely on the current behavior, willfully making the order more or
> less random by default would be similar, say for a spreadsheet app, to
> choose random font, size, centering, coloring and formatting of any cell
> where those attributes have not been explicitely set.
>
> Ask yourself, but I for one wouldn't make much use of such a spreadsheet
> app, even if some standard says it's legitimate behavior.
>
> If you ask somebody to enumerate strictly positive integers less than 6,
> 99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 4, 1,
> 3 is a perfectly valid answer, anyone would ask "Why this funny order?".
>
>
> ___
> 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] Why this query plan?

[Richard Hipp]

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
instead of ASC.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Bart Smissaert]

Well, it is only pragma, so you can take it or leave it.

RBS

On 12 Jan 2017 20:56, "David Raymond" <david.raym...@tomtom.com> wrote:

> Well, then you're handcuffing it when an index would be better but is in
> nowhere near rowid order.
>
>
> -Original Message-
> 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_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:
> > >
> > > 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
> > > tracking requirements and with the slowdown of skipping all over the
> > file.
> > > Shuffling the order is one thing, killing performance is another.
> > >
> >
> > The idea is that as each new database connection is opened, the
> > reversed_unordered_selects pragma
> > (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects)
> > would be enabled or disabled at random.  That means that results might
> > be backwards from one run to the next, but within the same run they
> > would always be the same.  That is not really "random" but I think it
> > should be sufficient to find instances of omitted ORDER BY clauses, at
> > least for the case where the developers test their application more
> > than once or twice.
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > 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
> ___
> 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] Why this query plan?

[David Raymond]

Well, then you're handcuffing it when an index would be better but is in 
nowhere near rowid order.


-Original Message-
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_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:
> >
> > 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
> > tracking requirements and with the slowdown of skipping all over the
> file.
> > Shuffling the order is one thing, killing performance is another.
> >
>
> The idea is that as each new database connection is opened, the
> reversed_unordered_selects pragma
> (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects)
> would be enabled or disabled at random.  That means that results might
> be backwards from one run to the next, but within the same run they
> would always be the same.  That is not really "random" but I think it
> should be sufficient to find instances of omitted ORDER BY clauses, at
> least for the case where the developers test their application more
> than once or twice.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[David Raymond]

Ok, random setting of the pragma, not completely random order of records. Makes 
infinitely more sense.

(I probably should've picked up on that. Bad me, no biscuit. (This is your 
brain on not enough sleep kids))

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@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 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
> tracking requirements and with the slowdown of skipping all over the file.
> Shuffling the order is one thing, killing performance is another.
>

The idea is that as each new database connection is opened, the
reversed_unordered_selects pragma
(https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects)
would be enabled or disabled at random.  That means that results might
be backwards from one run to the next, but within the same run they
would always be the same.  That is not really "random" but I think it
should be sufficient to find instances of omitted ORDER BY clauses, at
least for the case where the developers test their application more
than once or twice.
-- 
D. Richard Hipp
d...@sqlite.org
___
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] Why this query plan?

[Bart Smissaert]

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
> 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
> > tracking requirements and with the slowdown of skipping all over the
> file.
> > Shuffling the order is one thing, killing performance is another.
> >
>
> The idea is that as each new database connection is opened, the
> reversed_unordered_selects pragma
> (https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects)
> would be enabled or disabled at random.  That means that results might
> be backwards from one run to the next, but within the same run they
> would always be the same.  That is not really "random" but I think it
> should be sufficient to find instances of omitted ORDER BY clauses, at
> least for the case where the developers test their application more
> than once or twice.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Why this query plan?

[Richard Hipp]

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
> tracking requirements and with the slowdown of skipping all over the file.
> Shuffling the order is one thing, killing performance is another.
>

The idea is that as each new database connection is opened, the
reversed_unordered_selects pragma
(https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects)
would be enabled or disabled at random.  That means that results might
be backwards from one run to the next, but within the same run they
would always be the same.  That is not really "random" but I think it
should be sufficient to find instances of omitted ORDER BY clauses, at
least for the case where the developers test their application more
than once or twice.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[David Raymond]

Back to the original question: In this case, since the main table is a normal 
rowid table then the interior pages of the B tree that stores it are only going 
to contain the rowid part of the table's records, and you have to go all the 
way down to the leaves to get the rest of each record. In the (covering) index 
B tree every page has all the needed data, so you don't "waste" time accessing 
those extra interior pages.

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 
tracking requirements and with the slowdown of skipping all over the file. 
Shuffling the order is one thing, killing performance is another.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.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
thread.
So you could benefit from an index for reasons other than the usual reasons
eg assisting
the where clause.

RBS

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

Re: [sqlite] Why this query plan?

[Graham Holden]

> 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 (but note it isn't a universal cure-all, since -- 
as I  understand  it -- it uses more space and makes inserts/update slightly 
slower)

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

Re: [sqlite] Why this query plan?

[Roger Binns]

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 for years that has been one of the things mentioned in the (closed)
lint mode ticket :-)


https://www.sqlite.org/src/tktview/25e09aa2ab20d67a241b0164ac6818a27ea248d0

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Bart Smissaert]

> 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, 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 wants all the rows, and while it is true that the
> > order is something the engine can choose, why bother traversing an
> > index instead of a direct scan of the table?
>
> Because the index is smaller than the main table.  Less disk I/O.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Why this query plan?

[Richard Hipp]

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 engine can choose, why bother traversing an
> index instead of a direct scan of the table?

Because the index is smaller than the main table.  Less disk I/O.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Luca Ferrari]

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.  :-)
>

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 engine can choose, why bother traversing an
index instead of a direct scan of the table?

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

Re: [sqlite] Why this query plan?

[Jean-Christophe Deschamps]

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 this, as well as Hick previous reply. I'm well aware of the 
issue, which is in no way specific to SQLite.


Yet, providing some new SQLite build (source, amalgamation binaries) 
someday where the result order would be willingly random or different 
from the current behavior (call it natural or naively expectable or 
intuitive or whatelse) will break uncountable uses where the app isn't 
open to change. Remember that in many situations SQLite is being used 
as a loadable component either because the original code was designed 
so or because the language used can't statically link.


So it could be an improvement for *-future-* SQLite apps, or rather a 
good reminder aimed towards developpers, but that would potentially 
break gazillions legacy uses or at the very least cause a huge lot of 
unnecessary inconveniences. Expect a tsunami of disapprovals.


If a user has problems with her sqlite output early in the process, 
leading

to the discovery of a missing "ORDER BY" clause, the argument is that she
has been dealt a favor.  It's vastly worse for her to encounter a
mysterious bug when the sqlite version is updated years from now to one
which (perfectly correctly) returns a different ordering for that same
query.

Further, Dr. Hipp and his team won't have to deal with howls of "it's
broken" when such a version is released.


I also have to repeatedly point out in the community where I offer 
support that SQL deals with unordered sets and to the consequence, that 
issuing the very same SELECT twice in a row could rightfully return 
results in different orders when no ORDER BY clause is specified.  But 
I bet such an uncalled change (as salutary as it may be from a rational 
point of view) would result in a long term continuous higher saturation 
of this list and other support channels with posts from 
questionning/angry/disappointed users.



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

Re: [sqlite] Why this query plan?

[Simon Slavin]

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 is broken.  They can deal with any consistent 
behaviour for something they didn’t specify, but not with a program which does 
one thing one day and another another day.

I remember having a related problem with a mainframe database engine years ago. 
 This multi-user system had a client/server design and all data operations were 
performed by a program running centrally.  The server created various temporary 
indexes as it needed them and it kept them in memory until it needed the memory 
for something else.  If it wanted to read every row of a table, and an index 
for that sort was already in the cache, it would use it.  But if no index was 
already cached it had to make a new one up, which could take anything up to a 
couple of minutes.

So your program could perform differently depending on which sorts or searches 
/another/ user had done recently.  And you had no way of knowing what that was. 
 This lead to complaints since creating an index could take up a lot of 
processing time and input/output, and those were charged to the department 
which ran the program.  A programmer had no way of knowing how much running his 
program would cost the department and department managers hated this.

Nevertheless we eventually settled on the same answer you’ve see in this 
thread: If you want something specific, specify it !  Maybe you’ll get lucky 
and get a 'free' index.  Maybe you won’t.

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

Re: [sqlite] Why this query plan?

[Donald Griggs]

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 early in the process, leading
to the discovery of a missing "ORDER BY" clause, the argument is that she
has been dealt a favor.  It's vastly worse for her to encounter a
mysterious bug when the sqlite version is updated years from now to one
which (perfectly correctly) returns a different ordering for that same
query.

Further, Dr. Hipp and his team won't have to deal with howls of "it's
broken" when such a version is released.

Just my opinion,
   Donald

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

Re: [sqlite] Why this query plan?

[Hick Gunter]

I must disagree. SQL is based on sets. Sets do not have any order, even if the 
elements of the set (e.g. cardinal numbers) suggest a "natural" order (which 
may not be the same for all jurisdictions). An ordered set is called a 
permutation. Operations on sets (should) yield identical results, regardless of 
the permutation used. In real implementations, computing a result set may be 
made very much easier (faster) if an index (i.e. a permutation of elements=rows 
based on a specific permutation of fields within the row) can be used. As a 
side effect, the results will tend to be produced in the order suggested by the 
index used.

Selecting a specific permutation of the result set is specified with the ORDER 
BY clause and this also tells the database engine that the additional work that 
may be required is authorized by the writer of the query.

The telephone directory is an example of a (seemingly) natural order (surname, 
firstname) only for societies with the tribalistic concept of a basically 
immutable "family name". This would be totally useless in iceland, where the 
"surname" is composed of the first name of the father and the extension -son or 
-dottir (for male and female descendants respectively) and the natural order 
becomes (firstname, surname).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jean-Christophe Deschamps
Gesendet: Donnerstag, 12. Jänner 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 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 sufficient to initialize the
>reverse_unordered_selects setting to a random value (on or off) inside
>of sqlite3_open().

I read this as a provocative joke.

While I agree with you that way too many users and applications blindly
(naively?) rely on the current behavior, willfully making the order more or 
less random by default would be similar, say for a spreadsheet app, to choose 
random font, size, centering, coloring and formatting of any cell where those 
attributes have not been explicitely set.

Ask yourself, but I for one wouldn't make much use of such a spreadsheet app, 
even if some standard says it's legitimate behavior.

If you ask somebody to enumerate strictly positive integers less than 6, 
99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 4, 1, 3 is 
a perfectly valid answer, anyone would ask "Why this funny order?".

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Why this query plan?

[Jean-Christophe Deschamps]

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 sufficient to initialize the
reverse_unordered_selects setting to a random value (on or off) inside
of sqlite3_open().


I read this as a provocative joke.

While I agree with you that way too many users and applications blindly 
(naively?) rely on the current behavior, willfully making the order 
more or less random by default would be similar, say for a spreadsheet 
app, to choose random font, size, centering, coloring and formatting of 
any cell where those attributes have not been explicitely set.


Ask yourself, but I for one wouldn't make much use of such a 
spreadsheet app, even if some standard says it's legitimate behavior.


If you ask somebody to enumerate strictly positive integers less than 
6, 99.999% of people expect the answer to be 1, 2, 3, 4, 5. While 2, 5, 
4, 1, 3 is a perfectly valid answer, anyone would ask "Why this funny 
order?".


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

Re: [sqlite] Why this query plan?

[Hick Gunter]

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 fulfill the request. Retrieving fields from the covering index avoids 
having to access the complete row from the table and is thus very much faster 
(no second disk access, no unpacking of the retrieved row).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Bart Smissaert
Gesendet: Donnerstag, 12. Jänner 2017 01:40
An: General Discussion of SQLite Database 
Betreff: [sqlite] Why this query plan?

Say I have a table created like this:

create table table1(
[id] integer primary key,
[dob] integer)

with an index (not unique) on dob

and I run this SQL:

select id, dob from table1

then the query plan I get is:

SCAN TABLE TABLE1 USING COVERING INDEX IDX_TABLE1_DOB

The result is that the output is descending on DOB.
I expected and preferred if the output was ascending on rowid.
What is the idea/logic of this query plan?

Using SQLite 3.16.2


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Why this query plan?

[Bart Smissaert]

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  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 rows in whatever random
> order it chooses.  And it does not need to explain itself when it
> does.  :-)
>
> If you expect and/or prefer the output to be in order of ascending
> rowid, then add
>
>  ORDER BY rowid ASC
>
> to the end of the query.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Why this query plan?

[Richard Hipp]

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
>
> SQLITE_CONFIG_RANDOMIZEWHATYOUCAN_LOL
>

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 sufficient to initialize the
reverse_unordered_selects setting to a random value (on or off) inside
of sqlite3_open().

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Simon Slavin]

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 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.  :-)

Not only that, but SQLite may return them in one order one day, and another 
order a different day, without you changing a single line of your program or 
recompiling it.  You may use the command-line tool to add another index to the 
database and it may decide to use that one.  Or you may run ANALYZE.

So just because you’ve seen it decide to use IDX_TABLE1_DOB while you’re 
developing your program, don’t depend on it doing the same thing on your 
customer’s site.  As Richard wrote, if you want something specific, ask for it 
specifically.

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

SQLITE_CONFIG_RANDOMIZEWHATYOUCAN_LOL

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

Re: [sqlite] Why this query plan?

[Richard Hipp]

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 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.  I have so far resisted that
impulse because it makes testing harder.  But this email thread makes
me think that such a feature really would be worth the trouble of
adding.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why this query plan?

[Richard Hipp]

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 rows in whatever random
order it chooses.  And it does not need to explain itself when it
does.  :-)

If you expect and/or prefer the output to be in order of ascending
rowid, then add

 ORDER BY rowid ASC

to the end of the query.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users