Repository: duckdb/duckdb (language: python)

Issue:
asof join's result is incorrect in v1.1.0  when add filter for right table on join condition
### What happens?

asof join's result is incorrect in v1.1.0  when add filter for right table on join condition

### To Reproduce

data prepare
```sql
create table t1(seq_no int,amount decimal(10,2));
insert into t1
values
(1,1.00),(2,null),(3,null),(4,null),(5,2.00),
(6,null),(7,null),(8,3.00),(9,null),(10,null),(11,5.00);
```

diff results between v1.1.0 and v1.0.0 when query as below
```sql
select * from t1;
select a.seq_no,a.amount,b.amount
  from t1 as a 
  asof join t1 as b
    on a.seq_no>=b.seq_no
   and b.amount is not null;
```
expect result:
|seq_no|amount|amount|
|---|---|---|
|1|1.00|1.00|
|2||1.00|
|3||1.00|
|4||1.00|
|5|2.00|2.00|
|6||2.00|
|7||2.00|
|8|3.00|3.00|
|9||3.00|
|10||3.00|
|11|5.00|5.00|

result in v1.1.0
|seq_no|amount|amount|
|---|---|---|
|1|1.00|1.00|
|5|2.00|2.00|
|8|3.00|3.00|
|11|5.00|5.00|

change right table with filter to sub-query.results between v1.1.0 and v1.0.0 are the same.

```sql
select a.seq_no,a.amount,b.amount
  from t1 as a
  asof join (select seq_no,amount
               from t1
              where amount is not null
            ) as b
    on a.seq_no>=b.seq_no;
```
result:
|seq_no|amount|amount|
|---|---|---|
|1|1.00|1.00|
|2||1.00|
|3||1.00|
|4||1.00|
|5|2|2.00|
|6||2.00|
|7||2.00|
|8|3|3.00|
|9||3.00|
|10||3.00|
|11|5|5.00|

