[jira] [Updated] (SPARK-60250) LikeSimplification changes LIKE results under UTF8_LCASE

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-60250?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

ASF GitHub Bot updated SPARK-60250:
-----------------------------------
    Labels: pull-request-available  (was: )

> LikeSimplification changes LIKE results under UTF8_LCASE
> --------------------------------------------------------
>
>                 Key: SPARK-60250
>                 URL: https://issues.apache.org/jira/browse/SPARK-60250
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 4.1.0, 4.0.0, 4.2.0, 4.3.0, 4.4.0
>            Reporter: sepuri sai krishna
>            Priority: Major
>              Labels: pull-request-available
>
> Under UTF8_LCASE, LIKE is a Java regex match with {{UNICODE_CASE | CASE_INSENSITIVE}} ({{CollationSupport.collationAwareRegexFlags}}). Since SPARK-48621, {{LikeSimplification}} rewrites a LIKE with a literal pattern into {{StartsWith}}, {{EndsWith}}, {{Contains}} or {{EqualTo}} of the same collation, which compare the lowercase strings ({{CollationAwareUTF8String.lowerCaseCodePoints}}). The two disagree on some characters:
> * U+017F (long s) and 's': the regex matches them, as both uppercase to 'S', the lowercase strings differ. Same for U+0131 (dotless i) and 'i'.
> * U+0130 (I with dot above) and 'i' followed by U+0307: UTF8_LCASE lowercases U+0130 to these two code points, the regex does not match them.
> So a LIKE result depends on whether the optimizer can rewrite the pattern:
> {code:sql}
> CREATE OR REPLACE TEMP VIEW t AS SELECT * FROM VALUES ('ſ'), ('ſx'), ('xſ'), ('İx') AS t(c);
> SELECT c, c COLLATE UTF8_LCASE LIKE 's'   AS a,
>           c COLLATE UTF8_LCASE LIKE 's%'  AS b,
>           c COLLATE UTF8_LCASE LIKE '%s'  AS c2,
>           c COLLATE UTF8_LCASE LIKE 's_%' AS d,
>           c COLLATE UTF8_LCASE LIKE 'i̇%'  AS e
> FROM t;
> {code}
> ||c||a||b||c2||d||e||
> |ſ|false|false|false|false|false|
> |ſx|false|false|false|true|false|
> |xſ|false|false|false|false|false|
> |İx|false|false|false|false|true|
> With {{spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.LikeSimplification}}, a, b and c2 are true for the long s rows and e is false for İx. With the rule on, 'ſx' matches LIKE 's_%' but not LIKE 's%', which no string can do.
> {{LikeSimplificationSuite}} already asserts that the rewrite keeps the LIKE result under UTF8_LCASE (SPARK-59063, for the Kelvin sign). This case cannot be fixed by a length guard, as the difference depends on the characters of the input.
> Two ways to fix it:
> # Do not rewrite LIKE when the collation does not have binary equality, so that LIKE under UTF8_LCASE is always the regex match.
> # Make LIKE under UTF8_LCASE match the lowercase strings, which keeps the rewrite but changes LIKE results for these characters.



--
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