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]

Reply via email to