804e opened a new issue, #56436: URL: https://github.com/apache/doris/issues/56436
### 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 3.0. ### What's Wrong? 表定义 ```sql CREATE TABLE `jsm_salesman` ( `id` bigint NULL, `name_` varchar(765) NULL, `job_number` varchar(48) NULL, `role_type` tinyint NULL DEFAULT "5", `status_` varchar(6) NULL, `org_id` varchar(765) NULL, `org_name` varchar(765) NULL, `post_name` varchar(150) NULL, `wechat_user_id` int NULL, `user_id` int NULL, `id_path` varchar(3072) NULL, `leader_id` bigint NULL DEFAULT "-1", `leader_name` varchar(765) NULL, `job_nature` varchar(60) NULL, `product_attr` varchar(60) NULL, `can_sync_update` tinyint NULL DEFAULT "1", `is_deleted` tinyint NULL DEFAULT "0", `revision` int NULL DEFAULT "0", `create_by` varchar(192) NULL, `create_time` bigint NULL, `update_by` varchar(192) NULL, `update_time` bigint NULL ) ENGINE=OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ); CREATE TABLE `jdp_bus_sell_org` ( `id` bigint NULL, `parent_id` bigint NULL, `id_path` varchar(3072) NULL, `code_` varchar(768) NULL, `type_` tinyint NULL DEFAULT "5", `name_` varchar(768) NULL, `status_` tinyint NULL DEFAULT "0", `sort` int NULL, `create_source` tinyint NULL DEFAULT "1", `is_deleted` tinyint NULL DEFAULT "0", `revision` int NULL DEFAULT "0", `create_by` varchar(192) NULL, `create_time` bigint NULL, `update_by` varchar(192) NULL, `update_time` bigint NULL ) ENGINE=OLAP UNIQUE KEY(`id`) DISTRIBUTED BY HASH(`id`) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V2", "enable_unique_key_merge_on_write" = "true", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_mow_light_delete" = "false" ); ``` 使用sql查询，发现结果无法精准匹配 ```sql select js.job_number, js.status_ , jbso.id_path, jbso.id, js.org_id from js_marketing.jsm_salesman js left join js_dataplatform_business.jdp_bus_sell_org jbso on js.org_id = jbso.id where js.job_number = '2008092'; ``` 查询结果 ``` job_number|status_|id_path |id |org_id | ----------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------+-------------------+ 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043591/ |1945392950687043591|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043592/ |1945392950687043592|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043596/ |1945392950687043596|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043593/1945392950687043601/|1945392950687043601|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/1945392950687043605/|1945392950687043605|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043596/1945392950687043611/|1945392950687043611|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043599/1945392950687043624/|1945392950687043624|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/ |1945392950687043587|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043593/ |1945392950687043593|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043596/1945392950687043610/|1945392950687043610|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043599/1945392950687043625/|1945392950687043625|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043598/1945392950687043622/|1945392950687043622|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043599/ |1945392950687043599|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043596/1945392950687043613/|1945392950687043613|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043596/1945392950687043612/|1945392950687043612|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/1945392950687043616/|1945392950687043616|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/ |1945392950687043594|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/1945392950687043602/|1945392950687043602|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/1945392950687043603/|1945392950687043603|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043598/1945392950687043621/|1945392950687043621|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043598/ |1945392950687043598|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043595/1945392950687043609/|1945392950687043609|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/1945392950687043617/|1945392950687043617|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043599/1945392950687043626/|1945392950687043626|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043589/ |1945392950687043589|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043595/ |1945392950687043595|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043595/1945392950687043606/|1945392950687043606|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043595/1945392950687043607/|1945392950687043607|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043595/1945392950687043608/|1945392950687043608|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043598/1945392950687043620/|1945392950687043620|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1943510464860262402/1945392950687043586/ |1945392950687043586|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/1945392950687043604/|1945392950687043604|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/1945392950687043614/|1945392950687043614|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/1945392950687043618/|1945392950687043618|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/ |1945392950687043588|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043590/ |1945392950687043590|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/ |1945392950687043597|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043593/1945392950687043600/|1945392950687043600|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043597/1945392950687043615/|1945392950687043615|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043598/1945392950687043619/|1945392950687043619|1945392950687043594| 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043599/1945392950687043623/|1945392950687043623|1945392950687043594| ``` 无法精准匹配到最后三位数字，如果手动转换为varchar比较则正常 ```sql select js.job_number, js.status_ , jbso.id_path, jbso.id, js.org_id from js_marketing.jsm_salesman js left join js_dataplatform_business.jdp_bus_sell_org jbso on js.org_id = concat('',jbso.id) where js.job_number = '2008092'; job_number|status_|id_path |id |org_id | ----------+-------+-----------------------------------------------------------------------------------------------------+-------------------+-------------------+ 2008092 |0 |/1510056552495706113/1501395517266014209/1945392950687043587/1945392950687043588/1945392950687043594/|1945392950687043594|1945392950687043594| ``` ### What You Expected? 类型转换匹配一致 ### How to Reproduce? _No response_ ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
