Yibo Dong created SPARK-60130:
---------------------------------
Summary: 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
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
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
