LukaZdravic commented on code in PR #59134:
URL: https://github.com/apache/spark/pull/59134#discussion_r4167447699
##########
sql/core/src/test/scala/org/apache/spark/sql/AsOfJoinSortMergeSQLSuite.scala:
##########
@@ -440,6 +448,117 @@ class AsOfJoinSortMergeSQLSuite extends QueryTest
Row(Timestamp.valueOf("2026-06-29 10:03:00"), 1, "v1.1")))
}
+ test("NULL whole STRUCT column MATCH_CONDITION never matches") {
+ // A NULL struct must never match, the same as a NULL scalar operand.
+ for {
+ (nullStruct, one, five) <- Seq(
+ ("CAST(NULL AS STRUCT<a: INT>)", "named_struct('a', 1)",
"named_struct('a', 5)"),
+ ("CAST(NULL AS STRUCT<a: INT, b: INT>)",
+ "named_struct('a', 1, 'b', 1)", "named_struct('a', 5, 'b', 5)"),
+ ("CAST(NULL AS STRUCT<e: STRUCT<a: INT>>)",
+ "named_struct('e', named_struct('a', 1))", "named_struct('e',
named_struct('a', 5))"),
+ // Non-NULL values of this type are all equal.
+ ("CAST(NULL AS STRUCT<e: STRUCT<>>)",
+ "named_struct('e', named_struct())", "named_struct('e',
named_struct())"))
+ ansiEnabled <- Seq(true, false)
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled.toString) {
+ def asOfTags(joinType: String, left: String, op: String, rights:
String): DataFrame =
+ sql(
+ s"""
+ |SELECT r.tag
+ |FROM VALUES ($left, 'l') AS t(k, tag)
+ |$joinType ASOF JOIN VALUES $rights AS r(k, tag)
+ | MATCH_CONDITION (t.k $op r.k)
+ |""".stripMargin)
+ checkSortMergeAsOf(
+ asOfTags("", nullStruct, ">=", s"($nullStruct, 'rnull'), ($one,
'r1')"), Nil)
+ checkSortMergeAsOf(asOfTags("", nullStruct, "<=", s"($one, 'r1')"),
Nil)
+ checkSortMergeAsOf(asOfTags("", five, ">=", s"($nullStruct,
'rnull')"), Nil)
+ // LEFT ASOF JOIN keeps the NULL left row with NULL right columns.
+ checkSortMergeAsOf(
+ asOfTags("LEFT", nullStruct, ">=", s"($nullStruct, 'rnull'), ($one,
'r1')"),
+ Row(null) :: Nil)
+ }
+ }
+ }
+
+ test("nested STRUCT column MATCH_CONDITION keeps a NULL inner struct apart
from NULL fields") {
+ // {e: NULL} sorts before {e: {a: NULL}}, as in a plain comparison.
+ val eNull = "named_struct('e', CAST(NULL AS STRUCT<a: INT>))"
+ val aNull = "named_struct('e', named_struct('a', CAST(NULL AS INT)))"
+ for {
+ (left, op, right) <- Seq((aNull, ">", eNull), (eNull, "<", aNull))
+ ansiEnabled <- Seq(true, false)
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled.toString) {
+ checkSortMergeAsOf(
+ sql(
+ s"""
+ |SELECT r.tag
+ |FROM VALUES ($left, 'l') AS t(k, tag)
+ |ASOF JOIN VALUES ($right, 'r') AS r(k, tag)
+ | MATCH_CONDITION (t.k $op r.k)
+ |""".stripMargin),
+ Row("r") :: Nil)
+ }
+ }
+ }
+
+ test("tuple vs nullable STRUCT column MATCH_CONDITION: a NULL struct does
not end the scan") {
+ // A NULL struct and {NULL, NULL} have the same fields, so a sort by
fields ties them. Both
+ // input orders must pick r11: a NULL row after a match must not stop the
backward scan.
+ val allNull = "(named_struct('a', CAST(NULL AS INT), 'b', CAST(NULL AS
INT)), 'allnull')"
+ val nullStruct = "(CAST(NULL AS STRUCT<a: INT, b: INT>), 'rnull')"
+ val r11 = "(named_struct('a', 1, 'b', 1), 'r11')"
+ for {
+ rights <- Seq(s"$allNull, $nullStruct, $r11", s"$nullStruct, $allNull,
$r11")
+ ansiEnabled <- Seq(true, false)
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled.toString) {
+ withOrderedRightView(rights, "s, tag") {
+ checkSortMergeAsOf(
+ sql(
+ """
+ |SELECT r.tag
+ |FROM VALUES (1, 2) AS t(a, b)
+ |ASOF JOIN r
+ | MATCH_CONDITION ((t.a, t.b) >= r.s)
+ |""".stripMargin),
+ Row("r11") :: Nil)
+ }
+ }
+ }
+ }
+
+ test("tuple with a nullable inner STRUCT MATCH_CONDITION picks the closest
row") {
+ // NULL and {a: NULL} have the same field, but the comparison ranks NULL
lower. Both input
+ // orders must pick the row equal to the left one, in both directions.
+ val aNull = "named_struct('a', CAST(NULL AS INT))"
+ val nullA = "CAST(NULL AS STRUCT<a: INT>)"
+ val rAnull = s"(1, $aNull, 'r_anull')"
+ val rNull = s"(1, $nullA, 'r_null')"
+ for {
+ rights <- Seq(s"$rAnull, $rNull", s"$rNull, $rAnull")
+ ansiEnabled <- Seq(true, false)
+ } {
+ withSQLConf(SQLConf.ANSI_ENABLED.key -> ansiEnabled.toString) {
+ withOrderedRightView(rights, "x, s, tag") {
+ def asOfTag(left: String, op: String): DataFrame =
+ sql(
+ s"""
+ |SELECT r.tag
+ |FROM VALUES (1, $left) AS t(x, s)
+ |ASOF JOIN r
+ | MATCH_CONDITION ((t.x, t.s) $op (r.x, r.s))
+ |""".stripMargin)
+ checkSortMergeAsOf(asOfTag(aNull, ">="), Row("r_anull") :: Nil)
+ checkSortMergeAsOf(asOfTag(nullA, "<="), Row("r_null") :: Nil)
+ }
+ }
+ }
+ }
+
Review Comment:
Added the missing test, thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
