[
https://issues.apache.org/jira/browse/FLINK-29759?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17624525#comment-17624525
]
Alexandre Decuq commented on FLINK-29759:
-----------------------------------------
[~martijnvisser] noted we will try with 1.15 and let u updated.
> Cast type in LEFT JOIN
> ----------------------
>
> Key: FLINK-29759
> URL: https://issues.apache.org/jira/browse/FLINK-29759
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.14.5
> Reporter: Alexandre Decuq
> Priority: Critical
>
> Hello,
> I would like to use LEFT JOIN in order to implement a non blocking join
> without two tables (relationship is optional).
> There is a specificity: key on both side has not the same type (STRING vs
> INT).
> Here a snap a code:
> Avro input:
>
> {code:java}
> {
> "name": "CompanyBankAccountMessage",
> "type": "record",
> "namespace": "com.kyriba.dataproduct.core.model.input",
> "fields": [
> {
> "name": "data",
> "type": {
> "fields": [
> {
> "name": "CURRENCY_ID",
> "type": [
> "null",
> "string"
> ],
> "default": null,
> },
> ...
> ]
> }
> }
> ]
> }{code}
>
> Avro output:
>
> {code:java}
> {
> "name": "CurrencyMessage",
> "type": "record",
> "namespace": "com.kyriba.dataproduct.core.model.input",
> "fields": [
> {
> "name": "data",
> "type": {
> "fields": [
> {
> "name": "CURRENCY_ID",
> "type": "int"
> },
> ...
> ]
> }
> }
> ]
> }{code}
>
> Sql query:
>
> {code:java}
> SELECT ...
> FROM `my.input.COMPANY_BANK_ACCOUNT.v1.avro` as COMPANY_BANK_ACCOUNT
> LEFT JOIN `my.input.CURRENCY.v1.avro` as CURRENCY
> ON CAST(COMPANY_BANK_ACCOUNT.CURRENCY_ID as INT) = CURRENCY.CURRENCY_ID{code}
> I got this exception:
>
>
> {code:java}
> Conversion to relational algebra failed to preserve datatypes:
> validated type:
> RecordType(BIGINT currencyUid, ...)
> converted type:
> RecordType(BIGINT currencyUid NOT NULL, ...)
> rel:
> LogicalProject(currencyUid=[CAST($116.CURRENCY_ID):BIGINT NOT NULL], ...)
> LogicalJoin(condition=[=($11, $117)], joinType=[left])
> LogicalTableScan(table=[[data-platform, core,
> kyriba.flink-sql-test.core.cdc.COMPANY_BANK_ACCOUNT.v1.avro]])
> LogicalProject(the_port_key=[$0], data=[$1], $f2=[$1.CURRENCY_ID])
> LogicalTableScan(table=[[data-platform, core,
> kyriba.flink-sql-test.core.cdc.CURRENCY.v1.avro]])
> at
> org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:467){code}
> Did I make something wrong or this is a bug?
>
> Nota: it works well with this inner join:
> {code:java}
> FROM `my.input.COMPANY_BANK_ACCOUNT.v1.avro` as COMPANY_BANK_ACCOUNT,
> `my.input.CURRENCY.v1.avro` as CURRENCY
> WHERE CAST(COMPANY_BANK_ACCOUNT.CURRENCY_ID as INT) = CURRENCY.CURRENCY_ID
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
