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

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

> 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
>            Priority: Major
>              Labels: correctness, pull-request-available
>
> 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