跨表关联时，关联字段会有类型不匹配的问题

[allenchen0314]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

两表对比检测时，在使用impala和trino数据源，如果两边关联的字段是int类型，那么使用coalesce(" + table + "." + QuoteIdentifier.quote(column, needQuote) + ", '')就会报错，impala 和trino不适用改语法

DataVines Version

1

DataVines Config

coalesce(" + table + "." + QuoteIdentifier.quote(column, needQuote)

Running Command

transform sql is: select count(1) as actual_value_313cc67e from ( SELECT ods_ods_flume_agent_agent_config_di_1.id AS id_1, ods_ods_flume_agent_agent_config_di_2.id AS id_2 FROM (SELECT * FROM ods.ods_flume_agent_agent_config_di WHERE (date_id = '20240802') ) ods_ods_flume_agent_agent_config_di_1 LEFT JOIN (SELECT * FROM ods.ods_flume_agent_agent_config_di WHERE (date_id = '20240803') ) ods_ods_flume_agent_agent_config_di_2 ON coalesce(ods_ods_flume_agent_agent_config_di_1.id, '')=coalesce(ods_ods_flume_agent_agent_config_di_2.id, '') WHERE ( NOT (ods_ods_flume_agent_agent_config_di_1.id IS NULL )) AND ( ods_ods_flume_agent_agent_config_di_2.id IS NULL ) ) t, transform_type is : actual_value
[ERROR] 2024-09-26 15:06:18.514 [JobExecutionUniqueId=JOB-20240926-client_local_15:[112] - transform execute error:
java.sql.SQLException: Query failed (#20240926_070618_00004_2uqq9): line 1:465: All COALESCE operands must be the same type or coercible to a common type. Cannot find common type between integer and varchar(0), all types (without duplicates): [integer, varchar(0)]

Error Exception

transform sql is: select count(1) as actual_value_313cc67e from ( SELECT ods_ods_flume_agent_agent_config_di_1.id AS id_1, ods_ods_flume_agent_agent_config_di_2.id AS id_2 FROM (SELECT * FROM ods.ods_flume_agent_agent_config_di WHERE (date_id = '20240802') ) ods_ods_flume_agent_agent_config_di_1 LEFT JOIN (SELECT * FROM ods.ods_flume_agent_agent_config_di WHERE (date_id = '20240803') ) ods_ods_flume_agent_agent_config_di_2 ON coalesce(ods_ods_flume_agent_agent_config_di_1.id, '')=coalesce(ods_ods_flume_agent_agent_config_di_2.id, '') WHERE ( NOT (ods_ods_flume_agent_agent_config_di_1.id IS NULL )) AND ( ods_ods_flume_agent_agent_config_di_2.id IS NULL ) ) t, transform_type is : actual_value
[ERROR] 2024-09-26 15:06:18.514 [JobExecutionUniqueId=JOB-20240926-client_local_15:[112] - transform execute error:
java.sql.SQLException: Query failed (#20240926_070618_00004_2uqq9): line 1:465: All COALESCE operands must be the same type or coercible to a common type. Cannot find common type between integer and varchar(0), all types (without duplicates): [integer, varchar(0)]

Engine Type

local

Java Version

1.8

Screenshots

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[zixi0825]

This issue has been fixed in #464