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 tha
> 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 pro
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 j
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 sam
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
e-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_int
on 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" wrote:
> On 1/12/17, David Raymond wrote:
> >
> > In the same vane I assume
-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 wrote:
>
> In the same vane I assume DRH's random ordering would be only random by page
> of resul
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
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 wi
oun...@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
> 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 (
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
> 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, R
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
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 n
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
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 d
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
2017 09:11
An: SQLite mailing list
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&
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
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
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 resul
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 "PRAG
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, no
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
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
or
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 outp
28 matches
Mail list logo
