[
https://issues.apache.org/jira/browse/FLINK-32579?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17743009#comment-17743009
]
Yunhong Zheng commented on FLINK-32579:
---------------------------------------
Hi, [~jasonliangyc] . I got it.
For question1: there is no filter condition "p.name = '????'" in relNode
LookupJoin?
* This is normal because the filter condition has been pushed down to the jdbc
source (jdbc source supports filter pushdown). The pushed down condition will
not be displayed in the LookupJoin node.
For question2: wrong join result?
* I think this is a bug for jdbc lookup source. For the pushed filter
condition, the jdbc lookup source did not consume this filter correctly. After
reading the code, I speculate that this is because the jdbc source doesn't
process this filter condition for dim table.
* To quickly verify this error. you can disable filter push down by adding
config 'table.optimizer.source.predicate-pushdown-enabled'.
* Also, after verifying, if this error is caused by jdbc source, you can @
[~ruanhang1993] taking a look.
> The filter criteria on the lookup table of Lookup join has no effect
> ---------------------------------------------------------------------
>
> Key: FLINK-32579
> URL: https://issues.apache.org/jira/browse/FLINK-32579
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Client
> Affects Versions: 1.17.0, 1.17.1
> Reporter: jasonliangyc
> Priority: Major
> Attachments: image-2023-07-12-09-31-18-261.png,
> image-2023-07-12-09-42-59-231.png, image-2023-07-12-09-47-31-397.png,
> image-2023-07-13-17-19-26-972.png, image-2023-07-13-22-35-35-696.png,
> image-2023-07-13-22-38-16-709.png, image-2023-07-13-22-43-24-213.png,
> image-2023-07-13-22-43-45-957.png, test_case.sql
>
>
> *1.* I joined two tables using the lookup join as below query in sql-client,
> the filter criteria of (p.name = '??????') didn't shows up in the execution
> detail and it returned the rows only base on one condiction (cdc.product_id =
> p.id)
> {code:java}
> select
> cdc.order_id,
> cdc.order_date,
> cdc.customer_name,
> cdc.price,
> p.name
> FROM orders AS cdc
> left JOIN products
> FOR SYSTEM_TIME AS OF cdc.proc_time as p ON p.name = '??????' and
> cdc.product_id = p.id
> ; {code}
> !image-2023-07-12-09-31-18-261.png|width=657,height=132!
>
> *2.* It showed the werid results when i changed the query as below, cause
> there were no data in the table(products) that the value of column 'name' is
> '??????' and and execution detail didn't show us the where criteria.
> {code:java}
> select
> cdc.order_id,
> cdc.order_date,
> cdc.customer_name,
> cdc.price,
> p.name
> FROM orders AS cdc
> left JOIN products
> FOR SYSTEM_TIME AS OF cdc.proc_time as p ON cdc.product_id = p.id
> where p.name = '??????'
> ; {code}
> !image-2023-07-12-09-42-59-231.png|width=684,height=102!
> !image-2023-07-12-09-47-31-397.png|width=685,height=120!
>
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
