[jira] [Commented] (CALCITE-4335) Aggregate functions for BigQuery

["Jiabao Sun (Jira)" <ji...@apache.org>]

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

Jiabao Sun commented on CALCITE-4335:
-------------------------------------

Hi [~julianhyde].
I noticed that ARRAY_AGG in BigQuery and Postgres returns NULL if there are zero input rows or expression evaluates to NULL for all rows.
However, it will return `ARRAY<arg0> NOT NULL` in the return type of the function definition.
I'm not sure if it is intentional, if this is a problem, I am glad to fix it.

{code:java}
  /** The "ARRAY_AGG(value [ ORDER BY ...])" aggregate function,
   * in BigQuery and PostgreSQL, gathers values into arrays. */
  @LibraryOperator(libraries = {POSTGRESQL, BIG_QUERY})
  public static final SqlAggFunction ARRAY_AGG =
      SqlBasicAggFunction
          .create(SqlKind.ARRAY_AGG,
              ReturnTypes.andThen(ReturnTypes::stripOrderBy,
                  ReturnTypes.TO_ARRAY), OperandTypes.ANY)
          .withFunctionType(SqlFunctionCategory.SYSTEM)
          .withSyntax(SqlSyntax.ORDERED_FUNCTION)
          .withAllowsNullTreatment(true);
{code}
[https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#array_agg]
[https://www.postgresql.org/docs/8.4/functions-aggregate.html]
 
!image-2023-09-12-18-13-43-592.png!

> Aggregate functions for BigQuery
> --------------------------------
>
>                 Key: CALCITE-4335
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4335
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Assignee: Julian Hyde
>            Priority: Major
>             Fix For: 1.27.0
>
>
> Add aggregate functions for BigQuery ([spec|https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions]): {{ARRAY_AGG}}, {{ARRAY_CONCAT_AGG}}, {{COUNTIF}}, {{LOGICAL_AND}}, {{LOGICAL_OR}}, {{STRING_AGG}}.
> Calcite already has {{ANY_VALUE}}, {{BIT_AND}}, {{BIT_OR}}, {{BIT_XOR}}, {{MAX}}, {{MIN}}, {{SUM}} aggregate functions.
> (Edit, 2020/10/15: And, since CALCITE-2935 is fixed, it now has {{LOGICAL_AND}}, {{LOGICAL_OR}}.)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)