You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/09/13 00:59:39 UTC

[doris] branch master updated: [test](join)add some join cases (#12501)

This is an automated email from the ASF dual-hosted git repository.

yiguolei 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 8be5527be4 [test](join)add some join cases (#12501)
8be5527be4 is described below

commit 8be5527be436c5b27526eaed159551ef6e72097d
Author: zy-kkk <zh...@qq.com>
AuthorDate: Tue Sep 13 08:59:32 2022 +0800

    [test](join)add some join cases (#12501)
---
 regression-test/data/query/join/test_join2.out     | 236 +++++++++++++++++++++
 .../suites/query/join/test_join2.groovy            | 161 ++++++++++++++
 2 files changed, 397 insertions(+)

diff --git a/regression-test/data/query/join/test_join2.out b/regression-test/data/query/join/test_join2.out
new file mode 100644
index 0000000000..6ec7da8cdb
--- /dev/null
+++ b/regression-test/data/query/join/test_join2.out
@@ -0,0 +1,236 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !join1 --
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+
+-- !join2 --
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+
+-- !join3 --
+	\N	\N	null	\N	\N
+	\N	\N	null	\N	0
+	\N	\N	null	0	\N
+	\N	\N	null	1	-1
+	\N	\N	null	2	2
+	\N	\N	null	2	4
+	\N	\N	null	3	-3
+	\N	\N	null	5	-5
+	\N	\N	null	5	-5
+	\N	0	zero	\N	\N
+	\N	0	zero	\N	0
+	\N	0	zero	0	\N
+	\N	0	zero	1	-1
+	\N	0	zero	2	2
+	\N	0	zero	2	4
+	\N	0	zero	3	-3
+	\N	0	zero	5	-5
+	\N	0	zero	5	-5
+	0	\N	zero	\N	\N
+	0	\N	zero	\N	0
+	0	\N	zero	0	\N
+	0	\N	zero	1	-1
+	0	\N	zero	2	2
+	0	\N	zero	2	4
+	0	\N	zero	3	-3
+	0	\N	zero	5	-5
+	0	\N	zero	5	-5
+	1	4	one	\N	\N
+	1	4	one	\N	0
+	1	4	one	0	\N
+	1	4	one	1	-1
+	1	4	one	2	2
+	1	4	one	2	4
+	1	4	one	3	-3
+	1	4	one	5	-5
+	1	4	one	5	-5
+	2	3	two	\N	\N
+	2	3	two	\N	0
+	2	3	two	0	\N
+	2	3	two	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	2	3	two	3	-3
+	2	3	two	5	-5
+	2	3	two	5	-5
+	3	2	three	\N	\N
+	3	2	three	\N	0
+	3	2	three	0	\N
+	3	2	three	1	-1
+	3	2	three	2	2
+	3	2	three	2	4
+	3	2	three	3	-3
+	3	2	three	5	-5
+	3	2	three	5	-5
+	4	1	four	\N	\N
+	4	1	four	\N	0
+	4	1	four	0	\N
+	4	1	four	1	-1
+	4	1	four	2	2
+	4	1	four	2	4
+	4	1	four	3	-3
+	4	1	four	5	-5
+	4	1	four	5	-5
+	5	0	five	\N	\N
+	5	0	five	\N	0
+	5	0	five	0	\N
+	5	0	five	1	-1
+	5	0	five	2	2
+	5	0	five	2	4
+	5	0	five	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+	6	6	six	\N	\N
+	6	6	six	\N	0
+	6	6	six	0	\N
+	6	6	six	1	-1
+	6	6	six	2	2
+	6	6	six	2	4
+	6	6	six	3	-3
+	6	6	six	5	-5
+	6	6	six	5	-5
+	7	7	seven	\N	\N
+	7	7	seven	\N	0
+	7	7	seven	0	\N
+	7	7	seven	1	-1
+	7	7	seven	2	2
+	7	7	seven	2	4
+	7	7	seven	3	-3
+	7	7	seven	5	-5
+	7	7	seven	5	-5
+	8	8	eight	\N	\N
+	8	8	eight	\N	0
+	8	8	eight	0	\N
+	8	8	eight	1	-1
+	8	8	eight	2	2
+	8	8	eight	2	4
+	8	8	eight	3	-3
+	8	8	eight	5	-5
+	8	8	eight	5	-5
+
+-- !join4 --
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+
+-- !join5 --
+	0	\N	zero	\N	0
+	2	3	two	2	2
+	4	1	four	2	4
+
+-- !join5 --
+	0	\N	zero	\N	0
+	0	\N	zero	2	2
+	0	\N	zero	2	4
+	1	4	one	2	2
+	1	4	one	2	4
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	2	4
+	4	1	four	2	4
+
+-- !join6 --
+	\N	\N	null	\N	\N
+	\N	0	zero	\N	\N
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	4	1	four	\N	\N
+	5	0	five	5	-5
+	5	0	five	5	-5
+	6	6	six	\N	\N
+	7	7	seven	\N	\N
+	8	8	eight	\N	\N
+
+-- !join7 --
+	\N	\N	null	\N	\N
+	\N	0	zero	\N	\N
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	4	1	four	\N	\N
+	5	0	five	5	-5
+	5	0	five	5	-5
+	6	6	six	\N	\N
+	7	7	seven	\N	\N
+	8	8	eight	\N	\N
+
+-- !join8 --
+	\N	\N	\N	\N	\N
+	\N	\N	\N	\N	0
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+
+-- !join9 --
+	\N	\N	\N	\N	\N
+	\N	\N	\N	\N	0
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	5	0	five	5	-5
+	5	0	five	5	-5
+
+-- !join10 --
+	\N	\N	\N	\N	\N
+	\N	\N	\N	\N	0
+	\N	\N	null	\N	\N
+	\N	0	zero	\N	\N
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	4	1	four	\N	\N
+	5	0	five	5	-5
+	5	0	five	5	-5
+	6	6	six	\N	\N
+	7	7	seven	\N	\N
+	8	8	eight	\N	\N
+
+-- !join11 --
+	\N	\N	\N	\N	\N
+	\N	\N	\N	\N	0
+	\N	\N	null	\N	\N
+	\N	0	zero	\N	\N
+	0	\N	zero	0	\N
+	1	4	one	1	-1
+	2	3	two	2	2
+	2	3	two	2	4
+	3	2	three	3	-3
+	4	1	four	\N	\N
+	5	0	five	5	-5
+	5	0	five	5	-5
+	6	6	six	\N	\N
+	7	7	seven	\N	\N
+	8	8	eight	\N	\N
+
+-- !join12 --
+
+-- !join13 --
+	1	4	one	1	-1
+
diff --git a/regression-test/suites/query/join/test_join2.groovy b/regression-test/suites/query/join/test_join2.groovy
new file mode 100644
index 0000000000..60feb3207f
--- /dev/null
+++ b/regression-test/suites/query/join/test_join2.groovy
@@ -0,0 +1,161 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements.  See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership.  The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License.  You may obtain a copy of the License at
+//
+//   http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied.  See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_join2", "query,p0") {
+    def DBname = "test_join2"
+    def TBname1 = "J1_TBL"
+    def TBname2 = "J2_TBL"
+
+    sql "DROP DATABASE IF EXISTS ${DBname}"
+    sql "create database IF NOT EXISTS ${DBname}"
+    sql "use ${DBname}"
+
+    sql "DROP TABLE IF EXISTS ${TBname1}"
+    sql "DROP TABLE IF EXISTS ${TBname2}"
+
+    sql """
+            CREATE TABLE IF NOT EXISTS ${TBname1} (
+                i integer,
+                j integer,
+                t text
+            )
+            DISTRIBUTED BY HASH(i) properties("replication_num" = "1");
+        """
+    sql """
+            CREATE TABLE IF NOT EXISTS ${TBname2} (
+                i integer,
+                k integer
+            )
+            DISTRIBUTED BY HASH(i) properties("replication_num" = "1");
+        """
+
+    sql "INSERT INTO ${TBname1} VALUES (1, 4, 'one');"
+    sql "INSERT INTO ${TBname1} VALUES (2, 3, 'two');"
+    sql "INSERT INTO ${TBname1} VALUES (3, 2, 'three');"
+    sql "INSERT INTO ${TBname1} VALUES (4, 1, 'four');"
+    sql "INSERT INTO ${TBname1} VALUES (5, 0, 'five');"
+    sql "INSERT INTO ${TBname1} VALUES (6, 6, 'six');"
+    sql "INSERT INTO ${TBname1} VALUES (7, 7, 'seven');"
+    sql "INSERT INTO ${TBname1} VALUES (8, 8, 'eight');"
+    sql "INSERT INTO ${TBname1} VALUES (0, NULL, 'zero');"
+    sql "INSERT INTO ${TBname1} VALUES (NULL, NULL, 'null');"
+    sql "INSERT INTO ${TBname1} VALUES (NULL, 0, 'zero');"
+    sql "INSERT INTO ${TBname2} VALUES (1, -1);"
+    sql "INSERT INTO ${TBname2} VALUES (2, 2);"
+    sql "INSERT INTO ${TBname2} VALUES (3, -3);"
+    sql "INSERT INTO ${TBname2} VALUES (2, 4);"
+    sql "INSERT INTO ${TBname2} VALUES (5, -5);"
+    sql "INSERT INTO ${TBname2} VALUES (5, -5);"
+    sql "INSERT INTO ${TBname2} VALUES (0, NULL);"
+    sql "INSERT INTO ${TBname2} VALUES (NULL, NULL);"
+    sql "INSERT INTO ${TBname2} VALUES (NULL, 0);"
+
+    qt_join1 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1}
+            INNER JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join2 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1}
+            JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+    qt_join3 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} NATURAL JOIN ${TBname2}
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join4 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} JOIN ${TBname2}
+            ON (${TBname1}.i = ${TBname2}.i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join5 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} JOIN ${TBname2}
+            ON (${TBname1}.i = ${TBname2}.k)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join5 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} JOIN ${TBname2}
+            ON (${TBname1}.i <= ${TBname2}.k)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join6 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} LEFT OUTER JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join7 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} LEFT JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join8 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} RIGHT
+            OUTER JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join9 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1}
+            RIGHT JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+    qt_join10 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} FULL OUTER JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join11 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} FULL JOIN ${TBname2} USING (i)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join12 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} LEFT JOIN ${TBname2} USING (i)
+            WHERE (k = 1)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    qt_join13 """
+            SELECT '' AS "xxx", *
+            FROM ${TBname1} LEFT JOIN ${TBname2} USING (i)
+            WHERE (${TBname1}.i = 1)
+            ORDER BY 1,2,3,4,5,6;
+        """
+
+    sql "DROP TABLE IF EXISTS ${TBname1};"
+    sql "DROP TABLE IF EXISTS ${TBname2};"
+    sql "DROP DATABASE IF EXISTS ${DBname};"
+}
\ No newline at end of file


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org