[
https://issues.apache.org/jira/browse/SPARK-60871?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stevo Mitric updated SPARK-60871:
---------------------------------
Description:
{{variant_get}} and {{try_variant_get}} with a collated target type, e.g.
{{'STRING COLLATE UTF8_LCASE'}}, return a value of that type, but comparisons
and string functions ignore the collation. The analyzer casts the result back
to UTF8_BINARY, so filters silently return wrong rows.
{code:sql}
CREATE TABLE t (id INT, v VARIANT) USING parquet;
INSERT INTO t SELECT id, parse_json(j) FROM VALUES
(1, '{"status": "Open"}'), (2, '{"status": "open"}'), (3, '{"status":
"OPEN"}'), (4, '{"status": "closed"}') AS x(id, j);
SELECT collation(variant_get(v, '$.status', 'STRING COLLATE UTF8_LCASE')) FROM
t WHERE id = 1; -- SYSTEM.BUILTIN.UTF8_LCASE
SELECT id FROM t WHERE variant_get(v, '$.status', 'STRING COLLATE UTF8_LCASE')
= 'open'; -- returns 2, expected 1, 2, 3
{code}
The same happens with {{IN}}, {{CASE WHEN}}, {{startswith}}, {{contains}},
{{||}}, and {{array_contains}} or element access on an {{ARRAY<STRING COLLATE
...>}} target. {{CAST(variant_get(v, '$.status', 'STRING') AS STRING COLLATE
UTF8_LCASE) = 'open'}} returns the expected rows.
Cause: {{CollationTypeCoercion}} has no case for {{VariantGet}}, so the result
takes the collation of its only string argument, the path literal (UTF8_BINARY,
default strength), instead of the target type.
> Keep the collation of a collated variant_get target type in comparisons
> -----------------------------------------------------------------------
>
> Key: SPARK-60871
> URL: https://issues.apache.org/jira/browse/SPARK-60871
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.4.0
> Reporter: Stevo Mitric
> Priority: Major
>
> {{variant_get}} and {{try_variant_get}} with a collated target type, e.g.
> {{'STRING COLLATE UTF8_LCASE'}}, return a value of that type, but comparisons
> and string functions ignore the collation. The analyzer casts the result back
> to UTF8_BINARY, so filters silently return wrong rows.
> {code:sql}
> CREATE TABLE t (id INT, v VARIANT) USING parquet;
> INSERT INTO t SELECT id, parse_json(j) FROM VALUES
> (1, '{"status": "Open"}'), (2, '{"status": "open"}'), (3, '{"status":
> "OPEN"}'), (4, '{"status": "closed"}') AS x(id, j);
> SELECT collation(variant_get(v, '$.status', 'STRING COLLATE UTF8_LCASE'))
> FROM t WHERE id = 1; -- SYSTEM.BUILTIN.UTF8_LCASE
> SELECT id FROM t WHERE variant_get(v, '$.status', 'STRING COLLATE
> UTF8_LCASE') = 'open'; -- returns 2, expected 1, 2, 3
> {code}
> The same happens with {{IN}}, {{CASE WHEN}}, {{startswith}}, {{contains}},
> {{||}}, and {{array_contains}} or element access on an {{ARRAY<STRING COLLATE
> ...>}} target. {{CAST(variant_get(v, '$.status', 'STRING') AS STRING COLLATE
> UTF8_LCASE) = 'open'}} returns the expected rows.
> Cause: {{CollationTypeCoercion}} has no case for {{VariantGet}}, so the
> result takes the collation of its only string argument, the path literal
> (UTF8_BINARY, default strength), instead of the target type.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
