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

[J T]

I just thought of what may be a simpler solution.

I'm assuming that there is a certain limit to the length of the books 
(positions can be safely assumed to never exceed say, 100,000)

So what can be done is

update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;

This will work around the unique contraint and seems simpler than dropping it 
everytime you want to insert a page.

 
 

 

 

-Original Message-
From: Gwendal Roué <g...@pierlis.com>
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 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 deferred checking.  We don't need to invent our 
own syntax with a PRAGMA. However, it is done when the constraint is defined 
rather than being something one can turn on or off.  So you would need to think 
out whether you wanted row- or transaction-based checking when you define each 
constraint in the first place.

Hi Simon,

This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled 
upon this page which shows how deferred index maintenance affects Oracle query 
plan, and performance : 
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.

I now understand that the strategy for checking index constraints is tied to 
their maintenance.

The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 
1` query we are talking about has indeed to perform both. Such an 
innocuous-looking request, and it sends us right into the very guts of 
relational constraints :-)

Gwendal

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


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

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

[J T]

I just thought of what may be a simpler solution.

I'm assuming that there is a certain limit to the length of the books 
(positions can be safely assumed to never exceed say, 100,000)

So what can be done is

update page set position=position + 10 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 9 where position>10;

This will work around the unique contraint and seems simpler than dropping it 
everytime you want ot insert a page.

 

 

 

-Original Message-
From: James K. Lowden <jklow...@schemamania.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 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. If one exists, increment a counter. Do the opposite when 
> removing entries - decrement the counter if there are two or more 
> duplicates of the entry you are removing. If your counter is greater 
> than zero at commit time, a UNIQUE constraint has failed.

It's not *deferred* constraint checking.  It's constraint checking.
Best to honor the transaction first.  

Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction: 

1.  Create a temporary table to hold the after-image of the updated
rows. 
2.  begin transaction
3 . Delete the rows from the target table. 
3.  Insert the updated rows from the temporary table.  
4.  commit
5.  drop temporary table. 

Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced.  I guess they all involve deleting the
prior set from the index and inserting the new one.  

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

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

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

[James K. Lowden]

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 of course at this point in time already an entry
> like (0,2).

Yes, that's how SQLite works, or doesn't.  Whether or not it's a bug
depends on how you define the term.  

The issue has come up here before: contrary to the SQL standard, SQLite
does not support constraint enforcement with transaction semantics.
I've never heard of another SQL DBMS that behaves that way.  

sqlite> create table T (t int primary key);
sqlite> insert into T values (1), (2);
sqlite> update T set t = t+1;
Error: column t is not unique

As the OP discovered, the one recourse is to relieve the constraint
during the update.  Another is to update a temporary table, and then
delete & insert the rows in a transaction.  I would say "must implement
one's own transaction semantics" is, if not a bug, at least a
misfeature.  

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

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

[James K. Lowden]

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 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 counter if there are two or more 
> duplicates of the entry you are removing. If your counter is greater 
> than zero at commit time, a UNIQUE constraint has failed.

It's not *deferred* constraint checking.  It's constraint checking.
Best to honor the transaction first.  

Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction: 

1.  Create a temporary table to hold the after-image of the updated
rows. 
2.  begin transaction
3 . Delete the rows from the target table. 
3.  Insert the updated rows from the temporary table.  
4.  commit
5.  drop temporary table. 

Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced.  I guess they all involve deleting the
prior set from the index and inserting the new one.  

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

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

[Nico Williams]

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 counter if there are two or more duplicates of the entry you
> are removing. If your counter is greater than zero at commit time, a UNIQUE
> constraint has failed.
>
> I suspect there would be a non-trivial increase in the CPU use of UPDATE
> statements though.

Well, it'd be an option which, when not used, ought to cost very few
additional unlikely branches.

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

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

[Gwendal Roué]

> 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 syntax for deferred checking.  We don't need to invent our 
> own syntax with a PRAGMA. However, it is done when the constraint is defined 
> rather than being something one can turn on or off.  So you would need to 
> think out whether you wanted row- or transaction-based checking when you 
> define each constraint in the first place.

Hi Simon,

This topic is fascinating. Googling for SQL-99 deferred checking, I stumbled 
upon this page which shows how deferred index maintenance affects Oracle query 
plan, and performance : 
https://alexanderanokhin.wordpress.com/deferred-index-maintenance/.

I now understand that the strategy for checking index constraints is tied to 
their maintenance.

The `UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 
1` query we are talking about has indeed to perform both. Such an 
innocuous-looking request, and it sends us right into the very guts of 
relational constraints :-)

Gwendal

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

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

[Simon Slavin]

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 deferred checking.  We don't need to invent our 
own syntax with a PRAGMA.  However, it is done when the constraint is defined 
rather than being something one can turn on or off.  So you would need to think 
out whether you wanted row- or transaction-based checking when you define each 
constraint in the first place.

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

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

[Gwendal Roué]

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 respect to 
sqlite implementors and the wonderful design of sqlite.

> Le 8 déc. 2014 à 15:55, Nico Williams  a écrit :
> 
> 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 require something like transaction triggers, which
> IIRC exists in a "sessions" branch.)
> 
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
>> Le 8 déc. 2014 à 10:55, Gwendal Roué  a écrit :
>> 
>> 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 makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
>> CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we 
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position are 
>> not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >= 1;
>> 
>> The query should run without any error, since it does not break the unique 
>> index.
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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. If one exists, increment a counter. Do the opposite when 
removing entries - decrement the counter if there are two or more 
duplicates of the entry you are removing. If your counter is greater 
than zero at commit time, a UNIQUE constraint has failed.


I suspect there would be a non-trivial increase in the CPU use of UPDATE 
statements though.







   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 require something like transaction triggers, which
IIRC exists in a "sessions" branch.)

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


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

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

[Nico Williams]

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 require something like transaction triggers, which
IIRC exists in a "sessions" branch.)

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

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

[John McKown]

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 size and amount of features
> forced into it impresses me no end.  But, it was never intended to run with
> the big dogs.  The fact that, quite often, it can is a tribute to the
> people that work on it.
>

​I completely agree. I took the source code and copied to my z/OS mainframe
operating system. This system is a UNIX branded system. But is very weird.
Mainly in that it does not use ASCII or Unicode, but another coding
sequence called EBCDIC. Dr. Hipp already had the EBCDIC code in SQLite.
And, despite not having access to a z/OS system (as I understand it), the
code compiled and ran cleanly on z/OS "out of the box". Amazing!​



>
> When making a 'lite' version of something, it's normal to eliminate
> difficult or intensive features that can be lived without.  I think this is
> one of them.
>

​Again, I agree. The only other RDMS which I have used on the
aforementioned system, which was not especially designed for it (DB2), is
Derby (pure Java implementation). SQLite is, IMO, much nicer. And it is
definitely ​much less of a "hog".



>
> Marc
>
>
-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Luuk]

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 implement a book with several pages.
-- Page ordering is implemented via the `position` column in the pages table.
-- A unique index makes sure two pages do not share the same position.
CREATE TABLE books (
 id INT PRIMARY KEY
 )
CREATE TABLE pages (
 book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
CASCADE,
 position INT
)
CREATE UNIQUE INDEX pagination ON pages(book_id, position)

-- Let's populate the tables with a single book and three pages:
INSERT INTO books VALUES (0);
INSERT INTO pages VALUES (0,0);
INSERT INTO pages VALUES (0,1);
INSERT INTO pages VALUES (0,2);

-- We want to insert a page between the pages at positions 0 and 1. So we have
-- to increment the positions of all pages after page 1.
-- Unfortunately, this query yields an error: "columns book_id, position are not 
unique"/

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 of course at this point in time already an entry like (0,2).

Some engines allow you to defer the constraint checking until the end of the 
transaction (and you can do this for References, though you are cascading which 
is fine). In SQLite the check is immediate and will fail for the duplication 
attempted on the first iteration. The fact that the other record will 
eventually be changed to no longer cause a fail is irrelevant to the engine in 
a non-deferred checking.

Now that we have established it isn't a bug,


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 compatibility issue).

Thank you all for your support and explanations. The root cause has been found, 
and lies in the constraint checking algorithm of sqlite. I have been able to 
find a work around that is good enough for me.

Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer 
who his not attached to the constraint-checking algorithm fixes it.

Have a nice day,
Gwendal Roué



It's not a bug, it's in the manual that SQLite behave this way

(https://www.sqlite.org/lang_update.html)
Optional LIMIT and ORDER BY Clauses

If SQLite is built with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT 
compile-time option then the syntax of the UPDATE statement is extended 
with optional ORDER BY and LIMIT clauses as follows:

.
The ORDER BY clause on an UPDATE statement is used only to determine 
which rows fall within the LIMIT. The order in which rows are modified 
is arbitrary and is *not* influenced by the ORDER BY clause.




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

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

[Marc L. Allen]

Doesn't that code risk being broken in a later version that doesn't update in 
the order provided by the sub-query?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of J T
Sent: Monday, December 08, 2014 9:23 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)

and then insert your page.

Please see if that'll work. I tested it, but your results may differ.



 

 

 

-Original Message-
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 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 
> compatibility issue). Thank you all for your support and explanations. 
> The
root cause has been found, and lies in the constraint 
> checking algorithm of sqlite. I have been able to find a work around 
> that is
good enough for me. Now the subject deserves a rest, 
> until, maybe, someday, one sqlite maintainer who his not attached to 
> the
constraint-checking algorithm fixes it. Have a nice day, 
> Gwendal Roué

Your new-ness is irrelevant, if you have a worthy argument it deserves being 
heard. To that end, let me just clarify that nobody was saying the idea of 
deferring the constraint checking is invalid or ludicrous (at least I had no 
such intention) and you make a valid point, especially since most other DB 
engines do work as you suggest - and this will be fixed in SQLite4 I believe, 
where backward-compatibility is not an issue.

The reason I (and others) will say it isn't a bug is because it isn't working 
different than is intended, or more specifically, than is documented. It works 
exactly like described - whether you or I agree with that paradigm or not is up 
to discussion but does not make it a "bug" as long as it works as described.

I hope the work-around you found works great!



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


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



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Gwendal Roué]

> 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 - Broken, As Designed.​ As
> opposed to the normal WAD - Working As Designed.

Thanks RSmith, Marc and John. I can live with this :-)


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

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

[J T]

 

 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 : 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 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.

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

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

[Marc L. Allen]

I'm not sure I'd even consider it broken.

SQLite is wonderful.  Simply wonderful.  Code size and amount of features 
forced into it impresses me no end.  But, it was never intended to run with the 
big dogs.  The fact that, quite often, it can is a tribute to the people that 
work on it.

When making a 'lite' version of something, it's normal to eliminate difficult 
or intensive features that can be lived without.  I think this is one of them.

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf 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 <mlal...@outsitenetworks.com>
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.

--
The temperature of the aqueous content of an unremittingly ogled culinary 
vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[J T]

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 page.

Please see if that'll work. I tested it, but your results may differ.



 

 

 

-Original Message-
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 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 
> compatibility issue). Thank you all for your support and explanations. The 
root cause has been found, and lies in the constraint 
> checking algorithm of sqlite. I have been able to find a work around that is 
good enough for me. Now the subject deserves a rest, 
> until, maybe, someday, one sqlite maintainer who his not attached to the 
constraint-checking algorithm fixes it. Have a nice day, 
> Gwendal Roué 

Your new-ness is irrelevant, if you have a worthy argument it deserves being 
heard. To that end, let me just clarify that nobody was 
saying the idea of deferring the constraint checking is invalid or ludicrous 
(at 
least I had no such intention) and you make a valid 
point, especially since most other DB engines do work as you suggest - and this 
will be fixed in SQLite4 I believe, where 
backward-compatibility is not an issue.

The reason I (and others) will say it isn't a bug is because it isn't working 
different than is intended, or more specifically, than 
is documented. It works exactly like described - whether you or I agree with 
that paradigm or not is up to discussion but does not 
make it a "bug" as long as it works as described.

I hope the work-around you found works great!



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


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

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

[John McKown]

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.

-- 
The temperature of the aqueous content of an unremittingly ogled
culinary vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Marc L. Allen]

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, it cannot be considered a bug.

Changing it at this date might be a problem.  While unlikely, there is a 
possibility that code exists out there that takes advantage of that particular 
design attribute.  Then you get into pragmas and options and the like.  I don't 
do any of the development, but I suspect that's a serious pain when there are 
other features that are more useful to work on.

So, in short... not a bug, but a design feature that you don't care for.  I'm 
sure there's a way to make suggestions or requests to change the design.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[RSmith]

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 
compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint 
checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, 
until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, 
Gwendal Roué 


Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was 
saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid 
point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where 
backward-compatibility is not an issue.


The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than 
is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not 
make it a "bug" as long as it works as described.


I hope the work-around you found works great!



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

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

[Gwendal Roué]

> 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 several pages.
>> -- Page ordering is implemented via the `position` column in the pages table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>> id INT PRIMARY KEY
>> )
>> CREATE TABLE pages (
>> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON 
>> UPDATE CASCADE,
>> position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we 
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position are 
>> not unique"/
>> 
>> 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 of course at this point in time already an entry like (0,2).
> 
> Some engines allow you to defer the constraint checking until the end of the 
> transaction (and you can do this for References, though you are cascading 
> which is fine). In SQLite the check is immediate and will fail for the 
> duplication attempted on the first iteration. The fact that the other record 
> will eventually be changed to no longer cause a fail is irrelevant to the 
> engine in a non-deferred checking.
> 
> Now that we have established it isn't a bug,

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 compatibility issue).

Thank you all for your support and explanations. The root cause has been found, 
and lies in the constraint checking algorithm of sqlite. I have been able to 
find a work around that is good enough for me.

Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer 
who his not attached to the constraint-checking algorithm fixes it.

Have a nice day,
Gwendal Roué

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

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

[RSmith]

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` column in the pages table.
-- A unique index makes sure two pages do not share the same position.
CREATE TABLE books (
 id INT PRIMARY KEY
 )
