[BUG]: is_unique checks with composite unique columns marks all-NULL values as not unique

[ovidiu-eremia]

For validating uniqueness on composite columns constraints, DQX proposes the use of pyspark.sql.functions.struct to package the composing columns into a single struct with those columns as struct fields:

https://github.com/databrickslabs/dqx/blob/022fdb5b520139b5dec9404bc771006c6ab16255/docs/dqx/docs/reference/quality_rules.mdx#is_unique-for-multiple-columns-composite-key

But this doesn't follow the sql standard of ignoring rows with NULL values for some column's values part of the unique constraint.

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

def test_col_is_unique_handle_nulls_in_composite_cols(spark: SparkSession) -> None:
"""Test shows that is_unique with composite struct doesn't ignore null inner fields.

The SQL standard states that:
'A unique constraint on T is satisfied if and only if there do not exist two rows R1
and R2 of T such that R1 and R2 have the same non-NULL values in the unique cols.'

Applying this statement to the particular case of two rows with NULL values means:

# given the table
T = [Row(a=None, b=None, c=None),
        Row(a=None, b=None, c=None)]

# when we run is unique with the composite struct of the unique columns
is_unique(struct('b', 'c'))

# then we get that the unique constraint is not satisfied
Value '{null, null}' in Column 'struct(b, c)' is not unique
Value '{null, null}' in Column 'struct(b, c)' is not unique

So this test shows that we can't have two rows with only NULL values.

Args:
    spark (SparkSession): The spark session needed for context

"""
test_df = spark.createDataFrame(
    data=[
        [1, "", None],
        [2, "", None],
        [3, "str1", 1],
        [4, "str1", 1],
        [None, None, None],
        [None, None, None],
    ],
    schema="a: int, b: string, c:int",
)

actual = test_df.select(is_unique(f.col("a")), is_unique(f.struct("b", "c")))

checked_schema = "a_is_not_unique: string, struct_b_c_is_not_unique: string"
expected = spark.createDataFrame(
    [
        [None, "Value '{, null}' in Column 'struct(b, c)' is not unique"],
        [None, "Value '{, null}' in Column 'struct(b, c)' is not unique"],
        [None, "Value '{str1, 1}' in Column 'struct(b, c)' is not unique"],
        [None, "Value '{str1, 1}' in Column 'struct(b, c)' is not unique"],
        [None, "Value '{null, null}' in Column 'struct(b, c)' is not unique"],
        [None, "Value '{null, null}' in Column 'struct(b, c)' is not unique"],
    ],
    checked_schema,
)
assertDataFrameEqual(actual, expected)

Expected Behavior

def test_col_is_unique_handle_nulls_in_composite_cols(spark: SparkSession) -> None:

test_df = spark.createDataFrame(
    data=[
        [1, "", None],
        [2, "", None],
        [3, "str1", 1],
        [4, "str1", 1],
        [None, None, None],
        [None, None, None],
    ],
    schema="a: int, b: string, c:int",
)

actual = test_df.select(is_unique(f.col("a")), is_unique("b", "c"))

checked_schema = "a_is_not_unique: string, b_c_is_not_unique: string"
expected = spark.createDataFrame(
    [
        [None, None],
        [None, None],
        [None, "Value 'str1, 1' in Column 'b, c' is not unique"],
        [None, "Value 'str1, 1' in Column 'b, c' is not unique"],
        [None, None],
        [None, None],
    ],
    checked_schema,
)
assertDataFrameEqual(actual, expected)

Steps To Reproduce

1. Add this test to the test suite
2. Run the test suite.

Cloud

AWS

Operating System

Linux

Relevant log output

[mwojtyczka]

Thank you for reporting this. We will work on a fix for composite keys.

[mwojtyczka]

In standard SQL (ANSI), the unique constraint does not treat NULLs as duplicates. In your example:

[1, "", None],
[2, "", None],

the rows should not be treated as duplicate for composite key (struct("b", "c")).

Postgres introduced the option NULLS NOT DISTINCT to change the default behaviour. This makes the database treat NULLs as equal in the context of the unique constraint.

The default behaviour:
(1, NULL) not equals (1, NULL)
(NULL, NULL) not equals (NULL, NULL)

With NULLS NOT DISTINCT:
(1, NULL) equals (1, NULL)
(NULL, NULL) equals (NULL, NULL)

We should probably follow the default behaviour and have an option to change it.

[ovidiu-eremia]

TL;DR: Thank you for your response. The expected behavior in the second test was indeed wrong for the partially null composite unique constraint values. I have corrected it.

Explanation
I've done some research and found that indeed, in the SQL standard, a composite unique constraint is not violated if two or more rows have identical values in the non-null columns but contain NULL in at least one of the other columns of the unique key.

But because databases platforms implement this in different ways with respect to the NULL behavior, the SQL Standard introduced in 2023 the UNIQUE NULLS [NOT] DISTINCT clause, which allows developers to explicitly declare how NULLs should be treated within a unique constraint, providing a standardized way to achieve the desired behavior across different platforms.

For reference, this page explains the new NULLS NOT DISTINCT clause introduced in the SQL:2023 standard:

https://modern-sql.com/caniuse/unique-nulls-not-distinct
https://modern-sql.com/caniuse/unique-nulls-distinct

PS: The SQL standard itself is not freely available without a paid subscription, thus I can't link it.

[mwojtyczka]

Thanks for the research. We will implement standard behaviour and provide null_not_distinct option. The current implementation works the same as null_not_distinct.