liukundong opened a new issue, #30589: URL: https://github.com/apache/doris/issues/30589
### 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 doris v2.0.1 2.0.4 has this error ### What's Wrong? mysql> select mmct.trans_service_date trans_date, -> mmct.ent_id, -> mmct.venue_id, -> sm2tostring(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))) inden_no, -> timestampDiff(YEAR,DATE_FORMAT(IdToBirthday(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))),'yyyyMMdd'),DATE_FORMAT('2024-01-01','yyyy-MM-dd')) age -> from member_card_trans mmct **_-> join member_card mmc on mmc.id=mmct.member_card_id_** -> join eq_position ep on ep.id=mmct.position_id and ep.is_out_side=1 -> where mmct.trans_service_date is not null -> and mmct.trans_service_date>='2023-01-01' -> and mmct.trans_service_date<'2024-01-01' -> and mmct.state=1 -> and sm2tostring(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))) like '3502%' -> limit 10; ERROR 1105 (HY000): errCode = 2, detailMessage = (172.31.22.112)[CANCELLED][INTERNAL_ERROR]GetJniExceptionMsg meet error, query=SELECT `id`, `identity_number_cipher` FROM `xm_kjg_ticket`.`mkt_member_card` WHERE (default_cluster:my_test.sm2tostring(identity_number_cipher, concat(substring(id, -16, 2147483647), substring(id, -16, 2147483647))) like '3502%'), msg=[INTERNAL_ERROR]UdfRuntimeException: JDBC executor sql has error: CAUSED BY: MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that cor mysql> select mmct.trans_service_date trans_date, -> mmct.ent_id, -> mmct.venue_id, -> sm2tostring(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))) inden_no, -> timestampDiff(YEAR,DATE_FORMAT(IdToBirthday(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))),'yyyyMMdd'),DATE_FORMAT('2024-01-01','yyyy-MM-dd')) age -> from member_card_trans mmct _**-> left join member_card mmc on mmc.id=mmct.member_card_id**_ -> join eq_position ep on ep.id=mmct.position_id and ep.is_out_side=1 -> where mmct.trans_service_date is not null -> and mmct.trans_service_date>='2023-01-01' -> and mmct.trans_service_date<'2024-01-01' -> and mmct.state=1 -> and sm2tostring(mmc.identity_number_cipher,concat(substr(mmc.id,-16),substr(mmc.id,-16))) like '3502%' -> limit 10; +------------+--------+----------------------------+--------------------+------+ | trans_date | ent_id | venue_id | inden_no | age | +------------+--------+----------------------------+--------------------+------+ | 2023-01-27 | 1001 | 2022090111234323400017298 | 3502032xxxxxxx357 | 8 | | 2023-12-31 | 1001 | 2022090111234323400017298 | 3502041xxxxxxx013 | 39 | | 2023-10-21 | 1001 | 2022090111234323400017298 | 3502062xxxxxxx516 | 12 | | 2023-10-14 | 1001 | 2022090111234323400017298 | 3502061xxxxxxx031 | 45 | | 2023-10-15 | 1001 | 2022090111234323400017298 | 3502032xxxxxxx311 | 12 | | 2023-11-12 | 1001 | 2022090111234323400017298 | 3502032xxxxxxx311 | 12 | | 2023-10-15 | 1001 | 2022090111234323400017298 | 3502032xxxxxxx347 | 8 | | 2023-11-12 | 1001 | 2022090111234323400017298 | 3502032xxxxxxx347 | 8 | | 2023-11-11 | 1001 | 2022090111234323400017298 | 3502062xxxxxxx012 | 7 | | 2023-10-01 | 1001 | 2022090111234323400017298 | 3502041xxxxxxx032 | 61 | +------------+--------+----------------------------+--------------------+------+ ### What You Expected? use **_left join and join_** has is not same result ### How to Reproduce? _No response_ ### Anything Else? _No response_ ### Are you willing to submit PR? - [X] 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
