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

     1  -- @suit
     2  -- @case
     3  -- @desc:test for Built-in Function LEFT()
     4  -- @label:bvt
     5  
     6  SELECT LEFT('ABCDEFG', 2);
     7  SELECT LEFT('ABCDEFG', 0);
     8  SELECT LEFT('ABCDEFG', -1);
     9  SELECT LEFT('ABCDEFG', 2+1);
    10  SELECT LEFT('ABCDEFG', ABS(-2));
    11  SELECT LEFT('ABCDEFG', COS(0));
    12  SELECT LEFT('ABCDEFG', LENGTH('KING'));
    13  SELECT LEFT('ABCDEFG', NULL);
    14  SELECT LEFT(NULL, 1);
    15  SELECT LEFT(NULL, 0);
    16  SELECT LEFT(NULL, -1);
    17  
    18  -- String column
    19  DROP TABLE IF EXISTS t;
    20  CREATE TABLE t(
    21      id INT,
    22      str1 VARCHAR(20),
    23      str2 CHAR(20),
    24      PRIMARY KEY (id)
    25  );
    26  INSERT INTO t VALUES (123456, 'anike1001@gmail.com', 'googood'), (123457, 'nitin5438@yahoo.com','hainghing');
    27  SELECT LEFT(id, 3), LEFT(str1, 5), LEFT(str2, 5) FROM t;
    28  SELECT LEFT(id, '1'), LEFT(str1, '1'), LEFT(str2, '1') FROM t;
    29  SELECT LEFT(id, LENGTH(str1)/1-1), LEFT(str1, 1) FROM t;
    30  SELECT LEFT(id, FIND_IN_SET('b','a,b,c,d')), LEFT(str2, EMPTY(str1)) FROM t;
    31  SELECT LEFT(id, '-1'), LEFT(str1, '0') FROM t;
    32  SELECT LEFT(id, '1'+2), LEFT(str1, '1'+'1'), LEFT(str2, '1'+'0') FROM t;
    33  
    34  -- DATE column
    35  DROP TABLE IF EXISTS t;
    36  CREATE TABLE t(
    37      id INT,
    38      d1 DATE,
    39      d2 DATETIME,
    40      d3 TIMESTAMP,
    41      PRIMARY KEY (id)
    42  );
    43  INSERT INTO t VALUES (1, '2020-01-01', '2020-01-01 12:12:12', '2020-02-02 06:06:06.163');
    44  INSERT INTO t VALUES (2, '2021-11-11', '2021-01-11 23:23:23', '2021-12-12 16:16:16.843');
    45  SELECT LEFT(d1, MONTH(d1)), LEFT(d2, DAY(d2)) FROM t;
    46  SELECT LEFT(d2, LENGTH(d2)) FROM t;
    47  SELECT LEFT(d1, SIN(0)+MONTH(d1)) FROM t;
    48  SELECT LEFT(d1, COS(10)+MONTH(d1)) FROM t;
    49  
    50  -- Decimal column
    51  -- @bvt:issue#5511
    52  CREATE TABLE t(
    53      d INT,
    54      d1 BIGINT,
    55      d2 FLOAT,
    56      d3 DOUBLE,
    57      PRIMARY KEY (id)
    58  );
    59  -- @bvt:issue
    60  
    61  DROP TABLE IF EXISTS t;
    62  CREATE TABLE t(
    63      d INT,
    64      d1 BIGINT,
    65      d2 FLOAT,
    66      d3 DOUBLE,
    67      PRIMARY KEY (d)
    68  );
    69  INSERT INTO t VALUES (1,101210131014,50565056.5566,80898089.8899);
    70  INSERT INTO t VALUES (2,46863515648464,9876453.3156153,6486454631564.156153489);
    71  -- @bvt:issue#5513
    72  SELECT LEFT(d1,3), LEFT(d2,4), LEFT(d3,5) FROM t;
    73  SELECT LEFT(d1,LENGTH(d1)), LEFT(d2,LENGTH(d2)) FROM t;
    74  -- @bvt:issue
    75  
    76  -- JOIN
    77  DROP TABLE IF EXISTS t;
    78  DROP TABLE IF EXISTS t1;
    79  CREATE TABLE t(
    80      d INT,
    81      d1 VARCHAR(20),
    82      d2 BIGINT,
    83      PRIMARY KEY (d)
    84  );
    85  CREATE TABLE t1(
    86      d INT,
    87      d1 CHAR(20),
    88      d2 DATE,
    89      PRIMARY KEY (d)
    90  );
    91  INSERT INTO t VALUES (1,'lijklnfdsalj',19290988), (2,'xlziblkfdi',1949100132);
    92  INSERT INTO t VALUES (3,'ixioklakmaria',69456486), (4,'brzilaiusd',6448781575);
    93  INSERT INTO t1 VALUES (1,'usaisagoodnat','1970-01-02'),(2,'chanialfakbjap','1971-11-12');
    94  INSERT INTO t1 VALUES (3,'indiaisashit','1972-09-09'),(4,'xingoporelka','1973-12-07');
    95  SELECT t.d, LEFT(t.d1,FIND_IN_SET('d','a,b,c,d')) FROM t;
    96  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;
    97  SELECT t.d, LEFT(t1.d2, NULL) FROM t JOIN t1 ON t.d = t1.d;
    98  --SELECT t.d, LEFT(t1.d2, 'NULL') FROM t RIGHT JOIN t1 ON t.d = t1.d;
    99  --SELECT t.d, LEFT(t1.d2, 2>1) FROM t JOIN t1 ON t.d = t1.d;
   100  SELECT t.d, LEFT(t1.d2, BIN(1)) FROM t RIGHT JOIN t1 ON t.d = t1.d;
   101  SELECT t.d, LEFT(t1.d2, BIN(2)) FROM t RIGHT JOIN t1 ON t.d = t1.d;
   102  DROP TABLE IF EXISTS t;
   103  DROP TABLE IF EXISTS t1;