You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/04/14 01:43:57 UTC
(datafusion) branch gh-readonly-queue/main/pr-21549-0143dfe1692a7a56a72c736041d93797ef651642 created (now cda7b5cc41)
This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a change to branch gh-readonly-queue/main/pr-21549-0143dfe1692a7a56a72c736041d93797ef651642
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at cda7b5cc41 EliminateOuterJoin with Like, IsTrue, IsFalse, IsNotUnknown (#21549)
This branch includes the following new commits:
new cda7b5cc41 EliminateOuterJoin with Like, IsTrue, IsFalse, IsNotUnknown (#21549)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: EliminateOuterJoin with Like, IsTrue, IsFalse, IsNotUnknown (#21549)
Posted by gi...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch gh-readonly-queue/main/pr-21549-0143dfe1692a7a56a72c736041d93797ef651642
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit cda7b5cc4194d84cc2c52ed9c6d239981bc68e83
Author: Subham Singhal <Su...@users.noreply.github.com>
AuthorDate: Tue Apr 14 07:13:36 2026 +0530
EliminateOuterJoin with Like, IsTrue, IsFalse, IsNotUnknown (#21549)
## Which issue does this PR close?
Related to: https://github.com/apache/datafusion/issues/13232
## Rationale for this change
`EliminateOuterJoin` converts LEFT/RIGHT/FULL joins to INNER joins when
WHERE clause predicates are null-rejecting. The
`extract_non_nullable_columns` function recognizes specific expression
types but was missing several common ones: `LIKE`, `IS TRUE`, `IS
FALSE`, and `IS NOT UNKNOWN`.
All of these are null-rejecting:
- `NULL LIKE pattern` → NULL (filtered out)
- `NULL IS TRUE` → false (filtered out)
- `NULL IS FALSE` → false (filtered out)
- `NULL IS NOT UNKNOWN` → false (filtered out)
Note: `IS NOT TRUE`, `IS NOT FALSE`, and `IS UNKNOWN` are intentionally
**not** added — they return true for NULL input, so they are not
null-rejecting.
## What changes are included in this PR?
Added match arms in `extract_non_nullable_columns` for:
- `Expr::Like`
- `Expr::IsTrue` / `Expr::IsFalse` / `Expr::IsNotUnknown`
## Are these changes tested?
Yes — 7 new unit tests:
- 5 positive cases verifying outer → inner conversion (Like, IsTrue,
IsFalse, IsNotUnknown)
- 2 negative cases verifying outer join is preserved (IsNotTrue,
IsUnknown)
## Are there any user-facing changes?
No API changes.
---------
Co-authored-by: Subham Singhal <su...@Subhams-MacBook-Air.local>
---
datafusion/optimizer/src/eliminate_outer_join.rs | 479 ++++++++++++++++++++-
.../test_files/eliminate_outer_join.slt | 280 ++++++++++++
2 files changed, 758 insertions(+), 1 deletion(-)
diff --git a/datafusion/optimizer/src/eliminate_outer_join.rs b/datafusion/optimizer/src/eliminate_outer_join.rs
index c6e27b2603..db21f0eb6c 100644
--- a/datafusion/optimizer/src/eliminate_outer_join.rs
+++ b/datafusion/optimizer/src/eliminate_outer_join.rs
@@ -23,7 +23,7 @@ use datafusion_expr::{Expr, Filter, Operator};
use crate::optimizer::ApplyOrder;
use datafusion_common::tree_node::Transformed;
-use datafusion_expr::expr::{BinaryExpr, Cast, InList, TryCast};
+use datafusion_expr::expr::{BinaryExpr, Cast, InList, Like, TryCast};
use std::sync::Arc;
///
@@ -315,6 +315,37 @@ fn extract_non_nullable_columns(
right_schema,
false,
),
+ // LIKE is null-rejecting: if either the input column or the pattern
+ // is NULL, the result is NULL (filtered out by WHERE).
+ Expr::Like(Like { expr, pattern, .. }) => {
+ extract_non_nullable_columns(
+ expr,
+ non_nullable_cols,
+ left_schema,
+ right_schema,
+ false,
+ );
+ extract_non_nullable_columns(
+ pattern,
+ non_nullable_cols,
+ left_schema,
+ right_schema,
+ false,
+ );
+ }
+ // IS TRUE, IS FALSE, and IS NOT UNKNOWN are null-rejecting:
+ // if the input is NULL, they return false (filtered out by WHERE).
+ // Note: IS NOT TRUE, IS NOT FALSE, and IS UNKNOWN are NOT null-rejecting
+ // because they return true for NULL input.
+ Expr::IsTrue(arg) | Expr::IsFalse(arg) | Expr::IsNotUnknown(arg) => {
+ extract_non_nullable_columns(
+ arg,
+ non_nullable_cols,
+ left_schema,
+ right_schema,
+ false,
+ )
+ }
_ => {}
}
}
@@ -669,6 +700,204 @@ mod tests {
")
}
+ #[test]
+ fn eliminate_left_with_like() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // LIKE rejects nulls: if t2.b is NULL, the result is NULL (filtered out)
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").like(lit("%pattern%")))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r#"
+ Filter: t2.b LIKE Utf8("%pattern%")
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ "#)
+ }
+
+ #[test]
+ fn eliminate_left_with_like_pattern_column() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // LIKE with nullable column on the pattern side:
+ // 'x' LIKE t2.b → if t2.b is NULL, result is NULL (filtered out)
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(lit("x").like(col("t2.b")))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r#"
+ Filter: Utf8("x") LIKE t2.b
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ "#)
+ }
+
+ #[test]
+ fn eliminate_full_with_like_cross_side() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // LIKE with columns from both sides: t1.c LIKE t2.b
+ // If t1 is NULL → NULL LIKE t2.b → NULL → filtered out (left non-nullable)
+ // If t2 is NULL → t1.c LIKE NULL → NULL → filtered out (right non-nullable)
+ // Both sides are non-nullable → FULL → INNER
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t1.c").like(col("t2.b")))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t1.c LIKE t2.b
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_left_with_is_true() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // IS TRUE rejects nulls: if the expression is NULL, IS TRUE returns false
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_true())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS TRUE
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_left_with_is_false() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // IS FALSE rejects nulls: if the expression is NULL, IS FALSE returns false
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_false())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS FALSE
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_left_with_is_not_unknown() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // IS NOT UNKNOWN rejects nulls: if the expression is NULL, IS NOT UNKNOWN returns false
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_not_unknown())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS NOT UNKNOWN
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn no_eliminate_left_with_is_not_true() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // IS NOT TRUE is NOT null-rejecting: if the expression is NULL,
+ // IS NOT TRUE returns true, so null rows pass through
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_not_true())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS NOT TRUE
+ Left Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn no_eliminate_left_with_is_unknown() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // IS UNKNOWN is NOT null-rejecting: if the expression is NULL,
+ // IS UNKNOWN returns true, so null rows pass through
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_unknown())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS UNKNOWN
+ Left Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
#[test]
fn eliminate_full_with_type_cast() -> Result<()> {
let t1 = test_table_scan_with_name("t1")?;
@@ -696,4 +925,252 @@ mod tests {
TableScan: t2
")
}
+
+ // ----- FULL JOIN → LEFT / RIGHT tests -----
+ #[test]
+ fn eliminate_full_to_left_with_left_filter() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // FULL JOIN with null-rejecting filter only on left side → LEFT JOIN
+ // (left side becomes non-nullable, right side stays nullable)
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t1.b").gt(lit(10u32)))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t1.b > UInt32(10)
+ Left Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_full_to_right_with_right_filter() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // FULL JOIN with null-rejecting filter only on right side → RIGHT JOIN
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").in_list(vec![lit(1u32), lit(2u32)], false))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b IN ([UInt32(1), UInt32(2)])
+ Right Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_full_to_left_with_like() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // FULL JOIN with LIKE on left side only → LEFT JOIN
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t1.b").like(lit("%val%")))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r#"
+ Filter: t1.b LIKE Utf8("%val%")
+ Left Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ "#)
+ }
+
+ #[test]
+ fn eliminate_full_to_right_with_is_true() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // FULL JOIN with IS TRUE on right side only → RIGHT JOIN
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(col("t2.b").gt(lit(10u32)).is_true())?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) IS TRUE
+ Right Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ // ----- Nested AND / OR tests -----
+
+ #[test]
+ fn eliminate_left_with_and_multiple_null_rejecting() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // Multiple null-rejecting predicates combined with AND on nullable side
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(binary_expr(
+ col("t2.b").in_list(vec![lit(1u32), lit(2u32)], false),
+ And,
+ col("t2.c").between(lit(5u32), lit(20u32)),
+ ))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b IN ([UInt32(1), UInt32(2)]) AND t2.c BETWEEN UInt32(5) AND UInt32(20)
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn eliminate_left_with_or_same_side() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // OR of two null-rejecting predicates on different columns of the same
+ // nullable side. If t2 rows are NULL (from LEFT JOIN), both t2.b and
+ // t2.c are NULL, so the entire OR evaluates to NULL → filtered out.
+ // This IS null-rejecting, so join should be eliminated.
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(binary_expr(
+ col("t2.b").gt(lit(10u32)),
+ Or,
+ col("t2.c").lt(lit(20u32)),
+ ))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(10) OR t2.c < UInt32(20)
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ #[test]
+ fn no_eliminate_left_with_or_cross_side() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // OR with columns from different sides — t1.b (preserved) OR t2.b
+ // (nullable). When t2 is NULL, t1.b > 10 can still be true, so the
+ // OR is NOT null-rejecting. Join must be preserved.
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(binary_expr(
+ col("t1.b").gt(lit(10u32)),
+ Or,
+ col("t2.b").lt(lit(20u32)),
+ ))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t1.b > UInt32(10) OR t2.b < UInt32(20)
+ Left Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
+
+ // ----- Mixed predicate tests -----
+
+ #[test]
+ fn eliminate_full_with_mixed_predicates() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // FULL JOIN with different null-rejecting expr types on each side:
+ // LIKE on left, BETWEEN on right → INNER JOIN
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(binary_expr(
+ col("t1.b").like(lit("%pattern%")),
+ And,
+ col("t2.b").between(lit(1u32), lit(10u32)),
+ ))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r#"
+ Filter: t1.b LIKE Utf8("%pattern%") AND t2.b BETWEEN UInt32(1) AND UInt32(10)
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ "#)
+ }
+
+ #[test]
+ fn eliminate_left_with_is_true_and_in_list() -> Result<()> {
+ let t1 = test_table_scan_with_name("t1")?;
+ let t2 = test_table_scan_with_name("t2")?;
+
+ // AND of IS TRUE and IN on nullable side — both null-rejecting
+ let plan = LogicalPlanBuilder::from(t1)
+ .join(
+ t2,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(binary_expr(
+ col("t2.b").gt(lit(5u32)).is_true(),
+ And,
+ col("t2.c").in_list(vec![lit(1u32), lit(2u32)], false),
+ ))?
+ .build()?;
+
+ assert_optimized_plan_equal!(plan, @r"
+ Filter: t2.b > UInt32(5) IS TRUE AND t2.c IN ([UInt32(1), UInt32(2)])
+ Inner Join: t1.a = t2.a
+ TableScan: t1
+ TableScan: t2
+ ")
+ }
}
diff --git a/datafusion/sqllogictest/test_files/eliminate_outer_join.slt b/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
index a44d9af77c..dff7692a44 100644
--- a/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
+++ b/datafusion/sqllogictest/test_files/eliminate_outer_join.slt
@@ -198,6 +198,286 @@ select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2) or t2.x is nul
3 30 c NULL NULL NULL
NULL 40 d NULL NULL NULL
+###
+### LIKE tests
+###
+
+# LEFT JOIN + WHERE t2.z LIKE ... -> INNER JOIN
+# LIKE is null-rejecting: if t2.z is NULL, LIKE returns NULL (filtered out).
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where t2.z like 'p%';
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.z LIKE Utf8View("p%")
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where t2.z like 'p%';
+----
+1 10 a 1 100 p
+
+# RIGHT JOIN + WHERE t1.c LIKE ... -> INNER JOIN
+query TT
+explain select * from t1 right join t2 on t1.a = t2.x where t1.c like '%b%';
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--Filter: t1.c LIKE Utf8View("%b%")
+03)----TableScan: t1 projection=[a, b, c]
+04)--TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 right join t2 on t1.a = t2.x where t1.c like '%b%';
+----
+2 20 b 2 200 q
+
+# LEFT JOIN + nullable column on pattern side: 'x' LIKE t2.z -> INNER JOIN
+# LIKE returns NULL when the pattern is NULL, so this is null-rejecting.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where 'x' like t2.z;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: Utf8View("x") LIKE t2.z
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where 'x' like t2.z;
+----
+
+# FULL JOIN + LIKE with columns from both sides: t1.c LIKE t2.z -> INNER JOIN
+# Either side being NULL makes LIKE return NULL, so both sides are non-nullable.
+query TT
+explain select * from t1 full join t2 on t1.a = t2.x where t1.c like t2.z;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x Filter: t1.c LIKE t2.z
+02)--TableScan: t1 projection=[a, b, c]
+03)--TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 full join t2 on t1.a = t2.x where t1.c like t2.z;
+----
+
+# LEFT JOIN + LIKE on preserved side pattern — should NOT eliminate
+# t1.c is from the preserved (left) side, never NULL from the join.
+# This does not reject null t2 rows, so LEFT JOIN must stay.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where 'hello' like t1.c;
+----
+logical_plan
+01)Left Join: t1.a = t2.x
+02)--Filter: Utf8View("hello") LIKE t1.c
+03)----TableScan: t1 projection=[a, b, c]
+04)--TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where 'hello' like t1.c;
+----
+
+###
+### IS TRUE / IS FALSE / IS NOT UNKNOWN tests
+###
+
+# LEFT JOIN + WHERE (t2.y > 150) IS TRUE -> INNER JOIN
+# IS TRUE is null-rejecting: if the expression is NULL, IS TRUE returns false.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is true;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.y > Int32(150) IS TRUE
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is true;
+----
+2 20 b 2 200 q
+
+# LEFT JOIN + WHERE (t2.y > 150) IS FALSE -> INNER JOIN
+# IS FALSE is null-rejecting: if the expression is NULL, IS FALSE returns false.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is false;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.y > Int32(150) IS FALSE
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is false;
+----
+1 10 a 1 100 p
+
+# LEFT JOIN + WHERE (t2.y > 150) IS NOT UNKNOWN -> INNER JOIN
+# IS NOT UNKNOWN is null-rejecting: if the expression is NULL, IS NOT UNKNOWN returns false.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is not unknown;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.y > Int32(150) IS NOT UNKNOWN
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is not unknown;
+----
+1 10 a 1 100 p
+2 20 b 2 200 q
+
+###
+### Negative: IS NOT TRUE / IS UNKNOWN should NOT eliminate
+###
+
+# LEFT JOIN + WHERE (t2.y > 150) IS NOT TRUE -> stays LEFT JOIN
+# IS NOT TRUE returns true for NULL, so it is NOT null-rejecting.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is not true;
+----
+logical_plan
+01)Filter: t2.y > Int32(150) IS NOT TRUE
+02)--Left Join: t1.a = t2.x
+03)----TableScan: t1 projection=[a, b, c]
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is not true;
+----
+1 10 a 1 100 p
+3 30 c NULL NULL NULL
+NULL 40 d NULL NULL NULL
+
+# LEFT JOIN + WHERE (t2.y > 150) IS UNKNOWN -> stays LEFT JOIN
+# IS UNKNOWN returns true for NULL, so it is NOT null-rejecting.
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is unknown;
+----
+logical_plan
+01)Filter: t2.y > Int32(150) IS UNKNOWN
+02)--Left Join: t1.a = t2.x
+03)----TableScan: t1 projection=[a, b, c]
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is unknown;
+----
+3 30 c NULL NULL NULL
+NULL 40 d NULL NULL NULL
+
+###
+### FULL JOIN → LEFT / RIGHT conversion tests
+###
+
+# FULL JOIN + filter on left side only → LEFT JOIN
+# Left side becomes non-nullable, right side stays nullable.
+query TT
+explain select * from t1 full join t2 on t1.a = t2.x where t1.a > 0;
+----
+logical_plan
+01)Left Join: t1.a = t2.x
+02)--Filter: t1.a > Int32(0)
+03)----TableScan: t1 projection=[a, b, c]
+04)--Filter: t2.x > Int32(0)
+05)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 full join t2 on t1.a = t2.x where t1.a > 0;
+----
+1 10 a 1 100 p
+2 20 b 2 200 q
+3 30 c NULL NULL NULL
+
+# FULL JOIN + filter on right side only → RIGHT JOIN
+query TT
+explain select * from t1 full join t2 on t1.a = t2.x where t2.x in (1, 2);
+----
+logical_plan
+01)Right Join: t1.a = t2.x
+02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+03)----TableScan: t1 projection=[a, b, c]
+04)--Filter: t2.x = Int32(1) OR t2.x = Int32(2)
+05)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 full join t2 on t1.a = t2.x where t2.x in (1, 2);
+----
+1 10 a 1 100 p
+2 20 b 2 200 q
+
+# FULL JOIN + LIKE on left, BETWEEN on right → INNER JOIN (mixed predicates)
+query TT
+explain select * from t1 full join t2 on t1.a = t2.x where t1.c like '%a%' and t2.y between 50 and 250;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--Filter: t1.c LIKE Utf8View("%a%")
+03)----TableScan: t1 projection=[a, b, c]
+04)--Filter: t2.y >= Int32(50) AND t2.y <= Int32(250)
+05)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 full join t2 on t1.a = t2.x where t1.c like '%a%' and t2.y between 50 and 250;
+----
+1 10 a 1 100 p
+
+# FULL JOIN + IS TRUE on right only → RIGHT JOIN
+query TT
+explain select * from t1 full join t2 on t1.a = t2.x where (t2.y > 150) is true;
+----
+logical_plan
+01)Right Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.y > Int32(150) IS TRUE
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 full join t2 on t1.a = t2.x where (t2.y > 150) is true;
+----
+2 20 b 2 200 q
+NULL NULL NULL NULL 300 r
+
+###
+### Nested AND / OR tests
+###
+
+# AND of multiple null-rejecting predicates on nullable side → INNER JOIN
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2) and t2.y between 50 and 250;
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--Filter: t1.a = Int32(1) OR t1.a = Int32(2)
+03)----TableScan: t1 projection=[a, b, c]
+04)--Filter: (t2.x = Int32(1) OR t2.x = Int32(2)) AND t2.y >= Int32(50) AND t2.y <= Int32(250)
+05)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where t2.x in (1, 2) and t2.y between 50 and 250;
+----
+1 10 a 1 100 p
+2 20 b 2 200 q
+
+# IS TRUE AND IN on nullable side → INNER JOIN (mixed null-rejecting types)
+query TT
+explain select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is true and t2.z like 'q%';
+----
+logical_plan
+01)Inner Join: t1.a = t2.x
+02)--TableScan: t1 projection=[a, b, c]
+03)--Filter: t2.y > Int32(150) IS TRUE AND t2.z LIKE Utf8View("q%")
+04)----TableScan: t2 projection=[x, y, z]
+
+query IITIIT rowsort
+select * from t1 left join t2 on t1.a = t2.x where (t2.y > 150) is true and t2.z like 'q%';
+----
+2 20 b 2 200 q
+
###
### Cleanup
###
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org