trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Birdsall <>
Subject RE: Anomaly with [first n] and ORDER BY
Date Mon, 08 Jan 2018 23:25:47 GMT
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 [] 
Sent: Monday, January 8, 2018 2:54 PM
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


> On Jan 8, 2018, at 4:17 PM, Hans Zeller <> wrote:
> Hi Dave,
> The simple reason is that the person who implemented the [first n] feature is not a compiler
> 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 [] 
> Sent: Monday, January 8, 2018 12:24 PM
> To:
> Subject: Anomaly with [first n] and ORDER BY
> Hi,
> I've been studying, and the related
> 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

View raw message