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