This is an automated email from the ASF dual-hosted git repository.
huajianlan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 4e95b3afaf [test](nereids) add subquery regression Testing (#12372)
4e95b3afaf is described below
commit 4e95b3afaf557a2dedddd1817c5a914ca3daac09
Author: zhengshiJ <[email protected]>
AuthorDate: Tue Sep 6 16:37:17 2022 +0800
[test](nereids) add subquery regression Testing (#12372)
Added regression test of sub-queries. Currently only associated sub-queries
are added. Non-associated sub-queries will be added after project revision.
---
.../nereids_syntax_p0/sub_query_correlated.out | 106 +++++++++++++++++++
.../nereids_syntax_p0/sub_query_correlated.groovy | 117 +++++++++------------
2 files changed, 155 insertions(+), 68 deletions(-)
diff --git a/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
new file mode 100644
index 0000000000..9f77c5c293
--- /dev/null
+++ b/regression-test/data/nereids_syntax_p0/sub_query_correlated.out
@@ -0,0 +1,106 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !scalar_less_than_corr --
+1 3
+1 2
+2 4
+3 4
+3 3
+
+-- !scalar_not_equal_corr --
+1 3
+1 2
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !scalar_equal_to_corr --
+
+-- !not_in_corr --
+1 3
+1 2
+2 5
+2 4
+3 4
+20 2
+22 3
+24 4
+
+-- !not_in_uncorr --
+1 3
+1 2
+20 2
+22 3
+24 4
+
+-- !in_subquery_corr --
+3 3
+
+-- !in_subquery_uncorr --
+2 5
+2 4
+3 4
+3 3
+
+-- !not_exist_corr --
+2 5
+
+-- !exist_corr --
+1 3
+1 2
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !in_with_in_and_scalar --
+
+-- !exist_and_not_exist --
+
+-- !in_unCorrelated --
+2 5
+2 4
+3 4
+3 3
+
+-- !not_in_unCorrelated --
+1 3
+1 2
+20 2
+22 3
+24 4
+
+-- !alias_scalar --
+1 3
+1 2
+
+-- !alias_in --
+3 3
+
+-- !alias_not_in --
+1 3
+1 2
+2 5
+2 4
+3 4
+20 2
+22 3
+24 4
+
+-- !alias_exist --
+1 3
+1 2
+2 4
+3 4
+3 3
+20 2
+22 3
+24 4
+
+-- !alias_not_exist --
+2 5
+
diff --git
a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
index 038c47c53d..2ceff3492e 100644
--- a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
@@ -94,150 +94,131 @@ suite ("sub_query_correlated") {
// and the out file will be updated later
// unstable
- //qt_scalar_less_than_corr """
- sql """
- select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2)
+ qt_scalar_less_than_corr """
+ select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
- //qt_scalar_not_equal_corr """
- sql """
- select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2)
+ qt_scalar_not_equal_corr """
+ select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
//qt_scalar_not_equal_uncorr """
/*sql """
- select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3)
+ select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3) order by k1
"""*/
- //qt_scalar_equal_to_corr """
- sql """
- select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2)
+ qt_scalar_equal_to_corr """
+ select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
//qt_scalar_equal_to_uncorr """
/*sql """
- select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3)
+ select * from subquery1 where subquery1.k1 = (select sum(subquery3.k3)
from subquery3) order by k1
"""*/
- //qt_not_in_corr """
- sql """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2)
+ qt_not_in_corr """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
- //qt_not_in_uncorr """
- sql """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3)
+ qt_not_in_uncorr """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3) order by k1
"""
- //qt_in_subquery_corr """
- sql """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2)
+ qt_in_subquery_corr """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = subquery1.k2) order by k1
"""
- //qt_in_subquery_uncorr """
- sql """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3)
+ qt_in_subquery_uncorr """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3) order by k1
"""
- //qt_not_exist_corr """
- sql """
- select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2)
+ qt_not_exist_corr """
+ select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2) order by k1
"""
//qt_not_exist_uncorr """
/*sql """
- select * from subquery1 where not exists (select subquery3.k3 from
subquery3)
+ select * from subquery1 where not exists (select subquery3.k3 from
subquery3) order by k1
"""*/
- //qt_exist_corr """
- sql """
- select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2)
+ qt_exist_corr """
+ select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2) order by k1
"""
//qt_exist_uncorr """
/*sql """
- select * from subquery1 where exists (select subquery3.k3 from
subquery3)
+ select * from subquery1 where exists (select subquery3.k3 from
subquery3) order by k1
"""*/
- //qt_in_with_in_and_scalar """
- sql """
+ qt_in_with_in_and_scalar """
select * from subquery1 where subquery1.k1 in (
select subquery3.k3 from subquery3 where
subquery3.k3 in (select subquery4.k1 from subquery4 where
subquery4.k1 = 3)
- and subquery3.v2 > (select sum(subquery2.k2) from subquery2
where subquery2.k2 = subquery3.v1))
+ and subquery3.v2 > (select sum(subquery2.k2) from subquery2
where subquery2.k2 = subquery3.v1)) order by k1
"""
- //qt_exist_and_not_exist """
- sql """
- select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2)
- and not exists (select subquery4.k2 from
subquery4 where subquery1.k2 = subquery4.k2)
+ qt_exist_and_not_exist """
+ select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery1.k2 = subquery3.v2)
+ and not exists (select subquery4.k2 from
subquery4 where subquery1.k2 = subquery4.k2) order by k1
"""
//------------------unCorrelated-----------------
//qt_scalar_unCorrelated
/*sql """
- select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = 2)
+ select * from subquery1 where subquery1.k1 < (select sum(subquery3.k3)
from subquery3 where subquery3.v2 = 2) order by k1
"""*/
//qt_not_scalar_unCorrelated
/*sql """
- select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = 2);
+ select * from subquery1 where subquery1.k1 != (select
sum(subquery3.k3) from subquery3 where subquery3.v2 = 2) order by k1
"""*/
- //qt_in_unCorrelated
- sql """
- select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = 2);
+ qt_in_unCorrelated """
+ select * from subquery1 where subquery1.k1 in (select subquery3.k3
from subquery3 where subquery3.v2 = 2) order by k1
"""
- //qt_not_in_unCorrelated
- sql """
- select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = 2);
+ qt_not_in_unCorrelated """
+ select * from subquery1 where subquery1.k1 not in (select subquery3.k3
from subquery3 where subquery3.v2 = 2) order by k1
"""
//qt_exist_unCorrelated
/*sql """
- select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2);
+ select * from subquery1 where exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2) order by k1
"""*/
//qt_not_exists_unCorrelated
/*sql """
- select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2);
+ select * from subquery1 where not exists (select subquery3.k3 from
subquery3 where subquery3.v2 = 2) order by k1
"""*/
//----------with subquery alias----------
- //qt_scalar
- //Open after the project is completed
- //sql """
- // select * from subquery1
- // where subquery1.k1 < (select max(aa) from
- // (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3)
- //"""
-
- //qt_in
- sql """
+ qt_alias_scalar """
+ select * from subquery1
+ where subquery1.k1 < (select max(aa) from
+ (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
+ """
+
+ qt_alias_in """
select * from subquery1
where subquery1.k1 in (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3)
+ (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
"""
- //qt_not_in
- sql """
+ qt_alias_not_in """
select * from subquery1
where subquery1.k1 not in (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3)
+ (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
"""
- //qt_exist
- sql """
+ qt_alias_exist """
select * from subquery1
where exists (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3)
+ (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
"""
- //qt_not_exist
- sql """
+ qt_alias_not_exist """
select * from subquery1
where not exists (select aa from
- (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3)
+ (select k1 as aa from subquery3 where subquery1.k2 =
subquery3.v2) subquery3) order by k1
"""
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
