Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
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é 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 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
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 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 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
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
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
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
> 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
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
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
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
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
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
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
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 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 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
> 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
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 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 ___ 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
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 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
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 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 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
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
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
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
> 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
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
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é To: General Discussion 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, 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 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 > 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, >> >> 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
> 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
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 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 > 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, >> >> 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
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
Thanks J T. Let's give a look. > Le 8 déc. 2014 à 14:24, J T 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 > 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, >> >> 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
> 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
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, 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 ___ 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
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 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, > > 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
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
[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 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