in_ Operator does not work for column type NUMERIC

[pforero]

The command IN UNNEST does not work for columns with type NUMERIC the same way it works with INT64 or FLOAT64.

The in_ operator on python-bigquery-sqlalchemy uses the IN UNNEST(@PARAM_1) syntax. Therefore it is unable to run an in_ operation for columns with the NUMERIC type.

Note: IN (@PARAM_1) works well with NUMERIC. And when compiling with compile_kwargs "literal_binds"=True it uses that syntax. But for normal compile it uses IN UNNEST.

Environment details

• OS type and version: Ubuntu 20.04.6
• Python version: 3.12.4
• pip version: 24.2
• sqlalchemy-bigquery version: 1.11.0

Steps to reproduce

1. Create a sample BigQuery table with different types. Go to the the consoles, editor and run the code example.
2. Run in the console different versions on the test query to see what works and doesn't work.
3. Populate the table with some values.
4. Run an SQLAlchemy query with the in_ operator on the numeric column and it will raise an error.

Code example

--Create table for step 1
CREATE TABLE my_dataset.test_table(
 numeric_col NUMERIC,
 int_col INT64,
 float_col FLOAT64
)

--Tests for syntax that works and doesn't work with numeric
SELECT
 COUNT(*)
FROM my_dataset.test_table`
WHERE
 numeric_col IN UNNEST([1]) -- Doesn't work
 int_col IN UNNEST([1]) -- Works
 numeric_col IN (1) -- Works
 numeric_col IN UNNEST([1.1]) -- Doesn't work
 float_col IN UNNEST([1.1]) -- Works
 numeric_col IN (1.1) -- Works

--Populate the table with some values
INSERT my_dataset.test_table (numeric_col , int_col , float_col)
VALUES(1, 1, 1.1), (2.2, 2, 2.2)

# Set-up the ORM of the table
from sqlalchemy import create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass
class TestTable(Base):
    __tablename__ = "test_table"
    __table_args__ = {"schema": "my_dataset"}
    numeric_col: Mapped[float] = mapped_column(primary_key=True)
    int_col: Mapped[int]
    float_col: Mapped[float]

bq = create_engine("bigquery://")
conn = bq.connect()
session = Session(conn)

stmt_numeric = select(TestTable).where(TestTable.numeric_col.in_([1]))
stmt_int = select(TestTable).where(TestTable.int_col.in_([1]))
stmt_float = select(TestTable).where(TestTable.float_col.in_([1.1]))

session.execute(stmt_int) # Works
session.execute(stmt_float) # Works
session.execute(stmt_numeric) # Doesn't work

Seeing as the syntax

WHERE numeric_col IN (1)

actually works it is counter intuitive seeing it doesn't work with sqlalchame-bigquery.

Thanks!

[r1b]

@pforero As a workaround, does it work if you compare against a python Decimal type? I haven't tried this end to end, but it seems like the problem might be that BigQuery does not attempt to cast array elements when using the x IN UNNEST([...]) form.

As you say, this does not work:

SELECT CAST(1.1 AS NUMERIC) IN UNNEST([1.1])

No matching signature for operator IN UNNEST for argument types: NUMERIC, ARRAY at [1:29]

But this does:

SELECT CAST(1.1 AS NUMERIC) IN UNNEST([CAST(1.1 AS NUMERIC)])

If you use a python Decimal type when constructing the WHERE clause, the parameter will use the NUMERIC type:

from decimal import Decimal
import pytest

import sqlalchemy

@pytest.fixture
def test_table(metadata):
    from sqlalchemy_bigquery import FLOAT64, INT64, NUMERIC

    return sqlalchemy.Table(
        "test_table",
        metadata,
        sqlalchemy.Column("f", FLOAT64),
        sqlalchemy.Column("i", INT64),
        sqlalchemy.Column("n", NUMERIC),
    )


def test_numeric_in_unnest(faux_conn, test_table):
    expr = sqlalchemy.select(test_table.c.n).where(test_table.c.n.in_([Decimal(1)]))

    expected_sql = (
        "SELECT `test_table`.`n` \n"
        "FROM `test_table` \n"
        "WHERE `test_table`.`n` IN UNNEST(%(n_1:NUMERIC)s)"
    )

    actual_sql = expr.compile(
        faux_conn
    ).string

    assert expected_sql == actual_sql

---

Note that it is currently expected that python-bigquery-sqlalchemy rewrites x IN (...) to x IN UNNEST([...]) - looks like the idea there was to avoid chewing up query parameters when the set of values is large.