github.com/matrixorigin/matrixone@v1.2.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 Data truncation: data out of range: data type int64, value '-18446744073709551615' 75 select mid('hello', 18446744073709551615, 1); 76 Data truncation: data out of range: data type int64, value '18446744073709551615' 77 select mid('hello', -18446744073709551616, 1); 78 Data truncation: data out of range: data type int64, value '-18446744073709551616' 79 select mid('hello', 18446744073709551616, 1); 80 Data truncation: data out of range: data type int64, value '18446744073709551616' 81 select mid('hello', -18446744073709551617, 1); 82 Data truncation: data out of range: data type int64, value '-18446744073709551617' 83 select mid('hello', 18446744073709551617, 1); 84 Data truncation: data out of range: data type int64, value '18446744073709551617' 85 select mid('hello', 1, -1); 86 mid(hello, 1, -1) 87 88 select mid('hello', 1, -4294967295); 89 mid(hello, 1, -4294967295) 90 91 select mid('hello', 1, 4294967295); 92 mid(hello, 1, 4294967295) 93 hello 94 select mid('hello', 1, -4294967296); 95 mid(hello, 1, -4294967296) 96 97 select mid('hello', 1, 4294967296); 98 mid(hello, 1, 4294967296) 99 hello 100 select mid('hello', 1, -4294967297); 101 mid(hello, 1, -4294967297) 102 103 select mid('hello', 1, 4294967297); 104 mid(hello, 1, 4294967297) 105 hello 106 select mid('hello', 1, -18446744073709551615); 107 Data truncation: data out of range: data type int64, value '-18446744073709551615' 108 select mid('hello', 1, 18446744073709551615); 109 Data truncation: data out of range: data type int64, value '18446744073709551615' 110 select mid('hello', 1, -18446744073709551616); 111 Data truncation: data out of range: data type int64, value '-18446744073709551616' 112 select mid('hello', 1, 18446744073709551616); 113 Data truncation: data out of range: data type int64, value '18446744073709551616' 114 select mid('hello', 1, -18446744073709551617); 115 Data truncation: data out of range: data type int64, value '-18446744073709551617' 116 select mid('hello', 1, 18446744073709551617); 117 Data truncation: data out of range: data type int64, value '18446744073709551617' 118 select mid('hello', -1, -1); 119 mid(hello, -1, -1) 120 121 select mid('hello', -4294967295, -4294967295); 122 mid(hello, -4294967295, -4294967295) 123 124 select mid('hello', 4294967295, 4294967295); 125 mid(hello, 4294967295, 4294967295) 126 127 select mid('hello', -4294967296, -4294967296); 128 mid(hello, -4294967296, -4294967296) 129 130 select mid('hello', 4294967296, 4294967296); 131 mid(hello, 4294967296, 4294967296) 132 133 select mid('hello', -4294967297, -4294967297); 134 mid(hello, -4294967297, -4294967297) 135 136 select mid('hello', 4294967297, 4294967297); 137 mid(hello, 4294967297, 4294967297) 138 139 select mid('hello', -18446744073709551615, -18446744073709551615); 140 Data truncation: data out of range: data type int64, value '-18446744073709551615' 141 select mid('hello', 18446744073709551615, 18446744073709551615); 142 Data truncation: data out of range: data type int64, value '18446744073709551615' 143 select mid('hello', -18446744073709551616, -18446744073709551616); 144 Data truncation: data out of range: data type int64, value '-18446744073709551616' 145 select mid('hello', 18446744073709551616, 18446744073709551616); 146 Data truncation: data out of range: data type int64, value '18446744073709551616' 147 select mid('hello', -18446744073709551617, -18446744073709551617); 148 Data truncation: data out of range: data type int64, value '-18446744073709551617' 149 select mid('hello', 18446744073709551617, 18446744073709551617); 150 Data truncation: data out of range: data type int64, value '18446744073709551617' 151 SELECT MID('Sakila' FROM -4 FOR 2); 152 mid(Sakila, -4, 2) 153 ki 154 SELECT MID('foobarbar' FROM 4); 155 mid(foobarbar, 4) 156 barbar 157 SELECT MID('w3resource',4,3); 158 mid(w3resource, 4, 3) 159 eso 160 select mid('abc', cast(2 as unsigned int)); 161 mid(abc, cast(2 as int unsigned)) 162 bc 163 create table t1(f1 varchar(255)); 164 insert into t1 values ("123"),("456"); 165 select mid(f1,1,1) from t1 group by 1; 166 SQL syntax error: column "t1.f1" must appear in the GROUP BY clause or be used in an aggregate function 167 drop table t1; 168 SELECT '1' IN ('1', MID(-9223372036854775809, 1)); 169 1 in (1, mid(-9223372036854775809, 1)) 170 true 171 SELECT MID('1', year(FROM_UNIXTIME(-1))); 172 mid(1, year(from_unixtime(-1))) 173 null 174 select reverse(mid(1,1,1)); 175 reverse(mid(1, 1, 1)) 176 1 177 CREATE TABLE t1(c1 CHAR(30)); 178 INSERT INTO t1 VALUES('111'),('222'); 179 SELECT DISTINCT mid(c1, 1, 2147483647) FROM t1; 180 mid(c1, 1, 2147483647) 181 111 182 222 183 SELECT DISTINCT mid(c1, 1, 2147483648) FROM t1; 184 mid(c1, 1, 2147483648) 185 111 186 222 187 SELECT DISTINCT mid(c1, -1, 2147483648) FROM t1; 188 mid(c1, -1, 2147483648) 189 1 190 2 191 SELECT DISTINCT mid(c1, -2147483647, 2147483648) FROM t1; 192 mid(c1, -2147483647, 2147483648) 193 194 SELECT DISTINCT mid(c1, 9223372036854775807, 23) FROM t1; 195 mid(c1, 9223372036854775807, 23) 196 197 DROP TABLE t1; 198 create table t1 (b varchar(5)); 199 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 200 select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1; 201 b mid(b, 1) mid(b, -1) mid(b, -2) mid(b, -3) mid(b, -4) mid(b, -5) 202 ab ab b ab 203 abc abc c bc abc 204 abcd abcd d cd bcd abcd 205 abcde abcde e de cde bcde abcde 206 select * from (select *,mid(b,1),mid(b,-1),mid(b,-2),mid(b,-3),mid(b,-4),mid(b,-5) from t1) t; 207 b mid(b, 1) mid(b, -1) mid(b, -2) mid(b, -3) mid(b, -4) mid(b, -5) 208 ab ab b ab 209 abc abc c bc abc 210 abcd abcd d cd bcd abcd 211 abcde abcde e de cde bcde abcde 212 drop table t1; 213 CREATE TABLE t1 (a varchar(2000), b varchar(10)); 214 INSERT INTO t1 select space(1300),'one'; 215 INSERT INTO t1 select space(1300),'two'; 216 SELECT MID(a,1,10), LENGTH(a) FROM t1 GROUP BY a; 217 mid(a, 1, 10) length(a) 218 1300 219 drop table t1; 220 CREATE TABLE t1(a INT); 221 INSERT INTO t1 VALUES (0),(0); 222 SELECT 1 FROM t1 GROUP BY MID('',1,''); 223 invalid argument cast to int, bad value 224 drop table t1; 225 create table t1 (email varchar(50)); 226 insert into t1 values ('sasha@mysql.com'),('monty@mysql.com'),('foo@hotmail.com'),('foo@aol.com'),('bar@aol.com'); 227 create table t2(id int not null, t2 varchar(50)); 228 insert into t2 select 1, mid(email, 2,1) from t1; 229 select id from t2; 230 id 231 1 232 1 233 1 234 1 235 1 236 select t2 from t2; 237 t2 238 a 239 o 240 o 241 o 242 a 243 drop table t1; 244 drop table t2; 245 create table t1 (a varchar(50)); 246 insert into t1 values("你好"), ("再见"), ("今天"); 247 select * from t1; 248 a 249 你好 250 再见 251 今天 252 select mid(a, 1, 1) from t1; 253 mid(a, 1, 1) 254 你 255 再 256 今 257 drop table t1; 258 select mid(null, 1, 1); 259 mid(null, 1, 1) 260 null 261 create table t1 (b varchar(5)); 262 insert t1 values ('ab'), ('abc'), ('abcd'), ('abcde'); 263 select * from t1 where mid(b,1,1) = 'a'; 264 b 265 ab 266 abc 267 abcd 268 abcde 269 drop table t1; 270 drop table if exists t1; 271 create table t1(b varchar(5)); 272 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 273 select b from t1 group by b having mid(b,1,1)+'a'='aa'; 274 b 275 ab 276 abc 277 abcd 278 abcde 279 drop table t1; 280 drop table if exists t1; 281 drop table if exists t2; 282 create table t1(a varchar(5)); 283 create table t2(a varchar(20)); 284 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 285 insert into t2 values("2013-04-30"),("1994-10-04"),("2018-06-04"),("2012-10-12"), ("abc"),("abcd"); 286 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (mid(t1.a, 1,1) = mid(t2.a, 1,1 )); 287 a a 288 ab abc 289 abc abc 290 abcd abc 291 abcde abc 292 ab abcd 293 abc abcd 294 abcd abcd 295 abcde abcd 296 drop table t1; 297 drop table t2; 298 drop table if exists t1; 299 create table t1(a blob); 300 insert into t1 values('ab'), ('abc'), ('abcd'), ('abcde'); 301 SELECT MID(a,1,4), LENGTH(a) FROM t1 GROUP BY a; 302 mid(a, 1, 4) length(a) 303 ab 2 304 abc 3 305 abcd 4 306 abcd 5 307 SELECT MID(a,-1) FROM t1; 308 mid(a, -1) 309 b 310 c 311 d 312 e 313 drop table t1;