flink1.16.1 jdbc-3.1.0-1.16  There is a problem trying left join

[yangxueyong <ya...@nantian.com.cn>]

flink1.16.1
mysql8.0.33
jdbc-3.1.0-1.16 




I have a sql,
insert into test_flink_res2(id,name,address)
select a.id,a.name,a.address from test_flink_res1 a left join test_flink_res2 b on a.id=b.id where a.name='abc0.11317691217472489' and b.id is null;
Why does flinksql convert this statement into the following statement？
SELECT `address` FROM `test_flink_res1` WHERE ((`name` = 'abc0.11317691217472489')) AND ((`id` IS NULL))
As a result, there is no data in test_flink_res2，why？

Re: flink1.16.1 jdbc-3.1.0-1.16 There is a problem trying left join

[Hang Ruan <ru...@gmail.com>]

Hi, yangxueyong,

The filter(where condition) will be pushed down to the source if the
connector implements the interface `SupportsFilterPushDown`.
In your case, the sql planner analyzed that the records sent by
`test_flink_res1` would satisfy the conditions (`name` =
'abc0.11317691217472489') and (`id` IS NULL). These filters are pushed down
to the source.

Best,
Hang

Shammon FY <zj...@gmail.com> 于2023年5月10日周三 14:15写道：

> Hi Yangxueyong,
>
> Are you sure this is your Flink SQL job? This SQL statement looks very
> strange, the table 'test_flink_res2' is both source and sink, and the join
> key is null.
>
> Best,
> Shammon FY
>
> On Wed, May 10, 2023 at 12:54 PM yangxueyong <ya...@nantian.com.cn>
> wrote:
>
>> flink1.16.1
>>
>> mysql8.0.33
>>
>> jdbc-3.1.0-1.16
>>
>>
>> I have a sql,
>>
>> insert into test_flink_res2(id,name,address)
>> select a.id,a.name,a.address from test_flink_res1 a left join
>> test_flink_res2 b on a.id=b.id where a.name='abc0.11317691217472489' and
>> b.id is null;
>>
>> *Why does flinksql convert this statement into the following statement？*
>>
>> SELECT `address` FROM `test_flink_res1` WHERE ((`name` =
>> 'abc0.11317691217472489')) AND ((`id` IS NULL))
>>
>> *As a result, there is no data in test_flink_res2，why？*
>>
>>
>>
>>

Re: flink1.16.1 jdbc-3.1.0-1.16 There is a problem trying left join

[Shammon FY <zj...@gmail.com>]

Hi Yangxueyong,

Are you sure this is your Flink SQL job? This SQL statement looks very
strange, the table 'test_flink_res2' is both source and sink, and the join
key is null.

Best,
Shammon FY

On Wed, May 10, 2023 at 12:54 PM yangxueyong <ya...@nantian.com.cn>
wrote:

> flink1.16.1
>
> mysql8.0.33
>
> jdbc-3.1.0-1.16
>
>
> I have a sql,
>
> insert into test_flink_res2(id,name,address)
> select a.id,a.name,a.address from test_flink_res1 a left join
> test_flink_res2 b on a.id=b.id where a.name='abc0.11317691217472489' and
> b.id is null;
>
> *Why does flinksql convert this statement into the following statement？*
>
> SELECT `address` FROM `test_flink_res1` WHERE ((`name` =
> 'abc0.11317691217472489')) AND ((`id` IS NULL))
>
> *As a result, there is no data in test_flink_res2，why？*
>
>
>
>