CREATE TABLE pages (
 book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON UPDATE 
CASCADE,
 position INT
)
CREATE UNIQUE INDEX pagination ON pages(book_id, position)

-- Let's populate the tables with a single book and three pages:
INSERT INTO books VALUES (0);
INSERT INTO pages VALUES (0,0);
INSERT INTO pages VALUES (0,1);
INSERT INTO pages VALUES (0,2);

-- We want to insert a page between the pages at positions 0 and 1. So we have
-- to increment the positions of all pages after page 1.
-- Unfortunately, this query yields an error: "columns book_id, position are not 
unique"/

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 of course at this point in time already an entry like (0,2).


Some engines allow you to defer the constraint checking until the end of the transaction (and you can do this for References, though 
you are cascading which is fine). In SQLite the check is immediate and will fail for the duplication attempted on the first 
iteration. The fact that the other record will eventually be changed to no longer cause a fail is irrelevant to the engine in a 
non-deferred checking.


Now that we have established it isn't a bug, some methods of working round this exist, like Updating in the reverse order (though 
this has to be done in code as the UPDATE function cannot be ordered). Also creating a temp table then substituting it after an 
update (but then you have to recreate the index anyway, so dropping the index and re-making it is better though this can take a long 
time on really large tables).


My favourite is simply running the query twice, once making the values 
negative, and once more fixing them, like this:

