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?

[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

[sqlite] Null returned from NOT NULL column thanks to undetected corruption

[Rowan Worth]

Hi guys,

Ran into an interesting situation recently where a database was transferred
via FTP and the client somehow managed to truncate the file. As a result
the last page was only 337 bytes rather than the expected 1024.
Surprisingly running a SELECT on the affected table works without sqlite
returning any error!

However several of the returned rows are completely blank, despite 7 out of
8 columns having a NOT NULL constraint. Anyway we came up with a simple
reproducer:


 sqlite3 test.db "CREATE TABLE test_table (A INT4 NOT NULL, B INT4 NOT
NULL);"
 for N in `seq 1 10`
 do
 sqlite3 test.db "INSERT INTO test_table (A, B) VALUES ($N, $N);"
 done

 dd if=test.db of=cropped.db bs=2020 count=1
 sqlite3 cropped.db "SELECT * FROM test_table;"


Which produces results:

|
|
|
|
0|0
6|6
7|7
8|8
9|9
10|10

test.db is two pages long, so the truncation here is only 28 bytes. I
realise that some types of corruption are too small to notice without some
kind of checksum/error checking code, which feels a bit heavyweight for the
general case. But here it seems like there are some pretty significant red
flags:

1. the database contains an incomplete page
2. a NOT NULL column contains a NULL cell

"PRAGMA integrity_check" does flag problems with cropped.db, but because of
these two conditions I wonder if this is something sqlite could catch in
normal operation and return SQLITE_CORRUPT? Or are there reasons/history
which would render this conclusion inaccurate?

I notice that if I modify the database and another page gets added, sqlite
*does* start returning SQLITE_CORRUPT so I wonder if there's something
special about the last page?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT is corrupting a database

