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

["hnynes (via GitHub)" <gi...@apache.org>]

hnynes opened a new issue, #4364:
URL: https://github.com/apache/paimon/issues/4364

   ### Search before asking
   
   - [X] I searched in the [issues](https://github.com/apache/paimon/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';
   
   ![img_v3_02fu_9beaba0f-caaa-4d2a-a12d-0bb91727628g](https://github.com/user-attachments/assets/06a0b13e-fa52-461c-9d56-ce7a106eeec0)
   
   
   ### 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!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

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

["Shadowell (via GitHub)" <gi...@apache.org>]

Shadowell commented on issue #4364:
URL: https://github.com/apache/paimon/issues/4364#issuecomment-2437125520

   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
   ![image](https://github.com/user-attachments/assets/a1f09c33-1a7f-450f-9161-6ac70c3ceca5)
   You use time as the partition field, which may not be a good design. The partition identifier will be escaped which may cause uncertainty
   ![image](https://github.com/user-attachments/assets/a03ccde5-07fa-4c84-9461-4f8e998d7850)
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org