[
https://issues.apache.org/jira/browse/TRAFODION-2969?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16377833#comment-16377833
]
ASF GitHub Bot commented on TRAFODION-2969:
-------------------------------------------
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
----
> Subquery with [first 1] in select list results in Normalizer internal error
> ---------------------------------------------------------------------------
>
> Key: TRAFODION-2969
> URL: https://issues.apache.org/jira/browse/TRAFODION-2969
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.3
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
> Priority: Major
>
> The following script illustrates the error:
> {quote}?section setup
> CREATE TABLE subquery_table
> (
> x int no default not null,
> y int,
> primary key ( x )
> );
> CREATE TABLE main_table
> (
> a int no default not null,
> b int,
> primary key (a)
> );
> ?section testit
> prepare s1 from
> select
> (select [FIRST 1] y aa from subquery_table b where b.x = a.b) as company_id
> from main_table a;
> ?section testit2
> prepare s1 from
> select
> (select y aa from subquery_table b where b.x = a.b) as company_id
> from main_table a;
> {quote}
> If we run section setup to create the tables, then run section testit, the
> prepare fails with this error:
> *
> **
> *** ERROR[2006] Internal error: assertion failure
> (CmpCommon::getDefault(COMP_BOOL_137) == DF_ON) in file
> ../optimizer/NormItemExpr.cpp at line 6028.
> The second query, in section testit2, on the other hand compiles and executes
> just fine.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
