Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, Dec 8, 2014 12:07 pm Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail > Le 8 déc. 2014 à 17:21, Simon Slavin <slav...@bigfraud.org> a écrit : > >> Why n

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

mania.org> To: sqlite-users <sqlite-users@sqlite.org> Sent: Tue, Dec 9, 2014 10:38 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, 08 Dec 2014 22:01:15 +0700 Dan Kennedy <danielk1...@gmail.com> wrote: > On 12/08/2014 09:55 PM, Nico

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, 08 Dec 2014 15:48:41 +0200 RSmith wrote: > > UPDATE pages SET position = position + 1 WHERE book_id = 0 AND > > position >= 1; > > NOT a bug... the moment you SET position to position +1 for the > first iteration of the query, it tries to make that entry look like >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, 08 Dec 2014 22:01:15 +0700 Dan Kennedy wrote: > On 12/08/2014 09:55 PM, Nico Williams wrote: > > Ideally there would be something like DEFERRED foreign key checking > > for uniqueness constraints... > > You could hack SQLite to do enforce unique constraints the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 9:01 AM, Dan Kennedy wrote: > You could hack SQLite to do enforce unique constraints the same way as FKs. > When adding an entry to a UNIQUE index b-tree, you check for a duplicate. If > one exists, increment a counter. Do the opposite when removing

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 17:21, Simon Slavin a écrit : > >> Why not an opt-in way to ask for deferred constraint checking. The key here >> is only to allow perfectly legit requests to run. With all the due respect >> to sqlite implementors and the wonderful design of sqlite. >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 3:05pm, Gwendal Roué wrote: > Why not an opt-in way to ask for deferred constraint checking. The key here > is only to allow perfectly legit requests to run. With all the due respect to > sqlite implementors and the wonderful design of sqlite. SQL-99

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Yes, that would be nice. For example, sqlite already needs explicit opt-in for some of the relational toolkit. I think about "PRAGMA foreign_keys = ON". Why not an opt-in way to ask for deferred constraint checking. The key here is only to allow perfectly legit requests to run. With all the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 12/08/2014 09:55 PM, Nico Williams wrote: Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You could hack SQLite to do enforce unique constraints the same way as FKs. When adding an entry to a UNIQUE index b-tree, you check for a duplicate.

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Ideally there would be something like DEFERRED foreign key checking for uniqueness constraints... You can get something like that by using non-unique indexes (but there would also go your primary keys) and then check that there are no duplicates before you COMMIT. (Doing this reliably would

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 8:23 AM, Marc L. Allen wrote: > I'm not sure I'd even consider it broken. > ​Well, to some on that forum: "If it doesn't work the way that _I_ want, then it is ipso-facto broken". And I forgot the in my message. Sorry.​ > > SQLite is

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8-12-2014 14:58, Gwendal Roué wrote: Le 8 déc. 2014 à 14:48, RSmith a écrit : On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

...@rsweb.co.za; sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 15:18, John McKown a écrit : > > On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen > wrote: > >> I am like you, Gwendal, in that I don't like that behavior in SQLite; >> however, not liking it doesn't make it a bug. >>

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Cancel that, apparently that only updates the last record... -Original Message- From: John McKown <john.archie.mck...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, Dec 8, 2014 9:18 am Subject: Re: [sqlite] sqlite bugreport :

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen <mlal...@outsitenetworks.com> wrote: > I am like you, Gwendal, in that I d

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

- From: RSmith <rsm...@rsweb.co.za> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: > I'm new to this mail

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen wrote: > I am like you, Gwendal, in that I don't like that behavior in SQLite; > however, not liking it doesn't make it a bug. > ​On another of my forums, this is called a BAD - Broken, As Designed.​ As opposed to the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence,

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:48, RSmith a écrit : > > > On 2014/12/08 11:55, Gwendal Roué wrote: >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> -- The `books` and `pages` tables

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position`

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

eneral Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Mon, Dec 8, 2014 8:40 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail J T, I did provide a sequence of queries that reliably reproduce the issue (see below, from the first CREATE to the

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:39, Simon Slavin a écrit : > > On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > >> We share the same conclusion. I even tried to decorate the update query with >> "ORDER" clauses, in a foolish attempt to reverse the ordering of row

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

ion of SQLite Database <sqlite-users@sqlite.org> > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to > fail > > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote: > >> Hi, >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 8 Dec 2014, at 1:31pm, Gwendal Roué wrote: > We share the same conclusion. I even tried to decorate the update query with > "ORDER" clauses, in a foolish attempt to reverse the ordering of row updates, > and circumvent the issue. A way to solve this is to use REAL for

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

Original Message- > From: Richard Hipp <d...@sqlite.org> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Mon, Dec 8, 2014 8:14 am > Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to > fail > > >

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

> Le 8 déc. 2014 à 14:14, Richard Hipp a écrit : > > On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > >> Hi, >> >> Unique indexes make some valid update queries fail. >> >> Please find below the SQL queries that lead to the unexpected error: >> >> --

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

2014 8:14 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué <g...@pierlis.com> wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On Mon, Dec 8, 2014 at 4:55 AM, Gwendal Roué wrote: > Hi, > > Unique indexes make some valid update queries fail. > > Please find below the SQL queries that lead to the unexpected error: > > -- The `books` and `pages` tables implement a book with several pages. > -- Page

[sqlite] sqlite bugreport : unique index causes valid updates to fail

Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position` column in the pages table. -- A unique index