[Kevin O'Gorman]

On Tue, Jan 10, 2017 at 11:29 PM, Clemens Ladisch 
wrote:

> Kevin O'Gorman wrote:
> > If I go on to the second table, it appears to finish normally, but when I
> > try to look at the database with sqlite3, a command-line tool for
> > interacting with SQLite, it says the database is corrupt.
>
> What version?
>

It's whatever is in Python 3.5.2.'s builtin sqlite package.


> > If however, I split the program into two programs, one for each table,
> and
> > run them one after another, all is well.  Same code, each with parts of
> it
> > if-else-ed out.
>
> It's possible that there is a bug in your code.  Which you have not shown.
>
>
My opinion is that no user bug whatever should cause DB integrity problems
without
raising an exception.




-- 
word of the year: *kakistocracy*
___
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?

[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?

[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  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]

> 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] INSERT is corrupting a database

[Kevin O'Gorman]

On Tue, Jan 10, 2017 at 7:52 PM, Simon Slavin  wrote:

>
> On 11 Jan 2017, at 3:28am, Kevin O'Gorman  wrote:
>
> > I have a modest amount of data that I'm loading into an SQLite database
> for
> > the first time.  For the moment it contains just two tables and a few
> > indices, nothing else.  The first table loads okay, and if I stop the
> > process at that point, all is well and I can look at the database.
> >
> > If I go on to the second table, it appears to finish normally, but when I
> > try to look at the database with sqlite3, a command-line tool for
> > interacting with SQLite, it says the database is corrupt.
>
> Make absolutely sure you’re starting with a new database file each time,
> not continuing to write to an already-corrupt file.
>
> I'm sure.  The program tests for the existence of the main table before
starting, and throws an exception if it's there, then creates that table as
its first action.


> At stages during your Python program, including after you’ve finished
> loading the first table, use the following command to check to see whether
> the database is correct:
>
> It's no longer possible.  In fixing other things, the program has changed,
and it no longer corrupts the database.  Thanks for this next thing,
though



> PRAGMA integrity_check
>

Thanks for that.  I was not aware of this tool.  I'll keep it handy.


> Use the same command in the command-line tool.
>
> Simon.
>

-- 
word of the year: *kakistocracy*
___
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"  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"  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
> ___
> 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] INSERT is corrupting a database

[Jens Alfke]

> On Jan 12, 2017, at 3:52 PM, Kevin O'Gorman  wrote:
> 
> My opinion is that no user bug whatever should cause DB integrity problems
> without raising an exception.

That is a totally reasonable attitude … for programs running in a “safe” 
environment like an interpreter.
However, in the world of low-level native code, there’s nothing SQLite can do 
about a C program overwriting parts of its data structures or file buffers.

Granted, Clemens’ program is written in Python, a “safe” environment, so it 
shouldn’t be able to corrupt a database. There might be a bug in Python’s 
SQLite glue code, though.

—Jens
___
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"  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
___
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

[sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

[Jan Nijtmans]

2017-01-12 3:04 GMT+01:00 Warren Young:
> In fact, one improvement made to SQLite a few years ago was to switch it from 
> using native Windows file locking when built under Cygwin to use POSIX or BSD 
> locking mechanisms, so that two programs built under Cygwin that both used 
> SQLite would get the advisory locking semantics they expect, not the 
> mandatory locking semantics Windows gives you by default.  (It’s more 
> complicated than that, but I don’t want to go deeper into it here.)

Yeah ...  I'm slowly trying to submit all portability issues I discover
back to the SQLite developers, so far with little success.

For example, the following patch fixes possible crashes in
error-handling in 64-bit builds (not Cygwin-specific). Explanation:
On 64-bit builds '0' is a 32-bit integer, but Tcl_AppendResult()
expects a NULL-pointer as last element which is 64-bit.

Any chance for this to be in the next SQLite release?

Thanks!
Jan Nijtmans
===
$ fossil diff
Index: src/tclsqlite.c
==
--- src/tclsqlite.c
+++ src/tclsqlite.c
@@ -2534,11 +2534,11 @@
 for(i=3; i<(objc-1); i++){
   const char *z = Tcl_GetString(objv[i]);
   int n = strlen30(z);
   if( n>2 && strncmp(z, "-argcount",n)==0 ){
 if( i==(objc-2) ){
-  Tcl_AppendResult(interp, "option requires an argument: ", z, 0);
+  Tcl_AppendResult(interp, "option requires an argument: ",
z, (char *)0);
   return TCL_ERROR;
 }
 if( Tcl_GetIntFromObj(interp, objv[i+1], ) ) return TCL_ERROR;
 if( nArg<0 ){
   Tcl_AppendResult(interp, "number of arguments must be non-negative",
@@ -2549,11 +2549,11 @@
   }else
   if( n>2 && strncmp(z, "-deterministic",n)==0 ){
 flags |= SQLITE_DETERMINISTIC;
   }else{
 Tcl_AppendResult(interp, "bad option \"", z,
-"\": must be -argcount or -deterministic", 0
+"\": must be -argcount or -deterministic", (char *)0
 );
 return TCL_ERROR;
   }
 }

@@ -3206,11 +3206,11 @@
 if( rc==SQLITE_OK ){
   Tcl_Obj *pObj;
   pObj = Tcl_NewStringObj((char*)sqlite3_value_text(pValue), -1);
   Tcl_SetObjResult(interp, pObj);
 }else{
-  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
+  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), (char *)0);
   return TCL_ERROR;
 }
   }
 }
 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

[Hick Gunter]

There are a couple more cases of Tcl_AppendResults( ..., 0) in tclsqlite.c

Unfortunately, Tcl_AppendResults() is defined as having varargs and thus 
lacking type checking.

I would prefer NULL over (char*)0 anyway, which BTW is also present at least 
once in tclsqlite.c

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jan Nijtmans
Gesendet: Donnerstag, 12. Jänner 2017 10:43
An: SQLite mailing list 
Betreff: [sqlite] tclsqlite 64-bit bug [Was: extension to run bash]

2017-01-12 3:04 GMT+01:00 Warren Young:
> In fact, one improvement made to SQLite a few years ago was to switch
> it from using native Windows file locking when built under Cygwin to
> use POSIX or BSD locking mechanisms, so that two programs built under
> Cygwin that both used SQLite would get the advisory locking semantics
> they expect, not the mandatory locking semantics Windows gives you by
> default.  (It’s more complicated than that, but I don’t want to go
> deeper into it here.)

