[
https://issues.apache.org/jira/browse/SPARK-43718?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bruce Robbins updated SPARK-43718:
----------------------------------
Description:
Assume this data:
{noformat}
create or replace temp view t1 as values (1), (2), (3) as (c1);
create or replace temp view t2 as values (2), (3), (4) as (c1);
{noformat}
The following query produces incorrect results:
{noformat}
spark-sql (default)> select explode(array(t1.c1, t2.c1)) as x1
from t1
full outer join t2
using (c1);
1
-1 <== should be null
2
2
3
3
-1 <== should be null
4
Time taken: 0.663 seconds, Fetched 8 row(s)
spark-sql (default)>
{noformat}
Similar issues occur with right outer join and left outer join.
{{t1.c1}} and {{t2.c1}} have the wrong nullability at the time the array is
resolved, so the array's {{containsNull}} value is incorrect.
Queries that don't use arrays also can get wrong results. Assume this data:
{noformat}
create or replace temp view t1 as values (0), (1), (2) as (c1);
create or replace temp view t2 as values (1), (2), (3) as (c1);
create or replace temp view t3 as values (1, 2), (3, 4), (4, 5) as (a, b);
{noformat}
The following query produces incorrect results:
{noformat}
select t1.c1 as t1_c1, t2.c1 as t2_c1, b
from t1
full outer join t2
using (c1),
lateral (
select b
from t3
where a = coalesce(t2.c1, 1)
) lt3;
1 1 2
NULL 3 4
Time taken: 2.395 seconds, Fetched 2 row(s)
spark-sql (default)>
{noformat}
The result should be the following:
{noformat}
0 NULL 2
1 1 2
NULL 3 4
{noformat}
was:
Assume this data:
{noformat}
create or replace temp view t1 as values (1), (2), (3) as (c1);
create or replace temp view t2 as values (2), (3), (4) as (c1);
{noformat}
The following query produces incorrect results:
{noformat}
spark-sql (default)> select explode(array(t1.c1, t2.c1)) as x1
from t1
full outer join t2
using (c1);
1
-1 <== should be null
2
2
3
3
-1 <== should be null
4
Time taken: 0.663 seconds, Fetched 8 row(s)
spark-sql (default)>
{noformat}
Similar issues occur with right outer join and left outer join.
{{t1.c1}} and {{t2.c1}} have the wrong nullability at the time the array is
resolved, so the array's {{containsNull}} value is incorrect.
> References to a specific side's key in a USING join can have wrong nullability
> ------------------------------------------------------------------------------
>
> Key: SPARK-43718
> URL: https://issues.apache.org/jira/browse/SPARK-43718
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 3.3.2, 3.4.0, 3.5.0
> Reporter: Bruce Robbins
> Priority: Major
> Labels: correctness
>
> Assume this data:
> {noformat}
> create or replace temp view t1 as values (1), (2), (3) as (c1);
> create or replace temp view t2 as values (2), (3), (4) as (c1);
> {noformat}
> The following query produces incorrect results:
> {noformat}
> spark-sql (default)> select explode(array(t1.c1, t2.c1)) as x1
> from t1
> full outer join t2
> using (c1);
> 1
> -1 <== should be null
> 2
> 2
> 3
> 3
> -1 <== should be null
> 4
> Time taken: 0.663 seconds, Fetched 8 row(s)
> spark-sql (default)>
> {noformat}
> Similar issues occur with right outer join and left outer join.
> {{t1.c1}} and {{t2.c1}} have the wrong nullability at the time the array is
> resolved, so the array's {{containsNull}} value is incorrect.
> Queries that don't use arrays also can get wrong results. Assume this data:
> {noformat}
> create or replace temp view t1 as values (0), (1), (2) as (c1);
> create or replace temp view t2 as values (1), (2), (3) as (c1);
> create or replace temp view t3 as values (1, 2), (3, 4), (4, 5) as (a, b);
> {noformat}
> The following query produces incorrect results:
> {noformat}
> select t1.c1 as t1_c1, t2.c1 as t2_c1, b
> from t1
> full outer join t2
> using (c1),
> lateral (
> select b
> from t3
> where a = coalesce(t2.c1, 1)
> ) lt3;
> 1 1 2
> NULL 3 4
> Time taken: 2.395 seconds, Fetched 2 row(s)
> spark-sql (default)>
> {noformat}
> The result should be the following:
> {noformat}
> 0 NULL 2
> 1 1 2
> NULL 3 4
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
