[Bug] Using SparkSQL insert Paimon, the fields do not match

[luowanghaoyun]

Search before asking

• I searched in the issues and found nothing similar.

Paimon version

0.9

Compute Engine

Spark 3.5

Minimal reproduce step

We have some problems when upgrading the paimon version from 0.8 to 0.9.
for example:

CREATE TABLE T (
  id BIGINT,
  a BIGINT,
  b BIGINT,
  c STRING,
  d STRING)
 TBLPROPERTIES (
  'bucket' = '1',
  'primary-key' = 'id'
)

What doesn't meet your expectations?

Problem 1

If I submit the following sql：

INSERT INTO T(id, d, c, b, a)
SELECT CAST(1 AS BIGINT) AS id, 'd' AS d, 'c' AS c, CAST(3 AS BIGINT) AS b, CAST(2 AS BIGINT) AS a

• with Paimon-0.9: throw java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Long
• with Paimon-0.8: sql executed successfully, the data in table is (1, 2, 3, c, d)

I guess the fields do not match.
I changed the sql to the following, select item is the same as table schema：

INSERT INTO T
SELECT CAST(2 AS BIGINT) AS id, CAST(2 AS BIGINT) AS a, CAST(3 AS BIGINT) AS b, 'c' AS c, 'd' AS d

with Paimon-0.9: sql executed successfully, the data in table is (1, 2, 3, c, d), (2, 2, 3, c, d)

Problem 2

In addition, we also found that implicit conversion is also invalid:

SET spark.sql.storeAssignmentPolicy=LEGACY;
INSERT INTO T
SELECT 3 AS id, 2 AS a, 3 AS b, 'c' AS c, 'd' AS d

• with Paimon-0.9: throw java.lang.ClassCastException: java.lang.Int cannot be cast to java.lang.Long
• with Paimon-0.8: sql executed successfully

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[Zouxxyy]

@luowanghaoyun Can you try the latest 1.0-snapshot, these problems should have been fixed

[luowanghaoyun]

@Zouxxyy I have tried the latest version(paimon-spark-3.5-1.0-20241218.002704-69.jar). The problems still exist. Can you check again?

[Zouxxyy]

Here my test on the master code

[xuzifu666]

Here my test on the master code

+1 in my test，master is OK