github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_format.test (about) 1 -- @suit 2 -- @case 3 -- @test function format() 4 -- @label:bvt 5 6 -- constant test 7 SELECT FORMAT(12332.123456, 4); 8 SELECT FORMAT(12332.1,4); 9 SELECT FORMAT(12332.2,0); 10 SELECT FORMAT(19999999.999999999,4); 11 SELECT FORMAT(4545456,9); 12 SELECT format(73829324324,0); 13 14 SELECT FORMAT("-.12334.2",2); 15 SELECT FORMAT("12332.123456", 4); 16 SELECT FORMAT("12332.1",4); 17 SELECT FORMAT("12332.2",0); 18 SELECT FORMAT("19999999.999999999",4); 19 SELECT FORMAT(12332.123456, "4"); 20 SELECT FORMAT(12332.1,"4"); 21 SELECT FORMAT(12332.2,"0"); 22 SELECT FORMAT(19999999.999999999,"4"); 23 SELECT FORMAT("-.12334.2","2"); 24 SELECT FORMAT("12332.123456", "4"); 25 SELECT FORMAT("12332.1","4"); 26 SELECT FORMAT("12332.2","0"); 27 SELECT FORMAT("19999999.999999999","4"); 28 29 SELECT FORMAT("-.12334.2","2", "en_US"); 30 SELECT FORMAT("12332.123456", "4", "en_US"); 31 SELECT FORMAT("12332.1","4", "en_US"); 32 SELECT FORMAT("12332.2","0", "en_US"); 33 SELECT FORMAT("19999999.999999999","4", "en_US"); 34 35 SELECT FORMAT("-.12334.2","2", "ar_SA"); 36 SELECT FORMAT("12332.123456", "4", "ar_SA"); 37 SELECT FORMAT("12332.1","4", "ar_SA"); 38 SELECT FORMAT("12332.2","0", "ar_SA"); 39 SELECT FORMAT("19999999.999999999","4", "ar_SA"); 40 41 SELECT FORMAT("-.12334.2","2", "be_BY"); 42 SELECT FORMAT("12332.123456", "4", "be_BY"); 43 SELECT FORMAT("12332.1","4", "be_BY"); 44 SELECT FORMAT("12332.2","0", "be_BY"); 45 SELECT FORMAT("19999999.999999999","4", "be_BY"); 46 47 SELECT FORMAT("-.12334.2","2", "bg_BG"); 48 SELECT FORMAT("12332.123456", "4", "bg_BG"); 49 SELECT FORMAT("12332.1","4", "bg_BG"); 50 SELECT FORMAT("12332.2","0", "bg_BG"); 51 SELECT FORMAT("19999999.999999999","4", "bg_BG"); 52 53 SELECT FORMAT("-.12334.2","2", "de_CH"); 54 SELECT FORMAT("12332.123456", "4", "de_CH"); 55 SELECT FORMAT("12332.1","4", "de_CH"); 56 SELECT FORMAT("12332.2","0", "de_CH"); 57 SELECT FORMAT("19999999.999999999","4", "de_CH"); 58 59 60 create table test(a varchar(100), b varchar(100)); 61 insert into test values("-.12334.2","2"); 62 insert into test values("12332.123456", "4"); 63 insert into test values("12332.1","4"); 64 insert into test values("12332.2","0"); 65 insert into test values("19999999.999999999"); 66 SELECT FORMAT(a, b) from test; 67 SELECT FORMAT(a, b, null) from test; 68 SELECT FORMAT(a, b, "en_US") from test; 69 SELECT FORMAT(a, b, "ar_SA") from test; 70 SELECT FORMAT(a, b, "be_BY") from test; 71 SELECT FORMAT(a, b, "bg_BG") from test; 72 SELECT FORMAT(a, b, "de_CH") from test; 73 drop table test; 74 75 create table test(a varchar(100), b varchar(100), c varchar(20)); 76 insert into test values("-.12334.2","2", "en_US"); 77 insert into test values("12332.123456", "4", "en_US"); 78 insert into test values("12332.1","4", "en_US"); 79 insert into test values("12332.2","0", "en_US"); 80 insert into test values("19999999.999999999","en_US"); 81 create table test(a varchar(100), b varchar(100), c varchar(20)); 82 insert into test values("-.12334.2","2", "de_CH"); 83 insert into test values("12332.123456", "4", "de_CH"); 84 insert into test values("12332.1","4", "de_CH"); 85 insert into test values("12332.2","0", "de_CH"); 86 insert into test values("19999999.999999999","de_CH"); 87 drop table test; 88 89 90 -- @suite 91 -- @setup 92 DROP TABLE IF EXISTS format_01; 93 CREATE TABLE format_01(id INT, d1 tinyint, d2 smallint unsigned, d3 int, d4 bigint unsigned, PRIMARY KEY (id)); 94 INSERT INTO format_01 VALUES(1, -3, 46382, 46787698, 454987456415); 95 INSERT INTO format_01 VALUES(2, 126, NULL, 743482 , 938256415); 96 INSERT INTO format_01 VALUES(3, 21, 37821, 3782, 0); 97 INSERT INTO format_01 VALUES(4, 0, 0, 0, 0); 98 99 100 -- Abnormal insertion 101 INSERT INTO format_01 VALUES(5, 127, 3892, 743482 , 938256415); 102 INSERT INTO format_01 VALUES(6, 22, 3, 4, 184467440737095516152); 103 104 105 -- @case 106 -- @ integer test 107 SELECT format(d1, id) from format_01; 108 SELECT format(d1, id, NULL) from format_01; 109 SELECT format(d1, id, "en_US") from format_01; 110 SELECT format(d1, id, "ar_SA") from format_01; 111 SELECT format(d1, id, "be_BY") from format_01; 112 SELECT format(d1, id, "bg_BG") from format_01; 113 114 115 -- Filtering, nesting, mathematical operation 116 SELECT * FROM format_01 WHERE format(d1,0) = 126; 117 SELECT id FROM (SELECT * FROM format_01 WHERE format(d1,0) = 126) as a; 118 SELECT format(d1, id) FROM format_01 WHERE d3 % 2 = 0; 119 SELECT format(d3, 4) FROM format_01 WHERE ABS(d3) > 1000; 120 SELECT COUNT(format(d1, 2)) FROM format_01 GROUP BY d1; 121 SELECT * FROM format_01 ORDER BY format(id, 0) DESC; 122 SELECT SIN(format(d1,2)), TAN(format(id, 0)), COS(format(id,0)) from format_01; 123 SELECT cot(format(45, 0)), ATAN(format(75, 0)), SINH(format(90, 1)); 124 SELECT EXP(format(4, 2)); 125 SELECT MAX(format(d1, id)), MIN(format(d3, 0)) from format_01; 126 SELECT power(format(2,1), 2); 127 SELECT COUNT(format(d3,2)) FROM format_01; 128 129 130 -- Abnormal test 131 SELECT ABS(format(d3,0)) FROM format_01; 132 SELECT SUM(format(id,2)) FROM format_01; 133 SELECT CEIL(format(d3, 4))FROM format_01; 134 SELECT ROUND(format(d4, 0)) FROM format_01; 135 DROP TABLE format_01; 136 137 138 -- @suite 139 -- @setup 140 DROP TABLE IF EXISTS format_02; 141 CREATE TABLE format_02(id INT, d1 FLOAT, d2 DOUBLE NOT NULL); 142 143 -- float、double 144 INSERT INTO format_02 VALUES(1, 1.2, 47832.434); 145 INSERT INTO format_02 VALUES(2, -3283.33, 73.32); 146 INSERT INTO format_02 VALUES(3, NULL, 32789.33); 147 INSERT INTO format_02 VALUES(4, 0, -38902093.4324); 148 INSERT INTO format_02 VALUES(5, 323232.0, 0); 149 INSERT INTO format_02 VALUES(6, 323, 37827329.43432); 150 151 -- Abnormal insertion 152 INSERT INTO format_02 VALUES(6, -1.8976931348623157E+308, 0); 153 INSERT INTO format_02 VALUES(7, 328739232, NULL); 154 INSERT INTO format_02 VALUES(8, 7382,3923404.2132421); 155 156 SELECT d1, d2 FROM format_02 WHERE format(d2,0) IS NOT NULL; 157 SELECT * from format_02 WHERE format(d2, 3) NOT LIKE '47832.43%'; 158 SELECT format(d2, id) FROM format_02; 159 SELECT format(d2, id, NULL) from format_02; 160 SELECT format(d2, id, "en_US") from format_02; 161 SELECT format(d2,0)FROM format_02; 162 SELECT format(d1, id) FROM format_02 WHERE d2 % 2 = 0; 163 SELECT format(d2, 4) FROM format_02 WHERE ABS(d2) > 1000; 164 SELECT any_value(format(id,2)) FROM format_02; 165 SELECT d1, d2 FROM format_02 WHERE cast(format(id,0) as bigint) BETWEEN 0 AND 400; 166 SELECT * from format_02 WHERE format(id, 1) like '1.2'; 167 SELECT format(FLOOR(d2),4) FROM format_02; 168 SELECT format(CEIL(d2),6) FROM format_02; 169 SELECT * FROM format_02 WHERE format(d2,0) IN ('73', '32789'); 170 SELECT * FROM format_02 WHERE format(d2,0) NOT IN ('73', '32789'); 171 172 173 -- Abnormal test 174 SELECT * from format_02 WHERE format(d2, 2) >= 0; 175 SELECT * FROM format_02 WHERE format(d2, 0) NOT BETWEEN 0 AND 100; 176 SELECT * FROM format_02 WHERE format(d2, 2) MOD 2 = 0; 177 178 179 -- Nesting of functions related to string 180 SELECT concat_ws(format(d2,3),'abc') from format_02; 181 SELECT LENGTH(format(d2,3)) from format_02; 182 SELECT LPAD(format(d2,3),20,'**') from format_02; 183 SELECT RPAD(format(d1,2),20,'-') from format_02; 184 SELECT format(d2,3) from format_02; 185 SELECT startswith(format(d2,1),'47823') from format_02 WHERE id = 1; 186 SELECT endswith(format(d2,1),'32.4') from format_02 WHERE id = 1; 187 SELECT substring(format(d1,3),3,5) from format_02; 188 SELECT REVERSE(format(d1,5)) from format_02; 189 DROP TABLE format_02; 190 191 192 -- @suite 193 -- @setup 194 DROP TABLE IF EXISTS format_04; 195 196 CREATE TABLE format_04(a varchar(100), b varchar(100), c varchar(20)); 197 INSERT INTO format_04 VALUES("-0.12334","2", "en_US"); 198 INSERT INTO format_04 VALUES("12332.123456", "4", "en_US"); 199 INSERT INTO format_04 VALUES("12332.1","4", "en_US"); 200 INSERT INTO format_04 VALUES("12332.2","0", "en_US"); 201 202 SELECT format(a, 4) from format_04; 203 SELECT format(a, 2),format(b, 3) FROM format_04 WHERE b > 0; 204 SELECT a,b,c FROM format_04 WHERE format(b,0) = 4; 205 DROP table format_04; 206 207 208 -- @suite 209 -- @setup 210 DROP TABLE if exists format_05; 211 DROP TABLE if exists format_06; 212 CREATE TABLE format_05( 213 c1 int, 214 c2 bigint, 215 primary key (c1) 216 ); 217 create TABLE format_06( 218 i int, 219 b bigint, 220 primary key (i) 221 ); 222 223 -- join 224 INSERT INTO format_05 values ('111', '222'), ('000', '111'), ('-111', null); 225 INSERT INTO format_06 values (111, 999999), (0,0000000), (-1, null), (11111, 1); 226 SELECT c1, format(c1,1), i, format(i,1) from format_05 inner join format_06 on format_05.c1 = format_06.i order by c1 desc; 227 SELECT format(c2, 5) FROM format_05 join format_06 ON format_05.c1 = format_06.i; 228 SELECT format_05.c1 from format_05 join format_06 ON format(format_05.c1,1) = format(format_06.i,1); 229 SELECT format_06.i,format_06.b from format_05 left join format_06 ON format(format_05.c1,1) = format(format_06.i,1); 230 DROP TABLE format_05; 231 DROP TABLE format_06; 232 233 234 drop table if exists t; 235 create table t( 236 d date, 237 dt datetime, 238 primary key (d) 239 ); 240 insert into t values ('2022-12-04', '2022-12-04 00:03:36'); 241 insert into t values ('2023-11-11', '2023-11-11 03:00:48'); 242 select d, format(d, 1) from t order by d desc; 243 select d, format(d, 0) from t order by d desc; 244 select d, format(d, -1) from t order by d desc; 245 select d, format(d, length("123456789")) from t order by d desc; 246 select dt, format(dt, 1) from t order by dt desc; 247 select dt, format(dt, 0) from t order by dt desc; 248 select dt, format(dt, -1) from t order by dt desc; 249 select dt, format(dt, length("123456789")) from t order by dt desc; 250 drop table t; 251 252 drop table if exists t; 253 create table t( 254 i int, 255 b bigint, 256 primary key (i) 257 ); 258 insert into t values (-1, null), (0000000000000000, 1); 259 select b, format(b, 1) from t order by b desc; 260 select b, format(b, 0) from t order by b desc; 261 select b, format(b, -1) from t order by b desc; 262 select b, format(b, '1') from t order by b desc; 263 select b, format(b, '-1') from t order by b desc; 264 select b, format(b, '0') from t order by b desc; 265 select b, format(b, 'x') from t order by b desc; 266 drop table t; 267 268 drop table if exists t1; 269 drop table if exists t2; 270 create table t1( 271 c1 int, 272 c2 bigint, 273 primary key (c1) 274 ); 275 create table t2( 276 i int, 277 b bigint, 278 primary key (i) 279 ); 280 insert into t1 values ('111', '222'), ('000', '111'), ('-111', null); 281 insert into t2 values (111, 999999), (0,0000000), (-1, null), (11111, 1); 282 select c1, format(c1,1), i, format(i,1) from t1 left join t2 on t1.c1 = t2.i order by c1 desc; 283 select c1, format(c1,1), i, format(i,1) from t1 right join t2 on t1.c1 = t2.i order by c1 desc; 284 drop table t1; 285 drop table t2; 286 287 SELECT FORMAT("12332.2", "2","ca_ES"); 288 SELECT FORMAT("12332.2", "2","de_AT"); 289 SELECT FORMAT("12332.2", "2","el_GR"); 290 SELECT FORMAT("12332.2", "2","eu_ES"); 291 SELECT FORMAT("12332.2", "2","fr_BE"); 292 SELECT FORMAT("12332.2", "2","fr_CA"); 293 SELECT FORMAT("12332.2", "2","fr_CH"); 294 SELECT FORMAT("12332.2", "2","fr_FR"); 295 SELECT FORMAT("12332.2", "2","fr_LU"); 296 SELECT FORMAT("12332.2", "2","gl_ES"); 297 SELECT FORMAT("12332.2", "2","hr_HR"); 298 SELECT FORMAT("12332.2", "2","it_IT"); 299 SELECT FORMAT("12332.2", "2","nl_BE"); 300 SELECT FORMAT("12332.2", "2","nl_NL"); 301 SELECT FORMAT("12332.2", "2","pl_PL"); 302 SELECT FORMAT("12332.2", "2","pt_BR"); 303 SELECT FORMAT("12332.2", "2","pt_PT"); 304 SELECT FORMAT("12332.2", "2","sl_SI"); 305 306 SELECT FORMAT("12332.2", "2","it_CH"); 307 SELECT FORMAT("12332.2", "2","rm_CH"); 308 309 SELECT FORMAT("12332.2", "2","ro_RO");