oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: peihan.dph
5
# owner group: SQL1
6
# tags: optimizer
7
# description:
8
#
9
--disable_warnings
10
drop table if exists tbl1,tbl2,tbl3,tbl4;
11
--enable_warnings
12
create table tbl1 (i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6),i5 decimal(5,3), primary key(i1));
13
create table tbl2 (i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6), i5 decimal(5,3), primary key(i1));
14
create table tbl3 (i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6), i5 decimal(5,3), primary key(i1));
15
create table tbl4 (i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6), i5 decimal(5,3), primary key(i1));
16
insert into tbl1 values(1,'now','haha',1.6256,'2014-05-04 12:00:00',-10.235);
17
insert into tbl1 values(2,'now','haha',-1.6256,'2014-05-04 12:00:00',1.243);
18
insert into tbl1 values(3,'now','haha',1.656,'2014-05-04 12:00:00',-1.45);
19
insert into tbl2 values(1,'now','haha',1.6256,'2014-05-04 12:00:00',3.45);
20
insert into tbl2 values(2,'now1','haha',1.6256,'2014-05-04 12:00:00',-0.25);
21
insert into tbl2 values(3,'now2','haha',1.6256,'2014-05-04 12:00:00',0.253);
22
insert into tbl2 values(4,'now3','haha',1.6256,'2014-05-04 12:00:00',1.677);
23
insert into tbl3 values(1,'now','haha',1.6256,'2014-05-04 12:00:00',-10.235);
24
insert into tbl3 values(2,'now','haha',-1.6256,'2014-05-04 12:00:00',1.243);
25
insert into tbl3 values(3,'now','haha',1.656,'2014-05-04 12:00:00',-1.45);
26
insert into tbl4 values(1,'now','haha',1.6256,'2014-05-04 12:00:00',3.45);
27
insert into tbl4 values(2,'now1','haha',1.6256,'2014-05-04 12:00:00',-0.25);
28
insert into tbl4 values(3,'now2','haha',1.6256,'2014-05-04 12:00:00',0.253);
29
insert into tbl4 values(4,'now3','haha',1.6256,'2014-05-04 12:00:00',1.677);
30
select * from tbl1 join tbl2 on  tbl1.i1=tbl2.i1 join tbl3  on tbl1.i1=tbl3.i1 join tbl4 on tbl1.i1=tbl4.i1; 
31
select * from tbl1 a join tbl2 b on  a.i1=b.i1 join tbl3 c on a.i1=c.i1 join tbl4 d on a.i1=d.i1; 
32
select * from tbl1 join tbl2 on  tbl1.i1=tbl2.i1 join tbl3  on tbl1.i1=tbl3.i1 join tbl4 on tbl1.i1=tbl4.i1 where tbl1.i1=1;
33
select * from tbl1 join tbl2 on  tbl1.i1=tbl2.i1 join tbl3  on tbl2.i1=tbl3.i1 join tbl4 on tbl3.i1=tbl4.i1;
34
select * from tbl1 a join tbl2 b on a.i1=b.i1 join tbl3 c on b.i1=c.i1 join tbl4 d on c.i1=d.i1;
35
--replace_regex /Plan signature: [0-9]*/Plan signature/
36
--disable_result_log
37
explain select * from tbl1 join tbl2 on  tbl1.i1=tbl2.i1 join tbl3  on tbl1.i1=tbl3.i1 join tbl4 on tbl1.i1=tbl4.i1; 
38
--enable_result_log
39
--replace_regex /Plan signature: [0-9]*/Plan signature/
40
--disable_result_log
41
explain select * from tbl1 a join tbl2 b on  a.i1=b.i1 join tbl3 c on a.i1=c.i1 join tbl4 d on a.i1=d.i1; 
42
--enable_result_log
43
--replace_regex /Plan signature: [0-9]*/Plan signature/
44
--disable_result_log
45
explain select * from tbl1 join tbl2 on  tbl1.i1=tbl2.i1 join tbl3  on tbl2.i1=tbl3.i1 join tbl4 on tbl3.i1=tbl4.i1;
46
--enable_result_log
47
--replace_regex /Plan signature: [0-9]*/Plan signature/
48
--disable_result_log
49
explain select * from tbl1 a join tbl2 b on a.i1=b.i1 join tbl3 c on b.i1=c.i1 join tbl4 d on c.i1=d.i1;
50
--enable_result_log
51
select * from tbl1  left join tbl2 on  tbl1.i1=tbl2.i1  left join tbl3  on tbl1.i1=tbl3.i1  left join tbl4 on tbl1.i1=tbl4.i1; 
52
select * from tbl1 a  left join tbl2 b on  a.i1=b.i1  left join tbl3 c on a.i1=c.i1  left join tbl4 d on a.i1=d.i1; 
53
select * from tbl1  left join tbl2 on  tbl1.i1=tbl2.i1  left join tbl3  on tbl1.i1=tbl3.i1  left join tbl4 on tbl1.i1=tbl4.i1 where tbl1.i1=1;
54
select * from tbl1  left join tbl2 on  tbl1.i1=tbl2.i1  left join tbl3  on tbl2.i1=tbl3.i1  left join tbl4 on tbl3.i1=tbl4.i1;
55
select * from tbl1 a  left join tbl2 b on a.i1=b.i1  left join tbl3 c on b.i1=c.i1  left join tbl4 d on c.i1=d.i1;
56
--replace_regex /Plan signature: [0-9]*/Plan signature/
57
--disable_result_log
58
explain select* from tbl1  left join tbl2 on  tbl1.i1=tbl2.i1  left join tbl3  on tbl1.i1=tbl3.i1  left join tbl4 on tbl1.i1=tbl4.i1; 
59
--enable_result_log
60
--replace_regex /Plan signature: [0-9]*/Plan signature/
61
--disable_result_log
62
explain select* from tbl1 a  left join tbl2 b on  a.i1=b.i1  left join tbl3 c on a.i1=c.i1  left join tbl4 d on a.i1=d.i1; 
63
--enable_result_log
64
--replace_regex /Plan signature: [0-9]*/Plan signature/
65
--disable_result_log
66
explain select* from tbl1  left join tbl2 on  tbl1.i1=tbl2.i1  left join tbl3  on tbl2.i1=tbl3.i1  left join tbl4 on tbl3.i1=tbl4.i1;
67
--enable_result_log
68
--replace_regex /Plan signature: [0-9]*/Plan signature/
69
--disable_result_log
70
explain select* from tbl1 a  left join tbl2 b on a.i1=b.i1  left join tbl3 c on b.i1=c.i1  left join tbl4 d on c.i1=d.i1;
71
--enable_result_log
72