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 make
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 ordering is implemented v
: 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 implemen
That should have been "trigger occur before...", pardon.
-Original Message-
From: Richard Hipp
To: General Discussion of SQLite Database
Sent: Mon, Dec 8, 2014 8:14 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
On Mon, Dec 8,
> 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:
>>
>> -- The `books` and `pages` tables impl
age-
> From: Richard Hipp
> To: General Discussion of SQLite Database
> 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é wrote:
>
>> Hi,
>&g
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 page numbers instead
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é wrote:
>
>> Hi,
>>
>> Unique indexes make some valid update queries fail.
>>
>> Please find bel
> 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
>> updates, and circumvent the issue.
>
of SQLite Database
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 last UPDATE). There is no trigger involved,
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` colum
> 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 implement a book with
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
compatibil
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,
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 normal WAD - Working As Designed
riginal Message-
From: RSmith
To: General Discussion of SQLite Database
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 mailing list, and I won't try to push
ehalf Of John McKown
Sent: 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
wrote:
> I am like you, Gwendal, in that I don't like that be
Cancel that, apparently that only updates the last record...
-Original Message-
From: John McKown
To: General Discussion of SQLite Database
Sent: Mon, Dec 8, 2014 9:18 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
On Mon, Dec 8, 2014 at
> 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.
>>
>
> On another of my forums, this is called a BAD - Broke
AM
To: rsm...@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)
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 `pages` tables impleme
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 wonderful. Simply wonderful. Code
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 requi
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.
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 due
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 includes a syntax for de
> 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.
>
> SQL-99 includes a s
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 entries -
> decrement the
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 same way
> as FKs. When ad
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
> (0,2) and there is o
Lowden
To: sqlite-users
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 wrote:
> On 12/08/2014 09:55 PM, Nico Williams wrote:
> > Ideally there would be something lik
al Roué
To: General Discussion of SQLite Database
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 a écrit :
>
>> Why not an opt-in way to ask for deferred constraint che
32 matches
Mail list logo
