This is an automated email from the ASF dual-hosted git repository.
comphead pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 7ae1ccb497 Improve push down filter of join (#13184)
7ae1ccb497 is described below
commit 7ae1ccb4971474d376e5bed1d7116fcaf23f906f
Author: JasonLi <[email protected]>
AuthorDate: Fri Nov 1 02:11:32 2024 +0800
Improve push down filter of join (#13184)
* improve push down filter of join
---
datafusion/optimizer/src/push_down_filter.rs | 337 +++++++++---
datafusion/optimizer/src/utils.rs | 8 +
.../test_files/join_disable_repartition_joins.slt | 23 +-
.../test_files/push_down_filter_join.slt | 612 +++++++++++++++++++++
4 files changed, 909 insertions(+), 71 deletions(-)
diff --git a/datafusion/optimizer/src/push_down_filter.rs
b/datafusion/optimizer/src/push_down_filter.rs
index a0262d7d95..3b3693cc94 100644
--- a/datafusion/optimizer/src/push_down_filter.rs
+++ b/datafusion/optimizer/src/push_down_filter.rs
@@ -36,7 +36,9 @@ use datafusion_expr::{
};
use crate::optimizer::ApplyOrder;
-use crate::utils::{has_all_column_refs, is_restrict_null_predicate};
+use crate::utils::{
+ contain_all_columns, has_all_column_refs, is_restrict_null_predicate,
+};
use crate::{OptimizerConfig, OptimizerRule};
/// Optimizer rule for pushing (moving) filter expressions down in a plan so
@@ -214,19 +216,19 @@ impl<'a> ColumnChecker<'a> {
}
/// Return true if the expression references only columns from the left
side of the join
- fn is_left_only(&mut self, predicate: &Expr) -> bool {
+ fn left_only(&mut self, column_refs: &HashSet<&Column>) -> bool {
if self.left_columns.is_none() {
self.left_columns = Some(schema_columns(self.left_schema));
}
- has_all_column_refs(predicate, self.left_columns.as_ref().unwrap())
+ contain_all_columns(column_refs, self.left_columns.as_ref().unwrap())
}
/// Return true if the expression references only columns from the right
side of the join
- fn is_right_only(&mut self, predicate: &Expr) -> bool {
+ fn right_only(&mut self, column_refs: &HashSet<&Column>) -> bool {
if self.right_columns.is_none() {
self.right_columns = Some(schema_columns(self.right_schema));
}
- has_all_column_refs(predicate, self.right_columns.as_ref().unwrap())
+ contain_all_columns(column_refs, self.right_columns.as_ref().unwrap())
}
}
@@ -410,10 +412,13 @@ fn extract_or_clause(expr: &Expr, schema_columns:
&HashSet<Column>) -> Option<Ex
/// push down join/cross-join
fn push_down_all_join(
predicates: Vec<Expr>,
- inferred_join_predicates: Vec<Expr>,
+ inferred_from_predicates: Vec<Expr>,
mut join: Join,
on_filter: Vec<Expr>,
+ inferred_from_on_filter: Vec<Expr>,
) -> Result<Transformed<LogicalPlan>> {
+ assert_ne!(join.join_type, JoinType::Full);
+
let is_inner_join = join.join_type == JoinType::Inner;
// Get pushable predicates from current optimizer state
let (left_preserved, right_preserved) = lr_is_preserved(join.join_type);
@@ -429,14 +434,24 @@ fn push_down_all_join(
let mut keep_predicates = vec![];
let mut join_conditions = vec![];
let mut checker = ColumnChecker::new(left_schema, right_schema);
+
for predicate in predicates {
- if left_preserved && checker.is_left_only(&predicate) {
+ let columns = predicate.column_refs();
+ macro_rules! restrict_null {
+ () => {{
+ let predicate_cloned = predicate.clone();
+ let cols = columns.iter().cloned();
+ is_restrict_null_predicate(predicate_cloned,
cols).unwrap_or(false)
+ }};
+ }
+
+ if checker.left_only(&columns) && (left_preserved || restrict_null!())
{
left_push.push(predicate);
- } else if right_preserved && checker.is_right_only(&predicate) {
+ } else if checker.right_only(&columns) && (right_preserved ||
restrict_null!()) {
right_push.push(predicate);
} else if is_inner_join && can_evaluate_as_join_condition(&predicate)?
{
- // Here we do not differ it is eq or non-eq predicate,
ExtractEquijoinPredicate will extract the eq predicate
- // and convert to the join on condition
+ // Here we do not differ it is eq or non-eq predicate,
ExtractEquijoinPredicate will
+ // extract the eq predicate and convert to the join on condition
join_conditions.push(predicate);
} else {
keep_predicates.push(predicate);
@@ -444,10 +459,13 @@ fn push_down_all_join(
}
// For infer predicates, if they can not push through join, just drop them
- for predicate in inferred_join_predicates {
- if left_preserved && checker.is_left_only(&predicate) {
+ // Because we check whether it is_restrict_null in the process of Infer,
there is no need to
+ // check again
+ for predicate in inferred_from_predicates {
+ let columns = predicate.column_refs();
+ if checker.left_only(&columns) {
left_push.push(predicate);
- } else if right_preserved && checker.is_right_only(&predicate) {
+ } else if checker.right_only(&columns) {
right_push.push(predicate);
}
}
@@ -455,15 +473,24 @@ fn push_down_all_join(
let mut on_filter_join_conditions = vec![];
let (on_left_preserved, on_right_preserved) =
on_lr_is_preserved(join.join_type);
- if !on_filter.is_empty() {
- for on in on_filter {
- if on_left_preserved && checker.is_left_only(&on) {
- left_push.push(on)
- } else if on_right_preserved && checker.is_right_only(&on) {
- right_push.push(on)
- } else {
- on_filter_join_conditions.push(on)
- }
+ for on in on_filter {
+ let columns = on.column_refs();
+ if on_left_preserved && checker.left_only(&columns) {
+ left_push.push(on)
+ } else if on_right_preserved && checker.right_only(&columns) {
+ right_push.push(on)
+ } else {
+ on_filter_join_conditions.push(on)
+ }
+ }
+
+ // For infer predicates, if they can not push through join, just drop them
+ for on in inferred_from_on_filter {
+ let columns = on.column_refs();
+ if on_left_preserved && checker.left_only(&columns) {
+ left_push.push(on)
+ } else if on_right_preserved && checker.right_only(&columns) {
+ right_push.push(on)
}
}
@@ -519,6 +546,17 @@ fn push_down_join(
join: Join,
parent_predicate: Option<&Expr>,
) -> Result<Transformed<LogicalPlan>> {
+ if matches!(join.join_type, JoinType::Full) {
+ let plan = LogicalPlan::Join(join);
+ return Ok(match parent_predicate {
+ Some(predicate) =>
Transformed::yes(LogicalPlan::Filter(Filter::try_new(
+ predicate.clone(),
+ Arc::new(plan),
+ )?)),
+ None => Transformed::no(plan),
+ });
+ }
+
// Split the parent predicate into individual conjunctive parts.
let predicates = parent_predicate
.map_or_else(Vec::new, |pred| split_conjunction_owned(pred.clone()));
@@ -530,17 +568,24 @@ fn push_down_join(
.map_or_else(Vec::new, |filter|
split_conjunction_owned(filter.clone()));
// Are there any new join predicates that can be inferred from the filter
expressions?
- let inferred_join_predicates =
+ let (inferred_from_predicates, inferred_from_on_filter) =
infer_join_predicates(&join, &predicates, &on_filters)?;
if on_filters.is_empty()
+ && inferred_from_on_filter.is_empty()
&& predicates.is_empty()
- && inferred_join_predicates.is_empty()
+ && inferred_from_predicates.is_empty()
{
return Ok(Transformed::no(LogicalPlan::Join(join)));
}
- push_down_all_join(predicates, inferred_join_predicates, join, on_filters)
+ push_down_all_join(
+ predicates,
+ inferred_from_predicates,
+ join,
+ on_filters,
+ inferred_from_on_filter,
+ )
}
/// Extracts any equi-join join predicates from the given filter expressions.
@@ -553,11 +598,13 @@ fn push_down_join(
/// * `on_filters` filters from the join ON clause that have not already been
/// identified as join predicates
///
+/// # Return Value
+/// A tuple of Expr Vec - (inferred_from_predicates, inferred_from_on_filters).
fn infer_join_predicates(
join: &Join,
predicates: &[Expr],
on_filters: &[Expr],
-) -> Result<Vec<Expr>> {
+) -> Result<(Vec<Expr>, Vec<Expr>)> {
// Only allow both side key is column.
let join_col_keys = join
.on
@@ -578,6 +625,7 @@ fn infer_join_predicates(
predicates,
&mut inferred_predicates,
)?;
+ let inferred_from_predicates = inferred_predicates.take_all();
infer_join_predicates_from_on_filters(
&join_col_keys,
@@ -585,8 +633,9 @@ fn infer_join_predicates(
on_filters,
&mut inferred_predicates,
)?;
+ let inferred_from_on_filters = inferred_predicates.predicates;
- Ok(inferred_predicates.predicates)
+ Ok((inferred_from_predicates, inferred_from_on_filters))
}
/// Inferred predicates collector.
@@ -610,6 +659,12 @@ impl InferredPredicates {
}
}
+ fn take_all(&mut self) -> Vec<Expr> {
+ let mut temp = vec![];
+ std::mem::swap(&mut self.predicates, &mut temp);
+ temp
+ }
+
fn try_build_predicate(
&mut self,
predicate: Expr,
@@ -2107,11 +2162,10 @@ mod tests {
// filter not duplicated nor pushed down - i.e. noop
let expected = "\
- Filter: test2.a <= Int64(1)\
- \n Left Join: Using test.a = test2.a\
- \n TableScan: test, full_filters=[test.a <= Int64(1)]\
- \n Projection: test2.a\
- \n TableScan: test2";
+ Left Join: Using test.a = test2.a\
+ \n TableScan: test, full_filters=[test.a <= Int64(1)]\
+ \n Projection: test2.a\
+ \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -2145,11 +2199,10 @@ mod tests {
// filter not duplicated nor pushed down - i.e. noop
let expected = "\
- Filter: test.a <= Int64(1)\
- \n Right Join: Using test.a = test2.a\
- \n TableScan: test\
- \n Projection: test2.a\
- \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
+ Right Join: Using test.a = test2.a\
+ \n TableScan: test, full_filters=[test.a <= Int64(1)]\
+ \n Projection: test2.a\
+ \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -2187,7 +2240,7 @@ mod tests {
Left Join: Using test.a = test2.a\
\n TableScan: test, full_filters=[test.a <= Int64(1)]\
\n Projection: test2.a\
- \n TableScan: test2";
+ \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -2223,7 +2276,7 @@ mod tests {
// filter sent to right side of join, not duplicated to the left
let expected = "\
Right Join: Using test.a = test2.a\
- \n TableScan: test\
+ \n TableScan: test, full_filters=[test.a <= Int64(1)]\
\n Projection: test2.a\
\n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
@@ -2272,6 +2325,47 @@ mod tests {
assert_optimized_plan_eq(plan, expected)
}
+ #[test]
+ fn join_with_non_restrict_null_predicate() -> Result<()> {
+ let table_scan = test_table_scan()?;
+ let left = LogicalPlanBuilder::from(table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let right_table_scan = test_table_scan_with_name("test2")?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let filter = col("test.b").is_null().and(col("test2.b").is_null());
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ JoinType::Inner,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(filter)?
+ .build()?;
+
+ // not part of the test, just good to know:
+ assert_eq!(
+ format!("{plan}"),
+ "Filter: test.b IS NULL AND test2.b IS NULL\
+ \n Inner Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2"
+ );
+
+ let expected = "\
+ Inner Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test, full_filters=[test.b IS NULL]\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2, full_filters=[test2.b IS NULL]";
+ assert_optimized_plan_eq(plan, expected)
+ }
+
/// join filter should be completely removed after pushdown
#[test]
fn join_filter_removed() -> Result<()> {
@@ -2393,7 +2487,49 @@ mod tests {
\n Projection: test.a, test.b, test.c\
\n TableScan: test\
\n Projection: test2.a, test2.b, test2.c\
- \n TableScan: test2, full_filters=[test2.c > UInt32(4)]";
+ \n TableScan: test2, full_filters=[test2.c > UInt32(4), test2.a >
UInt32(1)]";
+ assert_optimized_plan_eq(plan, expected)
+ }
+
+ #[test]
+ fn left_join_with_non_restrict_null_predicate() -> Result<()> {
+ let table_scan = test_table_scan()?;
+ let left = LogicalPlanBuilder::from(table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let right_table_scan = test_table_scan_with_name("test2")?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let filter = col("test.b").is_null().and(col("test2.b").is_null());
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ JoinType::Left,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(filter)?
+ .build()?;
+
+ // not part of the test, just good to know:
+ assert_eq!(
+ format!("{plan}"),
+ "Filter: test.b IS NULL AND test2.b IS NULL\
+ \n Left Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2"
+ );
+
+ let expected = "\
+ Filter: test2.b IS NULL\
+ \n Left Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test, full_filters=[test.b IS NULL]\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2";
assert_optimized_plan_eq(plan, expected)
}
@@ -2440,6 +2576,87 @@ mod tests {
assert_optimized_plan_eq(plan, expected)
}
+ #[test]
+ fn right_join_with_non_restrict_null_predicate() -> Result<()> {
+ let table_scan = test_table_scan()?;
+ let left = LogicalPlanBuilder::from(table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let right_table_scan = test_table_scan_with_name("test2")?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let filter = col("test.b").is_null().and(col("test2.b").is_null());
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ JoinType::Right,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(filter)?
+ .build()?;
+
+ // not part of the test, just good to know:
+ assert_eq!(
+ format!("{plan}"),
+ "Filter: test.b IS NULL AND test2.b IS NULL\
+ \n Right Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2"
+ );
+
+ let expected = "\
+ Filter: test.b IS NULL\
+ \n Right Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2, full_filters=[test2.b IS NULL]";
+ assert_optimized_plan_eq(plan, expected)
+ }
+
+ #[test]
+ fn full_join() -> Result<()> {
+ let table_scan = test_table_scan()?;
+ let left = LogicalPlanBuilder::from(table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let right_table_scan = test_table_scan_with_name("test2")?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("a"), col("b"), col("c")])?
+ .build()?;
+ let filter = col("test.a")
+ .gt(lit(1u32))
+ .and(col("test.b").lt(col("test2.b")))
+ .and(col("test2.c").gt(lit(4u32)));
+ let plan = LogicalPlanBuilder::from(left)
+ .join(
+ right,
+ JoinType::Full,
+ (vec![Column::from_name("a")], vec![Column::from_name("a")]),
+ None,
+ )?
+ .filter(filter)?
+ .build()?;
+
+ // not part of the test, just good to know:
+ assert_eq!(
+ format!("{plan}"),
+ "Filter: test.a > UInt32(1) AND test.b < test2.b AND test2.c >
UInt32(4)\
+ \n Full Join: test.a = test2.a\
+ \n Projection: test.a, test.b, test.c\
+ \n TableScan: test\
+ \n Projection: test2.a, test2.b, test2.c\
+ \n TableScan: test2"
+ );
+
+ let expected = &format!("{plan}");
+ assert_optimized_plan_eq(plan, expected)
+ }
+
/// single table predicate parts of ON condition should not be pushed
#[test]
fn full_join_on_with_filter() -> Result<()> {
@@ -2963,11 +3180,10 @@ Projection: a, b
// Inferred the predicate `test1.a <= Int64(1)` and push it down to
the left side.
let expected = "\
- Filter: test2.a <= Int64(1)\
- \n LeftSemi Join: test1.a = test2.a\
- \n TableScan: test1, full_filters=[test1.a <= Int64(1)]\
- \n Projection: test2.a, test2.b\
- \n TableScan: test2";
+ LeftSemi Join: test1.a = test2.a\
+ \n TableScan: test1, full_filters=[test1.a <= Int64(1)]\
+ \n Projection: test2.a, test2.b\
+ \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -3044,11 +3260,10 @@ Projection: a, b
// Inferred the predicate `test2.a <= Int64(1)` and push it down to
the right side.
let expected = "\
- Filter: test1.a <= Int64(1)\
- \n RightSemi Join: test1.a = test2.a\
- \n TableScan: test1\
- \n Projection: test2.a, test2.b\
- \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
+ RightSemi Join: test1.a = test2.a\
+ \n TableScan: test1, full_filters=[test1.a <= Int64(1)]\
+ \n Projection: test2.a, test2.b\
+ \n TableScan: test2, full_filters=[test2.a <= Int64(1)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -3129,12 +3344,11 @@ Projection: a, b
// For left anti, filter of the right side filter can be pushed down.
let expected = "\
- Filter: test2.a > UInt32(2)\
- \n LeftAnti Join: test1.a = test2.a\
- \n Projection: test1.a, test1.b\
- \n TableScan: test1, full_filters=[test1.a > UInt32(2)]\
- \n Projection: test2.a, test2.b\
- \n TableScan: test2";
+ LeftAnti Join: test1.a = test2.a\
+ \n Projection: test1.a, test1.b\
+ \n TableScan: test1, full_filters=[test1.a > UInt32(2)]\
+ \n Projection: test2.a, test2.b\
+ \n TableScan: test2, full_filters=[test2.a > UInt32(2)]";
assert_optimized_plan_eq(plan, expected)
}
@@ -3220,12 +3434,11 @@ Projection: a, b
// For right anti, filter of the left side can be pushed down.
let expected = "\
- Filter: test1.a > UInt32(2)\
- \n RightAnti Join: test1.a = test2.a\
- \n Projection: test1.a, test1.b\
- \n TableScan: test1\
- \n Projection: test2.a, test2.b\
- \n TableScan: test2, full_filters=[test2.a > UInt32(2)]";
+ RightAnti Join: test1.a = test2.a\
+ \n Projection: test1.a, test1.b\
+ \n TableScan: test1, full_filters=[test1.a > UInt32(2)]\
+ \n Projection: test2.a, test2.b\
+ \n TableScan: test2, full_filters=[test2.a > UInt32(2)]";
assert_optimized_plan_eq(plan, expected)
}
diff --git a/datafusion/optimizer/src/utils.rs
b/datafusion/optimizer/src/utils.rs
index 9f325bc01b..9f8d7b7f97 100644
--- a/datafusion/optimizer/src/utils.rs
+++ b/datafusion/optimizer/src/utils.rs
@@ -79,6 +79,14 @@ pub fn optimize_children(
/// Returns true if `expr` contains all columns in `schema_cols`
pub(crate) fn has_all_column_refs(expr: &Expr, schema_cols: &HashSet<Column>)
-> bool {
let column_refs = expr.column_refs();
+ contain_all_columns(&column_refs, schema_cols)
+}
+
+/// Returns true if `column_refs` contains all columns in `schema_cols`
+pub(crate) fn contain_all_columns(
+ column_refs: &HashSet<&Column>,
+ schema_cols: &HashSet<Column>,
+) -> bool {
// note can't use HashSet::intersect because of different types (owned vs
References)
schema_cols
.iter()
diff --git
a/datafusion/sqllogictest/test_files/join_disable_repartition_joins.slt
b/datafusion/sqllogictest/test_files/join_disable_repartition_joins.slt
index c56c59b1bd..eb47ccdd43 100644
--- a/datafusion/sqllogictest/test_files/join_disable_repartition_joins.slt
+++ b/datafusion/sqllogictest/test_files/join_disable_repartition_joins.slt
@@ -89,20 +89,25 @@ logical_plan
02)--Projection: t2.a AS a2, t2.b
03)----RightSemi Join: t1.d = t2.d, t1.c = t2.c
04)------SubqueryAlias: t1
-05)--------TableScan: annotated_data projection=[c, d]
-06)------SubqueryAlias: t2
-07)--------Filter: annotated_data.d = Int32(3)
-08)----------TableScan: annotated_data projection=[a, b, c, d],
partial_filters=[annotated_data.d = Int32(3)]
+05)--------Filter: annotated_data.d = Int32(3)
+06)----------TableScan: annotated_data projection=[c, d],
partial_filters=[annotated_data.d = Int32(3)]
+07)------SubqueryAlias: t2
+08)--------Filter: annotated_data.d = Int32(3)
+09)----------TableScan: annotated_data projection=[a, b, c, d],
partial_filters=[annotated_data.d = Int32(3)]
physical_plan
01)SortPreservingMergeExec: [a2@0 ASC NULLS LAST,b@1 ASC NULLS LAST], fetch=10
02)--ProjectionExec: expr=[a@0 as a2, b@1 as b]
03)----CoalesceBatchesExec: target_batch_size=8192, fetch=10
04)------HashJoinExec: mode=CollectLeft, join_type=RightSemi, on=[(d@1, d@3),
(c@0, c@2)], projection=[a@0, b@1]
-05)--------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/core/tests/data/window_2.csv]]}, projection=[c, d],
has_header=true
-06)--------CoalesceBatchesExec: target_batch_size=8192
-07)----------FilterExec: d@3 = 3
-08)------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
-09)--------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/core/tests/data/window_2.csv]]}, projection=[a, b,
c, d], output_ordering=[a@0 ASC NULLS LAST, b@1 ASC NULLS LAST, c@2 ASC NULLS
LAST], has_header=true
+05)--------CoalescePartitionsExec
+06)----------CoalesceBatchesExec: target_batch_size=8192
+07)------------FilterExec: d@1 = 3
+08)--------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
+09)----------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/core/tests/data/window_2.csv]]}, projection=[c, d],
has_header=true
+10)--------CoalesceBatchesExec: target_batch_size=8192
+11)----------FilterExec: d@3 = 3
+12)------------RepartitionExec: partitioning=RoundRobinBatch(4),
input_partitions=1
+13)--------------CsvExec: file_groups={1 group:
[[WORKSPACE_ROOT/datafusion/core/tests/data/window_2.csv]]}, projection=[a, b,
c, d], output_ordering=[a@0 ASC NULLS LAST, b@1 ASC NULLS LAST, c@2 ASC NULLS
LAST], has_header=true
# preserve_right_semi_join
query II nosort
diff --git a/datafusion/sqllogictest/test_files/push_down_filter_join.slt
b/datafusion/sqllogictest/test_files/push_down_filter_join.slt
new file mode 100644
index 0000000000..f687c542a6
--- /dev/null
+++ b/datafusion/sqllogictest/test_files/push_down_filter_join.slt
@@ -0,0 +1,612 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+
+# http://www.apache.org/licenses/LICENSE-2.0
+
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+# Contents
+# Inner Join
+# `WHERE` clause
+# `ON` clause
+# Left Outer Join
+# `WHERE` clause
+# `ON` clause
+# Right Outer Join
+# `WHERE` clause
+# `ON` clause
+# Full Outer Join
+# `WHERE` clause
+# `ON` clause
+
+# Create table t1
+statement ok
+CREATE TABLE t1(t1_id INT, t1_name VARCHAR) AS VALUES
+(11, 'a'),
+(22, 'b'),
+(33, 'c'),
+(44, 'd'),
+(77, 'e'),
+(88, NULL),
+(99, NULL)
+
+# Create table t2
+statement ok
+CREATE TABLE t2(t2_id INT, t2_name VARCHAR) AS VALUES
+(11, 'z'),
+(22, NULL),
+(44, 'x'),
+(55, 'w'),
+(99, 'u')
+
+# Inner Join
+
+## `WHERE` clause
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id ORDER BY t1_id, t1_name,
t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+44 d 44 x
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+
+## `ON` clause
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 INNER JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+# Left Outer Join
+
+## `WHERE` clause
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id ORDER BY t1_id, t1_name,
t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d 44 x
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+88 NULL NULL NULL
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+33 c NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+33 c NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+
+## `ON` clause
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+
+# Right Outer Join
+
+## `WHERE` clause
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id ORDER BY t1_id, t1_name,
t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+44 d 44 x
+99 NULL 99 u
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+
+## `ON` clause
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+NULL NULL 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+NULL NULL 11 z
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+99 NULL 99 u
+NULL NULL 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+NULL NULL 11 z
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 RIGHT JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+# Full Outer Join
+
+## `WHERE` clause
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id ORDER BY t1_id, t1_name,
t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d 44 x
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+88 NULL NULL NULL
+99 NULL 99 u
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+22 b 22 NULL
+33 c NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id <= 22
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t1.t1_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id WHERE t2.t2_id IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+33 c NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+
+## `ON` clause
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name = 'a'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name = 'z'
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+NULL NULL 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 11 z
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b NULL NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL 99 u
+NULL NULL 11 z
+NULL NULL 22 NULL
+NULL NULL 44 x
+NULL NULL 55 w
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_name IS NULL
ORDER BY t1_id, t1_name, t2_id, t2_name
+----
+11 a NULL NULL
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 11 z
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t1.t1_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
+
+query ITIT
+SELECT * FROM t1 FULL JOIN t2 ON t1.t1_id = t2.t2_id AND t2.t2_id <= 22 ORDER
BY t1_id, t1_name, t2_id, t2_name
+----
+11 a 11 z
+22 b 22 NULL
+33 c NULL NULL
+44 d NULL NULL
+77 e NULL NULL
+88 NULL NULL NULL
+99 NULL NULL NULL
+NULL NULL 44 x
+NULL NULL 55 w
+NULL NULL 99 u
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
