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 <david_rad...@uk.ibm.com> 于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 <NULL>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 | > <NULL> | <NULL> | <NULL> | <NULL> | > <NULL> | > > | +I | 30.30.30.30 | 2023-12-15 17:38:05.172 | > <NULL> | <NULL> | <NULL> | <NULL> | > <NULL> | > > 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 >
