oceanbase

1
drop table if exists t1,t2;
2
create table t1(pk int primary key, c1 int, c2 int);
3
insert into t1 values(1,1,1),(2,2,2);
4
explain select * from t1 where c2=1 and c1=c2 and pk=c1;
5
select * from t1 where c2=1 and c1=c2 and pk=c1;
6
pk	c1	c2
7
1	1	1
8
drop table t1;
9
create table t1(pk int primary key, c1 int, c2 int, c3 int,c4 int , c5 int, c6 int, c7 int, c8 int, c9 int, c10 int);
10
insert into t1 values(1,1,1,1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2,2,2,2),(3,2,2,2,3,2,3,2,3,3,3);
11
explain select * from t1 where c10=1 and c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and pk=c1;
12
select * from t1 where c10=1 and c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and pk=c1;
13
pk	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
14
1	1	1	1	1	1	1	1	1	1	1
15
select * from t1 where c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and c10=1 and pk=c1;
16
pk	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
17
1	1	1	1	1	1	1	1	1	1	1
18
select * from t1 where c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and pk=c1;
19
pk	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
20
1	1	1	1	1	1	1	1	1	1	1
21
2	2	2	2	2	2	2	2	2	2	2
22
explain select * from t1 where c10>1 and c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and pk=c1;
23
select * from t1 where c10>1 and c9=c10 and c8=c9 and c7=c8 and c6=c7 and c5=c6 and c4=c5 and c3=c4 and c2=c3 and c1=c2 and pk=c1;
24
pk	c1	c2	c3	c4	c5	c6	c7	c8	c9	c10
25
2	2	2	2	2	2	2	2	2	2	2
26
drop table t1;
27
create table t1(pk int primary key, c1 int, c2 int);
28
insert into t1 values(1,1,1),(2,2,2);
29
create table t2(pk int primary key, c1 int, c2 int);
30
insert into t2 values(1,1,1),(2,2,2);
31
select  t1.pk,t2.c2 from t1,t2 where t1.pk=t2.pk and t1.pk=1;
32
pk	c2
33
1	1
34
select  t1.pk,t2.c2 from t1,t2 where t1.pk=t2.pk and t1.pk>1;
35
pk	c2
36
2	2
37
select  t1.pk,t2.c2 from t1 INNER join t2 on t1.pk=t2.pk and t2.pk=1;
38
pk	c2
39
1	1
40
select  t1.pk,t2.c2 from t1 LEFT join t2 on t1.pk=t2.pk and t2.pk=1;
41
pk	c2
42
1	1
43
2	NULL
44
explain select  t1.pk,t2.c2 from t1 LEFT join t2 on t1.pk=t2.pk and t2.pk=1;
45
select  t1.pk,t2.c2 from t1 RIGHT join t2 on t1.pk=t2.pk and t2.pk=1;
46
pk	c2
47
1	1
48
NULL	2
49
select  t1.pk,t2.c2 from t1 FULL join t2 on t1.pk=t2.pk and t2.pk=1;
50
pk	c2
51
1	1
52
2	NULL
53
NULL	2
54