On Sat, 16 Jan 2016 14:26:20 -0700 "Keith Medcalf" <kmedcalf at dessus.com> wrote:
> > the result is nondeterministic if more than one row in S matches. > > The update applies all rows in S matching T. Of course, only the > > last one is preserved. Of course, because order is nonsemantic, > > there's no way to know *which* S row will "win". > > And the problem is exactly what? The statement will do exactly and > precisely what it has been instructed to do. That it was given bad > instruction by someone incompetent to be giving instruction is > irrelevant. It's not a "bad instruction" to update one table from another when the join criteria aren't guaranteed, by DRI, to be 1:1 correspondence. Either the instruction can be carried out consistent with the database's constraints, or it cannot. A logically incoherent instruction -- such as "update row 1 in T from rows 2-5 in S -- should be rejected as erroneous. The system should not apply row 5 and ignore 2-4 arbitrarily. It is a bad implementation that allows an update statement to have nondeterministic results. I would think we agree on that. > The real problem with implementing this sort of update is that it in > order to prevent implementation errors (caused by the fact that the > set algebra is performed one row at a time, and not as a set) you > have to generate and intermediate update set and then apply that set > after the fact -- if a column being updated is used in a join > constraint (but not if it is used in a selection constraint). I'm not sure I understand. I guess by "set algebra is performed one row at a time" you're describing implementation, but of course all implementation is one row at a time (if that). Do you simply mean the semantics of the update preclude efficient implementation? Sure, that's something to be aware of. Is it a "problem", per se? I'm on record asking for more correct, even if less efficient, implementation in SQLite. Specifically, it shouldn't be necessary to drop unique constraints to increment consecutive values. --jkl
