[jira] [Comment Edited] (BEAM-11696) NULL values don't work correctly when implementing LOGICAL_AND aggregation function in ZetaSQL

["Kyle Weaver (Jira)" <ji...@apache.org>]

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

Kyle Weaver edited comment on BEAM-11696 at 2/2/21, 6:59 PM:
-------------------------------------------------------------

This seems like expected behavior in ZetaSQL. You can fix it by casting at least one of the null values to boolean:

SELECT LOGICAL_AND(a) AS logical_and FROM UNNEST([CAST(null AS bool), null, null]) AS a;


was (Author: ibzib):
This seems like expected behavior in ZetaSQL. You can fix it by casting at least one of the null values to boolean:

SELECT LOGICAL_AND(y) AS logical_and FROM UNNEST([CAST(null AS bool), null, null]) AS y;

> NULL values don't work correctly when implementing LOGICAL_AND aggregation function in ZetaSQL
> ----------------------------------------------------------------------------------------------
>
>                 Key: BEAM-11696
>                 URL: https://issues.apache.org/jira/browse/BEAM-11696
>             Project: Beam
>          Issue Type: Bug
>          Components: dsl-sql-zetasql
>            Reporter: Sonam Ramchand
>            Priority: P3
>
> Reference: [https://github.com/google/zetasql/blob/master/docs/functions-and-operators.md#logical_and]
> Performs a logical AND operation on expression and returns the result.
> Supported Argument Types: BOOL
>  Returned Data Types: BOOL
> Example:
>  
> {code:java}
> SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([null, null, null]) AS x;
> +-------------+
> | logical_and |
> +-------------+
> | false       |
> +-------------+
> {code}
>  
> Problems: 
> After implementation, there is a problem:
>  * When input contains all null values, the output is:
> {code:java}
> No matching signature for aggregate function LOGICAL_AND for argument types: INT64. Supported signature: LOGICAL_AND(BOOL)  {code}
>          which is same behavior as of BigQuery console. Though, it should return null.
> How to test: unit tests
>  



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