in regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy [329:649]
WHERE coalesce(bitand(
cast(
(SELECT sum(k1)
FROM sub_query_correlated_subquery3 ) AS int),
cast(t1.k1 AS int)),
coalesce(t1.k1, t1.k2)) is NULL
ORDER BY t1.k1, t1.k2;
"""
qt_in_subquery """
select * from sub_query_correlated_subquery3
where (k1 = 1 or k1 = 2 or k1 = 3) and v1 in (select k1 from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v2 and sub_query_correlated_subquery1.k1 = 3)
order by k1, k2
"""
qt_exist_subquery """
select * from sub_query_correlated_subquery3
where k1 = 2 and exists (select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v2 and sub_query_correlated_subquery1.k2 = 4)
order by k1, k2
"""
//----------complex nonEqual subqueries----------
qt_in_subquery """
select * from sub_query_correlated_subquery3
where (k1 = 1 or k1 = 2 or k1 = 3) and v1 in (select k1 from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k2 > sub_query_correlated_subquery3.v2 and sub_query_correlated_subquery1.k1 = 3)
order by k1, k2
"""
qt_exist_subquery """
select * from sub_query_correlated_subquery3
where k1 = 2 and exists (select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 < sub_query_correlated_subquery3.v2 and sub_query_correlated_subquery1.k2 = 4)
order by k1, k2
"""
//----------subquery with order----------
order_qt_scalar_subquery_with_order """
select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 > (select sum(sub_query_correlated_subquery3.k3) a from sub_query_correlated_subquery3 where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2 order by a);
"""
order_qt_in_subquery_with_order """
select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 not in (select sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2 order by k2);
"""
order_qt_in_subquery_mark_with_order """
select * from sub_query_correlated_subquery6 where sub_query_correlated_subquery6.k1 not in (select sub_query_correlated_subquery7.k3 from sub_query_correlated_subquery7 ) or k1 < 10;
"""
order_qt_exists_subquery_with_order """
select * from sub_query_correlated_subquery1 where exists (select sub_query_correlated_subquery3.k3 from sub_query_correlated_subquery3 where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2 order by k2);
"""
//----------subquery with limit----------
order_qt_scalar_subquery_with_limit """
select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 > (select sum(sub_query_correlated_subquery3.k3) a from sub_query_correlated_subquery3 where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2 limit 1);
"""
//----------subquery with order and limit----------
order_qt_scalar_subquery_with_order_and_limit """
select * from sub_query_correlated_subquery1 where sub_query_correlated_subquery1.k1 > (select sum(sub_query_correlated_subquery3.k3) a from sub_query_correlated_subquery3 where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2 order by a limit 1);
"""
//---------subquery with Disjunctions-------------
order_qt_scalar_subquery_with_disjunctions """
SELECT DISTINCT k1 FROM sub_query_correlated_subquery1 i1 WHERE ((SELECT count(*) FROM sub_query_correlated_subquery1 WHERE ((k1 = i1.k1) AND (k2 = 2)) or ((k1 = i1.k1) AND (k2 = 1)) ) > 0);
"""
//--------subquery case when-----------
order_qt_case_when_subquery """
SELECT CASE
WHEN (
SELECT COUNT(*) / 2
FROM sub_query_correlated_subquery3
) > v1 THEN (
SELECT AVG(v1)
FROM sub_query_correlated_subquery3
)
ELSE (
SELECT SUM(v2)
FROM sub_query_correlated_subquery3
)
END AS kk4
FROM sub_query_correlated_subquery3 ;
"""
//---------subquery mark join() Disjunctions------------
order_qt_in """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3) OR k1 < 10;
"""
order_qt_scalar """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 > (SELECT AVG(k1) FROM sub_query_correlated_subquery3) OR k1 < 10;
"""
order_qt_exists_true """
SELECT * FROM sub_query_correlated_subquery1 WHERE EXISTS (SELECT k1 FROM sub_query_correlated_subquery3 WHERE k1 = 10) OR k1 < 10;
"""
order_qt_in_exists_false """
SELECT * FROM sub_query_correlated_subquery1 WHERE EXISTS (SELECT k1 FROM sub_query_correlated_subquery3 WHERE k1 > 10) OR k1 < 10;
"""
order_qt_hash_join_with_other_conjuncts1 """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 > sub_query_correlated_subquery3.k3) OR k1 < 10 ORDER BY k1,k2;
"""
order_qt_hash_join_with_other_conjuncts2 """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 < sub_query_correlated_subquery3.k3) OR k1 < 10 ORDER BY k1,k2;
"""
order_qt_hash_join_with_other_conjuncts3 """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 > sub_query_correlated_subquery3.k3) OR k1 < 11 ORDER BY k1,k2;
"""
order_qt_hash_join_with_other_conjuncts4 """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 < sub_query_correlated_subquery3.k3) OR k1 < 11 ORDER BY k1,k2;
"""
order_qt_same_subquery_in_conjuncts """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3) OR k1 IN (SELECT k1 FROM sub_query_correlated_subquery3) OR k1 < 10 ORDER BY k1,k2;
"""
order_qt_two_subquery_in_one_conjuncts """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3) OR k1 IN (SELECT k3 FROM sub_query_correlated_subquery3) OR k1 < 10 ORDER BY k1,k2;
"""
order_qt_multi_subquery_in_and_scalry """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR k1 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_in_and_exist """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR exists (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_in_and_exist_sum """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR exists (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_in_and_in """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR k2 in (SELECT k2 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_scalar_and_exist """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR exists (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_scalar_and_scalar """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
OR k2 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.v1)
OR k1 < 10 ORDER BY k1;
"""
order_qt_multi_subquery_in_first_or_in_and_in """
SELECT * FROM sub_query_correlated_subquery1 WHERE (k1 in (SELECT k2 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
or k2 in (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1))
and k1 in (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
"""
order_qt_multi_subquery_in_second_or_in_and_in """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 in (SELECT k2 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
or k2 in (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
and k1 in (SELECT k1 FROM sub_query_correlated_subquery3 where sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.k1)
"""
order_qt_multi_subquery_scalar_and_in_or_scalar_and_exists_agg """
SELECT * FROM sub_query_correlated_subquery1 WHERE ((k1 != (SELECT sum(k1) FROM sub_query_correlated_subquery3) and k1 = 1 OR k1 < 10) and k1 = 10 and k1 = 15)
and (k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1)
OR k1 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1))
and exists (SELECT sum(k1) FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1);
"""
order_qt_multi_subquery_scalar_and_in_or_scalar_and_exists """
SELECT * FROM sub_query_correlated_subquery1 WHERE ((k1 != (SELECT sum(k1) FROM sub_query_correlated_subquery3) and k1 = 1 OR k1 < 10) and k1 = 10 and k1 = 15)
and (k1 IN (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1)
OR k1 < (SELECT sum(k1) FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1))
and exists (SELECT k1 FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1);
"""
//----------type coercion subquery-----------
qt_cast_subquery_in """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 < (cast('1.2' as decimal(2,1)) * (SELECT sum(k1) FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1)) order by k1, k2;
"""
qt_cast_subquery_in_with_disconjunct """
SELECT * FROM sub_query_correlated_subquery1 WHERE k1 < (cast('1.2' as decimal(2,1)) * (SELECT sum(k1) FROM sub_query_correlated_subquery3 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1)) or k1 > 100 order by k1, k2;
"""
qt_imitate_tpcds_10 """
SELECT * FROM sub_query_correlated_subquery1 WHERE exists (SELECT * FROM sub_query_correlated_subquery3, sub_query_correlated_subquery2 where sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1 and sub_query_correlated_subquery2.k1 = sub_query_correlated_subquery3.v1)
and (exists (SELECT * FROM sub_query_correlated_subquery3, sub_query_correlated_subquery4 WHERE sub_query_correlated_subquery1.k1 = sub_query_correlated_subquery3.k1 and sub_query_correlated_subquery3.v1 = sub_query_correlated_subquery4.k1)
OR exists (SELECT * FROM sub_query_correlated_subquery3, sub_query_correlated_subquery5 WHERE sub_query_correlated_subquery1.k2 = sub_query_correlated_subquery3.v1 and sub_query_correlated_subquery3.v1 = sub_query_correlated_subquery5.k1))
"""
qt_doris_6937 """
SELECT *
FROM sub_query_correlated_subquery1
WHERE EXISTS
(SELECT k1
FROM sub_query_correlated_subquery3
WHERE sub_query_correlated_subquery1.k1 > sub_query_correlated_subquery3.v1)
OR k1 < 10
order by k1, k2;
"""
qt_mark_join_nullable """
select sub_query_correlated_subquery8.k1 in (select sub_query_correlated_subquery9.k3 from sub_query_correlated_subquery9) from sub_query_correlated_subquery8 order by k1, k2;
"""
qt_cir_5218_in_ok """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE k1 IN
(SELECT k1
FROM
(SELECT k1,
sum(k3) AS bbb,
count(k2) AS aaa
FROM sub_query_correlated_subquery7
WHERE k1 > 0
AND k3 > 0
GROUP BY k1 ) y
WHERE y.aaa>0
AND k1>1);
"""
qt_cir_5218_exists_ok_1 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT k1
FROM
(SELECT k1,
sum(k3) AS bbb,
count(k2) AS aaa
FROM sub_query_correlated_subquery7
WHERE k1 > 0
AND k3 > 0
GROUP BY k1 ) y
WHERE y.aaa>0
AND k1>1);
"""
qt_cir_5218_exists_ok_2 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT k1
FROM
(SELECT k1
FROM sub_query_correlated_subquery7
WHERE sub_query_correlated_subquery6.k1 > 7
GROUP BY k1 ) y);
"""
qt_cir_5218_exists_ok_3 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT k1
FROM
(SELECT k1
FROM sub_query_correlated_subquery7
WHERE sub_query_correlated_subquery6.k1 > sub_query_correlated_subquery7.k3
GROUP BY k1 ) y);
"""
qt_cir_5218_exists_ok_4 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT sum(k3)
FROM
sub_query_correlated_subquery7
WHERE sub_query_correlated_subquery6.k1 > sub_query_correlated_subquery7.k3);
"""
qt_cir_5218_exists_ok_5 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT sum(k3)
FROM
sub_query_correlated_subquery10);
"""
qt_cir_5218_exists_ok_6 """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE exists
(SELECT sum(k3)
FROM
sub_query_correlated_subquery10 group by k2);
"""
test {
sql """
SELECT count(*)
FROM sub_query_correlated_subquery6
WHERE k1 IN
(SELECT k1
FROM
(SELECT k1,
sum(k3) AS bbb,
count(k2) AS aaa
FROM sub_query_correlated_subquery7
WHERE k1 > 0
AND k3 > 0 and sub_query_correlated_subquery6.k1 > 2
GROUP BY k1 ) y
WHERE y.aaa>0
AND k1>1); """
exception "Unsupported correlated subquery with grouping and/or aggregation";
}