Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

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

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

Re: [sqlite] INSERT is corrupting a database

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] INSERT is corrupting a database

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] INSERT is corrupting a database

> 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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

[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

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

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

Re: [sqlite] Why this query plan?

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,

Re: [sqlite] Insert into with Id...

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

Re: [sqlite] LIKE and the like and SIMilarity

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

Re: [sqlite] Why this query plan?

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

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

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;

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

On 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

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

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", ,

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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

Re: [sqlite] Why this query plan?

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