GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/trafodion/pull/1454
[TRAFODION-2969] Fix interaction of [first n] etc. with subqueries
Formerly, a [first n] in a subquery in a SELECT list resulted in an
internal error in the Normalizer. The problem was that the Normalizer method
Subquery::transformToRelExpr was not expecting a FirstN node at the top of the
subquery tree.
Besides the fact that the Normalizer doesn't like it, having FirstN above
the GroupByAgg node that enforces the one-row cardinality rule doesn't make
sense.
In this set of changes, the FirstN node is placed underneath the GroupByAgg
node when it matters. For [first n] / [any n] where n > 1, we can simply throw
the [first n] / [any n] away, since the subquery already enforces at most one
row semantics. For [first 1] and [last 1], we need to insert the FirstN node
below the GroupByAgg node so that we narrow the result set down to one (or
zero) rows before GroupByAgg enforces the cardinality rule. So, adding [first
1] to a subquery avoids cardinality violations. For [last 0] there is a
subtlety. [last 0] by definition returns no rows, so we want the subquery to
return null. The way we chose to do this is to force the creation of a one-row
GroupByAgg node always, and put the FirstN node under that. In this way,
GroupByAgg will see zero rows and produce a null result.
Regression test core/TEST002 has had many [first n] - subquery interaction
tests added.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/trafodion Trafodion2969
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1454.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1454
----
commit 2f2714d8fd719b92476e96642cd3fc4389523d92
Author: Dave Birdsall <dbirdsall@...>
Date: 2018-02-27T00:18:39Z
[TRAFODION-2969] Fix interaction of [first n] etc. with subqueries
----
---
