This probably a pointless argumnent, because it isn't about anything technical, just about historical and terminological accuracy, and it does appear that quite a few people have adopted historically inaccurate terminology (which is a pretty frequent phenomenon - for example we call Heath's normal form, published in 1971, "Boyce-Codd Normal Form" after a paper published several years later, although the earlier paper was certainly known to the senior of the authors of the later paper). But I'm going to make the argument for historical accuracy (especially since several of the people involved are still alive) anyway (if I were in touch with any of them I would ask them for their views, but unfortunately I'm not).
craig 81366 (10/31/2012)
L' Eomot Inversé (10/31/2012)
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.
Then why when I run the following by itself, does the plan not spool?
SELECT AVG(COL) FROM TABLE_NAME
Because a displayed MS SQL query plan only shows spool operations when they are not blatantly obvious (which I think is eminently sensible); no other reason. If you think you can deliver a result for a query for AVG without first reading all the records, then think again, because you are horribly wrong. You can't deliver the first value of AVG until you have done that. Then, why read the records again? Sure, you could track changes in AVG as the table changed, if you wanted to and had an awful lot of compute power to spare, but poeple neither wanted to nor had that power to spare so in the early versions of SQL (or of SEQUEL, or whatever it was called before it became SEQUEL) AVG was not recomputed. It isn't today, not because some additional spool operation, beyond what is required to compute AVG in the first place, is introduced, but because AVG has to be computed.
An average can be calculated and maintained incrementally without spooling data.
It can't be computed for the first time without a spool (ie reading all the input before going to the next step).
... 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."
So "something that's "a sort of variation on that" is an instance of "that"? YG&LF, or perhaps GHU - what else can I say? Why did she say "a sort of variation on that" rather than just "another instance of that" or "another example of that", I wonder? Perhaps she though the various possible phrases had different interpretations, and chose one which can't reasonably nbe taken to mean what you want it to mean precisely because what you want it to mean is not what she intended to say? I don't know, I wasn't there at that meeting, but I'm not the one trying to attribute to her a meaning which what she actually said cleafrly doesn't imply.
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?
So all spheroids are spheres, are they? They are all round - any corners would make them not spheroids. Even on a technical, as opposed to historical, basis your argument doesn't fly. Of course if in your sphere example the properties of roundness concerned require both that there be a single focus and that all diameters be the same (either of those is a consequence of the other in some geometries, but not in all) then yes, the new thing would be a sphere. That's rather a big and nasty requirement on the properties, I guess the equivalent condition wrt the Halloween Problem would be that it involved an insert statement, so you are seem to be comparing tautologies with contradictions - not usually a useful passtime.