github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_string_mid.result (about) 1 SELECT MID('Quadratically',5); 2 mid(Quadratically, 5) 3 ratically 4 SELECT MID('foobarbar' FROM 4); 5 mid(foobarbar, 4) 6 barbar 7 SELECT MID('Quadratically',5,6); 8 mid(Quadratically, 5, 6) 9 ratica 10 SELECT MID('Sakila', -3); 11 mid(Sakila, -3) 12 ila 13 SELECT MID('Sakila', -5, 3); 14 mid(Sakila, -5, 3) 15 aki 16 SELECT MID('Sakila' FROM -4 FOR 2); 17 mid(Sakila, -4, 2) 18 ki 19 SELECT MID('monty',5,1); 20 mid(monty, 5, 1) 21 y 22 SELECT MID('a',1,2); 23 mid(a, 1, 2) 24 a 25 select MID('abcdefg',3,2); 26 mid(abcdefg, 3, 2) 27 cd 28 select MID('abcdefg',3,2); 29 mid(abcdefg, 3, 2) 30 cd 31 select MID('abcdefg',-3,2); 32 mid(abcdefg, -3, 2) 33 ef 34 select MID('abcdefg',-1,5); 35 mid(abcdefg, -1, 5) 36 g 37 select MID('abcdefg',0,0); 38 mid(abcdefg, 0, 0) 39 40 select MID('abcdefg',-1,-1); 41 mid(abcdefg, -1, -1) 42 43 select MID('abcdefg',1,-1); 44 mid(abcdefg, 1, -1) 45 46 select mid('hello', 2, -1); 47 mid(hello, 2, -1) 48 49 select mid('hello', -1, 1); 50 mid(hello, -1, 1) 51 o 52 select mid('hello', -2, 1); 53 mid(hello, -2, 1) 54 l 55 select mid('hello', -4294967295, 1); 56 mid(hello, -4294967295, 1) 57 58 select mid('hello', 4294967295, 1); 59 mid(hello, 4294967295, 1) 60 61 select mid('hello', -4294967296, 1); 62 mid(hello, -4294967296, 1) 63 64 select mid('hello', 4294967296, 1); 65 mid(hello, 4294967296, 1) 66 67 select mid('hello', -4294967297, 1); 68 mid(hello, -4294967297, 1) 69 70 select mid('hello', 4294967297, 1); 71 mid(hello, 4294967297, 1) 72 73 select mid('hello', -18446744073709551615, 1); 74 mid(hello, -18446744073709551615, 1) 75 76 select mid('hello', 18446744073709551615, 1); 77 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 78 select mid('hello', -18446744073709551616, 1); 79 mid(hello, -18446744073709551616, 1) 80 81 select mid('hello', 18446744073709551616, 1); 82 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 83 select mid('hello', -18446744073709551617, 1); 84 mid(hello, -18446744073709551617, 1) 85 86 select mid('hello', 18446744073709551617, 1); 87 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 88 select mid('hello', 1, -1); 89 mid(hello, 1, -1) 90 91 select mid('hello', 1, -4294967295); 92 mid(hello, 1, -4294967295) 93 94 select mid('hello', 1, 4294967295); 95 mid(hello, 1, 4294967295) 96 hello 97 select mid('hello', 1, -4294967296); 98 mid(hello, 1, -4294967296) 99 100 select mid('hello', 1, 4294967296); 101 mid(hello, 1, 4294967296) 102 hello 103 select mid('hello', 1, -4294967297); 104 mid(hello, 1, -4294967297) 105 106 select mid('hello', 1, 4294967297); 107 mid(hello, 1, 4294967297) 108 hello 109 select mid('hello', 1, -18446744073709551615); 110 mid(hello, 1, -18446744073709551615) 111 112 select mid('hello', 1, 18446744073709551615); 113 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 114 select mid('hello', 1, -18446744073709551616); 115 mid(hello, 1, -18446744073709551616) 116 117 select mid('hello', 1, 18446744073709551616); 118 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 119 select mid('hello', 1, -18446744073709551617); 120 mid(hello, 1, -18446744073709551617) 121 122 select mid('hello', 1, 18446744073709551617); 123 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 124 select mid('hello', -1, -1); 125 mid(hello, -1, -1) 126 127 select mid('hello', -4294967295, -4294967295); 128 mid(hello, -4294967295, -4294967295) 129 130 select mid('hello', 4294967295, 4294967295); 131 mid(hello, 4294967295, 4294967295) 132 133 select mid('hello', -4294967296, -4294967296); 134 mid(hello, -4294967296, -4294967296) 135 136 select mid('hello', 4294967296, 4294967296); 137 mid(hello, 4294967296, 4294967296) 138 139 select mid('hello', -4294967297, -4294967297); 140 mid(hello, -4294967297, -4294967297) 141 142 select mid('hello', 4294967297, 4294967297); 143 mid(hello, 4294967297, 4294967297) 144 145 select mid('hello', -18446744073709551615, -18446744073709551615); 146 mid(hello, -18446744073709551615, -18446744073709551615) 147 148 select mid('hello', 18446744073709551615, 18446744073709551615); 149 invalid argument function substring(str, start, lenth), bad value 18446744073709551615 150 select mid('hello', -18446744073709551616, -18446744073709551616); 151 mid(hello, -18446744073709551616, -18446744073709551616) 152 153 select mid('hello', 18446744073709551616, 18446744073709551616); 154 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 155 select mid('hello', -18446744073709551617, -18446744073709551617); 156 mid(hello, -18446744073709551617, -18446744073709551617) 157 158 select mid('hello', 18446744073709551617, 18446744073709551617); 159 invalid argument function substring(str, start, lenth), bad value 1.8446744073709552e+19 160 SELECT MID('Sakila' FROM -4 FOR 2); 161 mid(Sakila, -4, 2) 162 ki 163 SELECT MID('foobarbar' FROM 4); 164 mid(foobarbar, 4) 165 barbar 166 SELECT MID('w3resource',4,3); 167 mid(w3resource, 4, 3) 168 eso 169 select mid('abc', cast(2 as unsigned int)); 170 mid(abc, cast(2 as int unsigned)) 171 bc 172 create table t1(f1 varchar(255)); 173 insert into t1 values ("123"),("456"); 174 select mid(f1,1,1) from t1 group by 1; 175 SQL syntax error: column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function 176 drop table t1; 177 SELECT '1' IN ('1', MID(-9223372036854775809, 1)); 178 1 in (1, mid(-9223372036854775809, 1)) 179 true 180 SELECT MID('1', year(FROM_UNIXTIME(-1))); 181 mid(1, year(from_unixtime(-1))) 182 null 183 select reverse(mid(1,1,1)); 184 reverse(mid(1, 1, 1)) 185 1 186 CREATE TABLE t1(c1 CHAR(30)); 187 INSERT INTO t1 VALUES('111'),('222'); 188 SELECT DISTINCT mid(c1, 1, 2147483647) FROM t1; 189 mid(c1, 1, 2147483647) 190 111 191 222 192 SELECT DISTINCT mid(c1, 1, 2147483648) FROM t1; 193 mid(c1, 1, 2147483648) 194 111 195 222 196 SELECT DISTINCT mid(c1, -1, 2147483648) FROM t1; 197 mid(c1, -1, 2147483648) 198 1 199 2 200 SELECT DISTINCT mid(c1, -2147483647, 2147483648) FROM t1; 201 mid(c1, -2147483647, 2147483648) 202 203 SELECT DISTINCT mid(c1, 9223372036854775807, 23) FROM t1; 204 mid(c1, 9223372036854775807, 23) 205 206 DROP TABLE t1; 207 create table t1 (b varchar(5)); 208 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 209 select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1; 210 b mid(b, 1) mid(b, -1) mid(b, -2) mid(b, -3) mid(b, -4) mid(b, -5) 211 ab ab b ab 212 abc abc c bc abc 213 abcd abcd d cd bcd abcd 214 abcde abcde e de cde bcde abcde 215 select * from (select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1) t; 216 b mid(b, 1) mid(b, -1) mid(b, -2) mid(b, -3) mid(b, -4) mid(b, -5) 217 ab ab b ab 218 abc abc c bc abc 219 abcd abcd d cd bcd abcd 220 abcde abcde e de cde bcde abcde 221 drop table t1; 222 CREATE TABLE t1 (a varchar(2000), b varchar(10)); 223 INSERT INTO t1 select space(1300),'one'; 224 INSERT INTO t1 select space(1300),'two'; 225 SELECT MID(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 226 mid(a, 1, 10) length(a) 227 1300 228 drop table t1; 229 CREATE TABLE t1(a INT); 230 INSERT INTO t1 VALUES (0),(0); 231 SELECT 1 FROM t1 GROUP BY MID('',1,''); 232 invalid argument cast to int, bad value 233 drop table t1; 234 create table t1 (email varchar(50)); 235 insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); 236 create table t2(id int not null, t2 varchar(50)); 237 insert into t2 select 1, mid(email, 2,1) from t1; 238 select id from t2; 239 id 240 1 241 1 242 1 243 1 244 1 245 select t2 from t2; 246 t2 247 a 248 o 249 o 250 o 251 a 252 drop table t1; 253 drop table t2; 254 create table t1 (a varchar(50)); 255 insert into t1 values("你好"), ("再见"), ("今天"); 256 select * from t1; 257 a 258 你好 259 再见 260 今天 261 select mid(a, 1, 1) from t1; 262 mid(a, 1, 1) 263 你 264 再 265 今 266 drop table t1; 267 select mid(null, 1, 1); 268 mid(null, 1, 1) 269 null 270 create table t1 (b varchar(5)); 271 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 272 select * from t1 where mid(b,1,1) = 'a'; 273 b 274 ab 275 abc 276 abcd 277 abcde 278 drop table t1; 279 drop table if exists t1; 280 create table t1(b varchar(5)); 281 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 282 select b from t1 group by b having mid(b,1,1)+'a'='aa'; 283 b 284 ab 285 abc 286 abcd 287 abcde 288 drop table t1; 289 drop table if exists t1; 290 drop table if exists t2; 291 create table t1(a varchar(5)); 292 create table t2(a varchar(20)); 293 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 294 insert into t2 values("2013-04-30"),("1994-10-04"),("2018-06-04"),("2012-10-12"), ("abc"),("abcd"); 295 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (mid(t1.a, 1,1) = mid(t2.a, 1,1 )); 296 a a 297 ab abc 298 abc abc 299 abcd abc 300 abcde abc 301 ab abcd 302 abc abcd 303 abcd abcd 304 abcde abcd 305 drop table t1; 306 drop table t2; 307 drop table if exists t1; 308 create table t1(a blob); 309 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 310 SELECT MID(a,1,4), LENGTH(a) FROM t1 GROUP BY a; 311 mid(a, 1, 4) length(a) 312 ab 2 313 abc 3 314 abcd 4 315 abcd 5 316 SELECT MID(a,-1) FROM t1; 317 mid(a, -1) 318 b 319 c 320 d 321 e 322 drop table t1;