[
https://issues.apache.org/jira/browse/SPARK-60450?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18130146#comment-18130146
]
Eric Yang commented on SPARK-60450:
-----------------------------------
I'm working on a fix.
> Wrong results filtering collated GROUP BY/window keys with another collation
> ----------------------------------------------------------------------------
>
> Key: SPARK-60450
> URL: https://issues.apache.org/jira/browse/SPARK-60450
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Reporter: Eric Yang
> Priority: Major
>
> When a column with a non-binary collation (e.g. UTF8_LCASE) is grouped or
> used as a window partition key, filtering the result with a predicate under a
> different collation (e.g. COLLATE UTF8_BINARY) silently returns wrong
> results. Groups lose members, so aggregates such as counts come out too low,
> and "latest row per key" queries return a stale row.
> {code:sql}
> CREATE TABLE events (email STRING COLLATE UTF8_LCASE, ts INT, v STRING) USING
> parquet;
> INSERT INTO events VALUES ('[email protected]', 1, 'old'), ('[email protected]', 2, 'new');
> SELECT * FROM (
> SELECT email, v, row_number() OVER (PARTITION BY email ORDER BY ts DESC) AS
> rn
> FROM events)
> WHERE rn = 1 AND email = '[email protected]' COLLATE UTF8_BINARY;
> -- Expected: no rows (the latest event for this email is '[email protected]')
> -- Actual: [email protected], old, 1
>
> {code}
> GROUP BY results, and LEFT SEMI/LEFT ANTI JOINs against such grouped or
> windowed results, are affected the same way.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
