trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dave Birdsall <>
Subject Anomaly with [first n] and ORDER BY
Date Mon, 08 Jan 2018 20:23:46 GMT

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?


  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message