[BUG] Column pair and multicolumn Expectations pass rows containing NULL on SQL backends

Describe the bug

On SQL data sources, a column pair or multicolumn Expectation treats any row where the condition evaluates to NULL as passing. So a row with a NULL in one of the compared columns passes even when it clearly doesn't meet the expectation. The same data fails on pandas.

The unexpected condition is built as sa.not_(expected_condition) in column_pair_condition_partial.py:192 and multicolumn_condition_partial.py:190. When the condition is NULL (e.g. 1 + NULL + 100 = 7), NOT NULL is still NULL, and the unexpected count's CASE WHEN ... THEN 1 ELSE 0 counts the row as 0.

Affected (confirmed): ExpectMulticolumnSumToEqual, ExpectColumnPairValuesAToBeGreaterThanB (with or without or_equal), ExpectColumnPairValuesToBeInSet.

Expected: these rows are counted as unexpected, same as on pandas.

Great Expectations version

1.23.1

Python version

3.13.3

Execution engine / data source

SQLite

If "Other", name the execution engine or data source

No response

Minimal reproduction

Load the same two-row table into a pandas asset and a SQLite asset.
Validate the Expectation. One row passes, the other has a NULL and should fail.
pandas reports 1 unexpected row, SQLite reports 0 and success=True.

import tempfile
from pathlib import Path

import pandas as pd
import sqlalchemy as sa

import great_expectations as gx
import great_expectations.expectations as gxe

cases = {
    "multicolumn_sum": (
        pd.DataFrame({"a": [1, 1], "b": [6, None], "c": [0, 100]}),
        gxe.ExpectMulticolumnSumToEqual(column_list=["a", "b", "c"], sum_total=7),
    ),
    "pair_greater": (
        pd.DataFrame({"a": [9.0, None], "b": [1.0, 5.0]}),
        gxe.ExpectColumnPairValuesAToBeGreaterThanB(column_A="a", column_B="b"),
    ),
    "pair_in_set": (
        pd.DataFrame({"a": ["x", "x"], "b": ["y", None]}),
        gxe.ExpectColumnPairValuesToBeInSet(column_A="a", column_B="b", value_pairs_set=[("x", "y")]),
    ),
}

context = gx.get_context(mode="ephemeral")
db = Path(tempfile.mkdtemp()) / "repro.db"
engine = sa.create_engine(f"sqlite:///{db}")
pandas_bd = context.data_sources.add_pandas("pd").add_dataframe_asset("df").add_batch_definition_whole_dataframe("all")
sqlite_ds = context.data_sources.add_sqlite("sqlite", connection_string=f"sqlite:///{db}")

for name, (df, expectation) in cases.items():
    df.to_sql(name, engine, index=False)
    sql_batch = sqlite_ds.add_table_asset(name, table_name=name).add_batch_definition_whole_table("all").get_batch()
    pd_batch = pandas_bd.get_batch(batch_parameters={"dataframe": df})
    p, s = pd_batch.validate(expectation), sql_batch.validate(expectation)
    print(f"{name}: pandas success={p.success} unexpected={p.result['unexpected_count']} | "
          f"sqlite success={s.success} unexpected={s.result['unexpected_count']}")

# sample data (inline above)
multicolumn_sum:  a=[1, 1]       b=[6, NULL]     c=[0, 100]   sum_total=7
pair_greater:     a=[9.0, NULL]  b=[1.0, 5.0]
pair_in_set:      a=["x", "x"]   b=["y", NULL]   value_pairs_set=[("x", "y")]

# output (no exception, SQLite result is wrong)
multicolumn_sum: pandas success=False unexpected=1 | sqlite success=True unexpected=0
pair_greater: pandas success=False unexpected=1 | sqlite success=True unexpected=0
pair_in_set: pandas success=False unexpected=1 | sqlite success=True unexpected=0

Additional environment details

Windows 11, pandas 3.0.6, SQLAlchemy 2.0.54. Also reproduces on develop (ca1c660).

Additional context

Only tested on SQLite, but the condition is built in dialect-independent code so other SQL backends are probably affected too. The existing integration tests for these Expectations don't include rows with a partial NULL.

Thanks for the clear report — the file and line pointers made this quick to confirm. I reproduced it on develop (ca1c660c6) on SQLite and on PostgreSQL 18, and opened #12255.

The fix is in the three metrics rather than the two partials. I tried the partials first, as a NULL-safe negation: it fixes these three Expectations, but the same partials serve ExpectSelectColumnValuesToBeUniqueWithinRecord, where pandas lets a NULL row pass, and that change made it disagree. So each metric now requires its compared columns to be present — the pattern ExpectColumnPairValuesToBeEqual already uses — and a None in value_pairs_set still matches a NULL row.

Comparing pandas and SQL across all 27 pair/multicolumn cases, disagreements go from 12 to 3 on both databases, and no case that agreed before changes. The PR lists the 3 that remain and why they're separate: ExpectCompoundColumnsToBeUnique works a different way, and there's a deliberate both-NULL rule for or_equal. It also notes that Spark likely has the same issue in two of these metrics — I couldn't run Spark here, so I left it alone.

(This change was developed with AI assistance.)

@rachithga

thanks for reporting this bug @rachithga! and thanks for the PR @adimalkar - I'll assign this issue to you, and review shortly.

One more Expectation has the same problem: ExpectCompoundColumnsToBeUnique. On SQL, a duplicated key that has a NULL in one of its columns isn't counted as unexpected. I reproduced it on develop (8d89627f5) against SQLite, and it still fails with #12255 applied.

