[
https://issues.apache.org/jira/browse/SPARK-58069?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Yang updated SPARK-58069:
------------------------------
Description:
*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.
was:
*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.
> 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
> Priority: Major
>
> *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: [email protected]
For additional commands, e-mail: [email protected]
