Re: Question on lookup joins
I don't see a problem in the result. Since you are using LEFT JOIN, the NULLs are expected where there is no matching result in the right table. Hang Ruan 于2023年12月18日周一 09:39写道： > > Hi, David. > > The FLIP-377[1] is about this part. You could take a look at it. > > Best, > Hang > > [1] > https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=276105768 > > > Hang Ruan 于2023年12月17日周日 20:56写道： > > > Hi, David. > > > > I think you are right that the value with NULL should not be returned if > > the filter push down is closed. > > > > Maybe you should explain this sql to make sure this filter not be pushed > > down to the lookup source. > > > > I see the configuration > > 'table.optimizer.source.predicate-pushdown-enabled' relies on the class > > FilterableTableSource, which is deprecated. > > I am not sure whether this configuration is still useful for jdbc > > connector, which is using the SupportsFilterPushDown. > > > > Maybe the jdbc connector should read this configuration and return an > > empty 'acceptedFilters' in the method 'applyFilters'. > > > > Best, > > Hang > > > > David Radley 于2023年12月16日周六 01:47写道： > > > >> Hi , > >> I am working on FLINK-33365 which related to JDBC predicate pushdown. I > >> want to ensure that the same results occur with predicate pushdown as > >> without. So I am asking this question outside the pr / issue. > >> > >> I notice the following behaviour for lookup joins without predicate > >> pushdown. I was not expecting all the s , when there is not a > >> matching join key. ’a’ is a table in paimon and ‘db’ is a relational > >> database. > >> > >> > >> > >> Flink SQL> select * from a; > >> > >> +++-+ > >> > >> | op | ip |proctime | > >> > >> +++-+ > >> > >> | +I |10.10.10.10 | 2023-12-15 17:36:10.028 | > >> > >> | +I |20.20.20.20 | 2023-12-15 17:36:10.030 | > >> > >> | +I |30.30.30.30 | 2023-12-15 17:36:10.031 | > >> > >> ^CQuery terminated, received a total of 3 rows > >> > >> > >> > >> Flink SQL> select * from db_catalog.menagerie.e; > >> > >> > >> +++-+-+-+-+ > >> > >> | op | ip |type | age | > >> height | weight | > >> > >> > >> +++-+-+-+-+ > >> > >> | +I |10.10.10.10 | 1 | 30 | > >>100 | 100 | > >> > >> | +I |10.10.10.10 | 2 | 40 | > >> 90 | 110 | > >> > >> | +I |10.10.10.10 | 2 | 50 | > >> 80 | 120 | > >> > >> | +I |10.10.10.10 | 3 | 50 | > >> 70 | 40 | > >> > >> | +I |20.20.20.20 | 3 | 30 | > >> 80 | 90 | > >> > >> > >> +++-+-+-+-+ > >> > >> Received a total of 5 rows > >> > >> > >> > >> Flink SQL> set table.optimizer.source.predicate-pushdown-enabled=false; > >> > >> [INFO] Execute statement succeed. > >> > >> > >> > >> Flink SQL> SELECT * FROM a left join mariadb_catalog.menagerie.e FOR > >> SYSTEM_TIME AS OF a.proctime on e.type = 2 and a.ip = e.ip; > >> > >> > >> +++-++-+-+-+-+ > >> > >> | op | ip |proctime | > >> ip0 |type | age | height | > >> weight | > >> > >> > >> +++-++-+-+-+-+ > >> > >> | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | > >> 10.10.10.10 | 2 | 40 | 90 | > >> 110 | > >> > >> | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | > >> 10.10.10.10 | 2 | 50 | 80 | > >> 120 | > >> > >> | +I |20.20.20.20 | 2023-12-15 17:38:05.170 | > >> | | | | > >> | > >> > >> | +I |30.30.30.30 | 2023-12-15 17:38:05.172 | > >> | | | | > >> | > >> > >> Unless otherwise stated above: > >> > >> IBM United Kingdom Limited > >> Registered in England and Wales with number 741598 > >> Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU > >> > > -- Best, Benchao Li
Re: Question on lookup joins
Hi, David. The FLIP-377[1] is about this part. You could take a look at it. Best, Hang [1] https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=276105768 Hang Ruan 于2023年12月17日周日 20:56写道： > Hi, David. > > I think you are right that the value with NULL should not be returned if > the filter push down is closed. > > Maybe you should explain this sql to make sure this filter not be pushed > down to the lookup source. > > I see the configuration > 'table.optimizer.source.predicate-pushdown-enabled' relies on the class > FilterableTableSource, which is deprecated. > I am not sure whether this configuration is still useful for jdbc > connector, which is using the SupportsFilterPushDown. > > Maybe the jdbc connector should read this configuration and return an > empty 'acceptedFilters' in the method 'applyFilters'. > > Best, > Hang > > David Radley 于2023年12月16日周六 01:47写道： > >> Hi , >> I am working on FLINK-33365 which related to JDBC predicate pushdown. I >> want to ensure that the same results occur with predicate pushdown as >> without. So I am asking this question outside the pr / issue. >> >> I notice the following behaviour for lookup joins without predicate >> pushdown. I was not expecting all the s , when there is not a >> matching join key. ’a’ is a table in paimon and ‘db’ is a relational >> database. >> >> >> >> Flink SQL> select * from a; >> >> +++-+ >> >> | op | ip |proctime | >> >> +++-+ >> >> | +I |10.10.10.10 | 2023-12-15 17:36:10.028 | >> >> | +I |20.20.20.20 | 2023-12-15 17:36:10.030 | >> >> | +I |30.30.30.30 | 2023-12-15 17:36:10.031 | >> >> ^CQuery terminated, received a total of 3 rows >> >> >> >> Flink SQL> select * from db_catalog.menagerie.e; >> >> >> +++-+-+-+-+ >> >> | op | ip |type | age | >> height | weight | >> >> >> +++-+-+-+-+ >> >> | +I |10.10.10.10 | 1 | 30 | >>100 | 100 | >> >> | +I |10.10.10.10 | 2 | 40 | >> 90 | 110 | >> >> | +I |10.10.10.10 | 2 | 50 | >> 80 | 120 | >> >> | +I |10.10.10.10 | 3 | 50 | >> 70 | 40 | >> >> | +I |20.20.20.20 | 3 | 30 | >> 80 | 90 | >> >> >> +++-+-+-+-+ >> >> Received a total of 5 rows >> >> >> >> Flink SQL> set table.optimizer.source.predicate-pushdown-enabled=false; >> >> [INFO] Execute statement succeed. >> >> >> >> Flink SQL> SELECT * FROM a left join mariadb_catalog.menagerie.e FOR >> SYSTEM_TIME AS OF a.proctime on e.type = 2 and a.ip = e.ip; >> >> >> +++-++-+-+-+-+ >> >> | op | ip |proctime | >> ip0 |type | age | height | >> weight | >> >> >> +++-++-+-+-+-+ >> >> | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | >> 10.10.10.10 | 2 | 40 | 90 | >> 110 | >> >> | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | >> 10.10.10.10 | 2 | 50 | 80 | >> 120 | >> >> | +I |20.20.20.20 | 2023-12-15 17:38:05.170 | >> | | | | >> | >> >> | +I |30.30.30.30 | 2023-12-15 17:38:05.172 | >> | | | | >> | >> >> Unless otherwise stated above: >> >> IBM United Kingdom Limited >> Registered in England and Wales with number 741598 >> Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU >> >
Re: Question on lookup joins
Hi, David. I think you are right that the value with NULL should not be returned if the filter push down is closed. Maybe you should explain this sql to make sure this filter not be pushed down to the lookup source. I see the configuration 'table.optimizer.source.predicate-pushdown-enabled' relies on the class FilterableTableSource, which is deprecated. I am not sure whether this configuration is still useful for jdbc connector, which is using the SupportsFilterPushDown. Maybe the jdbc connector should read this configuration and return an empty 'acceptedFilters' in the method 'applyFilters'. Best, Hang David Radley 于2023年12月16日周六 01:47写道： > Hi , > I am working on FLINK-33365 which related to JDBC predicate pushdown. I > want to ensure that the same results occur with predicate pushdown as > without. So I am asking this question outside the pr / issue. > > I notice the following behaviour for lookup joins without predicate > pushdown. I was not expecting all the s , when there is not a > matching join key. ’a’ is a table in paimon and ‘db’ is a relational > database. > > > > Flink SQL> select * from a; > > +++-+ > > | op | ip |proctime | > > +++-+ > > | +I |10.10.10.10 | 2023-12-15 17:36:10.028 | > > | +I |20.20.20.20 | 2023-12-15 17:36:10.030 | > > | +I |30.30.30.30 | 2023-12-15 17:36:10.031 | > > ^CQuery terminated, received a total of 3 rows > > > > Flink SQL> select * from db_catalog.menagerie.e; > > > +++-+-+-+-+ > > | op | ip |type | age | > height | weight | > > > +++-+-+-+-+ > > | +I |10.10.10.10 | 1 | 30 | > 100 | 100 | > > | +I |10.10.10.10 | 2 | 40 | > 90 | 110 | > > | +I |10.10.10.10 | 2 | 50 | > 80 | 120 | > > | +I |10.10.10.10 | 3 | 50 | > 70 | 40 | > > | +I |20.20.20.20 | 3 | 30 | > 80 | 90 | > > > +++-+-+-+-+ > > Received a total of 5 rows > > > > Flink SQL> set table.optimizer.source.predicate-pushdown-enabled=false; > > [INFO] Execute statement succeed. > > > > Flink SQL> SELECT * FROM a left join mariadb_catalog.menagerie.e FOR > SYSTEM_TIME AS OF a.proctime on e.type = 2 and a.ip = e.ip; > > > +++-++-+-+-+-+ > > | op | ip |proctime | > ip0 |type | age | height | > weight | > > > +++-++-+-+-+-+ > > | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | > 10.10.10.10 | 2 | 40 | 90 | > 110 | > > | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | > 10.10.10.10 | 2 | 50 | 80 | > 120 | > > | +I |20.20.20.20 | 2023-12-15 17:38:05.170 | > | | | | > | > > | +I |30.30.30.30 | 2023-12-15 17:38:05.172 | > | | | | > | > > Unless otherwise stated above: > > IBM United Kingdom Limited > Registered in England and Wales with number 741598 > Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU >
Question on lookup joins
Hi , I am working on FLINK-33365 which related to JDBC predicate pushdown. I want to ensure that the same results occur with predicate pushdown as without. So I am asking this question outside the pr / issue. I notice the following behaviour for lookup joins without predicate pushdown. I was not expecting all the s , when there is not a matching join key. ’a’ is a table in paimon and ‘db’ is a relational database. Flink SQL> select * from a; +++-+ | op | ip |proctime | +++-+ | +I |10.10.10.10 | 2023-12-15 17:36:10.028 | | +I |20.20.20.20 | 2023-12-15 17:36:10.030 | | +I |30.30.30.30 | 2023-12-15 17:36:10.031 | ^CQuery terminated, received a total of 3 rows Flink SQL> select * from db_catalog.menagerie.e; +++-+-+-+-+ | op | ip |type | age | height | weight | +++-+-+-+-+ | +I |10.10.10.10 | 1 | 30 | 100 | 100 | | +I |10.10.10.10 | 2 | 40 | 90 | 110 | | +I |10.10.10.10 | 2 | 50 | 80 | 120 | | +I |10.10.10.10 | 3 | 50 | 70 | 40 | | +I |20.20.20.20 | 3 | 30 | 80 | 90 | +++-+-+-+-+ Received a total of 5 rows Flink SQL> set table.optimizer.source.predicate-pushdown-enabled=false; [INFO] Execute statement succeed. Flink SQL> SELECT * FROM a left join mariadb_catalog.menagerie.e FOR SYSTEM_TIME AS OF a.proctime on e.type = 2 and a.ip = e.ip; +++-++-+-+-+-+ | op | ip |proctime | ip0 |type | age | height | weight | +++-++-+-+-+-+ | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | 10.10.10.10 | 2 | 40 | 90 | 110 | | +I |10.10.10.10 | 2023-12-15 17:38:05.169 | 10.10.10.10 | 2 | 50 | 80 | 120 | | +I |20.20.20.20 | 2023-12-15 17:38:05.170 | | | | | | | +I |30.30.30.30 | 2023-12-15 17:38:05.172 | | | | | | Unless otherwise stated above: IBM United Kingdom Limited Registered in England and Wales with number 741598 Registered office: PO Box 41, North Harbour, Portsmouth, Hants. PO6 3AU
