[
https://issues.apache.org/jira/browse/FLINK-32276?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xianxun Ye updated FLINK-32276:
-------------------------------
Description:
*How to reproduce:*
{code:java}
CREATE TABLE dim (
id BIGINT,
name STRING,
age INT,
status BOOLEAN,
PRIMARY KEY (id) NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/mydatabase',
'table-name' = 'users'
);
create table actions (
id bigint,
proc as proctime(),
primary key (id) not enforced
) with (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/mydatabase',
'table-name' = 'actions'
);
select
*
from
actions
left join dim for system_time as of actions.proc on actions.id = dim.id
where
dim.age > 10; {code}
When running the above SQL, the LookupJoin operator is executed based on
InnerJoin, contrary to the SQL's left join.
If I remove the where condition(dim.age>10), the LookupJoin's joinType is
LeftOuterJoin.
Is this a bug?
was:
How to reproduce:
```sql
CREATE TABLE dim (
id BIGINT,
name STRING,
age INT,
status BOOLEAN,
PRIMARY KEY (id) NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/mydatabase',
'table-name' = 'users'
);
create table actions (
id bigint,
proc as proctime(),
primary key (id) not enforced
) with (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://localhost:3306/mydatabase',
'table-name' = 'actions'
);
select
*
from
actions
left join dim for system_time as of actions.proc on actions.id = dim.id
where
dim.age > 10;
```
If I remove the where condition(dim.age>10), the LookupJoin's joinType is
LeftOuterJoin.
Is this a bug?
> After adding the where condition to the flink lookup left join, the joinType
> becomes innerJoin
> ----------------------------------------------------------------------------------------------
>
> Key: FLINK-32276
> URL: https://issues.apache.org/jira/browse/FLINK-32276
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Affects Versions: 1.17.1
> Reporter: Xianxun Ye
> Priority: Major
> Attachments: lookup_join_inner_join_type.jpg
>
>
> *How to reproduce:*
> {code:java}
> CREATE TABLE dim (
> id BIGINT,
> name STRING,
> age INT,
> status BOOLEAN,
> PRIMARY KEY (id) NOT ENFORCED
> ) WITH (
> 'connector' = 'jdbc',
> 'url' = 'jdbc:mysql://localhost:3306/mydatabase',
> 'table-name' = 'users'
> );
> create table actions (
> id bigint,
> proc as proctime(),
> primary key (id) not enforced
> ) with (
> 'connector' = 'jdbc',
> 'url' = 'jdbc:mysql://localhost:3306/mydatabase',
> 'table-name' = 'actions'
> );
> select
> *
> from
> actions
> left join dim for system_time as of actions.proc on actions.id = dim.id
> where
> dim.age > 10; {code}
> When running the above SQL, the LookupJoin operator is executed based on
> InnerJoin, contrary to the SQL's left join.
> If I remove the where condition(dim.age>10), the LookupJoin's joinType is
> LeftOuterJoin.
> Is this a bug?
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
