Yaoxuan Wu created SPARK-60564:
----------------------------------
Summary: LEFT JOIN LATERAL with a generator or Python UDTF and no
ON condition drops left rows (behaves as an inner join)
Key: SPARK-60564
URL: https://issues.apache.org/jira/browse/SPARK-60564
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.1.1, 4.2.0, 3.5.8, 4.0.0, 5.0.0
Reporter: Yaoxuan Wu
{code:python}
from pyspark.sql import functions as F
t = spark.createDataFrame([(1, [1, 2]), (2, []), (3, None)], "k int, a
array<int>")
t.createOrReplaceTempView("t")
spark.sql("SELECT * FROM t LEFT JOIN LATERAL explode(a)").count() # 2,
expected 4
spark.sql("SELECT * FROM t LEFT JOIN LATERAL explode(a) ON TRUE").count() # 4
t.lateralJoin(spark.tvf.explode(F.col("a").outer()), how="left").count() # 2,
expected 4 (4.0+)
{code}
Rows k = 2 (empty array) and k = 3 (NULL array) are dropped instead of being
padded with NULL. The same for {{inline}}, {{posexplode}}, {{stack}} and Python
UDTFs. {{DataFrame.lateralJoin}} has no {{on}} by default, so
{{lateralJoin(tvf, how='left')}} is always affected.
Cause: {{OptimizeOneRowRelationSubquery}} ({{optimizer/subquery.scala}})
matches {{LateralJoin(left, LateralSubquery(OneRowSubquery(plan)), _, None)}},
ignoring the join type. For a {{Generate}} over {{OneRowRelation}} it returns
{{g.copy(generator = ..., child = left)}}, which keeps the generator's {{outer
= false}}, so the LEFT OUTER lateral join becomes an inner {{Generate}}. The
analyzed plan is correct ({{LateralJoin ..., LeftOuter}}); the optimized plan
is {{Generate explode(a#1), false}}. With {{ON TRUE}} the rule does not match
and the result is right. A fix could restrict the {{Generate}} cases to inner
joins, or set {{outer = g.outer || joinType == LeftOuter}}.
Reproduces on 3.5.8, 4.0.0, 4.1.1 and 4.2.0 (master has the same code).
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
