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 22:17:18 GMT
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