Skip to content

Commit b5e6db9

Browse files
hhr293guowangyzhulipeng
committed
[SPARK-41416][SQL] Cover the A2 outputRemap path, remaining allowed types, and LocalRelation self-joins
Follow-up on the review of #58424, addressing the remaining non-blocking items from cloud-fan's latest APPROVE. Tests: * Extend "Pattern A': direct InSubquery self-join is rewritten" to loop the same query over a real Parquet table and a VALUES-backed temp view, so the LocalRelation branch of `isRowBagRepeatable` / `sameLeafSnapshotIdentity` -- a documented supported leaf -- gets an actual firing case instead of appearing only as an unrelated outer-query companion (e.g. `D` in the Pattern A2 tests). * Extend "Pattern A2: nested self-join is rewritten" so the wrapper Project aliases both self-join columns and the top-level Project selects the sjRight-derived one, forcing the `outputRemap` / `remapNamedExpressionAttributes` path in `rewriteNestedSelfJoin`. Every other A2 test's top-level Project only ever selected an unrelated column from the outer relation, so that path was never exercised. * Add one new test, positive firing + rewrite-on/off parity, for the allowed type families that had none: Decimal, Boolean, Date, Timestamp, TimestampNTZ, Binary. Using the same type for both the equi-key and the neq column in one query exercises `isSafeEquiKeyType` and `isSafeNeqColumnType` together per type. Comment: * Fix `createTable`'s docstring, which still claimed a VALUES temp view "cannot be used" for a self-joined fixture -- contradicting the class-level comment above it, which already explains that a VALUES-backed `LocalRelation` canonicalizes correctly too and is simply not the shape this rule targets. No production code changes; sql/testOnly *RewriteSelfJoinInequalityToAggregateSuite passes (38/38). Co-authored-by: Guo Wangyang <[email protected]> Co-authored-by: Lipeng Zhu <[email protected]> Signed-off-by: hengrui hu <[email protected]>
1 parent 36bc398 commit b5e6db9

1 file changed

Lines changed: 74 additions & 19 deletions

File tree

‎sql/core/src/test/scala/org/apache/spark/sql/execution/RewriteSelfJoinInequalityToAggregateSuite.scala‎

Lines changed: 74 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -106,8 +106,8 @@ class RewriteSelfJoinInequalityToAggregateSuite extends QueryTest with SharedSpa
106106
}
107107

108108
/**
109-
* A real table, so that a self-join of it dedups into two structurally identical sides. See the
110-
* class comment for why a temp view over VALUES cannot be used for a self-joined fixture.
109+
* A real table, mirroring the Parquet-backed shape this rule targets. See the class comment for
110+
* why a self-joined fixture need not be a real table.
111111
*/
112112
private def createTable(name: String, schema: String, values: String): Unit = {
113113
spark.sql(s"DROP TABLE IF EXISTS $name")
@@ -159,21 +159,37 @@ class RewriteSelfJoinInequalityToAggregateSuite extends QueryTest with SharedSpa
159159

160160
// ==================== Positive: rewrite fires and is semantically equivalent ===============
161161

162-
test("Pattern A': direct InSubquery self-join is rewritten") {
162+
test("Pattern A': direct InSubquery self-join is rewritten (real table and LocalRelation)") {
163+
// LocalRelation is a documented supported leaf with no other firing test; loop the same
164+
// query over T and an equivalent VALUES-backed temp view.
163165
withTable("T") {
164-
setupTable()
165-
val sql =
166-
"""SELECT k FROM T outer_t WHERE k IN (
167-
| SELECT s1.k FROM T s1 JOIN T s2
168-
| ON s1.k = s2.k AND s1.v <> s2.v)""".stripMargin
166+
withTempView("TV") {
167+
setupTable()
168+
spark.sql(
169+
"""CREATE OR REPLACE TEMP VIEW TV AS SELECT * FROM VALUES
170+
| (1, 10), (1, 10), (1, 20),
171+
| (2, 30),
172+
| (3, 40), (3, 50), (3, 60),
173+
| (4, 70), (4, CAST(NULL AS INT)),
174+
| (5, CAST(NULL AS INT)), (5, CAST(NULL AS INT)),
175+
| (6, 80), (6, 90), (6, CAST(NULL AS INT)),
176+
| (7, 100), (7, 100) AS TV(k, v)""".stripMargin)
177+
178+
Seq("T", "TV").foreach { rel =>
179+
val sql =
180+
s"""SELECT k FROM $rel outer_t WHERE k IN (
181+
| SELECT s1.k FROM $rel s1 JOIN $rel s2
182+
| ON s1.k = s2.k AND s1.v <> s2.v)""".stripMargin
169183

170-
assertRuleFired(sql)
171-
assertMinMaxRewriteShape(optimizedPlanWith(sql, rewrite = true))
172-
val (on, off) = runBoth(sql)
173-
assert(on == off, s"rewrite ON $on != OFF $off")
174-
// `setupTable()` seeds duplicate-only groups and NULL neq values, so this also pins the neq
175-
// 3VL: k=4 (v={70,NULL}) and k=5 (v={NULL,NULL}) do not satisfy SQL `<>`, leaving {1,3,6}.
176-
assert(on == Set(Row(1), Row(3), Row(6)), s"expected {1,3,6}, got $on")
184+
assertRuleFired(sql)
185+
assertMinMaxRewriteShape(optimizedPlanWith(sql, rewrite = true))
186+
val (on, off) = runBoth(sql)
187+
assert(on == off, s"$rel rewrite ON $on != OFF $off")
188+
// Duplicate-only groups and NULL neq values also pin the neq 3VL: k=4 (v={70,NULL}) and
189+
// k=5 (v={NULL,NULL}) do not satisfy `<>`, leaving {1,3,6}.
190+
assert(on == Set(Row(1), Row(3), Row(6)), s"$rel: expected {1,3,6}, got $on")
191+
}
192+
}
177193
}
178194
}
179195

@@ -217,19 +233,21 @@ class RewriteSelfJoinInequalityToAggregateSuite extends QueryTest with SharedSpa
217233
}
218234
}
219235

