oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: bin.lb
5
# owner group: SQL1
6
# tags: optimizer, join
7
# description:
8
#
9
--disable_abort_on_error
10

11
--disable_warnings
12
drop table if exists t1,t2;
13
--enable_warnings
14

15
create table t1(pk int primary key, c1 int, c2 int);
16
insert into t1 values(1,1,1),(2,2,2);
17

18
--replace_regex /Plan signature: [0-9]*/Plan signature/
19
--disable_result_log
20
explain select * from t1 where c2=1 and c1=c2 and pk=c1;
21
--enable_result_log
22
select * from t1 where c2=1 and c1=c2 and pk=c1;
23

24
drop table t1;
25

26

27

28
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);
29

30
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);
31

32
--replace_regex /Plan signature: [0-9]*/Plan signature/
33
--disable_result_log
34
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;
35
--enable_result_log
36
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;
37
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;
38
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;
39

40
--replace_regex /Plan signature: [0-9]*/Plan signature/
41
--disable_result_log
42
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;
43
--enable_result_log
44
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;
45

46
drop table t1;
47

48

49

50

51
create table t1(pk int primary key, c1 int, c2 int);
52

53
insert into t1 values(1,1,1),(2,2,2);
54

55
create table t2(pk int primary key, c1 int, c2 int);
56

57
insert into t2 values(1,1,1),(2,2,2);
58

59
select  t1.pk,t2.c2 from t1,t2 where t1.pk=t2.pk and t1.pk=1;
60
select  t1.pk,t2.c2 from t1,t2 where t1.pk=t2.pk and t1.pk>1;
61

62

63
select  t1.pk,t2.c2 from t1 INNER join t2 on t1.pk=t2.pk and t2.pk=1;
64

65
select  t1.pk,t2.c2 from t1 LEFT join t2 on t1.pk=t2.pk and t2.pk=1;
66
--replace_regex /Plan signature: [0-9]*/Plan signature/
67
--disable_result_log
68
explain select  t1.pk,t2.c2 from t1 LEFT join t2 on t1.pk=t2.pk and t2.pk=1;
69
--enable_result_log
70

71
select  t1.pk,t2.c2 from t1 RIGHT join t2 on t1.pk=t2.pk and t2.pk=1;
72

73
--sorted_result
74
select  t1.pk,t2.c2 from t1 FULL join t2 on t1.pk=t2.pk and t2.pk=1;
75

76

77