github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_string_length.test (about)

     1  #SELECT
     2  #0.5 MO的BINARY类型暂不支持
     3  #CREATE TABLE t1 (s CHAR(8) BINARY);
     4  #INSERT INTO t1 VALUES ('test');
     5  #SELECT LENGTH(s) FROM t1;
     6  #ALTER TABLE t1 MODIFY s CHAR(10) BINARY;
     7  #SELECT LENGTH(s) FROM t1;
     8  #DROP TABLE t1;
     9  
    10  CREATE TABLE t1 (s CHAR(8));
    11  INSERT INTO t1 VALUES ('test');
    12  SELECT LENGTH(s) FROM t1;
    13  CREATE TABLE t2 (s CHAR(10));
    14  INSERT INTO t2 VALUES ('1234589002');
    15  SELECT LENGTH(s) FROM t2;
    16  drop table t1;
    17  drop table t2;
    18  
    19  #DATE TYPE
    20  
    21  CREATE TABLE t1 (s char(8), b CHAR(100), c VARCHAR(255), d int, e float, f datetime);
    22  INSERT INTO t1 VALUES ('test', "hhhhhhh", "12jknfjniosdjcoijcpowef", 1234, 0.14123123, "2012-02-08 12:03:23");
    23  SELECT LENGTH(s), length(b), length(c), length(d), length(e), length(f) FROM t1;
    24  DROP TABLE t1;
    25  
    26  
    27  #INSERT, DISTINCT, ON CONDITION
    28  CREATE TABLE t1 (t1_fld1 int, b varchar(255));
    29  CREATE TABLE t2 (t2_fld1 int, b varchar(255));
    30  CREATE TABLE t3 (t3_fld1 int, b varchar(255));
    31  INSERT INTO t1 select LENGTH(space(300)), "abcdefg";
    32  INSERT INTO t1 select LENGTH(space(300)), "123124141";
    33  INSERT INTO t2 select LENGTH(space(65680)), "abcdefg";
    34  INSERT INTO t2 select LENGTH(space(65680)), "1238193";
    35  INSERT INTO t3 select LENGTH(space(65680)), "1238193";
    36  INSERT INTO t3 select LENGTH(space(16777300)),"123124141";
    37  INSERT INTO t3 select LENGTH(space(16777300)),"123asdq";
    38  SELECT DISTINCT * from t1;
    39  SELECT t1.t1_fld1, t2.t2_fld1, t3.t3_fld1 FROM t1 JOIN t2 JOIN t3 ON (length(t1.b) = length(t2.b));
    40  drop table t1;
    41  drop table t2;
    42  drop table t3;
    43  
    44  #EXTREME VALUE，中文
    45  select length('\n\t\r\b\0\_\%\\');
    46  
    47  select length(12314124);
    48  select length(0.14123124124);
    49  
    50  select length('1039214-#**$&#@*#(*($*');
    51  select length(NULL);
    52  select length("中文");
    53  
    54  
    55  
    56  
    57  #HAVING，嵌套
    58  CREATE TABLE t1 (a varchar(10));
    59  INSERT INTO t1 VALUES ('abc'), ('xyz');
    60  SELECT a, CONCAT_WS(",",a,' ',a) AS c FROM t1
    61  HAVING LENGTH(REVERSE(c)) >0;
    62  DROP TABLE t1;
    63  
    64  #嵌套
    65  select length(space(1)) as a;
    66  select length(space(1024*1024*1024)) as a;
    67  select length(space(1024*1024)) as a;
    68  
    69  select length(space(1024*1024*1024)) as a;
    70  
    71  #WHERE, 算术运算
    72  drop table if exists t1;
    73  create table t1(a INT,  b date);
    74  insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23");
    75  select * from t1 where length(b)-8>0;
    76  drop table t1;
    77  
    78  #BLOB
    79  DROP table if exists t1;
    80  CREATE TABLE t1 (s BLOB);
    81  INSERT INTO t1 VALUES ('test');
    82  SELECT LENGTH(s) FROM t1;
    83  CREATE TABLE t2 (s BLOB);
    84  INSERT INTO t2 VALUES ('1234589002');
    85  SELECT LENGTH(s) FROM t2;
    86  drop table t1;
    87  drop table t2;