Skip to content

Commit 774cb3a

Browse files
add dxm's case of join table (apache#140)
# Checklist * 内部Jira:http://39.106.86.136:8090/browse/CIR-1287 * 修复PR: * master: * 1.2-lts: * 1.2-lts: * Doris-master: * [ ] 已修复 * [ ] 未修复 * [x] 无问题 * Doris-1.2-lts: * [ ] 已修复 * [ ] 未修复 * [x] 无问题 * Doris-1.1-lts: * [ ] 已修复 * [ ] 未修复 * [ ] 无问题 --------- Co-authored-by: smallhibiscus <844981280>
1 parent 18a59f4 commit 774cb3a

5 files changed

Lines changed: 199 additions & 0 deletions

File tree

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
-- This file is automatically generated. You should know what you did if you want to edit this
2+
-- !test_read_size_error_sql --
3+
20221220 10002 123457 80000 \N 0 \N
4+
20221221 10002 123457 80000 \N 0 \N
5+
20221222 10002 123457 80000 \N 0 \N
6+
20221223 10002 123457 80000 \N 0 \N
7+
20221224 10002 123457 80000 \N 0 \N
8+
20221225 10002 123457 80000 \N 0 \N
9+
20221226 10002 123457 80000 \N 0 \N
10+
20221227 10002 123457 80000 \N 0 \N
11+
Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
CREATE TABLE `test_read_size_error_table1` (
2+
`f_dt` int(11) NOT NULL COMMENT "日期",
3+
`f_key_1` bigint(20) NOT NULL COMMENT "",
4+
`f_key_2` varchar(320) NOT NULL COMMENT "",
5+
`f_value_1` int(11) REPLACE_IF_NOT_NULL NULL COMMENT "",
6+
`f_value_2` int(11) REPLACE_IF_NOT_NULL NULL COMMENT "",
7+
`f_amount_1` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT "",
8+
`f_amount_2` bigint(20) REPLACE_IF_NOT_NULL NULL COMMENT ""
9+
) ENGINE=OLAP
10+
AGGREGATE KEY(`f_dt`, `f_key_1`, `f_key_2`)
11+
COMMENT ""
12+
PARTITION BY RANGE(`f_dt`)
13+
(PARTITION p202212 VALUES [("20221201"), ("20230101")),
14+
PARTITION p202301 VALUES [("20230101"), ("20230201")),
15+
PARTITION p202302 VALUES [("20230201"), ("20230301")),
16+
PARTITION p202303 VALUES [("20230301"), ("20230401")),
17+
PARTITION p202304 VALUES [("20230401"), ("20230501")))
18+
DISTRIBUTED BY HASH(`f_key_1`) BUCKETS 32
19+
PROPERTIES (
20+
"replication_allocation" = "tag.location.default: 1",
21+
"dynamic_partition.enable" = "true",
22+
"dynamic_partition.time_unit" = "MONTH",
23+
"dynamic_partition.time_zone" = "Asia/Shanghai",
24+
"dynamic_partition.start" = "-1200",
25+
"dynamic_partition.end" = "3",
26+
"dynamic_partition.prefix" = "p",
27+
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
28+
"dynamic_partition.buckets" = "32",
29+
"dynamic_partition.create_history_partition" = "false",
30+
"dynamic_partition.history_partition_num" = "-1",
31+
"dynamic_partition.hot_partition_num" = "0",
32+
"dynamic_partition.reserved_history_periods" = "NULL",
33+
"dynamic_partition.start_day_of_month" = "1",
34+
"in_memory" = "false",
35+
"storage_format" = "V2"
36+
);
Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
CREATE TABLE `test_read_size_error_table2` (
2+
`f_key_1` bigint(20) NOT NULL COMMENT "",
3+
`f_key_2` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
4+
`f_value_1` varchar(128) REPLACE NOT NULL DEFAULT "" COMMENT ""
5+
) ENGINE=OLAP
6+
AGGREGATE KEY(`f_key_1`, `f_key_2`)
7+
COMMENT "OLAP"
8+
DISTRIBUTED BY HASH(`f_key_1`, `f_key_2`) BUCKETS 32
9+
PROPERTIES (
10+
"replication_allocation" = "tag.location.default: 1",
11+
"in_memory" = "false",
12+
"storage_format" = "V2"
13+
);
Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
// The cases is copied from https://github.com/trinodb/trino/tree/master
19+
// /testing/trino-product-tests/src/main/resources/sql-tests/testcases/tpcds
20+
// and modified by Doris.
21+
suite("load") {
22+
def tables =
23+
[
24+
"test_read_size_error_table1",
25+
"test_read_size_error_table2"
26+
]
27+
for (String table in tables) {
28+
sql """DROP TABLE IF EXISTS ${table}"""
29+
sql new File("""${context.file.parent}/ddl/${table}.sql""").text
30+
}
31+
32+
sql """
33+
insert into test_read_size_error_table1 values
34+
(20221220, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
35+
(20221221, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
36+
(20221222, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
37+
(20221223, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
38+
(20221224, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
39+
(20221225, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
40+
(20221226, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
41+
(20221227, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
42+
(20221228, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
43+
(20221229, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
44+
(20221230, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
45+
(20221231, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
46+
(20230101, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
47+
(20230102, 123456, '2022122015233522dadv', 10001, 1, 10000, null),
48+
(20230103, 123456, '2022122015233522dadv', 10001, 1, 10000, null),(20221220, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
49+
(20221221, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
50+
(20221222, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
51+
(20221223, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
52+
(20221224, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
53+
(20221225, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
54+
(20221226, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
55+
(20221227, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
56+
(20221228, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
57+
(20221229, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
58+
(20221230, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
59+
(20221231, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
60+
(20230101, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
61+
(20230102, 123457, '2022122015233522dadv', 10002, 1, null, 10000),
62+
(20230103, 123457, '2022122015233522dadv', 10002, 1, null, 10000),(20221220, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
63+
(20221221, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
64+
(20221222, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
65+
(20221223, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
66+
(20221224, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
67+
(20221225, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
68+
(20221226, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
69+
(20221227, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
70+
(20221228, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
71+
(20221229, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
72+
(20221230, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
73+
(20221231, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
74+
(20230101, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
75+
(20230102, 123455, '2022122015233522dadv', 10003, 1, 10000, null),
76+
(20230103, 123455, '2022122015233522dadv', 10003, 1, 10000, null);
77+
"""
78+
79+
sql """
80+
insert into test_read_size_error_table2 values
81+
(1, 10001, 'label_1'),
82+
(2, 10002, 'label_1'),
83+
(3, 10003, 'label_1'),
84+
(4, 10001, 'label_2'),
85+
(5, 10002, 'label_2'),
86+
(6, 10003, 'label_2'),
87+
(7, 10003, 'label_3');
88+
"""
89+
}
Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
select
2+
a.f_dt,
3+
a.f_value_1,
4+
a.f_key_1,
5+
a.f_amount_2,
6+
b.f_value_2_char,
7+
ifnull(b.f_amount_1, 0) as f_amount_1,
8+
b.f_value_1 as f_value_1_b
9+
from (
10+
select
11+
f_dt,
12+
f_value_1,
13+
f_key_1,
14+
sum(f_amount_2) over(
15+
partition by f_key_1,f_value_1
16+
order by f_key_1,f_value_1
17+
rows between unbounded preceding and unbounded following
18+
) as f_amount_2
19+
from test_read_size_error_table1
20+
where f_dt between 20221220 and 20221227
21+
and f_amount_2 > 0
22+
and f_value_1 in (
23+
select f_key_2
24+
from test_read_size_error_table2
25+
where f_value_1 in ('label_1', 'label_2')
26+
)
27+
and f_value_1 not in (
28+
select f_key_2
29+
from test_read_size_error_table2
30+
where f_value_1 in ('label_3', 'label_4')
31+
)
32+
) a
33+
left join (
34+
select
35+
f_dt,
36+
f_value_1,
37+
f_key_1,
38+
case
39+
when f_value_2 = 1 then 'a'
40+
when f_value_2 = 2 then 'b'
41+
when f_value_2 = 3 then 'c'
42+
when f_value_2 = 4 then 'd'
43+
end as f_value_2_char,
44+
sum(f_amount_1) as f_amount_1
45+
from test_read_size_error_table1
46+
where f_dt >= 20221220
47+
and f_dt < 20230103
48+
and f_amount_1 > 0
49+
group by f_dt,f_value_1,f_key_1,f_value_2_char
50+
) b on a.f_key_1 = b.f_key_1 and a.f_dt <= b.f_dt and datediff(b.f_dt, a.f_dt) < 7 order by a.f_dt;

0 commit comments

Comments
 (0)