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.
Consider a DELETE statement of the form:
DELETE TABLE_NAME
WHERE COL < CONST
as opposed to
DELETE TABLE_NAME
WHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)
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. It is of course rather a good thing that the spool just happens to ensure that the Halloween Problem can't arise, so that no Halloween protection is needed in this case. One side effect of that good thing is that that 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, because the delete issue didn't exist; and the Halloween Problem is the name that they gave to the problem they discussed that day, and that is a problem with update only, and Halloween Protectiopn means protectin from that update only problem, not anything else. At the 1995 System R/SQL reunion surviving members of the team talked about the early development of the language now called SQL, and Pat Selinger (she may still have been Pat Griffiths when she and Morton Astrahan identified this problem, or maybe she was already married by then) gave a description of this particular thing. 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 as an SRC technical note, which is currently accessible at his wesite and (in a Russian version) at CITForum.ru. The relevant passage is here.
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.
Tom