UPDATE pages SET position = ((position + 1) * -1) WHERE book_id = 0 AND position 
>= 1;
UPDATE pages SET position = abs(position) WHERE book_id = 0 AND position < 0;

No mess, no fuss, no Unique constraint problem.

Cheers,
Ryan


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

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

[J T]

Sorry, wasn't focused on what I was looking at. Though, you said you already 
tried the order by without success which would have been my next suggestion or 
clarification of my first. As, you should be able to update the rows from the 
end down to the page that would be after your insertion (update pages set 
position=position + 1 where book=0 order by position desc.) and then inserting 
the new page at the desired position. But if that's not working, I have to 
agree with your opinion of it being a bug.

 

 

-Original Message-
From: Gwendal Roué <g...@pierlis.com>
To: General 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 last UPDATE). There is no trigger involved, 
as far as I know. Forgive me but I don't see how I could use your advice.

My work around has been to destroy the unique index, and then re-create it 
after 
the update. This solution is good enough as my table is not that big, and the 
"pure" code path remains intact, with only two inserted statements that are 
easily described and commented.

Gwendal Roué

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -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
> 
> 
> 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 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 makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


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

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

[Gwendal Roué]

> 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.
> 
> A way to solve this is to use REAL for page numbers instead of INTEGER.  To 
> insert a page between two existing ones, give it a number which is the mean 
> of the two pages you're inserting it between.  Every so often you can run a 
> maintenance routine which renumbers all pages to integers.
> 
> Alternatively, store your pages as a linked list.

