trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From DaveBirdsall <>
Subject [GitHub] trafodion pull request #1454: [TRAFODION-2969] Fix interaction of [first n] ...
Date Tue, 27 Feb 2018 00:27:48 GMT
GitHub user DaveBirdsall opened a pull request:

    [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 Trafodion2969

Alternatively you can review and apply these changes as the patch at:

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



View raw message