[Bug] Failed to execute delete with an append table by a timestamp condition statement, error "failed to parse timestamp datatype value"

[hnynes]

Search before asking

• I searched in the issues and found nothing similar.

Paimon version

0.8.2

Compute Engine

spark 3.4.1

Minimal reproduce step

1. create a append table in spark-sql
   CREATE TABLE paimon.events.event_test (
   id varchar(100),
   time timestamp,
   created_at timestamp,
   new_session varchar(600),
   source_tmp varchar(6000),
   time_dt date
   ) partitioned by (time);

2. insert some data to the table in spark-sql
   insert into paimon.events.event_test values('111', cast('2024-10-01 01:00:00' as timestamp), cast('2024-10-01 01:00:00' as timestamp), '111', '111', cast('2024-10-01' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-01 06:00:00' as timestamp), cast('2024-10-02 06:00:00' as timestamp), '111', '111', cast('2024-10-01' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-01 12:00:00' as timestamp), cast('2024-10-01 12:00:00' as timestamp), '111', '111', cast('2024-10-01' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-01 18:00:00' as timestamp), cast('2024-10-01 18:00:00' as timestamp), '111', '111', cast('2024-10-01' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-02 01:00:00' as timestamp), cast('2024-10-02 01:00:00' as timestamp), '111', '111', cast('2024-10-02' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-02 06:00:00' as timestamp), cast('2024-10-02 06:00:00' as timestamp), '111', '111', cast('2024-10-02' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-02 12:00:00' as timestamp), cast('2024-10-02 12:00:00' as timestamp), '111', '111', cast('2024-10-02' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-02 18:00:00' as timestamp), cast('2024-10-02 18:00:00' as timestamp), '111', '111', cast('2024-10-02' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-03 01:00:00' as timestamp), cast('2024-10-03 01:00:00' as timestamp), '111', '111', cast('2024-10-03' as date));
   insert into paimon.events.event_test values('111', cast('2024-10-03 01:00:01' as timestamp), cast('2024-10-03 01:00:01' as timestamp), '111', '111', cast('2024-10-03' as date));

3. execute select in spark-sql, it would execute successfully
   select * from paimon.events.event_test where time = '2024-10-03 01:00:01';

4. execute delete in spark-sql, it would raise an error
   delete from paimon.events.event_test where time = '2024-10-03 01:00:01';

What doesn't meet your expectations?

it could execute select successfully, and it failed to execute delete with the same condition statement.

Anything else?

I made a test with a primary table, both select and delete with timestamp datatype condition could be executed successfully.

I also made a test with a append table, both select and delete with date datatype condition could be executed successfully.

Are you willing to submit a PR?

• I'm willing to submit a PR!

[Shadowell]

Hi @hnynes, I test that case , it can not return anything with select * from paimon.default.dim where time = '2024-10-03 01:00:01';.
And also can not delete anything with delete from paimon.default.dim where time = '2024-10-03 01:00:01';

• Spark 3.4.3
• Paimon 0.8.2
  
  You use time as the partition field, which may not be a good design. The partition identifier will be escaped which may cause uncertainty