Polluting my database schema around such a bug is not an option for me, as long 
as I can find a work around that is good enough and leaves my intent intact. 
The one I chose involves destroying the unique index before running the failing 
update query, and then recreating it.

All I look for is this issue to enter the ticket list of sqlite at 
http://www.sqlite.org/src/reportlist, so that this fantastic embeddable 
database gets better.

Gwendal Roué

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

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

[Gwendal Roué]

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, 
as far as I know. Forgive me but I don't see how I could use your advice.

My work around has been to destroy the unique index, and then re-create it 
after the update. This solution is good enough as my table is not that big, and 
the "pure" code path remains intact, with only two inserted statements that are 
easily described and commented.

Gwendal Roué

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -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
> 
> 
> 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 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 makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

[Simon Slavin]

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 of INTEGER.  To 
insert a page between two existing ones, give it a number which is the mean of 
the two pages you're inserting it between.  Every so often you can run a 
maintenance routine which renumbers all pages to integers.

Alternatively, store your pages as a linked list.

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

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

[Gwendal Roué]

Thanks J T. Let's give a look.

> Le 8 déc. 2014 à 14:24, J T <drenho...@aol.com> a écrit :
> 
> Try having your cascade occur before the row is created, updated or deleted.
> 
> http://www.sqlite.org/lang_createtrigger.html
> 
> 
> 
> 
> 
> 
> 
> -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
> 
> 
> 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 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 makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.
> 
> 
>> 
>> Thank you for considering this issue.
>> 
>> Cheers,
>> Gwendal Roué
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

