Apologies... I misread your question. I answered the question, "should we support updatable first n views". The answer to that is No.
As for first n views, well, we already support them. -----Original Message----- From: Dave Birdsall [mailto:[email protected]] Sent: Monday, January 8, 2018 3:26 PM To: [email protected] Subject: RE: Anomaly with [first n] and ORDER BY We don't. That was the point of JIRA TRAFODION-2822. But there is this one case that escaped that fix. That's what I'm trying to patch here. -----Original Message----- From: Rohit Jain [mailto:[email protected]] Sent: Monday, January 8, 2018 2:54 PM To: [email protected] Subject: Re: Anomaly with [first n] and ORDER BY Stupid question, but why do we need to support first n in views? The use case I see for first n seems does not warrant the need for use in a view that often, of at all. Is this a customer request? Rohit > On Jan 8, 2018, at 4:17 PM, Hans Zeller <[email protected]> wrote: > > 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:[email protected]] > Sent: Monday, January 8, 2018 12:24 PM > To: [email protected] > 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
