uros-b commented on code in PR #57714:
URL: https://github.com/apache/spark/pull/57714#discussion_r3757352087
##########
sql/core/src/test/scala/org/apache/spark/sql/jdbc/v2/JDBCV2JoinPushdownIntegrationSuiteBase.scala:
##########
@@ -497,6 +495,55 @@ trait JDBCV2JoinPushdownIntegrationSuiteBase
}
}
+ test("Test aggregate with group by on top of join") {
+ val sqlQuery =
+ s"""
+ |SELECT t1.id, t1.address, min(t2.salary)
+ |FROM $catalogAndNamespace.$casedJoinTableName1 t1
+ |JOIN $catalogAndNamespace.$casedJoinTableName2 t2 ON t1.id = t2.id
+ |WHERE t1.amount > 1000
+ |GROUP BY t1.id, t1.address
+ |""".stripMargin
+
+ val rowsNoPushdown = withSQLConf(SQLConf.DATA_SOURCE_V2_JOIN_PUSHDOWN.key
-> "false") {
+ sql(sqlQuery).collect().toSeq
+ }
+
+ assert(rowsNoPushdown.nonEmpty)
+
+ withSQLConf(SQLConf.DATA_SOURCE_V2_JOIN_PUSHDOWN.key -> "true") {
+ val df = sql(sqlQuery)
+ checkJoinPushed(df)
+ checkAggregateRemoved(df, supportsAggregatePushdown)
+ checkAnswer(df, rowsNoPushdown)
+ }
+ }
+
+ test("Test multi-way join with function in join condition") {
Review Comment:
`checkJoinPushed(df)` is called unconditionally, but the `LOWER` function is
NOT pushable in the MsSQL, MySQL, Oracle, and Postgres dialects (their
`supportedFunctions` sets do not include `"LOWER"`, unlike H2). The join on
`LOWER(a.address) = LOWER(d.address)` therefore remains in the optimized plan
as a `Join` node for those dialects, and the assertion `joinNodes.isEmpty`
fails.
The CI `Report test results` check run reports this for four suites:
`MsSqlServerJoinPushdownIntegrationSuite`, `MySQLJoinPushdownIntegrationSuite`,
`OracleJoinPushdownIntegrationSuite`, `PostgresJoinPushdownIntegrationSuite`;
all with `TestFailedException: List(Join Inner, (lower(ADDRESS) =
lower(address))...) was not empty`. Annotation IDs: check-run 92609579381. The
H2-based `JDBCV2JoinPushdownSuite` passes because H2 explicitly includes
`"LOWER"` in its `supportedFunctions`.
Fix options (in preference order): (a) replace the `LOWER`-bearing join
condition with a dialect-agnostic non-equi expression that is pushable across
all dialects (e.g. a string-concatenation equality), or (b) add
`assume(jdbcDialect.isSupportedFunction("LOWER"))` before the `withSQLConf`
pushdown block so the test auto-skips for non-supporting dialects. Option (a)
is preferred since it preserves the "function in join condition" intent without
skipping.
--
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]
