BigQueryにおける 三値論理の all, any関数の実装

[takegue]

BigQuery上における all, anyの誤った実装例

Pythonでいうallやany, javasciptにおける allやsomeといった関数を
SQL上で実現しようと考えると unnest と logical_or , logical_and が思い浮かぶ

declare arr array<bool> default [true, false];
select
  (select logical_and(v) from unnest(arr) as v ) as ret

注意としてこれらはallやsomeと等価にならない
これは空集合に対しての値定義が一致しなたいためである。

また logical_or や logical_and は三値論理 は
nullの値を無視するつくりになっており、三値論理正しく実現していない。
例えば次のようなケースである

いずれかの値がnullであるケース:

• 論理積であれば いずれか falseであれば falseになる. それ以外は不定
• 論理積であれば いずれか trueであれば trueになる. それ以外は不定

allやanyと同等の結果を実現するためには、これらについて適切にハンドリングする必要がある

BigQuery上における 3値論理を実現する all, anyの正しい実装例

any

create temp function `any`(arr array<bool>)
as 
 ((
   select as value 
    if(
      array_length(arr) > 0
      , if(logical_or(v), true, if(countif(v is null) > 0, null, false))
      , false
    )
   from unnest(arr) as v
 ))
;
assert `any`([true, false]) = true;
assert `any`([true, null]) = true;
assert `any`([false, null]) is null;
assert `any`([]) = false;

all

create temp function `all`(arr array<bool>)
as 
 ((
   select as value 
    if(
      array_length(arr) > 0
      , if(logical_and(v), if(countif(v is null) > 0, null, true), false)
      , true
    ) 
   from unnest(arr) as v
 ))
;

assert `all`([true, false]) = false;
assert `all`([false, null]) = false;
assert `all`([true, null]) is null;
assert `all`([]) = true;