Skip to content

Commit fc8fb06

Browse files
authored
remove SubqueryFilterToJoin (#4731)
* remove `SubqueryFilterToJoin` * correct UT
1 parent 326117c commit fc8fb06

4 files changed

Lines changed: 187 additions & 425 deletions

File tree

‎datafusion/optimizer/src/decorrelate_where_in.rs‎

Lines changed: 187 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -220,10 +220,196 @@ mod tests {
220220
use crate::test::*;
221221
use datafusion_common::Result;
222222
use datafusion_expr::{
223-
col, in_subquery, lit, logical_plan::LogicalPlanBuilder, not_in_subquery,
223+
and, binary_expr, col, in_subquery, lit, logical_plan::LogicalPlanBuilder,
224+
not_in_subquery, or, Operator,
224225
};
225226
use std::ops::Add;
226227

228+
fn assert_optimized_plan_equal(plan: &LogicalPlan, expected: &str) -> Result<()> {
229+
assert_optimized_plan_eq_display_indent(
230+
Arc::new(DecorrelateWhereIn::new()),
231+
plan,
232+
expected,
233+
);
234+
Ok(())
235+
}
236+
237+
fn test_subquery_with_name(name: &str) -> Result<Arc<LogicalPlan>> {
238+
let table_scan = test_table_scan_with_name(name)?;
239+
Ok(Arc::new(
240+
LogicalPlanBuilder::from(table_scan)
241+
.project(vec![col("c")])?
242+
.build()?,
243+
))
244+
}
245+
246+
/// Test for several IN subquery expressions
247+
#[test]
248+
fn in_subquery_multiple() -> Result<()> {
249+
let table_scan = test_table_scan()?;
250+
let plan = LogicalPlanBuilder::from(table_scan)
251+
.filter(and(
252+
in_subquery(col("c"), test_subquery_with_name("sq_1")?),
253+
in_subquery(col("b"), test_subquery_with_name("sq_2")?),
254+
))?
255+
.project(vec![col("test.b")])?
256+
.build()?;
257+
258+
let expected = "Projection: test.b [b:UInt32]\
259+
\n LeftSemi Join: test.b = __sq_2.c [a:UInt32, b:UInt32, c:UInt32]\
260+
\n LeftSemi Join: test.c = __sq_1.c [a:UInt32, b:UInt32, c:UInt32]\
261+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]\
262+
\n SubqueryAlias: __sq_1 [c:UInt32]\
263+
\n Projection: sq_1.c AS c [c:UInt32]\
264+
\n TableScan: sq_1 [a:UInt32, b:UInt32, c:UInt32]\
265+
\n SubqueryAlias: __sq_2 [c:UInt32]\
266+
\n Projection: sq_2.c AS c [c:UInt32]\
267+
\n TableScan: sq_2 [a:UInt32, b:UInt32, c:UInt32]";
268+
269+
assert_optimized_plan_equal(&plan, expected)
270+
}
271+
272+
/// Test for IN subquery with additional AND filter
273+
#[test]
274+
fn in_subquery_with_and_filters() -> Result<()> {
275+
let table_scan = test_table_scan()?;
276+
let plan = LogicalPlanBuilder::from(table_scan)
277+
.filter(and(
278+
in_subquery(col("c"), test_subquery_with_name("sq")?),
279+
and(
280+
binary_expr(col("a"), Operator::Eq, lit(1_u32)),
281+
binary_expr(col("b"), Operator::Lt, lit(30_u32)),
282+
),
283+
))?
284+
.project(vec![col("test.b")])?
285+
.build()?;
286+
287+
let expected = "Projection: test.b [b:UInt32]\
288+
\n Filter: test.a = UInt32(1) AND test.b < UInt32(30) [a:UInt32, b:UInt32, c:UInt32]\
289+
\n LeftSemi Join: test.c = __sq_1.c [a:UInt32, b:UInt32, c:UInt32]\
290+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]\
291+
\n SubqueryAlias: __sq_1 [c:UInt32]\
292+
\n Projection: sq.c AS c [c:UInt32]\
293+
\n TableScan: sq [a:UInt32, b:UInt32, c:UInt32]";
294+
295+
assert_optimized_plan_equal(&plan, expected)
296+
}
297+
298+
/// Test for IN subquery with additional OR filter
299+
/// filter expression not modified
300+
#[test]
301+
fn in_subquery_with_or_filters() -> Result<()> {
302+
let table_scan = test_table_scan()?;
303+
let plan = LogicalPlanBuilder::from(table_scan)
304+
.filter(or(
305+
and(
306+
binary_expr(col("a"), Operator::Eq, lit(1_u32)),
307+
binary_expr(col("b"), Operator::Lt, lit(30_u32)),
308+
),
309+
in_subquery(col("c"), test_subquery_with_name("sq")?),
310+
))?
311+
.project(vec![col("test.b")])?
312+
.build()?;
313+
314+
let expected = "Projection: test.b [b:UInt32]\
315+
\n Filter: test.a = UInt32(1) AND test.b < UInt32(30) OR test.c IN (<subquery>) [a:UInt32, b:UInt32, c:UInt32]\
316+
\n Subquery: [c:UInt32]\
317+
\n Projection: sq.c [c:UInt32]\
318+
\n TableScan: sq [a:UInt32, b:UInt32, c:UInt32]\
319+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]";
320+
321+
assert_optimized_plan_equal(&plan, expected)
322+
}
323+
324+
#[test]
325+
fn in_subquery_with_and_or_filters() -> Result<()> {
326+
let table_scan = test_table_scan()?;
327+
let plan = LogicalPlanBuilder::from(table_scan)
328+
.filter(and(
329+
or(
330+
binary_expr(col("a"), Operator::Eq, lit(1_u32)),
331+
in_subquery(col("b"), test_subquery_with_name("sq1")?),
332+
),
333+
in_subquery(col("c"), test_subquery_with_name("sq2")?),
334+
))?
335+
.project(vec![col("test.b")])?
336+
.build()?;
337+
338+
let expected = "Projection: test.b [b:UInt32]\
339+
\n Filter: test.a = UInt32(1) OR test.b IN (<subquery>) [a:UInt32, b:UInt32, c:UInt32]\
340+
\n Subquery: [c:UInt32]\
341+
\n Projection: sq1.c [c:UInt32]\
342+
\n TableScan: sq1 [a:UInt32, b:UInt32, c:UInt32]\
343+
\n LeftSemi Join: test.c = __sq_1.c [a:UInt32, b:UInt32, c:UInt32]\
344+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]\
345+
\n SubqueryAlias: __sq_1 [c:UInt32]\
346+
\n Projection: sq2.c AS c [c:UInt32]\
347+
\n TableScan: sq2 [a:UInt32, b:UInt32, c:UInt32]";
348+
349+
assert_optimized_plan_equal(&plan, expected)
350+
}
351+
352+
/// Test for nested IN subqueries
353+
#[test]
354+
fn in_subquery_nested() -> Result<()> {
355+
let table_scan = test_table_scan()?;
356+
357+
let subquery = LogicalPlanBuilder::from(test_table_scan_with_name("sq")?)
358+
.filter(in_subquery(col("a"), test_subquery_with_name("sq_nested")?))?
359+
.project(vec![col("a")])?
360+
.build()?;
361+
362+
let plan = LogicalPlanBuilder::from(table_scan)
363+
.filter(in_subquery(col("b"), Arc::new(subquery)))?
364+
.project(vec![col("test.b")])?
365+
.build()?;
366+
367+
let expected = "Projection: test.b [b:UInt32]\
368+
\n LeftSemi Join: test.b = __sq_1.a [a:UInt32, b:UInt32, c:UInt32]\
369+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]\
370+
\n SubqueryAlias: __sq_1 [a:UInt32]\
371+
\n Projection: sq.a AS a [a:UInt32]\
372+
\n LeftSemi Join: sq.a = __sq_2.c [a:UInt32, b:UInt32, c:UInt32]\
373+
\n TableScan: sq [a:UInt32, b:UInt32, c:UInt32]\
374+
\n SubqueryAlias: __sq_2 [c:UInt32]\
375+
\n Projection: sq_nested.c AS c [c:UInt32]\
376+
\n TableScan: sq_nested [a:UInt32, b:UInt32, c:UInt32]";
377+
378+
assert_optimized_plan_equal(&plan, expected)
379+
}
380+
381+
/// Test for filter input modification in case filter not supported
382+
/// Outer filter expression not modified while inner converted to join
383+
#[test]
384+
fn in_subquery_input_modified() -> Result<()> {
385+
let table_scan = test_table_scan()?;
386+
let plan = LogicalPlanBuilder::from(table_scan)
387+
.filter(in_subquery(col("c"), test_subquery_with_name("sq_inner")?))?
388+
.project(vec![col("b"), col("c")])?
389+
.alias("wrapped")?
390+
.filter(or(
391+
binary_expr(col("b"), Operator::Lt, lit(30_u32)),
392+
in_subquery(col("c"), test_subquery_with_name("sq_outer")?),
393+
))?
394+
.project(vec![col("b")])?
395+
.build()?;
396+
397+
let expected = "Projection: wrapped.b [b:UInt32]\
398+
\n Filter: wrapped.b < UInt32(30) OR wrapped.c IN (<subquery>) [b:UInt32, c:UInt32]\
399+
\n Subquery: [c:UInt32]\
400+
\n Projection: sq_outer.c [c:UInt32]\
401+
\n TableScan: sq_outer [a:UInt32, b:UInt32, c:UInt32]\
402+
\n SubqueryAlias: wrapped [b:UInt32, c:UInt32]\
403+
\n Projection: test.b, test.c [b:UInt32, c:UInt32]\
404+
\n LeftSemi Join: test.c = __sq_1.c [a:UInt32, b:UInt32, c:UInt32]\
405+
\n TableScan: test [a:UInt32, b:UInt32, c:UInt32]\
406+
\n SubqueryAlias: __sq_1 [c:UInt32]\
407+
\n Projection: sq_inner.c AS c [c:UInt32]\
408+
\n TableScan: sq_inner [a:UInt32, b:UInt32, c:UInt32]";
409+
410+
assert_optimized_plan_equal(&plan, expected)
411+
}
412+
227413
#[cfg(test)]
228414
#[ctor::ctor]
229415
fn init() {

‎datafusion/optimizer/src/lib.rs‎

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,6 @@ pub mod push_down_projection;
3333
pub mod scalar_subquery_to_join;
3434
pub mod simplify_expressions;
3535
pub mod single_distinct_to_groupby;
36-
pub mod subquery_filter_to_join;
3736
pub mod type_coercion;
3837
pub mod utils;
3938

‎datafusion/optimizer/src/optimizer.rs‎

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,6 @@ use crate::rewrite_disjunctive_predicate::RewriteDisjunctivePredicate;
3535
use crate::scalar_subquery_to_join::ScalarSubqueryToJoin;
3636
use crate::simplify_expressions::SimplifyExpressions;
3737
use crate::single_distinct_to_groupby::SingleDistinctToGroupBy;
38-
use crate::subquery_filter_to_join::SubqueryFilterToJoin;
3938
use crate::type_coercion::TypeCoercion;
4039
use crate::unwrap_cast_in_comparison::UnwrapCastInComparison;
4140
use chrono::{DateTime, Utc};
@@ -244,7 +243,6 @@ impl Optimizer {
244243
Arc::new(DecorrelateWhereExists::new()),
245244
Arc::new(DecorrelateWhereIn::new()),
246245
Arc::new(ScalarSubqueryToJoin::new()),
247-
Arc::new(SubqueryFilterToJoin::new()),
248246
// simplify expressions does not simplify expressions in subqueries, so we
249247
// run it again after running the optimizations that potentially converted
250248
// subqueries to joins

0 commit comments

Comments
 (0)