oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: dachuan.sdc
5
#owner group: sql3
6
# tags: optimizer
7
--disable_query_log
8
set @@session.explicit_defaults_for_timestamp=off;
9
--enable_query_log
10

11
--result_format 4
12
--explain_protocol 2
13

14
--disable_warnings
15
drop database if exists muhangtest;
16
create database muhangtest;
17
use muhangtest;
18
--enable_warnings
19

20
--disable_warnings
21
drop table if exists xy_t1, xy_t2;
22
--enable_warnings
23

24
#create table t1(c1 int(11) not null, c2 int(11) not null, c3 int(11) not null, primary key (c1, c2, c3)) partition by hash(c2)
25
#subpartition by range columns(c2) subpartition template( subpartition sp_00 values less than (45), subpartition sp_01 values less than (100), subpartition sp_02 values less than (1000));
26

27
create table t2(c1 int(11) not null, c2 int(11) not null, c3 int(11) not null, primary key (c1, c2, c3)) partition by hash(c2)
28
subpartition by range columns(c2) subpartition template( subpartition sp_00 values less than (45), subpartition sp_01 values less than (100));
29

30
--disable_query_log
31
--disable_result_log
32
insert into t2 values(1,1,1);
33
insert into t2 values(99,99,99);
34
--sleep 1
35
--enable_result_log
36
--enable_query_log
37

38
--enable_sorted_result
39

40
## left outer join
41
## OB bug
42
##select * from t1 left join t2 on t1.c2 = t1.c2;
43

44
create table t1 (c1 int(11) not null, c2 int(11) not null, c3 int(11) not null, primary key (c1, c2, c3)) ;
45
--disable_query_log
46
--disable_result_log
47
insert into t1 values(1,1,1);
48
insert into t1 values(99,99,99);
49
insert into t1 values(999,999,999);
50
--sleep 1
51
--enable_result_log
52
--enable_query_log
53

54
##原始结果
55
select /*+use_px parallel(4) */ * from t1 left join t2 on t1.c2 = t2.c2;
56

57
##left outer join
58
select /*+use_px parallel(4) pq_distribute(t1 partition none) */ * from t1 left join t2 on t1.c2 = t2.c2;
59
select /*+use_px parallel(4) pq_distribute(t1 none partition) */ * from t1 left join t2 on t1.c2 = t2.c2;
60
##right outer join
61
select /*+use_px parallel(4) pq_distribute(t1 none partition) */ * from t2 right join t1 on t1.c2 = t2.c2;
62
select /*+use_px parallel(4) pq_distribute(t1 partition none) */ * from t2 right join t1 on t1.c2 = t2.c2;
63
##full outer join
64
select /*+use_px parallel(4) pq_distribute(t1 partition none) */ * from t2 full join t1 on t1.c2 = t2.c2;
65
select /*+use_px parallel(4) pq_distribute(t1 none partition) */ * from t2 full join t1 on t1.c2 = t2.c2;
66

67
## anti
68
select * from t1 where not exists (select 1 from t2 where t1.c1 = t2.c1);
69
select /*+ use_px parallel(2) LEADING(t1, t2) USE_NL(t1, t2) pq_distribute(t2 partition none)*/ * from t1 where not exists (select 1 from t2 where t1.c2 = t2.c2);
70
select /*+ use_px parallel(2) LEADING(t1, t2) USE_NL(t1, t2) pq_distribute(t2 none partition)*/ * from t1 where not exists (select 1 from t2 where t1.c2 = t2.c2);
71
## semi
72
select * from t1 where exists (select 1 from t2 where t1.c1 = t2.c1);
73
select /*+ use_px parallel(2) LEADING(t1, t2) USE_NL(t1, t2) pq_distribute(t2 partition none)*/ * from t1 where exists (select 1 from t2 where t1.c2 = t2.c2);
74
select /*+ use_px parallel(2) LEADING(t1, t2) USE_NL(t1, t2) pq_distribute(t2 none partition)*/ * from t1 where exists (select 1 from t2 where t1.c2 = t2.c2);
75

76
#--disable_warnings
77
#drop database if exists muhangtest;
78
#--enable_warnings
79

80