[
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: [email protected]
For additional commands, e-mail: [email protected]