Yeah ...  I'm slowly trying to submit all portability issues I discover back to 
the SQLite developers, so far with little success.

For example, the following patch fixes possible crashes in error-handling in 
64-bit builds (not Cygwin-specific). Explanation:
On 64-bit builds '0' is a 32-bit integer, but Tcl_AppendResult() expects a 
NULL-pointer as last element which is 64-bit.

Any chance for this to be in the next SQLite release?

Thanks!
Jan Nijtmans
===
$ fossil diff
Index: src/tclsqlite.c
==
--- src/tclsqlite.c
+++ src/tclsqlite.c
@@ -2534,11 +2534,11 @@
 for(i=3; i<(objc-1); i++){
   const char *z = Tcl_GetString(objv[i]);
   int n = strlen30(z);
   if( n>2 && strncmp(z, "-argcount",n)==0 ){
 if( i==(objc-2) ){
-  Tcl_AppendResult(interp, "option requires an argument: ", z, 0);
+  Tcl_AppendResult(interp, "option requires an argument: ",
z, (char *)0);
   return TCL_ERROR;
 }
 if( Tcl_GetIntFromObj(interp, objv[i+1], ) ) return TCL_ERROR;
 if( nArg<0 ){
   Tcl_AppendResult(interp, "number of arguments must be non-negative", 
@@ -2549,11 +2549,11 @@
   }else
   if( n>2 && strncmp(z, "-deterministic",n)==0 ){
 flags |= SQLITE_DETERMINISTIC;
   }else{
 Tcl_AppendResult(interp, "bad option \"", z,
-"\": must be -argcount or -deterministic", 0
+"\": must be -argcount or -deterministic", (char *)0
 );
 return TCL_ERROR;
   }
 }

@@ -3206,11 +3206,11 @@
 if( rc==SQLITE_OK ){
   Tcl_Obj *pObj;
   pObj = Tcl_NewStringObj((char*)sqlite3_value_text(pValue), -1);
   Tcl_SetObjResult(interp, pObj);
 }else{
-  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), 0);
+  Tcl_AppendResult(interp, sqlite3_errmsg(pDb->db), (char *)0);
   return TCL_ERROR;
 }
   }
 }
 #endif /* SQLITE_ENABLE_PREUPDATE_HOOK */ 
___
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?

[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 
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] Insert into with Id...

[Simon Slavin]

On 12 Jan 2017, at 4:06am, hfiandor  wrote:

> I’m trying to read a .csv file and introduce in an SQLite table, using the
> insert into command.
> 
> 
> 
> In the .csv file I have not used this Id field (it was defined as integer
> and autoincrease).

Specify the fields you need in the INSERT command.  So instead of

INSERT INTO MyTable VALUES ('a','b','c')

you should do

INSERT INTO MyTable (name, address, phone) VALUES ('a','b','c')

If you do this, you can miss out the Id field.  When you do that, SQL makes up 
its own values for the Id field based on the INTEGER AUTOINCREMENT you 
specified.

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

Re: [sqlite] LIKE and the like and SIMilarity

[Anony Mous]

Two decent suggestions in the replies:

1) Set the PRAGMA to case-sensitive, and then use lower() to get
insensitivity.
2) Define the column to use case-sensitive collation

For #1 = Set the PRAGMA. then use lower()
-
​Is the PRAGMA for case-sensitivity sticky?​ IOW, does it end up in the DB?
(on the road, can't check right now.)

If it is, that's good, as long as the DB is R/W, which may not be the case.

If it's not, then you have to kick the pragma out every time you open the
DB, right? Can I assume it sticks though one DB open/close sequence?

​For #2 - Define the column as case-sensitive

This is good for a new DB.

However, the DB may not be your DB. You may not have had the opportunity to
define anything. It may have been done by someone else, the DB established,
created by an application, etc. You may not have the user privileges to
change the R/W status of the original.
​
​The acts of defining a DB and ​using that DB may not be closely coupled in
either time or personnel. In a case where the coupling is loose or highly
disjoint, you would have to re-create the target column(s) (or the entire
DB, if the original is not R/W) with the new definitions. If you don't have
control over the original, that could be a significant pushup.

Whereas if you could simply say the equivalent of LIKE and ILIKE, there
would be no issue at all.
___
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] Bug report: Incorrect error information if db fails to open due to bad flags

