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 <[email protected]>
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: [email protected]
For additional commands, e-mail: [email protected]
