github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_left.sql (about) 1 -- @suit 2 -- @case 3 -- @test function LEFT 4 -- @label:bvt 5 6 -- constant test 7 select left('abcde', 3) from dual; 8 select left('abcde', 0) from dual; 9 select left('abcde', 10) from dual; 10 select left('abcde', -1) from dual; 11 select left('abcde', null) from dual; 12 select left(null, 3) from dual; 13 select left(null, null) from dual; 14 select left('foobarbar', 5) from dual; 15 select left('qwerty', 1.2) from dual; 16 select left('qwerty', 1.5) from dual; 17 select left('qwerty', 1.8) from dual; 18 select left("是都方式快递费",3) from dual; 19 select left("あいうえお",3) from dual; 20 select left("あいうえお ",3) from dual; 21 select left("あいうえお ",3) from dual; 22 select left("あいうえお ",3) from dual; 23 select left("龔龖龗龞龡",3) from dual; 24 select left("龔龖龗龞龡 ",3) from dual; 25 select left("龔龖龗龞龡 ",3) from dual; 26 select left("龔龖龗龞龡 ",3) from dual; 27 28 -- @suite 29 -- @setup 30 drop table if exists t1; 31 32 CREATE TABLE t1 (str VARCHAR(100) NOT NULL, len INT); 33 insert into t1 values('abcdefghijklmn',3); 34 insert into t1 values(' ABCDEFGH123456', 3); 35 insert into t1 values('ABCDEF GHIJKLMN', 20); 36 insert into t1 values('ABCDEFGHijklmn ', -1); 37 insert into t1 values('ABCDEFGH123456', -35627164); 38 insert into t1 values('', 3); 39 40 -- @case 41 -- String test 42 select left(str, len) from t1; 43 SELECT * from t1 where left(str, cos(0) + 3) = 'ABC' and len > 3; 44 select left(str, 3) from t1; 45 select left('sdfsdfsdfsdf', len) from t1; 46 select left(NULL, TAN(45)) FROM t1; 47 select left('str', COS(0) + TAN(45)) from t1 where len between 6 AND 21; 48 SELECT left(str, -2) from t1 where len%3 = 1; 49 drop table t1; 50 51 -- @suite 52 -- @setup 53 DROP TABLE IF EXISTS t; 54 55 CREATE table t(age INT, name CHAR(20), address VARCHAR(30)); 56 INSERT INTO t VALUES(20,'ejifwvewv','shanghaishi1032long'); 57 INSERT INTO t VALUES(30,'zhangzianjd','minhangqulongminglu'); 58 INSERT INTO t VALUES(3627832,'hcdusanjfds','xuhuiqudadao'); 59 INSERT INTO t VALUES(3782,'ehuwqhd3283&*^','ehiw3232$'); 60 INSERT INTO t VALUES(42,'','nkej32'); 61 INSERT INTO t VALUES(-2281928939,'wlll',''); 62 63 -- 异常：数值超过所表示范围 64 INSERT INTO t VALUES(-2281928939,'wlll',''); 65 66 67 -- @case 68 -- String test 69 SELECT left(age, NULL) from t; 70 SELECT left(age, 0) from t; 71 SELECT left(age, 2),left(name,5),left(address,10) from t; 72 SELECT left(age,'1'),left(name,'2'), left(address, '3') from t; 73 SELECT left(age, COS(0)),left(name, sin(90) + 1),left(address, TAN(45) + 3) from t; 74 SELECT left(age, length(name) / 2),left(name,1) from t; 75 SELECT left(name, 3) from t where age >= 20; 76 SELECT left(age, -4) from t where LENGTH(address) >= 20; 77 SELECT left(age, 2) from t where name LIKE 'hcdusanjfds'; 78 SELECT left(age, 2), address from t where name LIKE '%vewv'; 79 SELECT address from t where left(name, 3) = 'eji'; 80 81 -- @suite 82 -- @setup 83 DROP TABLE IF EXISTS t; 84 85 CREATE TABLE t(id INT,dd1 DATE, dd2 DATETIME, dd3 TIMESTAMP, PRIMARY KEY (id)); 86 INSERT INTO t VALUES (1, '2020-01-01', '2020-01-01 12:12:12', '2020-02-02 06:06:06.163'); 87 INSERT INTO t VALUES (2, '2021-11-11', '2021-01-11 23:23:23', '2021-12-12 16:16:16.843'); 88 INSERT INTO t VALUES (3, '2002-11-11', '2002-01-11 23:23:23', '2002-12-12 16:16:16.843'); 89 90 91 -- @case 92 -- data type test 93 SELECT left(dd1, length(dd2)) FROM t; 94 SELECT left(dd1, TAN(45) + 6) FROM t; 95 SELECT left(dd1,COS(10) + 1) FROM t; 96 SELECT left(dd1,NULL) FROM t; 97 SELECT left(NULL, -2) FROM t; 98 SELECT left(dd3, 4) FROM t WHERE dd1 LIKE '0001-01-01'; 99 SELECT * from t WHERE left(dd1, 2) = 20; 100 SELECT * from t WHERE MONTH(dd1) = 11 AND left(dd1, 4) = 2021; 101 102 103 -- @suite 104 -- @setup 105 DROP TABLE IF EXISTS t; 106 107 CREATE TABLE t(id INT,d1 BIGINT,d2 FLOAT,d3 DOUBLE,PRIMARY KEY (id)); 108 INSERT INTO t VALUES(1,12345678977,4679.45,-46576898.09877); 109 INSERT INTO t VALUES(2,4251382834,-456.785,32913023.3213); 110 INSERT INTO t VALUES(3,-46382749832,0,456215.454); 111 INSERT INTO t VALUES(4,0,8.121,0); 112 INSERT INTO t VALUES(5,-329323809293,0,0); 113 INSERT INTO t VALUES(6,47832745,4672493280324.37644342323242,-1.8976931348623157E+308); 114 INSERT INTO t VALUES(7,47832745,4.402823466351E+38,666.666); 115 116 117 -- 异常：数值超过所表示范围 118 INSERT INTO t VALUES(6,47832745,4672493280324.37644342323242,-1.8976931348623157E+308); 119 INSERT INTO t VALUES(7,47832745,4.402823466351E+38,666.666); 120 121 122 -- @case 123 -- @floating point types test 124 SELECT left(d1,abs(-5)) from t; 125 SELECT left(d2, LENGTH(d1) - 5) from t; 126 SELECT left(d1, 3),left(d2, 6) from t ORDER by d1; 127 SELECT * from t where ABS(d1) > 200000; 128 129 130 -- @suite 131 -- @setup 132 DROP TABLE IF EXISTS t; 133 DROP TABLE IF EXISTS t1; 134 135 CREATE TABLE t(d INT,d1 VARCHAR(20), d2 BIGINT,PRIMARY KEY (d)); 136 CREATE TABLE t1( d INT,d1 CHAR(20),d2 DATE,PRIMARY KEY (d)); 137 INSERT INTO t VALUES (1,'lijklnfdsalj',19290988), (2,'xlziblkfdi',1949100132); 138 INSERT INTO t VALUES (3,'ixioklakmaria',69456486), (4,'brzilaiusd',6448781575); 139 INSERT INTO t1 VALUES (1,'usaisagoodnat','1970-01-02'),(2,'chanialfakbjap','1971-11-12'); 140 INSERT INTO t1 VALUES (3,'indiaisashit','1972-09-09'),(4,'xingoporelka','1973-12-07'); 141 142 -- @case 143 -- @join and function test 144 SELECT t.d, LEFT(t.d1, abs(-4)) FROM t; 145 SELECT t.d, LEFT(t.d2, FIND_IN_SET('d','a,b,c,d')), LEFT(t1.d1, ABS(-3)+1) FROM t,t1 WHERE t.d = t1.d; 146 SELECT t.d, LEFT(t1.d2, NULL) FROM t JOIN t1 ON t.d = t1.d; 147 SELECT t.d,left(t1.d2, abs(-1)+1),left(t.d2, cos(0)+3) from t join t1 on t.d=t1.d; 148 SELECT t.d,left(t.d2, find_in_set('e','a,b,c,d,e')),left(t1.d1, 20%3)from t right join t1 on t.d=t1.d; 149 SELECT t.d,left(t.d1, find_in_set('d','a,b,c,d,e')),left(t1.d2, 20%3)from t right join t1 on t.d=t1.d; 150 151 DROP TABLE IF EXISTS t; 152 DROP TABLE IF EXISTS t1; 153