[
https://issues.apache.org/jira/browse/SPARK-60130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18138426#comment-18138426
]
Wayne Wang commented on SPARK-60130:
------------------------------------
Submitted a fix with regression tests:
https://github.com/apache/spark/pull/60116
> CREATE TABLE LIKE does not preserve the source table's default collation
> ------------------------------------------------------------------------
>
> Key: SPARK-60130
> URL: https://issues.apache.org/jira/browse/SPARK-60130
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Environment: Spark: 5.0.0-SNAPSHOT
> Commit: 38422aefaf599ffb4cfa579d98898d95b1f02081
> Java: OpenJDK 17.0.17
> OS: macOS 15.7.4
> Reporter: Yibo Dong
> Priority: Major
> Labels: pull-request-available, sql
>
> h2. What happened
> {{CREATE TABLE LIKE}} does not preserve the source table's {{DEFAULT
> COLLATION}}. When a new {{STRING}} column is added to the cloned table, it
> uses {{UTF8_BINARY}} instead of the source table's {{UTF8_LCASE}} default.
> This changes string comparison and grouping results.
> h2. How to reproduce
> Save the following SQL as {{tmp.sql}}:
> {code:sql}
> DROP TABLE IF EXISTS t_clone_collation_src;
> DROP TABLE IF EXISTS t_clone_collation_dst;
> CREATE TABLE t_clone_collation_src (s STRING)
> USING PARQUET
> DEFAULT COLLATION UTF8_LCASE;
> CREATE TABLE t_clone_collation_dst LIKE t_clone_collation_src;
> ALTER TABLE t_clone_collation_dst ADD COLUMNS (t STRING);
> INSERT INTO t_clone_collation_dst VALUES
> ('a', 'a'),
> ('A', 'A');
> SELECT t_collation, COUNT(*) AS distinct_t_values
> FROM (
> SELECT t, collation(t) AS t_collation
> FROM t_clone_collation_dst
> GROUP BY t, collation(t)
> ) groups
> GROUP BY t_collation
> ORDER BY t_collation;
> DROP TABLE t_clone_collation_dst;
> DROP TABLE t_clone_collation_src;
> {code}
> Run using {{spark-sql}}:
> {code:bash}
> spark-sql --master 'local[2]'
> --conf spark.ui.enabled=false
> --conf spark.sql.shuffle.partitions=1
> -f tmp.sql
> {code}
> h2. Expected result
> The cloned table should preserve the source table's {{UTF8_LCASE}} default
> collation. The newly added column {{t}} should therefore use case-insensitive
> comparison, grouping {{a}} and {{A}} together.
> {code:none}
> t_collation | distinct_t_values
> --------------------------+------------------
> SYSTEM.BUILTIN.UTF8_LCASE | 1
> {code}
> h2. Actual result
> The newly added column uses {{UTF8_BINARY}}, so {{a}} and {{A}} are treated
> as distinct values.
> {code:none}
> t_collation | distinct_t_values
> ---------------------------+------------------
> SYSTEM.BUILTIN.UTF8_BINARY | 2
> {code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
