NeufHuang opened a new issue, #42493: URL: https://github.com/apache/doris/issues/42493
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version Version : doris-2.0.2-rc05 ### What's Wrong? #### Issue with `INSERT INTO ... SELECT` Statement: In the `INSERT INTO ... SELECT` statement, the original condition was `>= DATE_TRUNC(CURRENT_TIMESTAMP(), 'DAY')`. This condition has been running in a scheduled task for some time, but recently it suddenly inserted 0 records, even though the `SELECT` statement retrieved the records correctly. Changing the condition to `>= CURRENT_DATE()` allowed data to be inserted correctly. This issue, where data can be retrieved but 0 records are inserted, has occurred before. We couldn't find the root cause at that time, but restarting the service resolved the issue. Example:  ```sql WHERE mmml.CreateDate >= DATE_TRUNC(CURRENT_TIMESTAMP(), 'DAY') -- mmml.CreateDate >= CURRENT_DATE() ``` #### Issue with `DELETE` Statements: Another error involves the `DELETE` statements. Below is the currently working statement followed by others with varying conditions. Note that the second statement fails to execute, while the third works fine despite having the same `DELETE` condition on different tables. The difference between the first and fourth statements is `CURRENT_DATE()` vs `CURDATE()`, with the fourth failing to execute while the fifth works. ```sql -- Currently working statement: DELETE FROM SRM_SMDM_ITEM WHERE CREATION_DATE >= DATE_SUB(CURDATE(), INTERVAL 1 DAY); -- Second statement (fails): DELETE FROM SRM_SMDM_ITEM WHERE CREATION_DATE >= DATE_TRUNC(DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), 'DAY'); -- Third statement (works): DELETE FROM test_db.PROD_MES2_PACK_BOARD_DATA WHERE BeginTime >= DATE_TRUNC(DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY), 'DAY'); -- Fourth statement (fails): DELETE FROM SRM_SMDM_ITEM WHERE CREATION_DATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY); -- Fifth statement (works): DELETE FROM test_db.PROD_MES2_PACK_BOARD_DATA WHERE BeginTime >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY); ``` All mentioned date fields are of type `datetime`.  **Error Messages:** ```sql [1105] errCode = 2, detailMessage = errCode = 2, detailMessage = Right expr of binary predicate should be value, predicate: `CREATION_DATE` >= CAST(date_trunc('2024-10-24 16:22:59', 'DAY') AS DATETIMEV2(3)), right expr type:DATETIMEV2(3) [1105] errCode = 2, detailMessage = errCode = 2, detailMessage = Right expr of binary predicate should be value, predicate: `CREATION_DATE` >= CAST(date_sub(current_date(), INTERVAL 1 DAY) AS DATETIMEV2(3)), right expr type:DATETIMEV2(3) ``` ### What You Expected? null ### How to Reproduce? - The table creation and SQL are normal and the issue arises after running for some time. - The issue is resolved by restarting the service, but is difficult to reproduce after restarting. ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- 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: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
