oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: yibo.tyf
5
#owner group: sql1
6

7
#null
8
SELECT SUBSTRING_INDEX(NULL, NULL, NULL);
9

10

11
SELECT SUBSTRING_INDEX(NULL, NULL, 0);
12
SELECT SUBSTRING_INDEX(NULL, NULL, 3);
13
SELECT SUBSTRING_INDEX(NULL, NULL, -3);
14

15
SELECT SUBSTRING_INDEX('abcdabcdabc', NULL, NULL);
16
SELECT SUBSTRING_INDEX('abcdabcdabc', NULL, 0);
17
SELECT SUBSTRING_INDEX('abcdabcdabc', NULL, 3);
18
SELECT SUBSTRING_INDEX('abcdabcdabc', NULL, -3);
19

20
SELECT SUBSTRING_INDEX(NULL, 'abc', NULL);
21
SELECT SUBSTRING_INDEX(NULL, 'abc', 0);
22
SELECT SUBSTRING_INDEX(NULL, 'abc', 3);
23
SELECT SUBSTRING_INDEX(NULL, 'abc', -3);
24

25
#empty
26
SELECT SUBSTRING_INDEX('', '', 0);
27
SELECT SUBSTRING_INDEX('', '', 3);
28
SELECT SUBSTRING_INDEX('', '', -3);
29

30
SELECT SUBSTRING_INDEX('abcdabcdabc', '', 0);
31
SELECT SUBSTRING_INDEX('abcdabcdabc', '', 3);
32
SELECT SUBSTRING_INDEX('abcdabcdabc', '', -3);
33

34
SELECT SUBSTRING_INDEX('', 'abc', 0);
35
SELECT SUBSTRING_INDEX('', 'abc', 3);
36
SELECT SUBSTRING_INDEX('', 'abc', -3);
37

38
#normal
39
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', 0);
40
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', 3);
41
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', -3);
42

43
#convert
44
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', '0');
45
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', '3');
46
SELECT SUBSTRING_INDEX('abcdabcdabc', 'abc', '-3');
47
SELECT SUBSTRING_INDEX(1.414, 1, '-1');
48

49
#special
50
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775806);
51
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775807);
52
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775808);
53
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775809);
54
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775810);
55
select SUBSTRING_INDEX( 'www.mysql.com','.', 9223372036854775811);
56
select SUBSTRING_INDEX( 'www.mysql.com','.', -9223372036854775806);
57
select SUBSTRING_INDEX( 'www.mysql.com','.', -9223372036854775807);
58
select SUBSTRING_INDEX( 'www.mysql.com','.', -9223372036854775808);
59
select SUBSTRING_INDEX( 'www.mysql.com','.', -9223372036854775809);
60

61
select substring_index('aaaaaaaaa1','aa',5);
62
select substring_index('aaaaaaaaa1','aa',4);
63
select substring_index('aaaaaaaaa1','aa',3);
64
select substring_index('aaaaaaaaa1','aa',2);
65
select substring_index('aaaaaaaaa1','aa',1);
66
select substring_index('aaaaaaaaa1','aa',0);
67
select substring_index('aaaaaaaaa1','aa',-1);
68
select substring_index('aaaaaaaaa1','aa',-2);
69
select substring_index('aaaaaaaaa1','aa',-3);
70
select substring_index('aaaaaaaaa1','aa',-4);
71
select substring_index('aaaaaaaaa1','aa',-5);
72

73
select substring_index('aaaaaaaaa1','aaaa',3);
74
select substring_index('aaaaaaaaa1','aaaa',2);
75
select substring_index('aaaaaaaaa1','aaaa',-1);
76
select substring_index('aaaaaaaaa1','aaaa',0);
77
select substring_index('aaaaaaaaa1','aaaa',-1);
78
select substring_index('aaaaaaaaa1','aaaa',-2);
79
select substring_index('aaaaaaaaa1','aaaa',-3);
80