[
https://issues.apache.org/jira/browse/FLINK-29759?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alexandre Decuq updated FLINK-29759:
------------------------------------
Description:
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}
was:
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?
> 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)
