[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 10:35 PM, Wade, William wrote: > I may be reading different documentation (or perhaps just misreading it). > > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [the] usual

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 9:10pm, David Baird wrote: > My cocnern is that: the database engine shouldn't > needlessly waste/discard perectly good chunks of rowids Please don't think of them as 'chunks'. There is no order to the numbers it picks. An alternative way to think of the AUTOINCREMENT

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Dec 9, 2015 7:14 PM, "Simon Slavin" wrote: > > > On 9 Dec 2015, at 9:10pm, David Baird wrote: > > > My cocnern is that: the database engine shouldn't > > needlessly waste/discard perectly good chunks of rowids > > Please don't think of them as 'chunks'. There is no order to the numbers it

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Simon Slavin
On 9 Dec 2015, at 8:35pm, Wade, William wrote: > https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, > which applies to the original post) "If no ROWID is specified on the insert > ... [the] usual algorithm is to give the newly created row a ROWID that is > one larger

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Wade, William
intended behavior. Best Regards, Bill -Original Message- From: Richard Hipp [mailto:d...@sqlite.org] Sent: Wednesday, December 09, 2015 7:07 AM To: SQLite mailing list Subject: Re: [sqlite] INSERT OR IGNORE consuming too many rowids As others have a pointed out, though, the "desired" beha

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Dec 9, 2015 8:06 AM, "Richard Hipp" wrote: > > On 12/9/15, David Baird wrote: > > > > Looks like it's fixed, as long as I stick to new versions. *cross fingers* > > Bisecting shows that the change in behavior occurred here: > https://www.sqlite.org/src/timeline?c=56bc5ce8 > > As others have a

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 2:21 PM, David Baird wrote: > Looks like it's fixed, as long as I stick to new versions. *cross > fingers* Thanks! Just to add to my previous reply: It wasn't "fixed", since it was never before "wrong", it was merely changed in a way that happens to currently suit your needs,

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread R Smith
On 2015/12/09 2:21 PM, David Baird wrote: > On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch wrote: > > Just tried two different versions: > > SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has > the undesired behavior > > SQLite version 3.9.2 2015-11-02 18:31:45 (that I

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Clemens Ladisch
David Baird wrote: > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior Works for me. Are you sure you actually executed this actual code? guarantees that a newly inserted row

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread Richard Hipp
On 12/9/15, David Baird wrote: > > Looks like it's fixed, as long as I stick to new versions. *cross fingers* Bisecting shows that the change in behavior occurred here: https://www.sqlite.org/src/timeline?c=56bc5ce8 As others have a pointed out, though, the "desired" behavior is "undocumented".

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch wrote: > David Baird wrote: > > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior > > Works for me. Are you sure you actually executed this actual code? > >

[sqlite] INSERT OR IGNORE consuming too many rowids

2015-12-09 Thread David Baird
Hi, I have a use case in which a table is being used to associate integers to unique strings. When loading the strings, it's possible there may be some redundancies, which INSERT OR IGNORE successfully ignores. However, redundant values that should have been ignored _sometimes_ have a side effect