L' Eomot Inversé (10/31/2012)
craig 81366 (10/31/2012)
GilaMonster (10/31/2012)
Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too :hehe:No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
I beg to differ.
You can differ as much as you like, but what is called the Halloween Problem (and its soluttion Halloween Protection) is a matter of real history which is on the record, not about what you choose to think it's about.
You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.
Actually, that's nonsense. It isn't possible to compute the AVG aggregate without a spool, the purpose of the spool is to allow AVG to be computed, nothing else.
Really?
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME
An average can be calculated and maintained incrementally without spooling data.
... 36 years ago (or was it only 35?) at this time of year the System R team were not discussing an issue with delete, but an issue with update only ... Paul McJones did a transcript of the whole reunion, published on the web later that year, and a couple of years later made it available ... The relevant passage is here.
Thanks for that interesting piece of history, and for the link. There's definitely some interesting reading there.
However, I didn't see anything that claimed the Halloween Problem was considered exclusive to the UPDATE statement; in spite of that being how it was discovered. In fact, Pat Selinger went on to say: "An interesting footnote is that we just discovered another one of these as sort of a variation on that, in the latest work that we did having to do with referential integrity and things like that, where the referential integrity relationships were going to trigger off the same kind of nonstop behavior."
In a nutshell, the problem boils down to: a data operation is expected to behave in a predetermined way, but is not due to a side effect of its work unexpectedly affecting its own behaviour recursively.
I guess my point is that if it's not the problem that those two people brought to the design issues meeting on Halloween that year, or about the solution to those issues, it is not the Halloween Problem, or Halloween Protection. Calling it that would be about as accurate as calling the Battle of the Nile the Battle of the Plate - unhistorical nonsense.
In contrast to your historical analogy, consider this:
If someone were to observe a ball and decide: Due to your properties of "roundedness", I dub thee sphere.
And later someone else observes that the Earth has the same properties of "roundedness", you're also a sphere. Would that be wrong?