You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit 17b1108
Browse filesBrowse the repository at this point in the historyBrowse files
[fix](nereids)support uncorrelated subquery in join condition (#26672)
sql select * from t1 a join t1 b on b.id in (select 1) and a.id = b.id; will report an error.
This pr support uncorrelated subquery in join condition to fix it
Copy file name to clipboardExpand all lines: regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+28Lines changed: 28 additions & 0 deletions
Original file line number
Diff line number
Diff line change
@@ -648,6 +648,34 @@ suite ("sub_query_correlated") {
648
648
exception "Unsupported correlated subquery with grouping and/or aggregation";
649
649
}
650
650
651
+
qt_doris_7643 """
652
+
SELECT sub_query_correlated_subquery6.*
653
+
FROM sub_query_correlated_subquery6
654
+
JOIN sub_query_correlated_subquery7
655
+
ON sub_query_correlated_subquery6.k2 = sub_query_correlated_subquery7.k3
656
+
AND EXISTS
657
+
(SELECT sub_query_correlated_subquery8.k1
658
+
FROM sub_query_correlated_subquery8 )
659
+
AND sub_query_correlated_subquery6.k2 IN
660
+
(SELECT sub_query_correlated_subquery8.k2
661
+
FROM sub_query_correlated_subquery8 )
662
+
AND sub_query_correlated_subquery6.k1 IN
663
+
(SELECT sub_query_correlated_subquery8.k2
664
+
FROM sub_query_correlated_subquery8
665
+
WHERE sub_query_correlated_subquery6.k2 = sub_query_correlated_subquery8.k2 )
666
+
AND sub_query_correlated_subquery7.k3 IN
667
+
(SELECT sub_query_correlated_subquery8.k1
668
+
FROM sub_query_correlated_subquery8 )
669
+
AND 10 >
670
+
(SELECT min(sub_query_correlated_subquery8.k2)
671
+
FROM sub_query_correlated_subquery8 )
672
+
AND sub_query_correlated_subquery7.k3 IN
673
+
(SELECT sub_query_correlated_subquery8.k2
674
+
FROM sub_query_correlated_subquery8
675
+
WHERE sub_query_correlated_subquery7.v1 = sub_query_correlated_subquery8.k2 )
676
+
ORDER BY sub_query_correlated_subquery6.k1, sub_query_correlated_subquery6.k2;
677
+
"""
678
+
651
679
// order_qt_doris_6937_2 """
652
680
// 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) or k1 < 10 order by k1, k2;
qt_join_on_predicate7"""SELECT t2.k1,t2.k2,t3.k1,t3.k2 FROM baseall t2 LEFT JOIN test t3 ON t2.k2=t3.k2 WHERE t2.k1 = 4 OR (t2.k1 > 4 AND t3.k1 IS NULL) order by 1, 2, 3, 4"""
1108
1108
1109
1109
test {
1110
-
sql "select a.k1 from baseall a join test b on b.k2 in (select 49) and a.k1 = b.k1 order by k1;"
1110
+
sql "select /*+ SET_VAR(enable_nereids_planner=false) */ a.k1 from baseall a join test b on b.k2 in (select 49) and a.k1 = b.k1 order by k1;"
0 commit comments