[Bug] Errors Encountered with `CURRENT_DATE()`, `CURDATE()`, `CURRENT_TIMESTAMP()`

[NeufHuang]

Search before asking

• I had searched in the issues 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:

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.

-- 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:

[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

• I agree to follow this project's Code of Conduct

[NeufHuang]

Another similar error, . The 'zdate' field is a string in the format of 'YYYYmmdd', No matter CURRENT_DATE(), CURDATE(), or CURRENT_TIMESTAMP() , It was able to execute before.

DELETE FROM table1 WHERE zdate >= DATE_FORMAT(DATE_TRUNC(CURRENT_TIMESTAMP(),'MONTH'),'%Y%m%d')

errMsg:

[1105] errCode = 2, detailMessage = errCode = 2, detailMessage = Right expr of binary predicate should be value, predicate: `zdate` >= date_format(date_trunc('2024-10-28 09:27:55', 'MONTH'), '%Y%m%d'), right expr type:VARCHAR(*)

Supplement: The model of this table1 is Duplicate. I know it will work after restart, but I am unable to restart the cluster at the moment. When I rebuilt the table using the unique model, the statement executed normally. However, I believe it is the table rebuilding that resolved this issue, as mentioned above, the same statement produces different results on different tables. The version I am using is quite old, and I am unsure if later versions have this issue.

[ixzc]

yes, we have fixed this issue. you can upgrade your doris to version 2.0.15