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
