[jira] [Commented] (CALCITE-4334) TRUE_AGG, an aggregate function that always returns TRUE

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4334?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17357583#comment-17357583 ] 

Julian Hyde commented on CALCITE-4334:
--------------------------------------

The built-in {{GROUP_ID()}} aggregate function might solve this. It takes no arguments, which is important if the input has zero columns. It always returns zero, unless you have non-unique GROUPING SETS, when it always returns a non-negative integer.

{{GROUP_ID()}} is non-standard (originating in [Oracle|https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions063.htm#SQLRF00646] but also present in Vertica and perhaps a few others), and not implemented on all DBs, so SqlImplementor would need to intercept it and translate it as 0 for those dialects.

But mainly we just need a function that returns a not-NULL value (and that the planner knows is NOT NULL) and takes no arguments, and {{GROUP_ID}} will do nicely.

> TRUE_AGG, an aggregate function that always returns TRUE
> --------------------------------------------------------
>
>                 Key: CALCITE-4334
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4334
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>
> It would be useful to have an (internal) aggregate function that has no arguments and returns a constant value, regardless of how many rows are in the group. We suggest {{TRUE_AGG}}, which always returns the {{BOOLEAN}} value {{TRUE}}.
> If there are reasons why it would be better to return other constants (such as 0, 1, FALSE or the empty string) we will consider them.
> For example, when rewriting sub-queries (see SubQueryRemoveRule) we introduce add "true as indicator" to the SELECT clause of sub-queries. It can be used to detect rows generated by an outer join. If it is an aggregate query, we would have to write "min(true) as indicator", which necessitates an extra {{Project}} below the {{Aggregate}} to provide the  "true" value. A TRUE_AGG aggregate function allows us to avoid the extra {{Project}}.
> Another example came up during CALCITE-4317. We would like to make {{RelBuilder.aggregate(groupKey())}} throw when given an empty group key and no aggregate calls. (Because it would create an {{Aggregate}} that has zero fields, and that is problematic elsewhere in Calcite.) But we would also like a pattern where an aggregate with an empty group key becomes a constant single-row relational expression. So, {{RelBulder.aggregate(groupKey(), aggregateCall(TRUE_AGG))}} should generate {{VALUES TRUE}}.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)