[jira] [Created] (SPARK-58069) approx_top_k silently returns incorrect results (internal collation sort keys / garbage bytes) for collated string columns

["Eric Yang (Jira)" <ji...@apache.org>]

Eric Yang created SPARK-58069:
---------------------------------

             Summary: approx_top_k silently returns incorrect results (internal collation sort keys / garbage bytes) for collated string columns
                 Key: SPARK-58069
                 URL: https://issues.apache.org/jira/browse/SPARK-58069
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Eric Yang


*Description:*
{{`approx_top_k`}} over a STRING column with a non-{{{}UTF8_BINARY{}}} collation returns wrong item labels. The frequency counts are correct, but the returned items are not actual input values: for ICU collations they are raw sort-key bytes (invalid UTF-8, shown as U+FFFD replacement chars), and for {{UTF8_LCASE}} they are the lower-cased form, which may never have appeared in the data. Silent wrong-result, no error thrown.
{code:sql}
SELECT approx_top_k(c, 5, 100)
FROM (
  SELECT CAST(col AS STRING COLLATE UNICODE_CI) AS c
  FROM VALUES ('HELLO'), ('HELLO'), ('HELLO'), ('hello'), ('world') AS t(col)
);
{code}
Top item (count 4) by collation:
||collation||returned item||expected||
|UTF8_BINARY|'HELLO' (correct)|'HELLO'|
|UTF8_LCASE|'hello' (never appeared verbatim)|an actual value, e.g. 'HELLO'|
|UNICODE_CI|bytes 39 33 41 41 47 01 09 00 (garbage)|an actual value, e.g. 'HELLO'|
|UNICODE|bytes 39 33 41 41 47 01 09 01 ef bf bd ... (garbage)|an actual value, e.g. 'HELLO'|

The equivalent exact query ({{{}... GROUP BY c ORDER BY count(*) DESC{}}}) returns the correct actual value in every case.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org