optimizer: add tests for predicate pushdown rule

[MingjiHan99]

• [ ] One table one predicate (select a from t where a > 10)
• [ ] One table multiple predicates (select a from t where a > 10 and a < 20). We need rule which can merge predicates.
• [ ] Multiple table multiple predicates (select a, b from t join s where a > 10 and b < 20)

[MingjiHan99]

@skyzh

[skyzh]

IIRC, all three should have been implemented in https://github.com/risinglightdb/risinglight/tree/main/src/optimizer/rules (filter join && filter scan). Let's change the issue title to "add tests for predicate pushdown"?

[skyzh]

Except the last one, I'm not sure if it has been implemented yet...

[likg227]

IIRC, all three should have been implemented in https://github.com/risinglightdb/risinglight/tree/main/src/optimizer/rules (filter join && filter scan). Let's change the issue title to "add tests for predicate pushdown"?

I have already add tests in storage, but left the filter_scan_rule untested yet.

[skyzh]

> explain select a, b from t, s where a > 10 and b < 20;
+-----------------------------------------------------------------------------------------------------------------------------------+
| PhysicalProjection: exprs [InputRef #0, InputRef #1]                                                                              |
|   PhysicalNestedLoopJoin: op Inner, predicate: Bool(true) (const)                                                                 |
|     PhysicalTableScan: table #0, columns [0], with_row_handler: false, is_sorted: false, expr: Gt(InputRef #0, Int32(10) (const)) |
|     PhysicalTableScan: table #1, columns [0], with_row_handler: false, is_sorted: false, expr: Lt(InputRef #0, Int32(20) (const)) |
|                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------+

Seems also supported 🤪