[Gwendal Roué]

> 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 implement a book with several pages.
>> -- Page ordering is implemented via the `position` column in the pages
>> table.
>> -- A unique index makes sure two pages do not share the same position.
>> CREATE TABLE books (
>>id INT PRIMARY KEY
>>)
>> CREATE TABLE pages (
>>book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
>> UPDATE CASCADE,
>>position INT
>> )
>> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>> 
>> -- Let's populate the tables with a single book and three pages:
>> INSERT INTO books VALUES (0);
>> INSERT INTO pages VALUES (0,0);
>> INSERT INTO pages VALUES (0,1);
>> INSERT INTO pages VALUES (0,2);
>> 
>> -- We want to insert a page between the pages at positions 0 and 1. So we
>> have
>> -- to increment the positions of all pages after page 1.
>> -- Unfortunately, this query yields an error: "columns book_id, position
>> are not unique"/
>> 
>> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
>> 1;
>> 
>> The query should run without any error, since it does not break the unique
>> index.
>> 
> 
> Uniqueness is checked for each row change, not just at the end of the
> transaction.  Hence, uniqueness might fail, depending on the order in which
> the individual rows are updated.

Thank you Richard for your answer.

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.

Our analysis describes an implementation detail. Still, this behavior can not 
be considered as normal, and closed as "behaves as expected". I still believe 
that my initial mail is an actual bug report and should be treated as such.

I hope it will find an interested ear. I'm unfortunately not familiar enough 
with the sqlite guts to fix it myself - especially considering the root cause. 
Messing with relational constraints validation is not an easy task.

Regards,
Gwendal Roué
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[J T]

Try having your cascade occur before the row is created, updated or deleted.

http://www.sqlite.org/lang_createtrigger.html

 

 

 

-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


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 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 makes sure two pages do not share the same position.
> CREATE TABLE books (
> id INT PRIMARY KEY
> )
> CREATE TABLE pages (
> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
> UPDATE CASCADE,
> position INT
> )
> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>
> -- Let's populate the tables with a single book and three pages:
> INSERT INTO books VALUES (0);
> INSERT INTO pages VALUES (0,0);
> INSERT INTO pages VALUES (0,1);
> INSERT INTO pages VALUES (0,2);
>
> -- We want to insert a page between the pages at positions 0 and 1. So we
> have
> -- to increment the positions of all pages after page 1.
> -- Unfortunately, this query yields an error: "columns book_id, position
> are not unique"/
>
> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
> 1;
>
> The query should run without any error, since it does not break the unique
> index.
>

Uniqueness is checked for each row change, not just at the end of the
transaction.  Hence, uniqueness might fail, depending on the order in which
the individual rows are updated.


>
> Thank you for considering this issue.
>
> Cheers,
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

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

[Richard Hipp]

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 via the `position` column in the pages
> table.
> -- A unique index makes sure two pages do not share the same position.
> CREATE TABLE books (
> id INT PRIMARY KEY
> )
> CREATE TABLE pages (
> book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE ON
> UPDATE CASCADE,
> position INT
> )
> CREATE UNIQUE INDEX pagination ON pages(book_id, position)
>
> -- Let's populate the tables with a single book and three pages:
> INSERT INTO books VALUES (0);
> INSERT INTO pages VALUES (0,0);
> INSERT INTO pages VALUES (0,1);
> INSERT INTO pages VALUES (0,2);
>
> -- We want to insert a page between the pages at positions 0 and 1. So we
> have
> -- to increment the positions of all pages after page 1.
> -- Unfortunately, this query yields an error: "columns book_id, position
> are not unique"/
>
> UPDATE pages SET position = position + 1 WHERE book_id = 0 AND position >=
> 1;
>
> The query should run without any error, since it does not break the unique
> index.
>

Uniqueness is checked for each row change, not just at the end of the
transaction.  Hence, uniqueness might fail, depending on the order in which
the individual rows are updated.


>
> Thank you for considering this issue.
>
> Cheers,
> Gwendal Roué
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users