Aleksandar Trajković created SPARK-61032:
--------------------------------------------
Summary: 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ć
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]
