github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/builtin.sql (about) 1 -- @suite 2 -- @setup 3 drop table if exists t1; 4 create table t1(a int,b int); 5 insert into t1 values(5,-2),(10,3),(100,0),(4,3),(6,-3); 6 -- @case 7 -- @desc:test for func power() select 8 -- @label:bvt 9 select power(a,b) from t1; 10 11 -- @case 12 -- @desc:test for func power() as where filter and order by power() 13 -- @label:level0 14 select power(a,2) as a1, power(b,2) as b1 from t1 where power(a,2) > power(b,2) order by a1 asc; 15 16 -- @suite 17 -- @setup 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 23 -- @case 24 -- @desc:test for func month() select 25 select month(a),month(b) from t1; 26 select * from t1 where month(a)>month(b); 27 select * from t1 where month(a) between 1 and 6; 28 -- @teardown 29 drop table if exists t1; 30 31 -- @suite 32 -- @setup 33 create table t1(a varchar(12),c char(30)); 34 35 insert into t1 values('sdfad ','2022-02-02 22:22:22'); 36 insert into t1 values(' sdfad ','2022-02-02 22:22:22'); 37 insert into t1 values('adsf sdfad','2022-02-02 22:22:22'); 38 insert into t1 values(' sdfad','2022-02-02 22:22:22'); 39 -- @case 40 -- @desc:test for func reverse() select 41 -- @separator:table 42 select reverse(a),reverse(c) from t1; 43 -- @separator:table 44 select a from t1 where reverse(a) like 'daf%'; 45 -- @separator:table 46 select reverse(a) reversea,reverse(reverse(a)) normala from t1; 47 48 49 -- @suite 50 -- @setup 51 drop table if exists t1; 52 create table t1(a int,b float); 53 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 54 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 55 -- @case 56 -- @desc:test for func acos() select 57 select acos(a*pi()/180) as acosa,acos(b*pi()/180) acosb from t1; 58 select acos(a*pi()/180)*acos(b*pi()/180) as acosab,acos(acos(a*pi()/180)) as c from t1; 59 select b from t1 where acos(a*pi()/180)<=acos(b*pi()/180) order by a; 60 61 -- @suite 62 -- @setup 63 drop table if exists t1; 64 create table t1(a int,b float); 65 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 66 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 67 68 -- @case 69 -- @desc:test for func atan() select 70 select atan(a*pi()/180) as atana,atan(b*pi()/180) atanb from t1; 71 select atan(a*pi()/180)*atan(b*pi()/180) as atanab,atan(atan(a*pi()/180)) as c from t1; 72 select b from t1 where atan(a*pi()/180)<=atan(b*pi()/180) order by a; 73 74 -- @suite 75 -- @setup 76 drop table if exists t1; 77 CREATE TABLE t1( 78 Employee_Name VARCHAR(100) NOT NULL, 79 Working_At VARCHAR(20) NOT NULL, 80 Work_Location VARCHAR(20) NOT NULL, 81 Joining_Date DATE NOT NULL, 82 Annual_Income INT NOT NULL); 83 INSERT INTO t1 84 VALUES 85 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 86 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 87 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 88 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 89 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 90 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 91 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 92 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 93 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 94 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 95 96 -- @case 97 -- @desc:test for func BIT_AND() select 98 SELECT Working_At, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 99 SELECT Work_Location, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 100 101 -- @suite 102 -- @setup 103 drop table if exists t1; 104 CREATE TABLE t1( 105 Employee_Name VARCHAR(100) NOT NULL, 106 Working_At VARCHAR(20) NOT NULL, 107 Work_Location VARCHAR(20) NOT NULL, 108 Joining_Date DATE NOT NULL, 109 Annual_Income INT NOT NULL); 110 INSERT INTO t1 111 VALUES 112 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 113 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 114 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 115 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 116 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 117 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 118 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 119 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 120 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 121 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 122 123 -- @case 124 -- @desc:test for func BIT_AND() select 125 SELECT Work_Location, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 126 SELECT Working_At, BIT_AND(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 127 128 -- @suite 129 -- @setup 130 drop table if exists t1; 131 CREATE TABLE t1( 132 Employee_Name VARCHAR(100) NOT NULL, 133 Working_At VARCHAR(20) NOT NULL, 134 Work_Location VARCHAR(20) NOT NULL, 135 Joining_Date DATE NOT NULL, 136 Annual_Income INT NOT NULL); 137 INSERT INTO t1 138 VALUES 139 ('Amit Khan', 'XYZ Digital', 'Kolkata', '2019-10-06', 350000), 140 ('Shreetama Pal', 'ABC Corp.', 'Kolkata', '2018-12-16', 500000), 141 ('Aniket Sharma', 'PQR Soln.', 'Delhi', '2020-01-11', 300000), 142 ('Maitree Jana', 'XYZ Digital', 'Kolkata', '2019-05-01', 400000), 143 ('Priyanka Ojha', 'ABC Corp.', 'Delhi', '2019-02-13', 350000), 144 ('Sayani Mitra', 'XYZ Digital', 'Kolkata', '2019-09-15', 320000), 145 ('Nitin Dey', 'PQR Soln.', 'Delhi', '2019-10-06', 250000), 146 ('Sujata Samanta', 'PQR Soln.', 'Kolkata', '2020-10-06', 350000), 147 ('Sudip Majhi', 'ABC Corp.', 'Delhi', '2018-10-30', 600000), 148 ('Sanjoy Kohli', 'XYZ Digital', 'Delhi', '2019-04-18', 450000); 149 150 -- @case 151 -- @desc:test for func BIT_XOR() select 152 SELECT Work_Location, BIT_XOR(Annual_Income) AS BITORINCOME FROM t1 Group By Work_Location; 153 SELECT Working_At, BIT_XOR(Annual_Income) AS BITORINCOME FROM t1 group by Working_At; 154 155 -- @suite 156 -- @setup 157 drop table if exists t1; 158 create table t1(a int,b float); 159 insert into t1 values(0,0); 160 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 161 -- @case 162 -- @desc:test for func cos() select 163 select cos(a),cos(b) from t1; 164 select cos(a)*cos(b),cos(cos(a)) as c from t1; 165 select distinct a from t1 where cos(a)<=cos(b) order by a desc; 166 167 -- @suite 168 -- @setup 169 drop table if exists t1; 170 create table t1(a int,b float); 171 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 172 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 173 -- @case 174 -- @desc:test for func cot() select 175 select cot(a*pi()/180) as cota,cot(b*pi()/180) cotb from t1; 176 select cot(a*pi()/180)*cot(b*pi()/180) as cotab,cot(cot(a*pi()/180)) as c from t1; 177 select b from t1 where cot(a*pi()/180)<=cot(b*pi()/180) order by a; 178 179 drop table if exists t1; 180 create table t1(a date, b datetime,c varchar(30)); 181 insert into t1 values('20220101','2022-01-01 01:01:01','2022-13-13 01:01:01'); 182 select * from t1; 183 184 185 -- @suite 186 -- @setup 187 drop table if exists t1; 188 create table t1(a date, b datetime,c varchar(30)); 189 insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01'); 190 insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01'); 191 insert into t1 values('2022-01-02','2022-01-02 23:01:01','2022-01-01 23:01:01'); 192 insert into t1 values('2021-12-31','2021-12-30 23:59:59','2021-12-30 23:59:59'); 193 insert into t1 values('2022-06-30','2021-12-30 23:59:59','2021-12-30 23:59:59'); 194 195 -- @case 196 -- @desc:test for func dayofyear() select 197 select distinct dayofyear(a) as dya from t1; 198 select * from t1 where dayofyear(a)>120; 199 select * from t1 where dayofyear(a) between 1 and 184; 200 201 -- @suite 202 -- @setup 203 drop table if exists t1; 204 CREATE TABLE t1(a INT,b VARCHAR(100),c CHAR(20)); 205 INSERT INTO t1 206 VALUES 207 (1,'Ananya Majumdar', 'XI'), 208 (2,'Anushka Samanta', 'X'), 209 (3,'Aniket Sharma', 'XI'), 210 (4,'Anik Das', 'X'), 211 (5,'Riya Jain', 'IX'), 212 (6,'Tapan Samanta', 'XI'); 213 214 -- @case 215 -- @desc:test for func endswith() select 216 select a,endswith(b,'a') from t1; 217 select a,b,c from t1 where endswith(b,'a') and endswith(c,'I'); 218 219 -- @suite 220 -- @setup 221 drop table if exists t1; 222 CREATE TABLE t1(Student_id INT,Student_name VARCHAR(100),Student_Class CHAR(20)); 223 INSERT INTO t1 224 VALUES 225 (1,'Ananya Majumdar', 'IX'), 226 (2,'Anushka Samanta', 'X'), 227 (3,'Aniket Sharma', 'XI'), 228 (4,'Anik Das', 'X'), 229 (5,'Riya Jain', 'IX'), 230 (6,'Tapan Samanta', 'X'); 231 232 -- @case 233 -- @desc:test for func LPAD() select 234 SELECT Student_id, Student_name,LPAD(Student_Class, 10, ' _') AS LeftPaddedString FROM t1; 235 SELECT Student_id, lpad(Student_name,4,'new') AS LeftPaddedString FROM t1; 236 SELECT Student_id, lpad(Student_name,-4,'new') AS LeftPaddedString FROM t1; 237 SELECT Student_id, lpad(Student_name,0,'new') AS LeftPaddedString FROM t1; 238 239 -- @suite 240 -- @setup 241 drop table if exists t1; 242 CREATE TABLE t1(Student_id INT,Student_name VARCHAR(100),Student_Class CHAR(20)); 243 INSERT INTO t1 244 VALUES 245 (1,'Ananya Majumdar', 'IX'), 246 (2,'Anushka Samanta', 'X'), 247 (3,'Aniket Sharma', 'XI'), 248 (4,'Anik Das', 'X'), 249 (5,'Riya Jain', 'IX'), 250 (6,'Tapan Samanta', 'X'); 251 252 -- @case 253 -- @desc:test for func rpad() select 254 SELECT Student_id, Student_name,RPAD(Student_Class, 10, ' _') AS LeftPaddedString FROM t1; 255 SELECT Student_id, rpad(Student_name,4,'new') AS LeftPaddedString FROM t1; 256 SELECT Student_id, rpad(Student_name,-4,'new') AS LeftPaddedString FROM t1; 257 SELECT Student_id, rpad(Student_name,0,'new') AS LeftPaddedString FROM t1; 258 259 -- @suite 260 -- @setup 261 drop table if exists t1; 262 CREATE TABLE t1 263 ( 264 Employee_name VARCHAR(100) NOT NULL, 265 Joining_Date DATE NOT NULL 266 ); 267 INSERT INTO t1 268 (Employee_name, Joining_Date ) 269 VALUES 270 (' Ananya Majumdar', '2000-01-11'), 271 (' Anushka Samanta', '2002-11-10' ), 272 (' Aniket Sharma ', '2005-06-11' ), 273 (' Anik Das', '2008-01-21' ), 274 (' Riya Jain', '2008-02-01' ), 275 (' Tapan Samanta', '2010-01-11' ), 276 (' Deepak Sharma', '2014-12-01' ), 277 (' Ankana Jana', '2018-08-17'), 278 (' Shreya Ghosh', '2020-09-10') ; 279 280 -- @case 281 -- @desc:test for func LTRIM() select 282 -- @sortkey:1 283 -- @separator:table 284 SELECT LTRIM( Employee_name) LTrimName,RTRIM(Employee_name) AS RTrimName FROM t1 order by RTrimName desc; 285 SELECT LTRIM(RTRIM(Employee_name)) as TrimName from t1 where Employee_name like '%Ani%' order by TrimName asc; 286 drop table if exists t1; 287 create table t1(a int,b float); 288 insert into t1 values(0,0); 289 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 290 select sin(a),sin(b) from t1; 291 select sin(a)*sin(b),sin(sin(a)) as c from t1; 292 select distinct a from t1 where sin(a)<=sin(b) order by a desc; 293 294 -- @suite 295 -- @setup 296 drop table if exists t1; 297 create table t1(a int,b float); 298 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 299 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 300 301 -- @case 302 -- @desc:test for func sinh() sinh() select 303 select sinh(a*pi()/180) as sinha,sinh(b*pi()/180) sinhb from t1; 304 select sinh(a*pi()/180)*sinh(b*pi()/180) as sinhab,sinh(sinh(a*pi()/180)) as c from t1; 305 select b from t1 where sinh(a*pi()/180)<=sinh(b*pi()/180) order by a; 306 307 -- @suite 308 -- @setup 309 drop table if exists t1; 310 CREATE TABLE t1 311 ( 312 Employee_name VARCHAR(100) NOT NULL, 313 Joining_Date DATE NOT NULL 314 ); 315 INSERT INTO t1 316 (Employee_name, Joining_Date ) 317 VALUES 318 (' Ananya Majumdar', '2000-01-11'), 319 (' Anushka Samanta', '2002-11-10' ), 320 (' Aniket Sharma ', '2005-06-11' ), 321 (' Anik Das', '2008-01-21' ), 322 (' Riya Jain', '2008-02-01' ), 323 (' Tapan Samanta', '2010-01-11' ), 324 (' Deepak Sharma', '2014-12-01' ), 325 (' Ankana Jana', '2018-08-17'), 326 (' Shreya Ghosh', '2020-09-10') ; 327 INSERT INTO t1 328 (Employee_name, Joining_Date ) values(' ','2014-12-01'); 329 -- @separator:table 330 select * from t1 where Employee_name=space(5); 331 drop table if exists t1; 332 CREATE TABLE t1(a INT,b VARCHAR(100),c CHAR(20)); 333 INSERT INTO t1 334 VALUES 335 (1,'Ananya Majumdar', 'IX'), 336 (2,'Anushka Samanta', 'X'), 337 (3,'Aniket Sharma', 'XI'), 338 (4,'Anik Das', 'X'), 339 (5,'Riya Jain', 'IX'), 340 (6,'Tapan Samanta', 'X'); 341 342 343 select a,startswith(b,'An') from t1; 344 select a,b,c from t1 where startswith(b,'An') and startswith(c,'I'); 345 346 -- @suite 347 -- @setup 348 drop table if exists t1; 349 CREATE TABLE t1(PlayerName VARCHAR(100) NOT NULL,RunScored INT NOT NULL,WicketsTaken INT NOT NULL); 350 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); 351 -- @case 352 -- @desc:test for func STDDEV_POP() select 353 SELECT STDDEV_POP(RunScored) as Pop_Standard_Deviation FROM t1; 354 SELECT STDDEV_POP(WicketsTaken) as Pop_Std_Dev_Wickets FROM t1; 355 356 -- @suite 357 -- @setup 358 drop table if exists t1; 359 create table t1(a int,b float); 360 insert into t1 values(0,0),(-15,-20),(-22,-12.5); 361 insert into t1 values(0,360),(30,390),(90,450),(180,270),(180,180); 362 -- @case 363 -- @desc:test for func tan() select 364 select tan(a*pi()/180) as tana,tan(b*pi()/180) tanb from t1; 365 366 select tan(a*pi()/180)*tan(b*pi()/180) as tanab,tan(a*pi()/180)+tan(b*pi()/180) as c from t1; 367 368 select b from t1 where tan(a*pi()/180)<=tan(b*pi()/180) order by a; 369 370 -- @suite 371 -- @setup 372 drop table if exists t1; 373 create table t1(a date,b datetime); 374 insert into t1 values("2022-06-01","2022-07-01 00:00:00"); 375 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 376 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 377 378 -- @case 379 -- @desc:test for func weekday() select 380 select a,weekday(a),b,weekday(b) from t1; 381 select * from t1 where weekday(a)>weekday(b); 382 select * from t1 where weekday(a) between 0 and 4; 383 384 -- @suite 385 -- @setup 386 drop table if exists t1; 387 create table t1(a date,b datetime); 388 insert into t1 values("2022-06-01","2022-07-01 00:00:00"); 389 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 390 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 391 -- @case 392 -- @desc:test for func weekday() select 393 select a,weekday(a),b,weekday(b) from t1; 394 select * from t1 where weekday(a)>weekday(b); 395 select * from t1 where weekday(a) between 0 and 4; 396 397 -- @suite 398 -- @setup 399 drop table if exists t1; 400 create table t1(a date, b datetime); 401 insert into t1 values('2022-01-01','2022-01-01 01:01:01'); 402 insert into t1 values('2022-01-01','2022-01-01 01:01:01'); 403 insert into t1 values('2022-01-02','2022-01-02 23:01:01'); 404 insert into t1 values('2021-12-31','2021-12-30 23:59:59'); 405 insert into t1 values('2022-06-30','2021-12-30 23:59:59'); 406 407 -- @case 408 -- @desc:test for func date() select 409 select date(a),date(b) from t1; 410 select date(a),date(date(a)) as dda from t1; 411 412 drop table t1; 413 414 -- @suite 415 -- @setup 416 drop table if exists t1; 417 create table t1(a datetime, b timestamp); 418 insert into t1 values("2022-07-01", "2011-01-31 12:00:00"); 419 insert into t1 values("2011-01-31 12:32:11", "1979-10-22"); 420 insert into t1 values(NULL, "2022-08-01 23:10:11"); 421 insert into t1 values("2011-01-31", NULL); 422 insert into t1 values("2022-06-01 14:11:09","2022-07-01 00:00:00"); 423 insert into t1 values("2022-12-31","2011-01-31 12:00:00"); 424 insert into t1 values("2022-06-12","2022-07-01 00:00:00"); 425 426 -- @case 427 -- @desc:test for func hour() select 428 select hour(a),hour(b) from t1; 429 select * from t1 where hour(a)>hour(b); 430 select * from t1 where hour(a) between 10 and 16; 431 432 -- @case 433 -- @desc:test for func minute() select 434 select minute(a),minute(b) from t1; 435 select * from t1 where minute(a)<=minute(b); 436 select * from t1 where minute(a) between 10 and 36; 437 438 -- @case 439 -- @desc:test for func second() select 440 select second(a),second(b) from t1; 441 select * from t1 where second(a)>=second(b); 442 select * from t1 where second(a) between 10 and 36; 443 444 -- @teardown 445 drop table if exists t1; 446 447 -- @suite 448 -- @setup 449 drop table if exists t1; 450 create table t1(a int, b int); 451 select mo_table_rows(db_name,'t1'),mo_table_size(db_name,'t1') from (select database() as db_name); 452 insert into t1 values(1, 2); 453 insert into t1 values(3, 4); 454 select mo_table_rows(db_name,'t1'),mo_table_size(db_name,'t1') from (select database() as db_name); 455 456 457 -- @teardown 458 drop table if exists t1; 459 460 drop database if exists test01; 461 create database test01; 462 use test01; 463 create table t(a int, b varchar(10)); 464 insert into t values(1, 'h'), (2, 'b'), (3, 'c'), (4, 'q'), (5, 'd'), (6, 'b'), (7, 's'), (8, 'a'), (9, 'z'), (10, 'm'); 465 -- @separator:table 466 select mo_ctl('dn', 'flush', 'test01.t'); 467 select mo_table_col_max('test01', 't', 'a'), mo_table_col_min('test01', 't', 'a'); 468 drop table t; 469 drop database test01; 470 471 drop database if exists test01; 472 create database test01; 473 use test01; 474 select trim(' abc '), trim('abc '), trim(' abc'), trim('abc'); 475 select trim('abc' from ' abc '), trim('abc' from 'abc '), trim('abc' from ' abc'), trim('abc' from 'abc'); 476 select trim(both from ' abc '), trim(leading from ' abcd'), trim(trailing from ' abc '); 477 select trim(both 'abc' from ' abc'), trim(leading 'abc' from 'abcd'), trim(trailing 'abc' from 'axabc'); 478 select trim('嗷嗷' from '嗷嗷abc嗷嗷'), trim(both '嗷嗷' from '嗷嗷abc嗷嗷'), trim(leading '嗷嗷' from '嗷嗷abcd嗷嗷'), trim(trailing '嗷嗷' from '嗷嗷abc嗷嗷'); 479 select trim(null from ' abc '), trim('abc' from null), trim(null from null); 480 481 drop table if exists t1; 482 create table t1(a varchar(100), b varchar(100)); 483 insert into t1 values('abc', 'abc'); 484 insert into t1 values('啊abc哦', '啊abc哦'); 485 insert into t1 values('啊啊o', 'o'); 486 insert into t1 values('啊啊o', '啊'); 487 insert into t1 values('啊啊o', 'o啊'); 488 select trim(a from b) from t1; 489 select trim(both a from b) from t1; 490 select trim(leading a from b) from t1; 491 select trim(trailing a from b) from t1; 492 insert into t1 values(null, 'abc'); 493 select trim(a from b) from t1; 494 select trim('a' from a) from t1; 495 select trim(null from b) from t1; 496 select trim('a' from null) from t1; 497 select trim(null from null) from t1; 498 drop table t1; 499 drop database test01;