kosiew commented on code in PR #25284: URL: https://github.com/apache/datafusion/pull/25284#discussion_r4103815290
########## datafusion/sqllogictest/test_files/subquery.slt: ########## @@ -656,6 +656,125 @@ SELECT t1_id, t1_name FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2_id = t 33 c 44 d +#exists_subquery_with_offset0 +#de-correlated, limit is removed +query TT +explain SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id limit 1 offset 0) +---- +logical_plan +01)LeftSemi Join: t1.t1_id = __correlated_sq_1.t2_id +02)--TableScan: t1 projection=[t1_id, t1_name] +03)--SubqueryAlias: __correlated_sq_1 +04)----TableScan: t2 projection=[t2_id] + +query IT rowsort +SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id limit 1 offset 0) +---- +11 a +22 b +44 d + +#exists_subquery_with_offset +#not de-correlated, the offset could make the subquery empty +query TT +explain SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id offset 1) +---- +logical_plan +01)Filter: EXISTS (<subquery>) +02)--Subquery: +03)----Limit: skip=1, fetch=None +04)------Projection: t2.t2_id, t2.t2_name, t2.t2_int +05)--------Filter: t2.t2_id = outer_ref(t1.t1_id) +06)----------TableScan: t2 +07)--TableScan: t1 projection=[t1_id, t1_name] + +# errors rather than returning wrong rows +query error Physical plan does not support logical expression Exists +SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id offset 1) + +#not_exists_subquery_with_offset +#not de-correlated, the offset could make the subquery empty +query TT +explain SELECT t1_id, t1_name FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id offset 1) +---- +logical_plan +01)Filter: NOT EXISTS (<subquery>) +02)--Subquery: +03)----Limit: skip=1, fetch=None +04)------Projection: t2.t2_id, t2.t2_name, t2.t2_int +05)--------Filter: t2.t2_id = outer_ref(t1.t1_id) +06)----------TableScan: t2 +07)--TableScan: t1 projection=[t1_id, t1_name] + +# errors rather than returning wrong rows +query error Physical plan does not support logical expression Exists +SELECT t1_id, t1_name FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id offset 1) + +#exists_subquery_with_offset_in_disjunction +#not de-correlated, errors rather than returning wrong rows +query error Physical plan does not support logical expression Exists +SELECT t1_id, t1_name FROM t1 WHERE t1_id > 40 OR EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id offset 1) + +#exists_subquery_with_limit0_and_offset +#de-correlated, limit 0 is empty whatever the offset +query TT +explain SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id limit 0 offset 1) +---- +logical_plan EmptyRelation: rows=0 + +query IT rowsort +SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id limit 0 offset 1) +---- + +#exists_subquery_with_non_literal_limit +#not de-correlated, the fetch could evaluate to 0 and make the subquery empty +query TT +explain SELECT t1_id, t1_name FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2_id = t1_id limit (SELECT count(*) FROM t2)) +---- +logical_plan +01)Filter: EXISTS (<subquery>) +02)--Subquery: Review Comment: Could you add a logical-plan-only EXPLAIN test for correlated EXISTS with `OFFSET (SELECT 1)`? Execution does not support that offset, but the plan should show the correlated `Limit` still below `EXISTS`. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
