trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hans Zeller <>
Subject RE: Anomaly with [first n] and ORDER BY
Date Mon, 08 Jan 2018 23:28:15 GMT
Hi Dave,

Overall, I like the idea of moving some of this logic into the optimizer. It's about time
to do that.

One small comment: The sort enforcer rule does not have a pattern, so it cannot look at the
FIRST_N node. It can look at the group, so you could probably mark the group of the FIRST_N

To be honest, I don't really like this solution, because it uses some flags to deal with required
physical properties. Ideally, we would figure out some way to make it work in a way that required
physical properties are passed as such, while things that alter the logical properties are
stored in RelExprs. The problem is that the FIRST_N operator, especially with an ORDER BY,
violates this separation of logical and physical properties. We have another operator like
that, the partial groupby.

Here is what I would do - it's not a perfect solution but hopefully it makes the required
physical properties more accurate:

For queries with a [FIRST n] and an ORDER BY, let both the root and the FIRST_N node (which
is then always added in the binder) ask for the sort order. This is more realistic. Both operators
require an order. We might consider a sort on top of the FIRST_N, but that sort would be eliminated,
because it is unnecessary, as the FIRST_N would always return its result in order.



-----Original Message-----
From: Dave Birdsall [] 
Sent: Monday, January 8, 2018 2:37 PM
Subject: RE: Anomaly with [first n] and ORDER BY

Hi Hans,

So my first attempt at a fix for Trafodion 2840 is to add code to RelRoot::isUpdatableBasic
to check if getFirstNRows != -1 or getFirstNParams is non-null.

That fix does half the trick. It makes new [first n] + ORDER BY views not updatable. The half
of the trick that it doesn't do is take care of existing [first n] + ORDER BY views; they
remain marked updatable in the metadata.

So I was trying to imagine approaches that would catch existing views as well.

In RelRoot::codeGen I saw these comments:

// if root has GET_N indication set, insert a FirstN node.
  // Usually this transformation is done in the binder, but in
  // some special cases it is not.
  // For example, if there is an 'order by' in the query, then
  // the Sort node is added by the optimizer. In this case, we
  // want to add the FirstN node on top of the Sort node and not
  // below it. If we add the FirstN node in the binder, the optimizer
  // will add the Sort node on top of the FirstN node. Maybe we
  // can teach optimizer to do this.

So, I thought: Well, let's explore the idea of having the Optimizer insert the Sort below
FirstN instead of above it. I've coded a first attempt (still getting it to compile cleanly
at this moment though). The idea was: Change the binder to always insert FirstN (even when
ORDER BY is present). Change SortEnforcerRule::topMatch so it does not match on FirstN nodes
(that prevents the Sort from being placed on top of FirstN). Add a new rule SortEnforcerFirstNRule
that matches FirstN trees only, and transforms FirstN(CutOp) to FirstN(Sort(CutOp)). Hopefully
that eliminates the need for the generator to insert the FirstN node, and gets the Sort node
in the right place.

This should catch existing [first n] + ORDER BY views since view composition happens in the
binder. Now the FirstN node will be generated there and the existing Normalizer check will
catch it and flag it not updatable.

What do you think?


-----Original Message-----
From: Hans Zeller [] 
Sent: Monday, January 8, 2018 2:17 PM
Subject: RE: Anomaly with [first n] and ORDER BY

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

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.



-----Original Message-----
From: Dave Birdsall [] 
Sent: Monday, January 8, 2018 12:24 PM
Subject: Anomaly with [first n] and ORDER BY


I've been studying, and the related case

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?


View raw message