github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/function_mid.result (about) 1 SELECT mid('zhanziziefw',1,2); 2 mid(zhanziziefw, 1, 2) 3 zh 4 SELECT mid('jdkajfkw83q9iwqjfeqw_7832',5,22); 5 mid(jdkajfkw83q9iwqjfeqw_7832, 5, 22) 6 jfkw83q9iwqjfeqw_7832 7 SELECT mid('我是一只自由的鸟',1,3); 8 mid(我是一只自由的鸟, 1, 3) 9 我是一 10 SELECT mid('好的fjewka鸭dfj00-=392*&*^$',-8,4); 11 mid(好的fjewka鸭dfj00-=392*&*^$, -8, 4) 12 392* 13 SELECT mid('好的fjewka鸭dfj00-=392',0,2); 14 mid(好的fjewka鸭dfj00-=392, 0, 2) 15 16 SELECT mid('abchfjwabcABCvshfjdrvoiewjvkmdns mndsabcAbcueiuiwqjemkvwme000',6,40); 17 mid(abchfjwabcABCvshfjdrvoiewjvkmdns mndsabcAbcueiuiwqjemkvwme000, 6, 40) 18 jwabcABCvshfjdrvoiewjvkmdns mndsabcAbcue 19 SELECT mid('http://www.google.com.cn/',-2,6); 20 mid(http://www.google.com.cn/, -2, 6) 21 n/ 22 SELECT mid('',1,3); 23 mid(, 1, 3) 24 25 SELECT mid(NULL,1,2); 26 mid(null, 1, 2) 27 null 28 SELECT mid('hduw',3,8); 29 mid(hduw, 3, 8) 30 uw 31 SELECT mid('',-1,3); 32 mid(, -1, 3) 33 34 SELECT mid('wfroiewjvre',NULL,3); 35 mid(wfroiewjvre, null, 3) 36 null 37 SELECT mid('vjdiejevfe',1,NULL); 38 mid(vjdiejevfe, 1, null) 39 null 40 SELECT mid('efjkw',NULL,NULL); 41 mid(efjkw, null, null) 42 null 43 SELECT mid('',2,9); 44 mid(, 2, 9) 45 46 DROP TABLE IF EXISTS mid_01; 47 CREATE TABLE mid_01(id int, 48 str1 CHAR, 49 d1 int, 50 d2 tinyint unsigned, 51 PRIMARY KEY(id)); 52 INSERT INTO mid_01 VALUES(1, 'h', 3, 12); 53 INSERT INTO mid_01 VALUES(2, '', -2, 32); 54 INSERT INTO mid_01 VALUES(3, '2',-2, 2); 55 INSERT into mid_01 VALUES(4, '*', NULL, 3); 56 INSERT INTO mid_01 VALUES(5, 'd', 2, NULL); 57 INSERT INTO mid_01 VALUES(6, 'ehiuwjqnelfkw', NULL, 3); 58 internal error: Can't cast 'ehiuwjqnelfkw' to CHAR type. Src length 13 is larger than Dest length 1 59 INSERT INTO mid_01 VALUES(7, '2',-2147483649, 0); 60 Data truncation: data out of range: data type int32, value '-2147483649' 61 INSERT INTO mid_01 VALUES(8, '1', 328, 258); 62 Data truncation: data out of range: data type uint8, value '258' 63 SELECT mid(str1, 1, 2) FROM mid_01; 64 mid(str1, 1, 2) 65 h 66 67 2 68 * 69 d 70 SELECT mid(str1, d1, 3) FROM mid_01; 71 mid(str1, d1, 3) 72 73 74 75 null 76 77 SELECT mid(str1, d1, d2) FROM mid_01; 78 mid(str1, d1, d2) 79 80 81 82 null 83 null 84 SELECT * FROM mid_01 WHERE mid(str1,1,1) = 'h'; 85 id str1 d1 d2 86 1 h 3 12 87 SELECT * FROM mid_01 WHERE mid(str1,-1,2) IS NULL AND d1 IS NOT NULL; 88 id str1 d1 d2 89 SELECT d1, d2 FROM mid_01 WHERE str1 = (SELECT str1 FROM mid_01 WHERE mid(str1,-1,1) = '*'); 90 d1 d2 91 null 3 92 SELECT(SELECT str1 FROM mid_01 WHERE mid(str1,2,3) = NULL),d1,d2 FROM mid_01 WHERE id = 4; 93 (select str1 from mid_01 where mid(str1, 2, 3) = null) d1 d2 94 null null 3 95 SELECT LENGTH(mid(str1, ABS(d1),d2)) FROM mid_01 WHERE str1 = '2'; 96 length(mid(str1, abs(d1), d2)) 97 0 98 SELECT empty(mid(str1, d1, d2)) FROM mid_01 WHERE ABS(d2) = 3; 99 empty(mid(str1, d1, d2)) 100 null 101 SELECT mid(str1, d1, d2) FROM mid_01; 102 mid(str1, d1, d2) 103 104 105 106 null 107 null 108 SELECT mid(str1, d1, 2) FROM mid_01 WHERE ABS(d1) % 2 = 0; 109 mid(str1, d1, 2) 110 111 112 113 SELECT mid(str1, d1, d2) FROM mid_01 WHERE d1 > 0; 114 mid(str1, d1, d2) 115 116 null 117 DROP TABLE IF EXISTS mid_02; 118 CREATE TABLE mid_02(id int, 119 s VARCHAR(50), 120 d1 smallint, 121 d2 bigint unsigned NOT NULL, 122 PRIMARY KEY(id)); 123 INSERT INTO mid_02 VALUES(1, 'woshishei3829', 3, 12); 124 INSERT INTO mid_02 VALUES(2, '', -2, 2132); 125 INSERT INTO mid_02 VALUES(3, ' 356284o 329&***((^%$%^&',-2, 2); 126 INSERT into mid_02 VALUES(4, NULL, NULL, 3); 127 INSERT INTO mid_02 VALUES(5, NULL, 2, 4); 128 INSERT INTO mid_02 VALUES(6, 'ehwqkjf8392__+ ',NULL,6); 129 INSERT INTO mid_02 values(7, '123', 0, 2); 130 INSERT INTO mid_02 VALUES(8, 'ehiuwjey73y8213092kjfm3e#$%^WHJfne32edwfdewvvcqeveqnelfkw', NULL, 3); 131 internal error: Can't cast 'ehiuwjey73y8213092kjfm3e#$%!^(MISSING)WHJfne32edwfdewvvcqeveqnelfkw' to VARCHAR type. Src length 57 is larger than Dest length 50 132 INSERT INTO mid_02 VALUES(9, '2',32769, 0); 133 Data truncation: data out of range: data type int16, value '32769' 134 INSERT INTO mid_02 VALUES(10, '1', 328, 18446744073709551618); 135 Data truncation: data out of range: data type uint64, value '18446744073709551618' 136 SELECT mid(s, NULL, NULL) FROM mid_02; 137 mid(s, null, null) 138 null 139 null 140 null 141 null 142 null 143 null 144 null 145 SELECT mid(s, NULL, 2) FROM mid_02; 146 mid(s, null, 2) 147 null 148 null 149 null 150 null 151 null 152 null 153 null 154 SELECT mid(s, 1, NULL) FROM mid_02; 155 mid(s, 1, null) 156 null 157 null 158 null 159 null 160 null 161 null 162 null 163 SELECT mid(s, 1, 9) FROM mid_02 WHERE mid(s, 1, 2) = 'eh'; 164 mid(s, 1, 9) 165 ehwqkjf83 166 SELECT mid(s, d1, d2) FROM mid_02; 167 mid(s, d1, d2) 168 shishei3829 169 170 ^& 171 null 172 null 173 null 174 175 SELECT mid(s, d1, -3) FROM mid_02 WHERE d2 = 2; 176 mid(s, d1, -3) 177 178 179 SELECT concat_ws('-',mid(s,2,3),mid(s,1,2)) FROM mid_02 WHERE id BETWEEN 2 AND 3; 180 concat_ws(-, mid(s, 2, 3), mid(s, 1, 2)) 181 - 182 356- 3 183 SELECT find_in_set(mid(s,1 + 2,9),'woshishei') FROM mid_02 WHERE id = 1; 184 find_in_set(mid(s, 1 + 2, 9), woshishei) 185 0 186 SELECT empty(mid(s,1,2)) FROM mid_02; 187 empty(mid(s, 1, 2)) 188 false 189 true 190 false 191 null 192 null 193 false 194 false 195 SELECT LENGTH(mid(s, -1, 7281979 % 2)) FROM mid_02; 196 length(mid(s, -1, 7281979 % 2)) 197 1 198 0 199 1 200 null 201 null 202 1 203 1 204 SELECT lengthutf8(mid(s, -1, 3)) FROM mid_02; 205 lengthutf8(mid(s, -1, 3)) 206 1 207 0 208 1 209 null 210 null 211 1 212 1 213 SELECT LTRIM(mid(s, 1, 16 - 11)) FROM mid_02; 214 ltrim(mid(s, 1, 16 - 11)) 215 woshi 216 217 3562 218 null 219 null 220 ehwqk 221 123 222 SELECT RTRIM(mid(s, -3, 2)) FROM mid_02 WHERE id = 6; 223 rtrim(mid(s, -3, 2)) 224 _+ 225 SELECT LPAD(mid(s, 1, 2),20,'*') FROM mid_02; 226 lpad(mid(s, 1, 2), 20, *) 227 ******************wo 228 ******************** 229 ****************** 3 230 null 231 null 232 ******************eh 233 ******************12 234 SELECT RPAD(mid(s, -8, 4), 5, '-') FROM mid_02 WHERE ABS(d1) = 0; 235 rpad(mid(s, -8, 4), 5, -) 236 ----- 237 SELECT startswith(mid(s, 1, 6), 'ehwq') FROM mid_02 WHERE d2 = NULL; 238 startswith(mid(s, 1, 6), ehwq) 239 SELECT endswith(mid(s,-1,1),' ') FROM mid_02 WHERE id = 6; 240 endswith(mid(s, -1, 1), ) 241 true 242 SELECT substring(mid(s, 3, 19),3, 10) FROM mid_02 WHERE id + 1 = 4; 243 substring(mid(s, 3, 19), 3, 10) 244 284o 329&* 245 SELECT REVERSE(mid(s, -1, 2)) FROM mid_02; 246 reverse(mid(s, -1, 2)) 247 9 248 249 & 250 null 251 null 252 253 3 254 SELECT hex(mid(s, 1, 2)) FROM mid_02 WHERE id = 7; 255 hex(mid(s, 1, 2)) 256 3132 257 SELECT * FROM mid_02 WHERE s = (SELECT s FROM mid_02 WHERE mid(s,1,2) = 'wo'); 258 id s d1 d2 259 1 woshishei3829 3 12 260 SELECT(SELECT s FROM mid_02 WHERE mid(s,1,3) = 'ehw'),d1,d2 FROM mid_02 WHERE id = 6; 261 (select s from mid_02 where mid(s, 1, 3) = ehw) d1 d2 262 ehwqkjf8392__+ null 6 263 SELECT * FROM mid_02 WHERE s = (SELECT s FROM mid_02 WHERE mid(s,1,10) = NULL); 264 id s d1 d2 265 DROP TABLE IF EXISTS mid_03; 266 DROP TABLE IF EXISTS mid_04; 267 CREATE TABLE mid_03( 268 id int, 269 d1 tinyint unsigned, 270 str1 VARCHAR(50), 271 primary key (id)); 272 CREATE TABLE mid_04( 273 id int, 274 d2 bigint, 275 str1 mediumtext NOT NULL, 276 primary key (id)); 277 INSERT INTO mid_03 VALUES(1, 255, 'zheshimeihaodeyitian,这是美好的一天'); 278 INSERT INTO mid_03 VALUES(2, 10, '明天更美好ehwqknjcw*^*qk67329&&*'); 279 INSERT INTO mid_03 VALUES(3, NULL, 'ewgu278wd-+ABNJDSK'); 280 INSERT INTO mid_03 VALUES(4, 1, NULL); 281 INSERT INTO mid_04 VALUES(1, 0, '盼望着,盼望着,东风来了,春天的脚步近了。 一切都像刚睡醒的样子,欣欣然张开了眼。山朗润起来了,水涨 起来了,太阳的脸红起来了。 小草偷偷地从土里钻出来，Choose to Be Alone on Purpose Here we are, all by ourselves, all 22 million of us by recent count, alone in our rooms'); 282 INSERT INTO mid_04 VALUES(2, -34, 'zheshimeihaodeyitian,这是美好的一天'); 283 INSERT INTO mid_04 VALUES(3, 35267192, 'ewgu278wd-+ABNJDSK'); 284 INSERT INTO mid_04 VALUES(4, NULL, 'hey32983..........,,'); 285 SELECT mid_03.id AS id_3,mid_04.id AS id_4 FROM mid_03,mid_04 WHERE mid(mid_03.str1,1,4) = mid(mid_04.str1,1,4); 286 id_3 id_4 287 1 2 288 3 3 289 SELECT mid_03.str1 AS str1_3,mid_04.str1 FROM mid_03,mid_04 WHERE mid(mid_03.str1,2,1) = mid(mid_04.str1,-1,1); 290 str1_3 str1 291 明天更美好ehwqknjcw*^*qk67329&&* zheshimeihaodeyitian,这是美好的一天 292 SELECT mid(mid_03.str1, -10, 5) FROM mid_03,mid_04 WHERE mid_03.str1 = mid_04.str1; 293 mid(mid_03.str1, -10, 5) 294 an,这是 295 d-+AB 296 SELECT * FROM mid_03 WHERE str1 = (SELECT str1 FROM mid_04 WHERE mid(str1, 1, 19) = 'ewgu278wd-+ABNJDSK'); 297 id d1 str1 298 3 null ewgu278wd-+ABNJDSK 299 SELECT mid(mid_03.str1, -10, 5)AS tmp, mid_04.str1 AS temp FROM mid_03 join mid_04 ON mid_03.str1 = mid_04.str1; 300 tmp temp 301 an,这是 zheshimeihaodeyitian,这是美好的一天 302 d-+AB ewgu278wd-+ABNJDSK 303 SELECT mid_03.id AS id_3,mid_04.id AS id_4 FROM mid_03 left join mid_04 ON mid(mid_03.str1,1,4) = mid(mid_04.str1,1,4); 304 id_3 id_4 305 1 2 306 2 null 307 3 3 308 4 null 309 SELECT mid_03.d1 AS d1_3,mid_04.d2 AS d2_4 FROM mid_03 right join mid_04 ON mid(mid_03.str1,1,4) = mid(mid_04.str1,1,4); 310 d1_3 d2_4 311 null 0 312 255 -34 313 null 35267192 314 null null