Eric Yang created SPARK-58441:
---------------------------------
Summary: instr and substring_index silently return wrong results
for ICU collations on accented or emoji text
Key: SPARK-58441
URL: https://issues.apache.org/jira/browse/SPARK-58441
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Eric Yang
Under ICU collations (UNICODE, UNICODE_CI, and locale collations), instr
(backward search, negative start) and substring_index return wrong positions,
spurious/missed matches, or the wrong substring whenever the input contains
characters that map to multiple collation elements (any accented letter) or
start with a surrogate pair (emoji, supplementary CJK). The results are
silently wrong — no error is raised. UTF8_BINARY and UTF8_LCASE are unaffected.
{code:sql}
-- returns 6, expected 4
SELECT instr(collate('bbébébé', 'UNICODE'), 'bé', -3, 1);
-- returns 0 (not found), expected 4
SELECT instr(collate('babaéa', 'UNICODE'), 'aé', -3, 1);
-- returns 'ééa' (whole string), expected 'éa'
SELECT substring_index(collate('ééa', 'UNICODE'), 'é', -2);
-- returns '' (empty), expected '😀a'
SELECT substring_index(collate('😀a😀b', 'UNICODE'), '😀', 2);
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
