Hi, I've been studying https://issues.apache.org/jira/browse/TRAFODION-2840, and the related case https://issues.apache.org/jira/browse/TRAFODION-2822.
I attempted to fix the latter case by making [first n] views not updatable. But the former case documents a hole in my fix. It seems that if we add ORDER BY to the view definition, the checks in 2822 are circumvented. I figured out why. At bind time, [first n] scans are transformed to a firstN(scan) tree (that is, a firstN node is created and inserted on top of the scan). EXCEPT, if there is an ORDER BY clause, we don't do this. Instead, we generate the firstN node at code generation time. But that means the Normalizer sees a [first n] + ORDERBY as just a scan, and a [first n] without ORDER BY as firstN(scan). The fix for 2822 was in the Normalizer; so this anomaly explains why the fix didn't work when ORDER BY was present. Now, I've figured out how to improve the fix so the Normalizer catches the ORDER BY example. But I am curious why we do this strange thing of deferring firstN insertion to generation time. It seems to me doing so could defeat many other checks for firstN processing. For example, an optimizer rule that does something for firstNs wouldn't fire if an ORDER BY is present. I'm wondering, for example, why we didn't have the Binder simply insert a firstN and a sort node into the tree. Any thoughts? Dave
