On Tue, 31 Dec 2013 22:20:15 +0000
Simon Slavin <slav...@bigfraud.org> wrote:
> > Meanwhile, here's a much more important failing that cannot be
> > worked around within SQL without a temporary table:
> >
> > sqlite> create table i ( i int primary key );
> > sqlite> insert into i values (1);
> > sqlite> insert into i values (2);
> > sqlite> update i set i = i + 1;
> > SQL error: column i is not unique
>
> Is that not actually a serious bug in SQLite ?
Well, a *bug* is defined as behavior in exception to the
documentation; I don't know if this behavior is documented.
Definitely it's a flaw, and IMO the worst one. As I said, it affects
any UPDATE of a column with a unique constraint or index.
> My understanding of SQL is that checks for consistency should occur
> at the end of a transaction, not at each row of an UPDATE.
Correct. That's what the standard says, and what atomicity requires.
The user interacts with the DBMS on a the basis of sets, a statement at
a time. The above update statement can logically be executed, and the
user by definition has no control over *how* it is executed.
> I hadn't realised this problem existed.
It's known to the developers; see the thread in around 6 September 2013
with the subject "UPDATE question".
Since we're on the topic, let me describe a possible shortcut.
The only general successful implementation strategy (within SQLite)
would be to write the updated rows to a temporary table, delete them
from the target table, and insert from the temporary. But that's not
always necessary.
In the update statement above, the query engine can ascertain that the
output of the update is greater than the input. By working "backwards"
over the index -- updating the "last" row first -- the row-by-row
update could succeed. With "just a little" work, the in-place update
algorithm could decide whether to work "backwards" or "forwards", and
succeed more often than it now does. By brute force it could try one
and then the other before giving up.
Will that always work? No. Consider
sqlite> update i set i = random();
or
sqlite> update i set i = (select min(t) from T where i.i = T.i);
Nevertheless, a slightly more intelligent algorithm for in-place
updates would solve the class of problem for which the output is a
determinisitic function of the same column. It might be easier to
implement within the existing framework than a general
copy-delete-insert strategy. Whether it's worthwhile to bother, I
don't know.
Happy New Year.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
