Skip to content

fix: fix the boolean coercion bug for any() and all() in SQLGlot #18090

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Draft
sycai wants to merge 2 commits into
base: main
Choose a base branch
from
Draft

fix: fix the boolean coercion bug for any() and all() in SQLGlot #18090

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
27f075d
fix: fix the boolean coercion bug for any() and all() in SQLGlot
sycai Aug 12, 2026
3eddb17
fix format
sycai Aug 12, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
16 changes: 7 additions & 9 deletions packages/bigframes/bigframes/core/compile/sqlglot/aggregations/unary_compiler.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -26,7 +26,7 @@
from bigframes.core import window_spec
from bigframes.core.compile.sqlglot import sql
from bigframes.core.compile.sqlglot.aggregations.windows import apply_window_if_present
from bigframes.core.compile.sqlglot.expressions import constants
from bigframes.core.compile.sqlglot.expressions import common, constants
from bigframes.operations import aggregations as agg_ops

UNARY_OP_REGISTRATION = reg.OpRegistration()
Expand All @@ -48,10 +48,9 @@ def _(
column: typed_expr.TypedExpr,
window: typing.Optional[window_spec.WindowSpec] = None,
) -> sge.Expression:
expr = column.expr
if column.dtype != dtypes.BOOL_DTYPE:
expr = sge.NEQ(this=expr, expression=sge.convert(0))
expr = apply_window_if_present(sge.func("LOGICAL_AND", expr), window)
expr = apply_window_if_present(
sge.func("LOGICAL_AND", common._to_nullable_bool(column)), window
)

# BQ will return null for empty column, result would be true in pandas.
return sge.func("COALESCE", expr, sge.convert(True))
Expand All @@ -63,10 +62,9 @@ def _(
column: typed_expr.TypedExpr,
window: typing.Optional[window_spec.WindowSpec] = None,
) -> sge.Expression:
expr = column.expr
if column.dtype != dtypes.BOOL_DTYPE:
expr = sge.NEQ(this=expr, expression=sge.convert(0))
expr = apply_window_if_present(sge.func("LOGICAL_OR", expr), window)
expr = apply_window_if_present(
sge.func("LOGICAL_OR", common._to_nullable_bool(column)), window
)

# BQ will return null for empty column, result would be false in pandas.
return sge.func("COALESCE", expr, sge.convert(False))
Expand Down
26 changes: 26 additions & 0 deletions packages/bigframes/bigframes/core/compile/sqlglot/expressions/common.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -14,8 +14,12 @@

from __future__ import annotations

import bigframes_vendored.sqlglot as sg
import bigframes_vendored.sqlglot.expressions as sge

from bigframes import dtypes
from bigframes.core.compile.sqlglot.expressions import typed_expr