Reproduction

Add to tests/integration/data_sources_and_expectations/expectations/test_expect_compound_column_values_to_be_unique.py:

@parameterize_batch_for_data_sources(
    data_source_configs=[SqliteDatasourceTestConfig()],
    data=pd.DataFrame({"a": [1, 1, 5, None, None], "b": [1, 1, 3, 5, 5]}),
)
def test_partial_null_duplicates_are_unexpected(batch_for_datasource: Batch) -> None:
    expectation = gxe.ExpectCompoundColumnsToBeUnique(column_list=["a", "b"])
    result = batch_for_datasource.validate(expectation)
    assert result.result["unexpected_count"] == 4

pytest tests/integration/data_sources_and_expectations/expectations/test_expect_compound_column_values_to_be_unique.py::test_partial_null_duplicates_are_unexpected -m sqlite

Observed: assert 2 == 4 (the two (None, 5) rows are not counted)

Expected: unexpected_count == 4, which is what pandas reports for the same data.

Requirements

When a row has a NULL in some, but not all, of the compared columns, ExpectMulticolumnSumToEqual, ExpectColumnPairValuesAToBeGreaterThanB, and ExpectColumnPairValuesToBeInSet must count it as unexpected on SQL backends, matching pandas under the default ignore_row_if.
For ExpectCompoundColumnsToBeUnique, rows whose key includes a NULL must count as duplicates of each other when the non-NULL values match, as pandas does.
Rows dropped by ignore_row_if (all values missing, or any value missing with any_value_is_missing) must stay excluded.

Out of scope: Spark, which uses separate code for these metrics and wasn't checked here.

Context

The unexpected rows come from a self-join on group_count_query.c[name] == original_table_clause.c[name] (compound_columns_unique.py:144). NULL = NULL never matches, so a duplicated key with a NULL in it drops out of the count.
In earlier testing on other backends, PostgreSQL, SQL Server and ClickHouse undercounted the same way. MySQL did not: it takes a separate code path (compound_columns_unique.py:105).

Thanks for the reproduction. I've pushed the fix to #12255: ExpectCompoundColumnsToBeUnique now counts each key with COUNT(*) OVER (PARTITION BY ...) on every dialect, the query the MySQL branch already used. PARTITION BY groups NULLs together, so your test now gives 4 on SQLite and PostgreSQL.

The new integration test also covers requirement 3: with two (None, 5) and two (None, None) rows it expects 4, 2 and 6 unexpected under all_values_are_missing, any_value_is_missing and never, and it matches pandas on each. I re-ran the pandas-vs-SQL comparison across every pair and multicolumn Expectation. Differences drop from 11 to 1 on both SQLite and PostgreSQL, with none newly introduced. The remaining one is the both-NULL or_equal=True case described in the PR. I couldn't run SQL Server or ClickHouse locally.

Hi! I noticed the PR description mentions the Spark side was left untouched due to no local Spark environment.
I've reproduced the same NULL-handling inconsistency on Spark locally and have a fix ready

multicolumn_sum_equal._spark: removes the COALESCE(col, 0) substitution and adds col IS NOT NULL guards, so a row with a NULL operand is counted as unexpected (matching pandas skipna=False behavior)
column_pair_values_greater._spark: adds both_present = A.isNotNull() & B.isNotNull() to both the or_equal=True and or_equal=False paths
Would it be okay if I opened a follow-up PR for the Spark fix? Happy to rebase on top of this one once it merges.

@adimalkar

⏰ Assignment Reminder

Hi @adimalkar, this is a friendly reminder about your assignment to this issue.

Warning

This issue will be automatically unassigned in 4 days if there's no activity.

How to keep your assignment

You can prevent automatic unassignment by:

Making progress on the issue (commenting, submitting PR)
Asking for the pinned label if you need more time
Letting maintainers know if you're still working on it

We appreciate your contribution and are here to help if needed!

@joshua-stauffer

@joshua-stauffer I'm still maintaining this fix. I've updated #12255 with the latest develop (7d52570c9); the merge was conflict-free and preserves the NULL-handling fixes, including the compound-key case you requested.

After the update, local validation passed:

133 tests across pandas, SQLite and PostgreSQL in all seven pair/multicolumn test files
69 tests selected by the unit marker in those files
49 checkpoint/metric-provider tests
invoke lint, invoke fmt --check, and invoke type-check --ci --pretty (971 source files)

Could you rerun the full CI with maintainer permissions and review the PR when available? The external-contributor check-actor-permissions gate prevents the upstream test jobs from running.

Navigation Menu

Repository navigation

[BUG] Column pair and multicolumn Expectations pass rows containing NULL on SQL backends #12252

Describe the bug

Great Expectations version

Python version

Execution engine / data source

If "Other", name the execution engine or data source

Minimal reproduction

Additional environment details

Additional context

Reproduction

Requirements

Context

⏰ Assignment Reminder

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[BUG] Column pair and multicolumn Expectations pass rows containing NULL on SQL backends #12252

Description

Describe the bug

Great Expectations version

Python version

Execution engine / data source

If "Other", name the execution engine or data source

Minimal reproduction

Additional environment details

Additional context

Activity

adimalkar commented on Sep 23, 2026

joshua-stauffer commented on Sep 29, 2026

joshua-stauffer commented on Sep 29, 2026

Reproduction

Requirements

Context

adimalkar commented on Sep 29, 2026

dbsgh3344 commented on Oct 1, 2026

github-actions commented on Oct 4, 2026

⏰ Assignment Reminder

adimalkar commented on Oct 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions