[
https://issues.apache.org/jira/browse/SPARK-59858?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Luka Zdravic updated SPARK-59858:
---------------------------------
Description:
In SQL ASOF JOIN, a NULL STRUCT operand can match, but a NULL must never match.
{code:sql}
SET spark.sql.join.asofJoin.enabled=true;
SELECT r.tag
FROM VALUES (CAST(NULL AS STRUCT<a: INT>), 'l') AS t(k, tag)
ASOF JOIN VALUES (CAST(NULL AS STRUCT<a: INT>), 'rnull'), (named_struct('a',
1), 'r1') AS r(k, tag)
MATCH_CONDITION (t.k >= r.k);
{code}
Expected: no row. Actual: rnull.
Cause: before comparing, ASOF rebuilds each struct from its fields. A NULL
struct becomes a struct with a NULL field, which is
not NULL, so it matches. A plain WHERE with the same comparison is correct.
This happens for flat and nested structs, with ANSI on and off.
Fix: keep a NULL struct NULL when ASOF rebuilds it.
Assigned to: [~lukazdravic]
Umbrella: SPARK-59526
was:
In SQL ASOF JOIN, a NULL STRUCT operand can match, but a NULL must never match.
{code:sql}
SET spark.sql.join.asofJoin.enabled=true;
SELECT r.tag
FROM VALUES (CAST(NULL AS STRUCT<a: INT>), 'l') AS t(k, tag)
ASOF JOIN VALUES (CAST(NULL AS STRUCT<a: INT>), 'rnull'), (named_struct('a',
1), 'r1') AS r(k, tag)
MATCH_CONDITION (t.k >= r.k);
{code}
Expected: no row. Actual: rnull.
Cause: before comparing, ASOF rebuilds each struct from its fields. A NULL
struct becomes a struct with a NULL field, which is
not NULL, so it matches. A plain WHERE with the same comparison is correct.
This happens for flat and nested structs, with ANSI on and off.
Fix: keep a NULL struct NULL when ASOF rebuilds it.
Umbrella: SPARK-59526
> ASOF JOIN matches NULL STRUCT operands because MATCH_CONDITION rebuilds them
> from their fields
> ----------------------------------------------------------------------------------------------
>
> Key: SPARK-59858
> URL: https://issues.apache.org/jira/browse/SPARK-59858
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Luka Zdravic
> Priority: Minor
>
> In SQL ASOF JOIN, a NULL STRUCT operand can match, but a NULL must never
> match.
> {code:sql}
> SET spark.sql.join.asofJoin.enabled=true;
> SELECT r.tag
> FROM VALUES (CAST(NULL AS STRUCT<a: INT>), 'l') AS t(k, tag)
> ASOF JOIN VALUES (CAST(NULL AS STRUCT<a: INT>), 'rnull'),
> (named_struct('a', 1), 'r1') AS r(k, tag)
> MATCH_CONDITION (t.k >= r.k);
> {code}
> Expected: no row. Actual: rnull.
> Cause: before comparing, ASOF rebuilds each struct from its fields. A NULL
> struct becomes a struct with a NULL field, which is
> not NULL, so it matches. A plain WHERE with the same comparison is correct.
> This happens for flat and nested structs, with ANSI on and off.
> Fix: keep a NULL struct NULL when ASOF rebuilds it.
> Assigned to: [~lukazdravic]
> Umbrella: SPARK-59526
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
