[jira] [Updated] (FLINK-29759) Cast type in LEFT JOIN

["Shengkai Fang (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/FLINK-29759?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Shengkai Fang updated FLINK-29759:
----------------------------------
    Component/s: Table SQL / Planner
                     (was: API / Core)

> 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?
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)