v1.0.0 screenshot
![17261262705990](https://github.com/user-attachments/assets/7e6d4921-ed2e-4a2a-8a4e-27f290332383)
v1.1.0 screenshot
![17261261624687](https://github.com/user-attachments/assets/5fee500e-068d-4ebc-816a-a17a79c88b54)

### OS:

windows10 x86_64

### DuckDB Version:

1.1.0

### DuckDB Client:

cli

### Hardware:

_No response_

### Full Name:

detran

### Affiliation:

sunline

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

### Did you include all relevant data sets for reproducing the issue?

Yes

### Did you include all code required to reproduce the issue?

- [ ] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [ ] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/optimizer/filter_pushdown.cpp b/src/optimizer/filter_pushdown.cpp
index 4af2a075ff53..f5a7b405f013 100644
--- a/src/optimizer/filter_pushdown.cpp
+++ b/src/optimizer/filter_pushdown.cpp
@@ -132,6 +132,10 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownJoin(unique_ptr<LogicalOpera
 
 	switch (join.join_type) {
 	case JoinType::INNER:
+		//	AsOf joins can't push anything into the RHS, so treat it as a left join
+		if (op->type == LogicalOperatorType::LOGICAL_ASOF_JOIN) {
+			return PushdownLeftJoin(std::move(op), left_bindings, right_bindings);
+		}
 		return PushdownInnerJoin(std::move(op), left_bindings, right_bindings);
 	case JoinType::LEFT:
 		return PushdownLeftJoin(std::move(op), left_bindings, right_bindings);
diff --git a/src/optimizer/pushdown/pushdown_cross_product.cpp b/src/optimizer/pushdown/pushdown_cross_product.cpp
index a05da2c27b82..40ff4dc3cc5d 100644
--- a/src/optimizer/pushdown/pushdown_cross_product.cpp
+++ b/src/optimizer/pushdown/pushdown_cross_product.cpp
@@ -14,9 +14,6 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownCrossProduct(unique_ptr<Logi
 	switch (op->type) {
 	case LogicalOperatorType::LOGICAL_CROSS_PRODUCT:
 		break;
-	case LogicalOperatorType::LOGICAL_ASOF_JOIN:
-		join_ref_type = JoinRefType::ASOF;
-		break;
 	default:
 		throw InternalException("Unsupported join type for cross product push down");
 	}
@@ -33,14 +30,7 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownCrossProduct(unique_ptr<Logi
 				// bindings match left side: push into left
 				left_pushdown.filters.push_back(std::move(f));
 			} else if (side == JoinSide::RIGHT) {
-				// bindings match right side: push into right
-				if (join_ref_type == JoinRefType::ASOF) {
-					// AsOf is really a table lookup, so we don't push filters
-					// down into the lookup (right) table
-					join_expressions.push_back(std::move(f->filter));
-				} else {
-					right_pushdown.filters.push_back(std::move(f));
-				}
+				right_pushdown.filters.push_back(std::move(f));
 			} else {
 				D_ASSERT(side == JoinSide::BOTH || side == JoinSide::NONE);
 				// bindings match both: turn into join condition
diff --git a/src/optimizer/pushdown/pushdown_inner_join.cpp b/src/optimizer/pushdown/pushdown_inner_join.cpp
index 791bbb9dcc02..8370f4ca9ba6 100644
--- a/src/optimizer/pushdown/pushdown_inner_join.cpp
+++ b/src/optimizer/pushdown/pushdown_inner_join.cpp
@@ -26,8 +26,7 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownInnerJoin(unique_ptr<Logical
 		}
 	} else {
 		// comparison join
-		D_ASSERT(op->type == LogicalOperatorType::LOGICAL_COMPARISON_JOIN ||
-		         op->type == LogicalOperatorType::LOGICAL_ASOF_JOIN);
+		D_ASSERT(op->type == LogicalOperatorType::LOGICAL_COMPARISON_JOIN);
 		auto &comp_join = join.Cast<LogicalComparisonJoin>();
 		// turn the conditions into filters
 		for (auto &i : comp_join.conditions) {
@@ -40,11 +39,6 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownInnerJoin(unique_ptr<Logical
 	}
 	GenerateFilters();
 
-	//	For AsOf joins, just use the original op
-	if (op->type == LogicalOperatorType::LOGICAL_ASOF_JOIN) {
-		return PushdownCrossProduct(std::move(op));
-	}
-
 	// turn the inner join into a cross product
 	auto cross_product = make_uniq<LogicalCrossProduct>(std::move(op->children[0]), std::move(op->children[1]));
 
diff --git a/src/optimizer/pushdown/pushdown_left_join.cpp b/src/optimizer/pushdown/pushdown_left_join.cpp
index fae4b3f6ea20..a20b81327c64 100644
--- a/src/optimizer/pushdown/pushdown_left_join.cpp
+++ b/src/optimizer/pushdown/pushdown_left_join.cpp
@@ -93,7 +93,7 @@ unique_ptr<LogicalOperator> FilterPushdown::PushdownLeftJoin(unique_ptr<LogicalO
 			// erase the filter from the list of filters
 			filters.erase_at(i);
 			i--;
-		} else {
+		} else if (op->type != LogicalOperatorType::LOGICAL_ASOF_JOIN) {
 			// bindings match right side or both sides: we cannot directly push it into the right
 			// however, if the filter removes rows with null values from the RHS we can turn the left outer join
 			// in an inner join, and then push down as we would push down an inner join
diff --git a/src/planner/binder/tableref/plan_joinref.cpp b/src/planner/binder/tableref/plan_joinref.cpp
index 7285e643aba9..53c2ee8cc903 100644
--- a/src/planner/binder/tableref/plan_joinref.cpp
+++ b/src/planner/binder/tableref/plan_joinref.cpp
@@ -76,9 +76,7 @@ void LogicalComparisonJoin::ExtractJoinConditions(
 		auto total_side = JoinSide::GetJoinSide(*expr, left_bindings, right_bindings);
 		if (total_side != JoinSide::BOTH) {
 			// join condition does not reference both sides, add it as filter under the join
-			// BUT don't push right side filters into AsOf because it is really a table lookup
-			// and we shouldn't remove anything from the table.
-			if (type == JoinType::LEFT && total_side == JoinSide::RIGHT && ref_type != JoinRefType::ASOF) {
+			if ((type == JoinType::LEFT || ref_type == JoinRefType::ASOF) && total_side == JoinSide::RIGHT) {
 				// filter is on RHS and the join is a LEFT OUTER join, we can push it in the right child
 				if (right_child->type != LogicalOperatorType::LOGICAL_FILTER) {
 					// not a filter yet, push a new empty filter


Test patch:
diff --git a/test/sql/join/asof/test_asof_join_pushdown.test b/test/sql/join/asof/test_asof_join_pushdown.test
index ba298378c495..1ef308a6eb91 100644
--- a/test/sql/join/asof/test_asof_join_pushdown.test
+++ b/test/sql/join/asof/test_asof_join_pushdown.test
@@ -44,6 +44,29 @@ ASOF LEFT JOIN (
 0 	0	0.0	0.0
 1 	0	NULL	0.0
 
+statement ok
+CREATE OR REPLACE TABLE issue12215 AS
+	SELECT col0 AS starts, col1 AS ends
+	FROM (VALUES
+		(5, 9),
+		(10, 13),
+		(14, 20),
+		(21, 23)
+	);
+
+query II
+SELECT
+    s1.starts as s1_starts,
+    s2.starts as s2_starts,
+FROM issue12215 AS s1 
+ASOF JOIN issue12215 as s2 
+  ON s2.ends >= (s1.ends - 5)
+WHERE s1_starts <> s2_starts
+ORDER BY ALL
+----
+10	5
+21	14
+
 query II
 WITH t as (
     SELECT
@@ -79,3 +102,43 @@ ORDER BY ALL
 ----
 5	6
 10	11
+
+statement ok
+CREATE TABLE issue13899(seq_no INT, amount DECIMAL(10,2));
+
+statement ok
+INSERT INTO issue13899 VALUES
+	(1,1.00),
+	(2,null),
+	(3,null),
+	(4,null),
+	(5,2.00),
+	(6,null),
+	(7,null),
+	(8,3.00),
+	(9,null),
+	(10,null),
+	(11,5.00);
+
+query III
+select 
+	a.seq_no,
+	a.amount,
+	b.amount
+from issue13899 as a 
+asof join issue13899 as b
+    on a.seq_no>=b.seq_no
+   and b.amount is not null
+ORDER BY 1
+----
+1	1.00	1.00
+2	NULL	1.00
+3	NULL	1.00
+4	NULL	1.00
+5	2.00	2.00
+6	NULL	2.00
+7	NULL	2.00
+8	3.00	3.00
+9	NULL	3.00
+10	NULL	3.00
+11	5.00	5.00