jayzhan211 commented on code in PR #26904: URL: https://github.com/apache/datafusion/pull/26904#discussion_r4504883295
########## datafusion/sqllogictest/test_files/subquery_decorrelation.slt: ########## @@ -0,0 +1,874 @@ +# SPDX-License-Identifier: Apache-2.0 +# SPDX-FileCopyrightText: See the NOTICE file distributed with this work for additional information regarding copyright ownership +# SPDX-FileContributor: Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements + +# https://github.com/apache/datafusion/issues/5492#issuecomment-5934476405 +statement ok +CREATE TABLE t1(t1_id INT, t1_name TEXT, t1_int INT) AS VALUES +(11, 'a', 1), +(22, 'b', 2), +(33, 'c', 3), +(44, 'd', 4); + +statement ok +CREATE TABLE t2(t2_id INT, t2_name TEXT, t2_int INT) AS VALUES +(11, 'z', 3), +(22, 'y', 1), +(44, 'x', 3), +(55, 'w', 3); + +statement ok +CREATE TABLE t3(t3_id INT PRIMARY KEY, t3_name TEXT, t3_int INT) AS VALUES +(11, 'e', 3), +(22, 'f', 1), +(44, 'g', 3), +(55, 'h', 3); + +# exists_depth_2_correlation_skipping_a_level +query error Schema error: No field named t1\.t1_id +SELECT t1.t1_id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.t2_id = t1.t1_id AND EXISTS (SELECT 1 FROM t3 WHERE t3.t3_id > t1.t1_id)); + +# exists_depth_2_correlation_one_level_each +query I rowsort +SELECT t1.t1_id FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.t2_id = t1.t1_id AND EXISTS (SELECT 1 FROM t3 WHERE t3.t3_id = t2.t2_id)); +---- +11 +22 +44 + +# exists_in_inner_join_on_reading_both_inputs +query error Schema error: No field named t2\.t2_id +SELECT t1.t1_id FROM t1 JOIN t2 ON EXISTS (SELECT 1 FROM t3 WHERE t3.t3_id = t1.t1_id AND t3.t3_id = t2.t2_id); + +# exists_in_scalar_subquery_correlated_to_both_levels +query error Schema error: No field named t1\.t1_id +SELECT t1.t1_id, (SELECT COUNT(*) FROM t2 WHERE EXISTS (SELECT 1 FROM t3 WHERE t3.t3_id = t2.t2_id AND t3.t3_id = t1.t1_id)) AS c FROM t1; + +# outer_column_in_aggregate_argument +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT SUM(t2.t2_int + t1.t1_int) FROM t2) FROM t1; + +# outer_column_added_to_aggregate_result +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT MIN(t2.t2_int) + t1.t1_int FROM t2) AS c FROM t1; + +# outer_column_projected_under_limit +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT t1.t1_int FROM t2 LIMIT 1) AS c FROM t1; + +# greater_than_all_over_expression_with_outer_column +query error Physical plan does not support logical expression Exists +SELECT t1.t1_id FROM t1 WHERE t1.t1_id > ALL(SELECT (t2.t2_id + t1.t1_id - 1) / 2 FROM t2 WHERE t2.t2_id IS NOT NULL); + +# correlated_inequality_below_count +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT COUNT(*) FROM t2 WHERE t2.t2_int > t1.t1_int) AS c FROM t1; + +# outer_column_as_grouping_sets_key +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT SUM(t2.t2_int) FROM t2 GROUP BY GROUPING SETS((t1.t1_int))) AS c FROM t1; + +# invalid_aggregate_over_outer_column_without_from +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.t1_int, (SELECT SUM(t1.t1_int)) AS c FROM t1; + +# invalid_aggregate_over_outer_column_in_having +query error Physical plan does not support logical expression Exists +SELECT t1.t1_id FROM t1 WHERE EXISTS (SELECT t2.t2_id FROM t2 GROUP BY t2.t2_id HAVING t2.t2_id > MIN(t1.t1_int)); + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +statement ok +DROP TABLE t3; + +# https://github.com/apache/datafusion/issues/26710 +statement ok +CREATE TABLE invoices (id INT, order_id INT, total INT); + +statement ok +INSERT INTO invoices VALUES (1,1,10),(2,1,20),(3,2,30),(4,2,40); + +statement ok +CREATE TABLE orders (id INT); + +statement ok +INSERT INTO orders VALUES (1),(2),(3); + +# correlated_scalar_subquery_order_by_limit_1 +query error Physical plan does not support logical expression ScalarSubquery +SELECT o.id, (SELECT i.total FROM invoices i WHERE i.order_id = o.id ORDER BY i.id DESC LIMIT 1) AS last_total FROM orders o ORDER BY o.id; + +statement ok +DROP TABLE invoices; + +statement ok +DROP TABLE orders; + +# https://github.com/apache/datafusion/issues/16137 +statement ok +CREATE TABLE src (id INT, parent_id INT, name VARCHAR); + +statement ok +INSERT INTO src VALUES (1, NULL, 'Europe'), (2, 1, 'Warsaw'), (3, 1, 'Paris'); + +# non_aggregated_correlated_scalar_subquery_over_same_table +query error Correlated scalar subquery must be aggregated to return at most one row +SELECT id, name, (SELECT p.name FROM src p WHERE p.id = s.parent_id) AS parent_name FROM src s ORDER BY id; + +statement ok +DROP TABLE src; + +# https://github.com/apache/datafusion/issues/16134 +statement ok +CREATE TABLE s (a VARCHAR); + +statement ok +INSERT INTO s VALUES ('a'),('b'); + +# uncorrelated_scalar_subquery_returning_two_rows +query error Scalar subquery returned more than one row +SELECT s.a, (SELECT x FROM (VALUES (1), (2)) t(x)) sub FROM s ORDER BY 1,2; + +statement ok +DROP TABLE s; + +statement ok +CREATE TABLE d (k INT, v INT); + +statement ok +INSERT INTO d VALUES (1,10),(1,11); + +# correlated_scalar_subquery_returning_two_rows_per_outer_row +query error A GROUP BY clause in a scalar correlated subquery cannot contain non-correlated columns +SELECT k, (SELECT max(v) FROM d d2 WHERE d2.k = d.k GROUP BY d2.k, d2.v) FROM d ORDER BY 1,2; + +statement ok +DROP TABLE d; + +# https://github.com/apache/datafusion/issues/16134 +statement ok +set datafusion.optimizer.enable_physical_uncorrelated_scalar_subquery = false; + +statement ok +CREATE TABLE s (a VARCHAR); + +statement ok +INSERT INTO s VALUES ('a'),('b'); + +# uncorrelated_scalar_subquery_returning_two_rows_on_left_join_path +query TI +SELECT s.a, (SELECT x FROM (VALUES (1), (2)) t(x)) sub FROM s ORDER BY 1,2; +---- +a 1 +a 2 +b 1 +b 2 + +statement ok +DROP TABLE s; + +statement ok +RESET datafusion.optimizer.enable_physical_uncorrelated_scalar_subquery; + +# https://github.com/apache/datafusion/issues/15558 +statement ok +CREATE TABLE employees (employee_id INT, employee_name VARCHAR, dept_id INT, salary INT); + +statement ok +INSERT INTO employees VALUES (1,'a',1,10),(2,'b',1,20),(3,'c',1,30),(4,'d',2,40); + +# correlated_scalar_subquery_depth_2 +query error Schema error: No field named e1\.dept_id +SELECT e1.employee_name FROM employees e1 WHERE e1.salary >= (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id AND e2.salary >= (SELECT AVG(e3.salary) FROM employees e3 WHERE e3.dept_id = e1.dept_id)) ORDER BY 1; + +statement ok +DROP TABLE employees; + +# https://github.com/apache/datafusion/issues/21727 +statement ok +CREATE TABLE t0 (g INT); + +statement ok +CREATE TABLE t1 (a INT); + +statement ok +CREATE TABLE t2 (c INT); + +statement ok +INSERT INTO t0 VALUES (1), (2); + +statement ok +INSERT INTO t1 VALUES (10); + +statement ok +INSERT INTO t2 VALUES (1); + +# exists_grandparent_reference_at_deepest_level +query error Schema error: No field named t0\.g +SELECT g FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.c = t0.g)) ORDER BY g; + +statement ok +DROP TABLE t0; + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +# https://github.com/apache/datafusion/issues/14554 +statement ok +CREATE TABLE t1 (id INT, name VARCHAR, v INT); + +statement ok +INSERT INTO t1 VALUES (1,'a',0),(2,'b',5),(3,'z',1); + +statement ok +CREATE TABLE t2 (id INT, name VARCHAR); + +statement ok +INSERT INTO t2 VALUES (1,'b'),(2,'x'),(3,'x'); + +# correlated_disjunction_below_count +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.id FROM t1 WHERE (SELECT count(*) FROM t2 WHERE t1.id = t2.id OR t1.name = t2.name) > t1.v ORDER BY 1; + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +statement ok +CREATE TABLE o (k INT); + +statement ok +INSERT INTO o VALUES (0),(2),(5); + +statement ok +CREATE TABLE i (y INT); + +statement ok +INSERT INTO i VALUES (1),(2),(3); + +# correlated_range_below_count +query error Physical plan does not support logical expression ScalarSubquery +SELECT o.k, (SELECT count(*) FROM i WHERE i.y > o.k) AS c FROM o ORDER BY 1; + +statement ok +DROP TABLE o; + +statement ok +DROP TABLE i; + +# https://github.com/apache/datafusion/issues/25789 +statement ok +CREATE TABLE o(k INT) AS VALUES (1), (5); + +statement ok +CREATE TABLE a(id INT) AS VALUES (1), (2); + +statement ok +CREATE TABLE b(id INT, y INT) AS VALUES (1, 1), (2, 2); + +# correlated_filter_below_nullable_side_of_left_join +query error Physical plan does not support logical expression Exists +SELECT o.k, EXISTS (SELECT 1 FROM a LEFT JOIN (SELECT * FROM b WHERE b.y = o.k) AS b ON a.id = b.id WHERE b.y IS NULL) AS e FROM o ORDER BY k; + +statement ok +DROP TABLE o; + +statement ok +DROP TABLE a; + +statement ok +DROP TABLE b; + +# https://github.com/apache/datafusion/issues/26704 +statement ok +CREATE TABLE t1 (a INT, b VARCHAR, x INT); + +statement ok +INSERT INTO t1 VALUES (1, 'p', 10), (2, 'p', 20), (3, 'q', 30), (NULL, 'q', 40), (2, 'q', 50); + +statement ok +CREATE TABLE t2 (a INT, y INT); + +statement ok +INSERT INTO t2 VALUES (2, 7), (2, 9), (3, 5), (NULL, 1); + +# exists_in_aggregate_filter +query error Physical plan does not support logical expression Exists +SELECT count(*) FILTER (WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a)) AS n FROM t1; + +# correlated_scalar_subquery_in_aggregate_argument +query error Physical plan does not support logical expression ScalarSubquery +SELECT t1.b, sum((SELECT max(t2.y) FROM t2 WHERE t2.a = t1.a)) AS s FROM t1 GROUP BY t1.b ORDER BY t1.b; + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +# https://github.com/apache/datafusion/issues/26234 +statement ok +CREATE TABLE test(k INT) AS VALUES (1), (2); + +# exists_same_named_columns_of_two_inner_relations +query I +SELECT outer_t.k FROM test outer_t WHERE EXISTS (SELECT 1 FROM test one, test two WHERE one.k = outer_t.k AND two.k = outer_t.k + 1) ORDER BY 1; +---- + +statement ok +DROP TABLE test; + +# https://github.com/apache/datafusion/issues/26014 +statement ok +CREATE TABLE o(d DATE) AS VALUES (DATE '2024-01-01'), (DATE '2024-01-02'); + +statement ok +CREATE TABLE e(ts TIMESTAMP, v INT) AS VALUES (TIMESTAMP '2024-01-01 10:00:00', 1), (TIMESTAMP '2024-01-01 11:00:00', 2), (TIMESTAMP '2024-01-02 09:00:00', 3); + +# correlated_lossy_cast_below_count +query DI +SELECT o.d, (SELECT count(*) FROM e WHERE CAST(e.ts AS DATE) = o.d) AS c FROM o ORDER BY o.d, c; +---- +2024-01-01 1 +2024-01-01 1 +2024-01-02 1 + +statement ok +DROP TABLE o; + +statement ok +DROP TABLE e; + +# https://github.com/apache/datafusion/issues/26032 +statement ok +CREATE TABLE o(k INT) AS VALUES (1), (2), (5); + +statement ok +CREATE TABLE i(id INT, y INT) AS VALUES (1, 1), (2, 2); + +# in_with_window_and_repeated_in_predicate +query I +SELECT o.k FROM o WHERE o.k IN (SELECT i.y FROM i WHERE i.y = o.k QUALIFY row_number() OVER (ORDER BY i.id) = 1) ORDER BY o.k; +---- +1 + +statement ok +DROP TABLE o; + +statement ok +DROP TABLE i; + +# https://github.com/apache/datafusion/issues/25808 +statement ok +CREATE TABLE po(k INT) AS VALUES (0), (5); + +statement ok +CREATE TABLE pb(x INT, y INT) AS VALUES (1, 1), (1, 2), (2, 3); + +# lateral_distinct_with_correlated_inequality +query II +SELECT po.k, s.x FROM po, LATERAL (SELECT DISTINCT pb.x FROM pb WHERE pb.y > po.k) AS s ORDER BY po.k, s.x; +---- +0 1 +0 1 +0 2 + +statement ok +DROP TABLE po; + +statement ok +DROP TABLE pb; + +# https://github.com/apache/datafusion/issues/24960 +statement ok +CREATE TABLE t1(a INT); + +statement ok +INSERT INTO t1 VALUES (1),(2); + +statement ok +CREATE TABLE t2(a INT, b INT); + +statement ok +INSERT INTO t2 VALUES (1,10),(1,NULL); + +# in_over_correlated_groupless_count +query error Physical plan does not support logical expression InSubquery +SELECT a FROM t1 WHERE 0 IN (SELECT count(*) FROM t2 WHERE t2.a = t1.a) ORDER BY a; + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +# https://github.com/apache/datafusion/issues/26503 +# correlated_count_with_having_on_outer_column +query III +SELECT x.a, x.d, (SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) >= x.a) AS v FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d) ORDER BY x.d, x.a; +---- +1 1 1 +2 1 0 +1 9 0 + +# https://github.com/apache/datafusion/issues/26505 +# correlated_sum_with_having_true_for_empty_input +query II +SELECT x.a, (SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a HAVING sum(y.b) IS NULL) AS v FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a; +---- +1 10 +2 NULL + +# https://github.com/apache/datafusion/issues/26508 +# aggregate_over_correlated_groupless_aggregate +query error Physical plan does not support logical expression ScalarSubquery +SELECT x.a, (SELECT count(*) FROM (SELECT max(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a)) AS v1 FROM (VALUES (1), (2)) AS x(a) ORDER BY x.a; + +# https://github.com/apache/datafusion/issues/26695 +statement ok +CREATE TABLE t1(a INT) AS VALUES (1), (2), (-1); + +statement ok +CREATE TABLE t2(a INT) AS VALUES (1); + +# exists_over_groupless_count_with_outer_only_having +query I +SELECT a FROM t1 WHERE EXISTS (SELECT count(*) FROM t2 WHERE t2.a = t1.a HAVING t1.a > 0) ORDER BY a; +---- +-1 +1 +2 + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +# https://github.com/apache/datafusion/issues/26629 +statement ok +CREATE TABLE a(k INT, x INT) AS VALUES (1, 10), (2, 20); + +statement ok +CREATE TABLE b(k INT, v INT) AS VALUES (1, 100), (1, 200), (2, 300); + +# exists_with_limit_below_having +query I +SELECT a.k FROM a WHERE EXISTS (SELECT 1 FROM (SELECT b.k, b.v FROM b WHERE b.k = a.k LIMIT 1) sub HAVING COUNT(*) > 1) ORDER BY 1; +---- +1 + +statement ok +DROP TABLE a; + +statement ok +DROP TABLE b; + +# https://github.com/apache/datafusion/issues/25347 +statement ok +CREATE TABLE t_out(id INT, g INT) AS VALUES (1, 1), (2, 2); + +statement ok +CREATE TABLE t_in(id INT, g INT) AS VALUES (1, 1), (NULL, 2); + +# correlated_not_in_with_null_in_subquery +query I +SELECT id FROM t_out WHERE 3 NOT IN (SELECT id FROM t_in WHERE t_in.g = t_out.g) ORDER BY id; +---- +1 + +statement ok +DROP TABLE t_out; + +statement ok +DROP TABLE t_in; + +# https://github.com/apache/datafusion/issues/25480 +statement ok +CREATE TABLE t1(id INT, z INT) AS VALUES (1,10), (2,20), (NULL,30), (4,40); + +statement ok +CREATE TABLE t2(id INT, z INT) AS VALUES (1,5), (NULL,50), (5,10); + +# correlated_not_in_with_equality_on_in_value +query I +SELECT id FROM t1 WHERE id NOT IN (SELECT t2.id FROM t2 WHERE t2.id = t1.id) ORDER BY id; +---- +2 +4 +NULL + +statement ok +DROP TABLE t1; + +statement ok +DROP TABLE t2; + +# https://github.com/apache/datafusion/issues/26285 +statement ok +set datafusion.optimizer.prefer_hash_join = false; Review Comment: Optional cleanup: on current main this query also returns the right rows (1, 3, 100000) with the default hash join, so after the rebase this override and the RESET at line 543 can go, and the test runs the default join path. ```diff -statement ok -set datafusion.optimizer.prefer_hash_join = false; - statement ok CREATE TABLE a(k INT) AS VALUES (1), (100000); @@ -statement ok -RESET datafusion.optimizer.prefer_hash_join; - ``` -- 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]
