sepuri sai krishna created SPARK-60250:
------------------------------------------
Summary: 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.2.0, 4.0.0, 4.1.0, 4.3.0, 4.4.0
Reporter: sepuri sai krishna
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]
