@@ -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 ( ) {
0 commit comments