[BUG] Filter not pushdown to ScanNode

[GoGoWen]

1,
CREATE TABLE lineorders (
orderdate varchar(100) NOT NULL,
orderid int NOT NULL,
country_id int NOT NULL,
vender_id int NOT NULL,
ordernum int NOT NULL,
ordemoney int NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(orderdate, orderid, country_id)
COMMENT 'OLAP'
PARTITION BY LIST(orderdate)
(PARTITION p1992 VALUES IN ("0-2020"),
PARTITION p1993 VALUES IN ("0-2021"),
PARTITION p1994 VALUES IN ("0-2022"),
PARTITION p1995 VALUES IN ("0-2023"),
PARTITION p1996 VALUES IN ("0-2024"),
PARTITION p1997 VALUES IN ("0-2025"))
DISTRIBUTED BY HASH(orderid) BUCKETS 48
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

2,
explain select * from ( select orderid,orderdate,country_id,ordernum, ordemoney, SUBSTR(lineorders.orderdate,3,4) AS dt, ROW_NUMBER() OVER(PARTITION BY lineorders.orderid,lineorders.orderdate ORDER BYlineorders.country_id DESC) AS rn from lineorders) a where SUBSTR(a.dt, 1, 4) = SUBSTR(curdate(), 1, 4);

3, will get explain string like following

| 0:VOlapScanNode(169) |
| TABLE: t.lineorders(lineorders), PREAGGREGATION: ON |
| partitions=6/6 (p1992,p1993,p1994,p1995,p1996,p1997) |
| tablets=288/288, tabletList=1011601,1011605,1011609 ... |
| cardinality=6, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| final projections: orderid[https://github.com//issues/1], orderdate[#0], country_id[https://github.com//issues/2], ordernum[https://github.com//issues/4], ordemoney[https://github.com//issues/5], substring(orderdate[#0], 3, 4) |
| final project output tuple id: 1 |
+---------------------------------------------------------------------------------------------------------------------------------+

[GoGoWen]

Doris Plan:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========== PARSED PLAN (time: 4ms) ========== |
| UnboundResultSink[6] ( ) |
| +--LogicalProject[5] ( distinct=false, projects=[*], excepts=[] ) |
| +--LogicalFilter[4] ( predicates=('SUBSTR('a.dt, 1, 4) = 'SUBSTR('curdate(), 1, 4)) ) |
| +--LogicalSubQueryAlias ( qualifier=[a] ) |
| +--LogicalProject[2] ( distinct=false, projects=['orderid, 'orderdate, 'country_id, 'ordernum, 'ordemoney, UnboundAlias('SUBSTR('lineorders.orderdate, 3, 4)) AS dt, UnboundAlias('ROW_NUMBER() WindowSpec(PARTITION BY 'lineorders.orderid, 'lineorders.orderdate ORDER BY 'lineorders.country_id desc)) AS rn], excepts=[] ) |
| +--LogicalCheckPolicy ( ) |
| +--UnboundRelation ( id=RelationId#0, nameParts=lineorders ) |
| |
| ========== ANALYZED PLAN (time: 12ms) ========== |
| LogicalResultSink[22] ( outputExprs=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, dt#6, rn#7] ) |
| +--LogicalProject[20] ( distinct=false, projects=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, dt#6, rn#7], excepts=[] ) |
| +--LogicalFilter[18] ( predicates=(substring(dt#6, 1, 4) = substring(cast(current_date() as VARCHAR(65533)), 1, 4)) ) |
| +--LogicalSubQueryAlias ( qualifier=[a] ) |
| +--LogicalProject[15] ( distinct=false, projects=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, substring(orderdate#0, 3, 4) AS dt#6, row_number() WindowSpec(PARTITION BY orderid#1, orderdate#0 ORDER BY country_id#2 desc) AS rn#7], excepts=[] ) |
| +--LogicalOlapScan ( qualified=internal.t.lineorders, indexName=<index_not_selected>, selectedIndexId=1010640, preAgg=UNSET ) |
| |
| ========== REWRITTEN PLAN (time: 5ms) ========== |
| LogicalResultSink[81] ( outputExprs=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, dt#6, rn#7] ) |
| +--LogicalFilter[79] ( predicates=(substring(dt#6, 1, 4) = '2025') ) |
| +--LogicalWindow ( windowExpressions=[row_number() WindowSpec(PARTITION BY orderid#1, orderdate#0 ORDER BY country_id#2 desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn#7], isChecked=true ) |
| +--LogicalProject[74] ( distinct=false, projects=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, substring(orderdate#0, 3, 4) AS dt#6], excepts=[] ) |
| +--LogicalOlapScan ( qualified=internal.t.lineorders, indexName=lineorders, selectedIndexId=1010640, preAgg=ON ) |
| |
| ========== OPTIMIZED PLAN (time: 2ms) ========== |
| PhysicalResultSink[181] ( outputExprs=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, dt#6, rn#7] ) |
| +--PhysicalDistribute[178]@3 ( stats=0, distributionSpec=DistributionSpecGather ) |
| +--PhysicalFilter[175]@3 ( stats=0, predicates=(substring(dt#6, 1, 4) = '2025') ) |
| +--PhysicalWindow[172]@2 ( windowFrameGroup=(Funcs=[row_number() WindowSpec(PARTITION BY orderid#1, orderdate#0 ORDER BY country_id#2 desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rn#7], PartitionKeys=[orderid#1, orderdate#0], OrderKeys=[country_id#2 desc], WindowFrame=WindowFrame(ROWS, UNBOUNDED_PRECEDING, CURRENT_ROW)), requiredProperties=[DistributionSpecHash ( orderedShuffledColumns=[1, 0], shuffleType=REQUIRE, tableId=-1, selectedIndexId=-1, partitionIds=[], equivalenceExprIds=[[1], [0]], exprIdToEquivalenceSet={1=0, 0=1} ) Order: ([orderid#1 asc, orderdate#0 asc, country_id#2 desc])], stats=6 ) |
| +--PhysicalQuickSort[169]@1 ( orderKeys=[orderid#1 asc, orderdate#0 asc, country_id#2 desc], phase=LOCAL_SORT, stats=6 ) |
| +--PhysicalDistribute[166]@1 ( stats=6, distributionSpec=DistributionSpecHash ( orderedShuffledColumns=[1, 0], shuffleType=EXECUTION_BUCKETED, tableId=-1, selectedIndexId=-1, partitionIds=[], equivalenceExprIds=[[1], [0]], exprIdToEquivalenceSet={1=0, 0=1} ) ) |
| +--PhysicalProject[163]@1 ( stats=6, projects=[orderid#1, orderdate#0, country_id#2, ordernum#4, ordemoney#5, substring(orderdate#0, 3, 4) AS dt#6] ) |
| +--PhysicalOlapScan[lineorders]@0 ( stats=6 ) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
33 rows in set (0.04 sec)