sepuri sai krishna created SPARK-61621:
------------------------------------------
Summary: Filtering a cached table with startswith on a collated
string column drops matching rows
Key: SPARK-61621
URL: https://issues.apache.org/jira/browse/SPARK-61621
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.0.3
Reporter: sepuri sai krishna
Caching a table changes the result of a {{startswith}} filter on a string
column whose collation is not {{UTF8_BINARY}}: rows that match are dropped.
Default configuration, reproduced on 4.0.3 and on master.
{code:sql}
CREATE TABLE t (a STRING COLLATE UNICODE_CI_AI, b STRING COLLATE UNICODE,
c STRING COLLATE UTF8_LCASE, d STRING COLLATE UNICODE_CI) USING parquet;
-- 'œuvre', 'été' with a decomposed first letter, 'İstanbul', 'final' with the
fi ligature
INSERT INTO t VALUES ('œuvre', 'été', 'İstanbul', 'final');
SELECT count(*) FROM t WHERE startswith(a, 'oe'); -- 1
SELECT count(*) FROM t WHERE startswith(b, 'ét'); -- 1
SELECT count(*) FROM t WHERE startswith(c, 'i̇s'); -- 1
SELECT count(*) FROM t WHERE startswith(d, 'fin'); -- 1
CACHE TABLE t;
SELECT count(*) FROM t WHERE startswith(a, 'oe'); -- 0
SELECT count(*) FROM t WHERE startswith(b, 'ét'); -- 0
SELECT count(*) FROM t WHERE startswith(c, 'i̇s'); -- 0
SELECT count(*) FROM t WHERE startswith(d, 'fin'); -- 0
SET spark.sql.inMemoryColumnarStorage.partitionPruning=false;
SELECT count(*) FROM t WHERE startswith(a, 'oe'); -- 1 again (same for
b, c, d)
{code}
The same happens with {{UTF8_BINARY_RTRIM}} ({{startswith(s, 'a ')}} on the
value {{'ab'}}), and with the DataFrame API ({{df.cache()}} and
{{col.startsWith}}).
*Cause*
{{SimpleMetricsCachedBatchSerializer.buildFilter}} skips a cached batch for
{{StartsWith(a, l)}} unless
{code:scala}
statsFor(a).lowerBound.substr(0, Length(l)) <= l && l <=
statsFor(a).upperBound.substr(0, Length(l))
{code}
This compares the first {{length(l)}} characters of the bounds of the batch
with the literal. It is correct only if a string starts with {{l}} exactly when
its first {{length(l)}} characters are {{l}}, and if strings are ordered by
their characters. Both hold for {{UTF8_BINARY}} only. In the other collations a
prefix can match a different number of characters:
* {{UNICODE_CI_AI}}: {{'œuvre'}} starts with {{'oe'}}, but its first two
characters are {{'œu'}}, which is greater than {{'oe'}}.
* {{UNICODE}}: a decomposed {{'é'}} (e + U+0301) is equal to the precomposed
one, so the first two characters of the value are only its first letter.
* {{UTF8_LCASE}}: {{'İ'}} (U+0130) matches the two characters {{'i'}} + U+0307.
* {{UNICODE_CI}}: the ligature {{'fi'}} matches {{'fi'}}.
* {{UTF8_BINARY_RTRIM}}: the trailing space of the prefix is ignored.
So the batch that holds the row is skipped. The other predicates that
{{buildFilter}} handles ({{=}}, {{<}}, {{IN}}, ...) are not affected, because
the bounds are gathered with the collation of the column
({{StringColumnStats}}).
The pruning by {{StartsWith}} is from before collations (4.0.0) and was not
restricted when they were added. The same code serves the default and the Arrow
cache serializers.
*Fix*
Prune by {{StartsWith}} only when the collation of the column supports binary
ordering. For the other collations the batch is read and the filter is
evaluated on its rows, as for any predicate that {{buildFilter}} does not
handle.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]