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)
