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

[Alexandre Decuq (Jira)]

Alexandre Decuq created FLINK-29759:
---------------------------------------

             Summary: Cast type in LEFT JOIN
                 Key: FLINK-29759
                 URL: https://issues.apache.org/jira/browse/FLINK-29759
             Project: Flink
          Issue Type: Bug
          Components: API / Core
    Affects Versions: 1.14.5
            Reporter: Alexandre Decuq


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)