oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#### owner: xiaoyi.xy
5
#### owner group: sql3
6
#### description: 测试locate函数
7
# tags: expr, datatype
8

9
--echo ================ expression locate ================
10

11
select locate('', 'abc');
12
select locate('', '');
13
select locate('abcd', 'abc');
14
select locate('abc', 'abc');
15
select locate('a', 'abc');
16
select locate('b', 'abc');
17
select locate('c', 'abc');
18
select locate('bc', 'abc');
19
select locate('bc', 'abcbc');
20
select locate('阿里', '阿里巴巴');
21
select locate('巴巴', '阿里巴巴巴巴');
22
select locate('阿里巴巴', '阿里巴巴');
23
select locate(true, '123');
24
select locate(true, '23');
25
select locate(23, 123);
26
select locate('', 23);
27
select locate('23', 123);
28
select locate(123.4, '123.400000');
29
select locate('123.400000', 123.4);
30
select locate('123.400000', null);
31
select locate(null, '巴巴');
32
select locate('巴巴', null);
33
select locate(null, null);
34

35

36
select locate(false, true);
37
select locate(true, true);
38
select locate(true, 123);
39
select locate(false, 123);
40
select locate(false, 0123);
41
select locate(false, 1023);
42
select locate(false,1023.4);
43
select locate(true, 1023.4);
44
select locate(true, null);
45
select locate(null, true);
46

47
select locate(23, 123);
48
select locate(123456, 123);
49
select locate(123, 123.4);
50
select locate(123.4, 1234);
51
select locate(123, null);
52
select locate(null, 123);
53

54
select locate(123.4, 123.400000);
55
select locate(123.41, 123.400000);
56
select locate(123.400000, null);
57
select locate(null, 123.41);
58

59
--disable_warnings
60
drop table if exists test;
61
drop table if exists t1;
62
--enable_warnings
63
create table t1(c1 bigint unsigned);
64
insert into t1 values(locate('a','b',9223372036854775807));
65
insert into t1 values(locate('a','b',9223372036854775808));
66
insert into t1 values(locate('a','b',12233720368547758000));
67
select * from t1;
68
create table test(c1 datetime primary key);
69
sleep 5;
70
insert into test values('2015-5-5');
71
select locate('201', c1) from test;
72
select locate('', c1) from test;
73
select locate('haha', c1) from test;
74
select locate('-5',c1) from test;
75
select locate('2015-5-5', c1) from test;
76
select locate(true, c1) from test;
77
select locate(201, c1) from test;
78
select locate(201.1, c1) from test;
79
select locate(c1, null) from test;
80
select locate(null, c1) from test;
81
--disable_warnings
82
drop table if exists test,t1;
83
--enable_warnings
84

85
select locate('', 'abc', 0);
86
select locate('', 'abc', 1);
87
select locate('', 'abc', -1);
88
select locate('', '', 0);
89
select locate('', '', 1);
90
select locate('', '', -1);
91
select locate('abc', '', 0);
92
select locate('abc', '', -1);
93
select locate('abc', '', 1);
94
select locate('abcd', 'abc', 1);
95
select locate('abc', 'abc', 1);
96
select locate('abc', 'abc', 2);
97
select locate('a', 'abc', 1);
98
select locate('a', 'abc', 2);
99
select locate('a', 'abac', 1);
100
select locate('a', 'abac', 2);
101
select locate('b', 'abc', 1);
102
select locate('b', 'abc', 2);
103
select locate('b', 'abc', 3);
104
select locate('c', 'abc', 1);
105
select locate('c', 'abc', 3);
106
select locate('c', 'abc', 4);
107
select locate('bc', 'abc', 1);
108
select locate('bc', 'abc', 3);
109
select locate('', 'abc', 3);
110
select locate('', 'abc', 4);
111
select locate('', 'abc', 5);
112
select locate('阿里', '阿里巴巴', 1);
113
select locate('阿里', '阿里巴巴', 2);
114
select locate('巴巴', '阿里巴巴', 1);
115
select locate('巴巴', '阿里巴巴', 3);
116
select locate('巴巴', '阿里巴巴', 4);
117
select locate('巴巴', '阿里巴巴', 5);
118
select locate('', '阿里阿里', 3);
119
select locate('', '阿里阿里', 4);
120
select locate('', '阿里阿里', 5);
121
select locate('阿里巴巴', '阿里巴巴', 0);
122
select locate('阿里巴巴', '阿里巴巴', 1);
123
select locate(23, 123, 1);
124
select locate('', 23, 1);
125
select locate('23', 123, 1);
126
select locate(true, '123', 1);
127
select locate(true, '123', 2);
128
select locate(true, '123', 2);
129
select locate(true, '123', 2);
130
select locate(true, '1', 1);
131
select locate('1', true, 1);
132
select locate(1.3, '1.300000', 2);
133
select locate(1.3, '2321.300000', 2);
134
select locate(1.3, '2321.3', 2);
135
select locate('1.3000', 451.3, 2);
136
select locate(null, '巴巴', 3);
137
select locate(null, '巴巴', 2);
138
select locate('巴巴', null, 3);
139
select locate('巴巴', null, 2);
140
select locate('巴巴', '阿里巴巴', null);
141
select locate(null, null, 0);
142
select locate(null, null, 1);
143

144
select locate(false, true, 1);
145
select locate(false, true, 2);
146
select locate(true, true, 1);
147
select locate(true, 123, 1);
148
select locate(true, 123, 2);
149
select locate(false, 1023.4, 2);
150
select locate(false, 1023.4, 3);
151
select locate(true, null, 0);
152
select locate(true, null, 1);
153
select locate(null, true, 0);
154
select locate(null, true, 3);
155
select locate(true, true, null);
156

157
select locate(23, 123, 1);
158
select locate(23, 123, 3);
159
select locate(123456, 123, 9);
160
select locate(123, 123.4, 1);
161
select locate(123, 123.4, 2);
162
select locate(123.4, 1234, 4);
163
select locate(123, null, 1);
164
select locate(123, null, null);
165
select locate(null, 123, 1);
166
select locate(null, 123, null);
167

168
select locate(123.4, 123.400000, 1);
169
select locate(123.4, 123.400000, 2);
170
select locate(123.41, 123.400000, 3);
171
select locate(123.400000, null, 3);
172
select locate(null, 123.41, 3);
173
select locate(null, 123.41, null);
174
select locate(null, 123.41, 126);
175

176
--disable_warnings
177
drop table if exists test;
178
--enable_warnings
179
create table test(c1 datetime primary key);
180
sleep 5;
181
insert into test values('2015-5-5');
182
select locate('201', c1, 1) from test;
183
select locate('', c1 , 1) from test;
184
select locate('haha', c1 , 1) from test;
185
select locate('-5',c1 , 1) from test;
186
select locate('2015-5-5', c1 , 1) from test;
187
select locate(true, c1 , 1) from test;
188
select locate(true, c1 , 4) from test;
189
select locate(201, c1 , 1) from test;
190
select locate(201.1, c1 , 1) from test;
191
select locate(null, c1 , 1) from test;
192
select locate(c1, null, 1) from test;
193
select locate(c1, null, null) from test;
194
select mod(locate('a','b'),1.000);
195
select ifnull(locate('a','a'),2.345 );
196
--disable_warnings
197
drop table if exists test;
198
--enable_warnings
199

200
--echo issue 40636846
201
select locate('', '中文', 1);
202
select locate('', '中文', 2);
203
select locate('', '中文', 3);
204
select locate('', '中文', 4);
205
select locate('', '中文', 5);
206
select locate('', '中文', 6);
207
select locate('', '中文', 7);
208