220-
test("Pattern A2: nested self-join is rewritten") {
236+
test("Pattern A2: nested self-join is rewritten, incl. a sjRight top-Project remap") {
221237
withTable("T") {
222238
withTempView("D") {
239+
// Selecting `k2`, originally derived from sjRight, exercises sjRight -> sjLeft
240+
// rebinding in canonicalizeWrapper and the rebuilt output in the top Project.
223241
setupTable()
224242
spark.sql(
225243
"""CREATE OR REPLACE TEMP VIEW D AS SELECT * FROM VALUES
226244
| (1), (3), (6) AS D(k)""".stripMargin)
227245
val sql =
228246
"""SELECT k FROM T outer_t WHERE k IN (
229-
| SELECT d.k
230-
| FROM D d, (SELECT s1.k FROM T s1 JOIN T s2
247+
| SELECT sj.k2
248+
| FROM D d, (SELECT s1.k AS k1, s2.k AS k2 FROM T s1 JOIN T s2
231249
| ON s1.k = s2.k AND s1.v <> s2.v) sj
232-
| WHERE d.k = sj.k)""".stripMargin
250+
| WHERE d.k = sj.k1)""".stripMargin
233251

234252
assertRuleFired(sql)
235253
assertMinMaxRewriteShape(optimizedPlanWith(sql, rewrite = true))
@@ -1230,6 +1248,43 @@ class RewriteSelfJoinInequalityToAggregateSuite extends QueryTest with SharedSpa
12301248
}
12311249
}
12321250

1251+
test("Every remaining allowed type family fires as both equi-key and neq column") {
1252+
// Int and String are covered elsewhere; these have no firing test at all. Same type for k
1253+
// and v exercises isSafeEquiKeyType and isSafeNeqColumnType together.
1254+
case class TypeCase(sqlType: String, lo: String, hi: String)
1255+
val cases = Seq(
1256+
TypeCase("DECIMAL(10,2)", "100.50", "200.75"),
1257+
TypeCase("BOOLEAN", "true", "false"),
1258+
TypeCase("DATE", "DATE'2024-01-01'", "DATE'2024-06-01'"),
1259+
TypeCase("TIMESTAMP", "TIMESTAMP'2024-01-01 00:00:00'", "TIMESTAMP'2024-06-01 00:00:00'"),
1260+
TypeCase(
1261+
"TIMESTAMP_NTZ",
1262+
"CAST(TIMESTAMP'2024-01-01 00:00:00' AS TIMESTAMP_NTZ)",
1263+
"CAST(TIMESTAMP'2024-06-01 00:00:00' AS TIMESTAMP_NTZ)"),
1264+
TypeCase("BINARY", "X'AA'", "X'BB'"))
1265+
1266+
cases.foreach { c =>
1267+
withTable("TTypes") {
1268+
createTable(
1269+
"TTypes",
1270+
s"k ${c.sqlType}, v ${c.sqlType}",
1271+
s""" (${c.lo}, ${c.lo}), (${c.lo}, ${c.hi}),
1272+
| (${c.hi}, ${c.lo})""".stripMargin)
1273+
// Project a constant, not `k`: `k` still exercises the equi-key gate and `v` the
1274+
// neq-column gate, while keeping the raw value under test (e.g. BINARY's Array[Byte])
1275+
// out of runBoth's Set[Row].
1276+
val sql =
1277+
"""SELECT 1 AS matched FROM TTypes outer_t WHERE k IN (
1278+
| SELECT s1.k FROM TTypes s1 JOIN TTypes s2
1279+
| ON s1.k = s2.k AND s1.v <> s2.v)""".stripMargin
1280+
assertRuleFired(sql)
1281+
val (on, off) = runBoth(sql)
1282+
assert(on == off, s"${c.sqlType} control diverges: ON=$on OFF=$off")
1283+
assert(on == Set(Row(1)), s"${c.sqlType} unexpected result: $on")
1284+
}
1285+
}
1286+
}
1287+
12331288
test("String neq/equi key: default (UTF8_BINARY) fires, non-binary collation fails closed") {
12341289
withTable("TStrBin", "TStrCiNeq", "TStrCiEqui") {
12351290
// On strings the two type gates diverge: an equi key is admitted only under binary equality

0 commit comments

Comments
 (0)