This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 8cca998ce6c branch-2.1:[fix](case)fix mark_join and
right_semi_mark_join has same table name… (#51158)
8cca998ce6c is described below
commit 8cca998ce6ccc299b5ad386cf988b246f229d5ab
Author: zhangdong <[email protected]>
AuthorDate: Thu May 22 20:29:49 2025 +0800
branch-2.1:[fix](case)fix mark_join and right_semi_mark_join has same table
name… (#51158)
… (#51124)
pick: https://github.com/apache/doris/pull/51124
---
.../query_p0/join/mark_join/mark_join.groovy | 181 +++++++++++----------
.../join/mark_join/right_semi_mark_join.groovy | 29 ++--
2 files changed, 111 insertions(+), 99 deletions(-)
diff --git a/regression-test/suites/query_p0/join/mark_join/mark_join.groovy
b/regression-test/suites/query_p0/join/mark_join/mark_join.groovy
index 0292fd4ae30..289c9d5b06b 100644
--- a/regression-test/suites/query_p0/join/mark_join/mark_join.groovy
+++ b/regression-test/suites/query_p0/join/mark_join/mark_join.groovy
@@ -16,10 +16,17 @@
// under the License.
suite("mark_join") {
- sql "drop table if exists t1;"
- sql "drop table if exists t2;"
+ String suiteName = "mark_join"
+ String table_t1 = "${suiteName}_table_t1"
+ String table_t2 = "${suiteName}_table_t2"
+ String table_tbl1 = "${suiteName}_table_tbl1"
+ String table_tbl2 = "${suiteName}_table_tbl2"
+ String table_tbl3 = "${suiteName}_table_tbl3"
+
+ sql "drop table if exists ${table_t1};"
+ sql "drop table if exists ${table_t2};"
sql """
- create table t1 (
+ create table ${table_t1} (
k1 int null,
k2 int null,
k3 bigint null,
@@ -31,7 +38,7 @@ suite("mark_join") {
"""
sql """
- create table t2 (
+ create table ${table_t2} (
k1 int null,
k2 int null,
k3 bigint null,
@@ -42,32 +49,32 @@ suite("mark_join") {
properties("replication_num" = "1");
"""
- sql "insert into t1 select 1,1,1,'a';"
- sql "insert into t1 select 2,2,2,'b';"
- sql "insert into t1 select 3,-3,null,'c';"
- sql "insert into t1 select 3,3,null,'c';"
+ sql "insert into ${table_t1} select 1,1,1,'a';"
+ sql "insert into ${table_t1} select 2,2,2,'b';"
+ sql "insert into ${table_t1} select 3,-3,null,'c';"
+ sql "insert into ${table_t1} select 3,3,null,'c';"
- sql "insert into t2 select 1,1,1,'a';"
- sql "insert into t2 select 2,2,2,'b';"
- sql "insert into t2 select 3,-3,null,'c';"
- sql "insert into t2 select 3,3,null,'c';"
+ sql "insert into ${table_t2} select 1,1,1,'a';"
+ sql "insert into ${table_t2} select 2,2,2,'b';"
+ sql "insert into ${table_t2} select 3,-3,null,'c';"
+ sql "insert into ${table_t2} select 3,3,null,'c';"
qt_test """
- select * from t1 where exists (select t2.k3 from t2 where t1.k2 = t2.k2)
or k1 < 10 order by k1, k2;
+ select * from ${table_t1} where exists (select ${table_t2}.k3 from
${table_t2} where ${table_t1}.k2 = ${table_t2}.k2) or k1 < 10 order by k1, k2;
"""
qt_test """
- select * from t1 where not exists (select t2.k3 from t2 where t1.k2 =
t2.k2) or k1 < 10 order by k1, k2;
+ select * from ${table_t1} where not exists (select ${table_t2}.k3 from
${table_t2} where ${table_t1}.k2 = ${table_t2}.k2) or k1 < 10 order by k1, k2;
"""
qt_test """
- select * from t1 where t1.k1 not in (select t2.k3 from t2 where t2.k2 =
t1.k2) or k1 < 10 order by k1, k2;
+ select * from ${table_t1} where ${table_t1}.k1 not in (select
${table_t2}.k3 from ${table_t2} where ${table_t2}.k2 = ${table_t1}.k2) or k1 <
10 order by k1, k2;
"""
- sql "drop table if exists tbl1;"
- sql "drop table if exists tbl2;"
- sql "drop table if exists tbl3;"
+ sql "drop table if exists ${table_tbl1};"
+ sql "drop table if exists ${table_tbl2};"
+ sql "drop table if exists ${table_tbl3};"
sql """
- CREATE TABLE `tbl1` (
+ CREATE TABLE `${table_tbl1}` (
`unit_name` varchar(1080) NULL,
`cur_unit_name` varchar(1080) NOT NULL
) ENGINE=OLAP
@@ -79,7 +86,7 @@ suite("mark_join") {
"""
sql """
- CREATE TABLE `tbl2` (
+ CREATE TABLE `${table_tbl2}` (
`org_code` varchar(150) NOT NULL ,
`org_name` varchar(300) NULL
) ENGINE=OLAP
@@ -91,7 +98,7 @@ suite("mark_join") {
"""
sql """
- CREATE TABLE `tbl3` (
+ CREATE TABLE `${table_tbl3}` (
`id` bigint NOT NULL,
`acntm_name` varchar(500) NULL ,
`vendor_name` varchar(500) NULL
@@ -104,7 +111,7 @@ suite("mark_join") {
"""
sql """
- insert into tbl1 (unit_name, cur_unit_name) values
+ insert into ${table_tbl1} (unit_name, cur_unit_name) values
('v1', 'o1'),
('v2', 'o2'),
('v3', 'o3'),
@@ -129,7 +136,7 @@ suite("mark_join") {
"""
sql """
- insert into tbl2(org_code, org_name) values
+ insert into ${table_tbl2}(org_code, org_name) values
('v1', 'o1'),
('v2', 'o2'),
('v3', 'o3'),
@@ -143,7 +150,7 @@ suite("mark_join") {
"""
sql """
- insert into tbl3 (id, vendor_name, acntm_name)
+ insert into ${table_tbl3} (id, vendor_name, acntm_name)
values(1, 'o1', 'v1'),
(2, 'o2', 'v2'),
(3, 'o3', 'v3'),
@@ -156,123 +163,123 @@ suite("mark_join") {
(10, null, 'v5');
"""
- sql " analyze table tbl1 with sync;"
- sql " analyze table tbl2 with sync;"
- sql " analyze table tbl3 with sync;"
+ sql " analyze table ${table_tbl1} with sync;"
+ sql " analyze table ${table_tbl2} with sync;"
+ sql " analyze table ${table_tbl3} with sync;"
sql "set disable_join_reorder=0;"
qt_test_right_semi_mark_join """
select
- tbl3.id,
- tbl3.acntm_name,
- tbl3.vendor_name,
- tbl3.vendor_name in (
+ ${table_tbl3}.id,
+ ${table_tbl3}.acntm_name,
+ ${table_tbl3}.vendor_name,
+ ${table_tbl3}.vendor_name in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
) v1,
- tbl3.vendor_name not in (
+ ${table_tbl3}.vendor_name not in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
) v2
from
- tbl3 order by 1,2,3,4,5;
+ ${table_tbl3} order by 1,2,3,4,5;
"""
sql "set disable_join_reorder=1;"
qt_test_right_semi_mark_join_2 """
select
- tbl3.id,
- tbl3.acntm_name,
- tbl3.vendor_name,
- tbl3.vendor_name in (
+ ${table_tbl3}.id,
+ ${table_tbl3}.acntm_name,
+ ${table_tbl3}.vendor_name,
+ ${table_tbl3}.vendor_name in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
) v1,
- tbl3.vendor_name not in (
+ ${table_tbl3}.vendor_name not in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
) v2
from
- tbl3 order by 1,2,3,4,5;
+ ${table_tbl3} order by 1,2,3,4,5;
"""
sql "set disable_join_reorder=0;"
qt_test_right_semi_mark_join_no_null """
select
- tbl3.id,
- tbl3.acntm_name,
- tbl3.vendor_name,
- tbl3.vendor_name in (
+ ${table_tbl3}.id,
+ ${table_tbl3}.acntm_name,
+ ${table_tbl3}.vendor_name,
+ ${table_tbl3}.vendor_name in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
- and tbl1.unit_name is not null
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
+ and ${table_tbl1}.unit_name is not null
) v1,
- tbl3.vendor_name not in (
+ ${table_tbl3}.vendor_name not in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
- and tbl1.unit_name is not null
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
+ and ${table_tbl1}.unit_name is not null
) v2
from
- tbl3 order by 1,2,3,4,5;
+ ${table_tbl3} order by 1,2,3,4,5;
"""
sql "set disable_join_reorder=1;"
qt_test_right_semi_mark_join_no_null_2 """
select
- tbl3.id,
- tbl3.acntm_name,
- tbl3.vendor_name,
- tbl3.vendor_name in (
+ ${table_tbl3}.id,
+ ${table_tbl3}.acntm_name,
+ ${table_tbl3}.vendor_name,
+ ${table_tbl3}.vendor_name in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
- and tbl1.unit_name is not null
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
+ and ${table_tbl1}.unit_name is not null
) v1,
- tbl3.vendor_name not in (
+ ${table_tbl3}.vendor_name not in (
select
- tbl1.unit_name
+ ${table_tbl1}.unit_name
from
- tbl2
- join tbl1 on tbl1.cur_unit_name = tbl2.org_name
+ ${table_tbl2}
+ join ${table_tbl1} on ${table_tbl1}.cur_unit_name =
${table_tbl2}.org_name
where
- tbl2.org_code = tbl3.acntm_name
- and tbl1.unit_name is not null
+ ${table_tbl2}.org_code = ${table_tbl3}.acntm_name
+ and ${table_tbl1}.unit_name is not null
) v2
from
- tbl3 order by 1,2,3,4,5;
+ ${table_tbl3} order by 1,2,3,4,5;
"""
}
diff --git
a/regression-test/suites/query_p0/join/mark_join/right_semi_mark_join.groovy
b/regression-test/suites/query_p0/join/mark_join/right_semi_mark_join.groovy
index 3557475cdd2..cc287f1a6f7 100644
--- a/regression-test/suites/query_p0/join/mark_join/right_semi_mark_join.groovy
+++ b/regression-test/suites/query_p0/join/mark_join/right_semi_mark_join.groovy
@@ -17,17 +17,22 @@
suite("right_semi_mark_join") {
- sql "drop table if exists tbl1;"
- sql "drop table if exists tbl2;"
- sql "drop table if exists tbl3;"
+ String suiteName = "right_semi_mark_join"
+ String table_tbl1 = "${suiteName}_table_tbl1"
+ String table_tbl2 = "${suiteName}_table_tbl2"
+ String table_tbl3 = "${suiteName}_table_tbl3"
+
+ sql "drop table if exists ${table_tbl1};"
+ sql "drop table if exists ${table_tbl2};"
+ sql "drop table if exists ${table_tbl3};"
sql """
- create table tbl1 (pk int, col1 bigint, col2 bigint) engine = olap
DUPLICATE KEY(pk) distributed by hash(pk) buckets 10
properties("replication_num" = "1");
+ create table ${table_tbl1} (pk int, col1 bigint, col2 bigint) engine =
olap DUPLICATE KEY(pk) distributed by hash(pk) buckets 10
properties("replication_num" = "1");
"""
sql """
insert into
- tbl1(pk, col1, col2)
+ ${table_tbl1}(pk, col1, col2)
values
(0, null, 18332), (1, 788547, null), (2, 4644959, -56), (3,
8364628, 72), (4, null, -5581),
(5, 2344024, -62), (6, -2689177, 22979), (7, 1320, -41), (8,
null, -54), (9, 12, -6236),
@@ -35,7 +40,7 @@ suite("right_semi_mark_join") {
"""
sql """
- create table tbl2 (
+ create table ${table_tbl2} (
pk int, col1 bigint, col2 bigint
) engine = olap
distributed by hash(pk) buckets 4
@@ -44,14 +49,14 @@ suite("right_semi_mark_join") {
sql """
insert into
- tbl2(pk, col1, col2)
+ ${table_tbl2}(pk, col1, col2)
values
(0, 108, 31161), (1, 1479175, 6764263), (2, 110, 25), (3, 110,
-18656), (4, null, -51),
(5, 21, 27), (6, -6950217, 1585978), (7, null, null), (8, null,
3453467), (9, null, -6701140);
"""
sql """
- create table tbl3 (
+ create table ${table_tbl3} (
pk int, col1 bigint, col2 bigint, col3 bigint
) engine = olap
DUPLICATE KEY(pk) distributed by hash(pk) buckets 10
@@ -60,7 +65,7 @@ suite("right_semi_mark_join") {
sql """
insert into
- tbl3(pk, col1, col2)
+ ${table_tbl3}(pk, col1, col2)
values
(0, 55, -58), (1, 49, 29792), (2, 95, 32361), (3, 31243, -27428),
(4, -27400, null),
(5, 31243, null), (6, null, -27428), (7, null, 7), (8, 31243,
-21951), (9, 13186, 24466),
@@ -87,13 +92,13 @@ suite("right_semi_mark_join") {
T1.pk AS C1,
T1.col2 AS C2
FROM
- tbl1 AS T1 FULL
- OUTER JOIN tbl2 AS T2 ON T1.col1 <= T2.col2
+ ${table_tbl1} AS T1 FULL
+ OUTER JOIN ${table_tbl2} AS T2 ON T1.col1 <= T2.col2
OR T2.col1 IN (
SELECT
T3.col2
FROM
- tbl3 AS T3
+ ${table_tbl3} AS T3
WHERE
T2.col2 = T3.col1
)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
