[
https://issues.apache.org/jira/browse/SPARK-61032?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-61032:
-----------------------------------
Labels: pull-request-available (was: )
> MySQL TopN pushdown over a pushed GROUP BY expression fails under
> ONLY_FULL_GROUP_BY
> ------------------------------------------------------------------------------------
>
> Key: SPARK-61032
> URL: https://issues.apache.org/jira/browse/SPARK-61032
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.4.0
> Reporter: Aleksandar Trajković
> Priority: Major
> Labels: pull-request-available
> Fix For: 3.4.0
>
>
> When an aggregate is pushed to a JDBC source, V2ScanRelationPushDown rewrites
> the sort keys of a
> following ORDER BY ... LIMIT back to the original GROUP BY expressions, and
> JDBCScanBuilder.pushTopN
> pushes them as long as they compile. MySQL's ONLY_FULL_GROUP_BY mode (on by
> default) rejects an
> ORDER BY term that uses a column outside an aggregate unless that column is
> grouped or the term
> equals a GROUP BY expression, so the query fails with
> JDBC_EXTERNAL_ENGINE_SYNTAX_ERROR (MySQL 1055).
> Example (ANSI mode on, MySQL 9.6):
> SELECT k + 1, COUNT(*) FROM mysql.db.t GROUP BY k + 1 ORDER BY (k + 1) *
> 2 LIMIT 10
> is pushed as
> SELECT `k` + 1,COUNT(*) FROM `db`.`t` GROUP BY `k` + 1 ORDER BY (`k` + 1)
> * 2 ASC LIMIT 10
> and fails. An alias shadowing the column (SELECT k + 1 AS k ... ORDER BY k
> * 2) is pushed the same
> way. Without LIMIT the sort stays in Spark and the query succeeds.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
