Hi Dave, The simple reason is that the person who implemented the [first n] feature is not a compiler developer.
Ideally, we would be aware of the [first n] throughout the compilation and have a new required property in the optimizer that says "optimize for first N rows", so that we could favor certain query plans such as nested joins, but this is not happening today and it would be a significant project. One other comment about being able to update a [first n] view: Ideally, such a view would be updatable if no WITH CHECK OPTION was specified, and it would not be updatable when the WITH CHECK OPTION was specified in the CREATE VIEW DDL. Again, that's the ideal case, and we may not be able to make that happen today. Thanks, Hans -----Original Message----- From: Dave Birdsall [mailto:dave.birds...@esgyn.com] Sent: Monday, January 8, 2018 12:24 PM To: dev@trafodion.apache.org Subject: Anomaly with [first n] and ORDER BY 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