def round_towards_zero(expr: sge.Expression):
"""
Expand All @@ -31,3 +35,25 @@ def round_towards_zero(expr: sge.Expression):
),
to="INT64",
)


def _to_nullable_bool(expr: typed_expr.TypedExpr) -> sge.Expression:
"""
Cast the value of an expression to bool based on its truthiness. If the value is null, the result is null.
"""
from_type = expr.dtype
sg_expr = expr.expr

if from_type == dtypes.BOOL_DTYPE:
return sg_expr
elif dtypes.is_numeric(from_type):
return sge.NEQ(this=sg_expr, expression=sge.convert(0))
elif dtypes.is_string_like(from_type):
return sge.GT(this=sge.func("LENGTH", sg_expr), expression=sge.convert(0))
elif dtypes.is_array_like(from_type):
return sge.GT(this=sge.func("ARRAY_LENGTH", sg_expr), expression=sge.convert(0))

return sge.Is(
this=sge.paren(sg_expr, copy=False),
expression=sg.not_(sge.Null(), copy=False),
)
22 changes: 4 additions & 18 deletions packages/bigframes/bigframes/core/compile/sqlglot/expressions/generic_ops.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -21,6 +21,7 @@
from bigframes import dtypes
from bigframes import operations as ops
from bigframes.core.compile.sqlglot import sql, sqlglot_types
from bigframes.core.compile.sqlglot.expressions import common
from bigframes.core.compile.sqlglot.expressions.typed_expr import TypedExpr

register_unary_op = expression_compiler.expression_compiler.register_unary_op
Expand Down Expand Up @@ -150,24 +151,9 @@ def _(expr: TypedExpr) -> sge.Expression:

@register_unary_op(ops.coerce_to_bool_op)
def _(expr: TypedExpr) -> sge.Expression:
from_type = expr.dtype
sg_expr = expr.expr

if from_type == dtypes.BOOL_DTYPE:
res = sg_expr
elif dtypes.is_numeric(from_type):
res = sge.NEQ(this=sg_expr, expression=sge.convert(0))
elif dtypes.is_string_like(from_type):
res = sge.GT(this=sge.func("LENGTH", sg_expr), expression=sge.convert(0))
elif dtypes.is_array_like(from_type):
res = sge.GT(this=sge.func("ARRAY_LENGTH", sg_expr), expression=sge.convert(0))
else:
res = sge.Is(
this=sge.paren(sg_expr, copy=False),
expression=sg.not_(sge.Null(), copy=False),
)

return sge.Coalesce(this=res, expressions=[sge.convert(False)])
return sge.Coalesce(
this=common._to_nullable_bool(expr), expressions=[sge.convert(False)]
)


@register_ternary_op(ops.where_op)
Expand Down
13 changes: 8 additions & 5 deletions ...sts/unit/core/compile/sqlglot/aggregations/snapshots/test_unary_compiler/test_all/out.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,15 +1,18 @@
WITH `bfcte_0` AS (
SELECT
`bool_col`,
`int64_col`
`int64_col`,
`string_col`
FROM `bigframes-dev`.`sqlglot_test`.`scalar_types` AS `bft_0`
), `bfcte_1` AS (
SELECT
COALESCE(LOGICAL_AND(`bool_col`), TRUE) AS `bfcol_2`,
COALESCE(LOGICAL_AND(`int64_col` <> 0), TRUE) AS `bfcol_3`
COALESCE(LOGICAL_AND(`bool_col`), TRUE) AS `bfcol_3`,
COALESCE(LOGICAL_AND(`int64_col` <> 0), TRUE) AS `bfcol_4`,
COALESCE(LOGICAL_AND(LENGTH(`string_col`) > 0), TRUE) AS `bfcol_5`
FROM `bfcte_0`
)
SELECT
`bfcol_2` AS `bool_col`,
`bfcol_3` AS `int64_col`
`bfcol_3` AS `bool_col`,
`bfcol_4` AS `int64_col`,
`bfcol_5` AS `string_col`
FROM `bfcte_1`
12 changes: 12 additions & 0 deletions .../core/compile/sqlglot/aggregations/snapshots/test_unary_compiler/test_all_w_array/out.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,12 @@
WITH `bfcte_0` AS (
SELECT
`int_list_col`
FROM `bigframes-dev`.`sqlglot_test`.`repeated_types` AS `bft_0`
), `bfcte_1` AS (
SELECT
COALESCE(LOGICAL_AND(ARRAY_LENGTH(`int_list_col`) > 0), TRUE) AS `bfcol_1`
FROM `bfcte_0`
)
SELECT
`bfcol_1` AS `int_list_col`
FROM `bfcte_1`
13 changes: 8 additions & 5 deletions ...sts/unit/core/compile/sqlglot/aggregations/snapshots/test_unary_compiler/test_any/out.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,15 +1,18 @@
WITH `bfcte_0` AS (
SELECT
`bool_col`,
`int64_col`
`int64_col`,
`string_col`
FROM `bigframes-dev`.`sqlglot_test`.`scalar_types` AS `bft_0`
), `bfcte_1` AS (
SELECT
COALESCE(LOGICAL_OR(`bool_col`), FALSE) AS `bfcol_2`,
COALESCE(LOGICAL_OR(`int64_col` <> 0), FALSE) AS `bfcol_3`
COALESCE(LOGICAL_OR(`bool_col`), FALSE) AS `bfcol_3`,
COALESCE(LOGICAL_OR(`int64_col` <> 0), FALSE) AS `bfcol_4`,
COALESCE(LOGICAL_OR(LENGTH(`string_col`) > 0), FALSE) AS `bfcol_5`
FROM `bfcte_0`
)
SELECT
`bfcol_2` AS `bool_col`,
`bfcol_3` AS `int64_col`
`bfcol_3` AS `bool_col`,
`bfcol_4` AS `int64_col`,
`bfcol_5` AS `string_col`
FROM `bfcte_1`
12 changes: 12 additions & 0 deletions .../core/compile/sqlglot/aggregations/snapshots/test_unary_compiler/test_any_w_array/out.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,12 @@
WITH `bfcte_0` AS (
SELECT
`int_list_col`
FROM `bigframes-dev`.`sqlglot_test`.`repeated_types` AS `bft_0`
), `bfcte_1` AS (
SELECT
COALESCE(LOGICAL_OR(ARRAY_LENGTH(`int_list_col`) > 0), FALSE) AS `bfcol_1`
FROM `bfcte_0`
)
SELECT
`bfcol_1` AS `int_list_col`
FROM `bfcte_1`
24 changes: 22 additions & 2 deletions packages/bigframes/tests/unit/core/compile/sqlglot/aggregations/test_unary_compiler.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -63,16 +63,26 @@ def _apply_unary_window_op(


def test_all(scalar_types_df: bpd.DataFrame, snapshot):
bf_df = scalar_types_df[["bool_col", "int64_col"]]
bf_df = scalar_types_df[["bool_col", "int64_col", "string_col"]]
ops_map = {
"bool_col": agg_ops.AllOp().as_expr("bool_col"),
"int64_col": agg_ops.AllOp().as_expr("int64_col"),
"string_col": agg_ops.AllOp().as_expr("string_col"),
}
sql = _apply_unary_agg_ops(bf_df, list(ops_map.values()), list(ops_map.keys()))

snapshot.assert_match(sql, "out.sql")


def test_all_w_array(repeated_types_df: bpd.DataFrame, snapshot):
col_name = "int_list_col"
bf_df = repeated_types_df[[col_name]]
agg_expr = agg_ops.AllOp().as_expr(col_name)
sql = _apply_unary_agg_ops(bf_df, [agg_expr], [col_name])

snapshot.assert_match(sql, "out.sql")


def test_all_w_window(scalar_types_df: bpd.DataFrame, snapshot):
col_name = "bool_col"
bf_df = scalar_types_df[[col_name]]
Expand All @@ -85,16 +95,26 @@ def test_all_w_window(scalar_types_df: bpd.DataFrame, snapshot):


def test_any(scalar_types_df: bpd.DataFrame, snapshot):
bf_df = scalar_types_df[["bool_col", "int64_col"]]
bf_df = scalar_types_df[["bool_col", "int64_col", "string_col"]]
ops_map = {
"bool_col": agg_ops.AnyOp().as_expr("bool_col"),
"int64_col": agg_ops.AnyOp().as_expr("int64_col"),
"string_col": agg_ops.AnyOp().as_expr("string_col"),
}
sql = _apply_unary_agg_ops(bf_df, list(ops_map.values()), list(ops_map.keys()))

snapshot.assert_match(sql, "out.sql")


def test_any_w_array(repeated_types_df: bpd.DataFrame, snapshot):
col_name = "int_list_col"
bf_df = repeated_types_df[[col_name]]
agg_expr = agg_ops.AnyOp().as_expr(col_name)
sql = _apply_unary_agg_ops(bf_df, [agg_expr], [col_name])

snapshot.assert_match(sql, "out.sql")


def test_any_w_window(scalar_types_df: bpd.DataFrame, snapshot):
col_name = "bool_col"
bf_df = scalar_types_df[[col_name]]
Expand Down
Loading