github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/builtin.result (about) 1 drop table if exists t1; 2 create table t1(a int,b int); 3 insert into t1 values(5,-2),(10,3),(100,0),(4,3),(6,-3); 4 select power(a,b) from t1; 5 power(a, b) 6 0.04 7 1000.0 8 1.0 9 64.0 10 0.004629629629629629 11 select power(a,2) as a1, power(b,2) as b1 from t1 where power(a,2) > power(b,2) order by a1 asc; 12 a1 b1 13 16.0 9.0 14 25.0 4.0 15 36.0 9.0 16 100.0 9.0 17 10000.0 0.0 18 drop table if exists t1; 19 create table t1(a date,b datetime); 20 insert into t1 values("2022-06-01","2022-07-01 00:00:00"); 21 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 22 select month(a),month(b) from t1; 23 month(a) month(b) 24 6 7 25 12 1 26 select * from t1 where month(a)>month(b); 27 a b 28 2022-12-31 2011-01-31 12:00:00 29 select * from t1 where month(a) between 1 and 6; 30 a b 31 2022-06-01 2022-07-01 00:00:00 32 drop table if exists t1; 33 create table t1(a varchar(12),c char(30)); 34 insert into t1 values('sdfad ','2022-02-02 22:22:22'); 35 insert into t1 values(' sdfad ','2022-02-02 22:22:22'); 36 insert into t1 values('adsf sdfad','2022-02-02 22:22:22'); 37 insert into t1 values(' sdfad','2022-02-02 22:22:22'); 38 select reverse(a),reverse(c) from t1; 39 reverse(a) reverse(c) 40 dafds 22:22:22 20-20-2202 41 dafds 22:22:22 20-20-2202 42 dafds fsda 22:22:22 20-20-2202 43 dafds 22:22:22 20-20-2202 44 select a from t1 where reverse(a) like 'daf%'; 45 a 46 adsf sdfad 47 sdfad 48 select reverse(a) reversea,reverse(reverse(a)) normala from t1; 49 reversea normala 50 dafds sdfad 51 dafds sdfad 52 dafds fsda adsf sdfad 53 dafds sdfad 54 drop table if exists t1; 55 create table t1(a int,b float); 56 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 57 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 58 select acos(a*pi()/180) as acosa,acos(b*pi()/180) acosb from t1; 59 acosa acosb 60 1.5707963267948966 1.5707963267948966 61 1.8356824738191324 1.927370391646567 62 1.9648910192076245 1.7907312931992256 63 1.5707963267948966 null 64 1.0197267436954502 null 65 null null 66 null null 67 null null 68 select acos(a*pi()/180)*acos(b*pi()/180) as acosab,acos(acos(a*pi()/180)) as c from t1; 69 acosab c 70 2.4674011002723395 null 71 3.5380400485035204 null 72 3.518591835821214 null 73 null null 74 null null 75 null null 76 null null 77 null null 78 select b from t1 where acos(a*pi()/180)<=acos(b*pi()/180) order by a; 79 b 80 -20.0 81 0.0 82 drop table if exists t1; 83 create table t1(a int,b float); 84 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 85 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 86 select atan(a*pi()/180) as atana,atan(b*pi()/180) atanb from t1; 87 atana atanb 88 0.0 0.0 89 -0.25605276998075555 -0.33584237256640787 90 -0.3666136182932664 -0.2148004503403853 91 0.0 1.4129651365067377 92 0.48234790710102493 1.4249275378445119 93 1.0038848218538872 1.4441537892065186 94 1.2626272556789115 1.3616916829711634 95 1.2626272556789115 1.2626272556789115 96 select atan(a*pi()/180)*atan(b*pi()/180) as atanab,atan(atan(a*pi()/180)) as c from t1; 97 atanab c 98 0.0 0.0 99 0.08599336977253765 -0.25066722482387166 100 0.07874877031031174 -0.3513980316581596 101 0.0 0.0 102 0.6873108156499168 0.44942647356532794 103 1.449764069407202 0.7873368062499201 104 1.7193090327506784 0.900952887864509 105 1.5942275867832594 0.900952887864509 106 select b from t1 where atan(a*pi()/180)<=atan(b*pi()/180) order by a; 107 b 108 -12.5 109 0.0 110 360.0 111 390.0 112 450.0 113 270.0 114 180.0 115 drop table if exists t1; 116 CREATE TABLE t1( 117 Employee_Name VARCHAR(100) NOT NULL, 118 Working_At VARCHAR(20) NOT NULL, 119 Work_Location VARCHAR(20) NOT NULL, 120 Joining_Date DATE NOT NULL, 121 Annual_Income INT NOT NULL); 122 INSERT INTO t1 123 VALUES 124 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 125 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 126 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 127 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 128 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 129 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 130 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 131 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 132 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 133 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 134 SELECT Working_At, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 135 working_at bitorincome 136 XYZ Digital 262144 137 ABC Corp. 65792 138 PQR Soln. 4096 139 SELECT Work_Location, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 140 work_location bitorincome 141 Kolkata 262144 142 Delhi 0 143 drop table if exists t1; 144 CREATE TABLE t1( 145 Employee_Name VARCHAR(100) NOT NULL, 146 Working_At VARCHAR(20) NOT NULL, 147 Work_Location VARCHAR(20) NOT NULL, 148 Joining_Date DATE NOT NULL, 149 Annual_Income INT NOT NULL); 150 INSERT INTO t1 151 VALUES 152 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 153 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 154 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 155 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 156 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 157 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 158 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 159 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 160 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 161 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 162 SELECT Work_Location, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 163 work_location bitorincome 164 Kolkata 262144 165 Delhi 0 166 SELECT Working_At, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 167 working_at bitorincome 168 XYZ Digital 262144 169 ABC Corp. 65792 170 PQR Soln. 4096 171 drop table if exists t1; 172 CREATE TABLE t1( 173 Employee_Name VARCHAR(100) NOT NULL, 174 Working_At VARCHAR(20) NOT NULL, 175 Work_Location VARCHAR(20) NOT NULL, 176 Joining_Date DATE NOT NULL, 177 Annual_Income INT NOT NULL); 178 INSERT INTO t1 179 VALUES 180 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 181 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 182 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 183 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 184 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 185 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 186 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 187 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 188 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 189 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 190 SELECT Work_Location, BIT_XOR(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 191 work_location bitorincome 192 Kolkata 350624 193 Delhi 912976 194 SELECT Working_At, BIT_XOR(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 195 working_at bitorincome 196 XYZ Digital 94816 197 ABC Corp. 774608 198 PQR Soln. 136256 199 drop table if exists t1; 200 create table t1(a int,b float); 201 insert into t1 values(0,0); 202 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 203 select cos(a),cos(b) from t1; 204 cos(a) cos(b) 205 1.0 1.0 206 1.0 -0.2836910914865273 207 0.15425144988758405 0.9036792973912307 208 -0.4480736161291701 -0.7301529641805058 209 -0.5984600690578581 0.9843819506325049 210 -0.5984600690578581 -0.5984600690578581 211 select cos(a)*cos(b),cos(cos(a)) as c from t1; 212 cos(a) * cos(b) c 213 1.0 0.5403023058681398 214 -0.2836910914865273 0.5403023058681398 215 0.13939384185599057 0.9881268151992377 216 0.32716227898779165 0.9012833416649748 217 -0.5891132901548379 0.8262041463870422 218 0.35815445425673625 0.8262041463870422 219 select distinct a from t1 where cos(a)<=cos(b) order by a desc; 220 a 221 180 222 30 223 0 224 drop table if exists t1; 225 create table t1(a int,b float); 226 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 227 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 228 select cot(a*pi()/180) as cota,cot(b*pi()/180) cotb from t1; 229 invalid argument cot, bad value cot(0) 230 select cot(a*pi()/180)*cot(b*pi()/180) as cotab,cot(cot(a*pi()/180)) as c from t1; 231 invalid argument cot, bad value cot(0) 232 select b from t1 where cot(a*pi()/180)<=cot(b*pi()/180) order by a; 233 invalid argument cot, bad value cot(0) 234 drop table if exists t1; 235 create table t1(a date, b datetime,c varchar(30)); 236 insert into t1 values('20220101','2022-01-01 01:01:01','2022-13-13 01:01:01'); 237 select * from t1; 238 a b c 239 2022-01-01 2022-01-01 01:01:01 2022-13-13 01:01:01 240 drop table if exists t1; 241 create table t1(a date, b datetime,c varchar(30)); 242 insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01'); 243 insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01'); 244 insert into t1 values('2022-01-02','2022-01-02 23:01:01','2022-01-01 23:01:01'); 245 insert into t1 values('2021-12-31','2021-12-30 23:59:59','2021-12-30 23:59:59'); 246 insert into t1 values('2022-06-30','2021-12-30 23:59:59','2021-12-30 23:59:59'); 247 select distinct dayofyear(a) as dya from t1; 248 dya 249 1 250 2 251 365 252 181 253 select * from t1 where dayofyear(a)>120; 254 a b c 255 2021-12-31 2021-12-30 23:59:59 2021-12-30 23:59:59 256 2022-06-30 2021-12-30 23:59:59 2021-12-30 23:59:59 257 select * from t1 where dayofyear(a) between 1 and 184; 258 a b c 259 2022-01-01 2022-01-01 01:01:01 2022-01-01 01:01:01 260 2022-01-01 2022-01-01 01:01:01 2022-01-01 01:01:01 261 2022-01-02 2022-01-02 23:01:01 2022-01-01 23:01:01 262 2022-06-30 2021-12-30 23:59:59 2021-12-30 23:59:59 263 drop table if exists t1; 264 CREATE TABLE t1(a INT,b VARCHAR(100),c CHAR(20)); 265 INSERT INTO t1 266 VALUES 267 (1,'Ananya Majumdar', 'XI'), 268 (2,'Anushka Samanta', 'X'), 269 (3,'Aniket Sharma', 'XI'), 270 (4,'Anik Das', 'X'), 271 (5,'Riya Jain', 'IX'), 272 (6,'Tapan Samanta', 'XI'); 273 select a,endswith(b,'a') from t1; 274 a endswith(b, a) 275 1 0 276 2 1 277 3 1 278 4 0 279 5 0 280 6 1 281 select a,b,c from t1 where endswith(b,'a')=1 and endswith(c,'I')=1; 282 a b c 283 3 Aniket Sharma XI 284 6 Tapan Samanta XI 285 drop table if exists t1; 286 CREATE TABLE t1(Student_id INT,Student_name VARCHAR(100),Student_Class CHAR(20)); 287 INSERT INTO t1 288 VALUES 289 (1,'Ananya Majumdar', 'IX'), 290 (2,'Anushka Samanta', 'X'), 291 (3,'Aniket Sharma', 'XI'), 292 (4,'Anik Das', 'X'), 293 (5,'Riya Jain', 'IX'), 294 (6,'Tapan Samanta', 'X'); 295 SELECT Student_id, Student_name,LPAD(Student_Class, 10, ' _') AS LeftPaddedString FROM t1; 296 student_id student_name leftpaddedstring 297 1 Ananya Majumdar _ _ _ _IX 298 2 Anushka Samanta _ _ _ _ X 299 3 Aniket Sharma _ _ _ _XI 300 4 Anik Das _ _ _ _ X 301 5 Riya Jain _ _ _ _IX 302 6 Tapan Samanta _ _ _ _ X 303 SELECT Student_id, lpad(Student_name,4,'new') AS LeftPaddedString FROM t1; 304 student_id leftpaddedstring 305 1 Anan 306 2 Anus 307 3 Anik 308 4 Anik 309 5 Riya 310 6 Tapa 311 SELECT Student_id, lpad(Student_name,-4,'new') AS LeftPaddedString FROM t1; 312 student_id leftpaddedstring 313 1 null 314 2 null 315 3 null 316 4 null 317 5 null 318 6 null 319 SELECT Student_id, lpad(Student_name,0,'new') AS LeftPaddedString FROM t1; 320 student_id leftpaddedstring 321 1 322 2 323 3 324 4 325 5 326 6 327 drop table if exists t1; 328 CREATE TABLE t1(Student_id INT,Student_name VARCHAR(100),Student_Class CHAR(20)); 329 INSERT INTO t1 330 VALUES 331 (1,'Ananya Majumdar', 'IX'), 332 (2,'Anushka Samanta', 'X'), 333 (3,'Aniket Sharma', 'XI'), 334 (4,'Anik Das', 'X'), 335 (5,'Riya Jain', 'IX'), 336 (6,'Tapan Samanta', 'X'); 337 SELECT Student_id, Student_name,RPAD(Student_Class, 10, ' _') AS LeftPaddedString FROM t1; 338 student_id student_name leftpaddedstring 339 1 Ananya Majumdar IX _ _ _ _ 340 2 Anushka Samanta X _ _ _ _ 341 3 Aniket Sharma XI _ _ _ _ 342 4 Anik Das X _ _ _ _ 343 5 Riya Jain IX _ _ _ _ 344 6 Tapan Samanta X _ _ _ _ 345 SELECT Student_id, rpad(Student_name,4,'new') AS LeftPaddedString FROM t1; 346 student_id leftpaddedstring 347 1 Anan 348 2 Anus 349 3 Anik 350 4 Anik 351 5 Riya 352 6 Tapa 353 SELECT Student_id, rpad(Student_name,-4,'new') AS LeftPaddedString FROM t1; 354 student_id leftpaddedstring 355 1 null 356 2 null 357 3 null 358 4 null 359 5 null 360 6 null 361 SELECT Student_id, rpad(Student_name,0,'new') AS LeftPaddedString FROM t1; 362 student_id leftpaddedstring 363 1 364 2 365 3 366 4 367 5 368 6 369 drop table if exists t1; 370 CREATE TABLE t1 371 ( 372 Employee_name VARCHAR(100) NOT NULL, 373 Joining_Date DATE NOT NULL 374 ); 375 INSERT INTO t1 376 (Employee_name, Joining_Date ) 377 VALUES 378 (' Ananya Majumdar', '2000-01-11'), 379 (' Anushka Samanta', '2002-11-10' ), 380 (' Aniket Sharma ', '2005-06-11' ), 381 (' Anik Das', '2008-01-21' ), 382 (' Riya Jain', '2008-02-01' ), 383 (' Tapan Samanta', '2010-01-11' ), 384 (' Deepak Sharma', '2014-12-01' ), 385 (' Ankana Jana', '2018-08-17'), 386 (' Shreya Ghosh', '2020-09-10') ; 387 SELECT LTRIM( Employee_name) LTrimName,RTRIM(Employee_name) AS RTrimName FROM t1 order by RTrimName desc; 388 ltrimname rtrimname 389 Shreya Ghosh Shreya Ghosh 390 Riya Jain Riya Jain 391 Deepak Sharma Deepak Sharma 392 Anushka Samanta Anushka Samanta 393 Ankana Jana Ankana Jana 394 Aniket Sharma Aniket Sharma 395 Anik Das Anik Das 396 Tapan Samanta Tapan Samanta 397 Ananya Majumdar Ananya Majumdar 398 SELECT LTRIM(RTRIM(Employee_name)) as TrimName from t1 where Employee_name like '%Ani%' order by TrimName asc; 399 trimname 400 Anik Das 401 Aniket Sharma 402 drop table if exists t1; 403 create table t1(a int,b float); 404 insert into t1 values(0,0); 405 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 406 select sin(a),sin(b) from t1; 407 sin(a) sin(b) 408 0.0 0.0 409 0.0 0.9589157234143065 410 -0.9880316240928618 0.4282099105187685 411 0.893996663600558 -0.6832837250355235 412 -0.8011526357338306 -0.17604594647121138 413 -0.8011526357338306 -0.8011526357338306 414 select sin(a)*sin(b),sin(sin(a)) as c from t1; 415 sin(a) * sin(b) c 416 0.0 0.0 417 0.0 0.0 418 -0.4230849333425179 -0.8349443318035336 419 -0.610853370474319 0.77958108276669 420 0.14103967402566783 -0.7181586632423703 421 0.6418455457432638 -0.7181586632423703 422 select distinct a from t1 where sin(a)<=sin(b) order by a desc; 423 a 424 180 425 30 426 0 427 drop table if exists t1; 428 create table t1(a int,b float); 429 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 430 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 431 select sinh(a*pi()/180) as sinha,sinh(b*pi()/180) sinhb from t1; 432 sinha sinhb 433 0.0 0.0 434 -0.2648002276022707 -0.3561979324000117 435 -0.3934773854637668 -0.219900936381245 436 0.0 267.74489404101644 437 0.5478534738880397 451.9789818592585 438 2.3012989023072947 1287.985054197183 439 11.548739357257748 55.65439759941754 440 11.548739357257748 11.548739357257748 441 select sinh(a*pi()/180)*sinh(b*pi()/180) as sinhab,sinh(sinh(a*pi()/180)) as c from t1; 442 sinhab c 443 0.0 0.0 444 0.09432129357098132 -0.26790569019819105 445 0.0865260455083264 -0.40370959509281085 446 0.0 0.0 447 247.61825533597406 0.57567347843079 448 2964.038591412179 4.943508829600678 449 642.7381319608645 51823.146734897804 450 133.37338074187412 51823.146734897804 451 select b from t1 where sinh(a*pi()/180)<=sinh(b*pi()/180) order by a; 452 b 453 -12.5 454 0.0 455 360.0 456 390.0 457 450.0 458 270.0 459 180.0 460 drop table if exists t1; 461 CREATE TABLE t1 462 ( 463 Employee_name VARCHAR(100) NOT NULL, 464 Joining_Date DATE NOT NULL 465 ); 466 INSERT INTO t1 467 (Employee_name, Joining_Date ) 468 VALUES 469 (' Ananya Majumdar', '2000-01-11'), 470 (' Anushka Samanta', '2002-11-10' ), 471 (' Aniket Sharma ', '2005-06-11' ), 472 (' Anik Das', '2008-01-21' ), 473 (' Riya Jain', '2008-02-01' ), 474 (' Tapan Samanta', '2010-01-11' ), 475 (' Deepak Sharma', '2014-12-01' ), 476 (' Ankana Jana', '2018-08-17'), 477 (' Shreya Ghosh', '2020-09-10') ; 478 INSERT INTO t1 479 (Employee_name, Joining_Date ) values(' ','2014-12-01'); 480 select * from t1 where Employee_name=space(5); 481 employee_name joining_date 482 2014-12-01 483 drop table if exists t1; 484 CREATE TABLE t1(a INT,b VARCHAR(100),c CHAR(20)); 485 INSERT INTO t1 486 VALUES 487 (1,'Ananya Majumdar', 'IX'), 488 (2,'Anushka Samanta', 'X'), 489 (3,'Aniket Sharma', 'XI'), 490 (4,'Anik Das', 'X'), 491 (5,'Riya Jain', 'IX'), 492 (6,'Tapan Samanta', 'X'); 493 select a,startswith(b,'An') from t1; 494 a startswith(b, An) 495 1 1 496 2 1 497 3 1 498 4 1 499 5 0 500 6 0 501 select a,b,c from t1 where startswith(b,'An')=1 and startswith(c,'I')=1; 502 a b c 503 1 Ananya Majumdar IX 504 drop table if exists t1; 505 CREATE TABLE t1(PlayerName VARCHAR(100) NOT NULL,RunScored INT NOT NULL,WicketsTaken INT NOT NULL); 506 INSERT INTO t1 VALUES('KL Rahul', 52, 0 ),('Hardik Pandya', 30, 1 ),('Ravindra Jadeja', 18, 2 ),('Washington Sundar', 10, 1),('D Chahar', 11, 2 ), ('Mitchell Starc', 0, 3); 507 SELECT STDDEV_POP(RunScored) as Pop_Standard_Deviation FROM t1; 508 pop_standard_deviation 509 16.876183086099637 510 SELECT STDDEV_POP(WicketsTaken) as Pop_Std_Dev_Wickets FROM t1; 511 pop_std_dev_wickets 512 0.957427107756338 513 drop table if exists t1; 514 create table t1(a int,b float); 515 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 516 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 517 select tan(a*pi()/180) as tana,tan(b*pi()/180) tanb from t1; 518 tana tanb 519 0.0 0.0 520 -0.2679491924311227 -0.36397023426620234 521 -0.4040262258351568 -0.22169466264293988 522 0.0 -2.449293598294703E-16 523 0.5773502691896257 0.5773502691896246 524 1.6331239353195392E16 3.2662478706390785E15 525 -1.2246467991473515E-16 5.443746451065131E15 526 -1.2246467991473515E-16 -1.2246467991473515E-16 527 select tan(a*pi()/180)*tan(b*pi()/180) as tanab,tan(a*pi()/180)+tan(b*pi()/180) as c from t1; 528 tanab c 529 0.0 0.0 530 0.09752553034059545 -0.631919426697325 531 0.08957045783542533 -0.6257208884780967 532 -0.0 -2.449293598294703E-16 533 0.33333333333333265 1.1547005383792504 534 5.334187576227157E31 1.9597487223834472E16 535 -0.6666666666666667 5.443746451065131E15 536 1.4997597826618535E-32 -2.449293598294703E-16 537 select b from t1 where tan(a*pi()/180)<=tan(b*pi()/180) order by a; 538 b 539 -12.5 540 0.0 541 270.0 542 180.0 543 drop table if exists t1; 544 create table t1(a date,b datetime); 545 insert into t1 values("2022-06-01","2022-07-01 00:00:00"); 546 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 547 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 548 select a,weekday(a),b,weekday(b) from t1; 549 a weekday(a) b weekday(b) 550 2022-06-01 2 2022-07-01 00:00:00 4 551 2022-12-31 5 2011-01-31 12:00:00 0 552 2022-06-12 6 2022-07-01 00:00:00 4 553 select * from t1 where weekday(a)>weekday(b); 554 a b 555 2022-12-31 2011-01-31 12:00:00 556 2022-06-12 2022-07-01 00:00:00 557 select * from t1 where weekday(a) between 0 and 4; 558 a b 559 2022-06-01 2022-07-01 00:00:00 560 drop table if exists t1; 561 create table t1(a date,b datetime); 562 insert into t1 values("2022-06-01","2022-07-01 00:00:00"); 563 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 564 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 565 select a,weekday(a),b,weekday(b) from t1; 566 a weekday(a) b weekday(b) 567 2022-06-01 2 2022-07-01 00:00:00 4 568 2022-12-31 5 2011-01-31 12:00:00 0 569 2022-06-12 6 2022-07-01 00:00:00 4 570 select * from t1 where weekday(a)>weekday(b); 571 a b 572 2022-12-31 2011-01-31 12:00:00 573 2022-06-12 2022-07-01 00:00:00 574 select * from t1 where weekday(a) between 0 and 4; 575 a b 576 2022-06-01 2022-07-01 00:00:00 577 drop table if exists t1; 578 create table t1(a date, b datetime); 579 insert into t1 values('2022-01-01','2022-01-01 01:01:01'); 580 insert into t1 values('2022-01-01','2022-01-01 01:01:01'); 581 insert into t1 values('2022-01-02','2022-01-02 23:01:01'); 582 insert into t1 values('2021-12-31','2021-12-30 23:59:59'); 583 insert into t1 values('2022-06-30','2021-12-30 23:59:59'); 584 select date(a),date(b) from t1; 585 date(a) date(b) 586 2022-01-01 2022-01-01 587 2022-01-01 2022-01-01 588 2022-01-02 2022-01-02 589 2021-12-31 2021-12-30 590 2022-06-30 2021-12-30 591 select date(a),date(date(a)) as dda from t1; 592 date(a) dda 593 2022-01-01 2022-01-01 594 2022-01-01 2022-01-01 595 2022-01-02 2022-01-02 596 2021-12-31 2021-12-31 597 2022-06-30 2022-06-30 598 drop table t1; 599 drop table if exists t1; 600 create table t1(a datetime, b timestamp); 601 insert into t1 values("2022-07-01", "2011-01-31 12:00:00"); 602 insert into t1 values("2011-01-31 12:32:11", "1979-10-22"); 603 insert into t1 values(NULL, "2022-08-01 23:10:11"); 604 insert into t1 values("2011-01-31", NULL); 605 insert into t1 values("2022-06-01 14:11:09","2022-07-01 00:00:00"); 606 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 607 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 608 select hour(a),hour(b) from t1; 609 hour(a) hour(b) 610 0 12 611 12 0 612 null 23 613 0 null 614 14 0 615 0 12 616 0 0 617 select * from t1 where hour(a)>hour(b); 618 a b 619 2011-01-31 12:32:11 1979-10-22 00:00:00 620 2022-06-01 14:11:09 2022-07-01 00:00:00 621 select * from t1 where hour(a) between 10 and 16; 622 a b 623 2011-01-31 12:32:11 1979-10-22 00:00:00 624 2022-06-01 14:11:09 2022-07-01 00:00:00 625 select minute(a),minute(b) from t1; 626 minute(a) minute(b) 627 0 0 628 32 0 629 null 10 630 0 null 631 11 0 632 0 0 633 0 0 634 select * from t1 where minute(a)<=minute(b); 635 a b 636 2022-07-01 00:00:00 2011-01-31 12:00:00 637 2022-12-31 00:00:00 2011-01-31 12:00:00 638 2022-06-12 00:00:00 2022-07-01 00:00:00 639 select * from t1 where minute(a) between 10 and 36; 640 a b 641 2011-01-31 12:32:11 1979-10-22 00:00:00 642 2022-06-01 14:11:09 2022-07-01 00:00:00 643 select second(a),second(b) from t1; 644 second(a) second(b) 645 0 0 646 11 0 647 null 11 648 0 null 649 9 0 650 0 0 651 0 0 652 select * from t1 where second(a)>=second(b); 653 a b 654 2022-07-01 00:00:00 2011-01-31 12:00:00 655 2011-01-31 12:32:11 1979-10-22 00:00:00 656 2022-06-01 14:11:09 2022-07-01 00:00:00 657 2022-12-31 00:00:00 2011-01-31 12:00:00 658 2022-06-12 00:00:00 2022-07-01 00:00:00 659 select * from t1 where second(a) between 10 and 36; 660 a b 661 2011-01-31 12:32:11 1979-10-22 00:00:00 662 drop table if exists t1; 663 drop table if exists t1; 664 create table t1(a int, b int); 665 select mo_table_rows(db_name,'t1'),mo_table_size(db_name,'t1') from (select database() as db_name); 666 mo_table_rows(db_name, t1) mo_table_size(db_name, t1) 667 0 0 668 insert into t1 values(1, 2); 669 insert into t1 values(3, 4); 670 select mo_table_rows(db_name,'t1'),mo_table_size(db_name,'t1') from (select database() as db_name); 671 mo_table_rows(db_name, t1) mo_table_size(db_name, t1) 672 2 48 673 drop table if exists t1; 674 drop database if exists test01; 675 create database test01; 676 use test01; 677 create table t(a int, b varchar(10)); 678 insert into t values(1, 'h'), (2, 'b'), (3, 'c'), (4, 'q'), (5, 'd'), (6, 'b'), (7, 's'), (8, 'a'), (9, 'z'), (10, 'm'); 679 select mo_ctl('dn', 'flush', 'test01.t'); 680 mo_ctl(dn, flush, test01.t) 681 {\n "method": "Flush",\n "result": [\n {\n "returnStr": "OK"\n }\n ]\n}\n 682 select mo_table_col_max('test01', 't', 'a'), mo_table_col_min('test01', 't', 'a'); 683 mo_table_col_max(test01, t, a) mo_table_col_min(test01, t, a) 684 10 1 685 drop table t; 686 drop database test01; 687 drop database if exists test01; 688 create database test01; 689 use test01; 690 select trim(' abc '), trim('abc '), trim(' abc'), trim('abc'); 691 trim( abc ) trim(abc ) trim( abc) trim(abc) 692 abc abc abc abc 693 select trim('abc' from ' abc '), trim('abc' from 'abc '), trim('abc' from ' abc'), trim('abc' from 'abc'); 694 trim(abc from abc ) trim(abc from abc ) trim(abc from abc) trim(abc from abc) 695 abc 696 select trim(both from ' abc '), trim(leading from ' abcd'), trim(trailing from ' abc '); 697 trim(both from abc ) trim(leading from abcd) trim(trailing from abc ) 698 abc abcd abc 699 select trim(both 'abc' from ' abc'), trim(leading 'abc' from 'abcd'), trim(trailing 'abc' from 'axabc'); 700 trim(both abc from abc) trim(leading abc from abcd) trim(trailing abc from axabc) 701 d ax 702 select trim('嗷嗷' from '嗷嗷abc嗷嗷'), trim(both '嗷嗷' from '嗷嗷abc嗷嗷'), trim(leading '嗷嗷' from '嗷嗷abcd嗷嗷'), trim(trailing '嗷嗷' from '嗷嗷abc嗷嗷'); 703 trim(嗷嗷 from 嗷嗷abc嗷嗷) trim(both 嗷嗷 from 嗷嗷abc嗷嗷) trim(leading 嗷嗷 from 嗷嗷abcd嗷嗷) trim(trailing 嗷嗷 from 嗷嗷abc嗷嗷) 704 abc abc abcd嗷嗷 嗷嗷abc 705 select trim(null from ' abc '), trim('abc' from null), trim(null from null); 706 trim(null from abc ) trim(abc from null) trim(null from null) 707 null null null 708 drop table if exists t1; 709 create table t1(a varchar(100), b varchar(100)); 710 insert into t1 values('abc', 'abc'); 711 insert into t1 values('啊abc哦', '啊abc哦'); 712 insert into t1 values('啊啊o', 'o'); 713 insert into t1 values('啊啊o', '啊'); 714 insert into t1 values('啊啊o', 'o啊'); 715 select trim(a from b) from t1; 716 trim(a from b) 717 718 719 o 720 啊 721 o啊 722 select trim(both a from b) from t1; 723 trim(both a from b) 724 725 726 o 727 啊 728 o啊 729 select trim(leading a from b) from t1; 730 trim(leading a from b) 731 732 733 o 734 啊 735 o啊 736 select trim(trailing a from b) from t1; 737 trim(trailing a from b) 738 739 740 o 741 啊 742 o啊 743 insert into t1 values(null, 'abc'); 744 select trim(a from b) from t1; 745 trim(a from b) 746 747 748 o 749 啊 750 o啊 751 null 752 select trim('a' from a) from t1; 753 trim(a from a) 754 bc 755 啊abc哦 756 啊啊o 757 啊啊o 758 啊啊o 759 null 760 select trim(null from b) from t1; 761 trim(null from b) 762 null 763 null 764 null 765 null 766 null 767 null 768 select trim('a' from null) from t1; 769 trim(a from null) 770 null 771 null 772 null 773 null 774 null 775 null 776 select trim(null from null) from t1; 777 trim(null from null) 778 null 779 null 780 null 781 null 782 null 783 null 784 drop table t1; 785 drop database test01;