Eric Yang created SPARK-60450:
---------------------------------
Summary: 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
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]
