You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "liukundong (via GitHub)" <gi...@apache.org> on 2024/01/30 10:09:56 UTC
[I] [Bug] use jdbc udf function error by select function [doris]
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-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] use jdbc udf function error by select function [doris]
Posted by "liukundong (via GitHub)" <gi...@apache.org>.
liukundong commented on issue #30589:
URL: https://github.com/apache/doris/issues/30589#issuecomment-1990998605
没有人遇到过这种问题吗？join与left join会影响udf函数的检验使用。
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] use jdbc udf function error by select function [doris]
Posted by "liukundong (via GitHub)" <gi...@apache.org>.
liukundong commented on issue #30589:
URL: https://github.com/apache/doris/issues/30589#issuecomment-1990989852
没有人遇到过这种问题吗？join与left join会影响udf函数的检验使用。
--
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-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org