[Dan Kennedy]

On 01/13/2017 06:25 AM, Jens Alfke wrote:

I’ve found a case where incorrect error information gets reported to client C 
code trying to open a SQLite database. (This is with SQLite 3.14 on mac OS 
10.12.)

After the following C code runs (the path here is irrelevant):
sqlite3 *db;
int ret = sqlite3_open_v2("/tmp/foo", , SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READONLY, NULL);

the value of `ret` is SQLITE_MISUSE, which makes sense because “create” and 
“readonly” is an illegal combination of flags.
However, the value of `db` is NULL, i.e. no database handle was allocated.
This causes problems when the code continues by trying to get more information 
about the error:

if (ret != SQLITE_OK) {
int extendedCode = sqlite3_extended_errcode(db);
const char *message = sqlite3_errmsg(db);
report_error_to_user(extendedCode, message);
}

The value of extendedCode will be SQLITE_NOMEM, and message will be “out of 
memory”. This naturally causes the problem to be reported as an out-of-memory 
error. This happened to me today, and I thought it seemed unlikely on my 16GB 
laptop; but it took me a while to dig to the source of the problem, which is 
that the wrong flags were being used.

sqlite3_open_v2 normally allocates a db handle even on error, so that the 
caller can use the handle to get more information about the error, as above. 
The docs say that the db handle will not be allocated if there wasn’t enough 
memory for the allocation. So it appears that sqlite3_errmsg and 
sqlite3_extended_errcode accept a NULL parameter, but assume that it’s NULL 
because there wasn’t enough memory to allocate a database … which is not true 
in this case.


Fair point.

Note that technically speaking, when SQLite returns SQLITE_MISUSE the 
behavior is actually undefined. Situations in which the program might 
segfault if the stars were aligned differently. So this is not actually 
a bug - just a situation that could be made easier to debug.


Dan.







The fix would seem to be to allocate a database handle in the situation here 
where an illegal combination of flags is used.

—Jens
___
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?

[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?

[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?

[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?

[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

[sqlite] Bug report: Incorrect error information if db fails to open due to bad flags

[Jens Alfke]

I’ve found a case where incorrect error information gets reported to client C 
code trying to open a SQLite database. (This is with SQLite 3.14 on mac OS 
10.12.)

After the following C code runs (the path here is irrelevant):
sqlite3 *db;
int ret = sqlite3_open_v2("/tmp/foo", , SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READONLY, NULL);

the value of `ret` is SQLITE_MISUSE, which makes sense because “create” and 
“readonly” is an illegal combination of flags.
However, the value of `db` is NULL, i.e. no database handle was allocated.
This causes problems when the code continues by trying to get more information 
about the error:

if (ret != SQLITE_OK) {
int extendedCode = sqlite3_extended_errcode(db);
const char *message = sqlite3_errmsg(db);
report_error_to_user(extendedCode, message);
}

The value of extendedCode will be SQLITE_NOMEM, and message will be “out of 
memory”. This naturally causes the problem to be reported as an out-of-memory 
error. This happened to me today, and I thought it seemed unlikely on my 16GB 
laptop; but it took me a while to dig to the source of the problem, which is 
that the wrong flags were being used.

sqlite3_open_v2 normally allocates a db handle even on error, so that the 
caller can use the handle to get more information about the error, as above. 
The docs say that the db handle will not be allocated if there wasn’t enough 
memory for the allocation. So it appears that sqlite3_errmsg and 
sqlite3_extended_errcode accept a NULL parameter, but assume that it’s NULL 
because there wasn’t enough memory to allocate a database … which is not true 
in this case.

The fix would seem to be to allocate a database handle in the situation here 
where an illegal combination of flags is used.

—Jens
___
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?

[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?

[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?

[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