github.com/matrixorigin/matrixone@v0.7.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  -- @bvt:issue#5510
    33  SELECT LEFT(id, '1'+2), LEFT(str1, '1'+'1'), LEFT(str2, '1'+'0') FROM t;
    34  -- @bvt:issue
    35  
    36  -- DATE column
    37  DROP TABLE IF EXISTS t;
    38  CREATE TABLE t(
    39      id INT,
    40      d1 DATE,
    41      d2 DATETIME,
    42      d3 TIMESTAMP,
    43      PRIMARY KEY (id)
    44  );
    45  INSERT INTO t VALUES (1, '2020-01-01', '2020-01-01 12:12:12', '2020-02-02 06:06:06.163');
    46  INSERT INTO t VALUES (2, '2021-11-11', '2021-01-11 23:23:23', '2021-12-12 16:16:16.843');
    47  SELECT LEFT(d1, MONTH(d1)), LEFT(d2, DAY(d2)) FROM t;
    48  SELECT LEFT(d2, LENGTH(d2)) FROM t;
    49  SELECT LEFT(d1, SIN(0)+MONTH(d1)) FROM t;
    50  SELECT LEFT(d1, COS(10)+MONTH(d1)) FROM t;
    51  
    52  -- Decimal column
    53  -- @bvt:issue#5511
    54  CREATE TABLE t(
    55      d INT,
    56      d1 BIGINT,
    57      d2 FLOAT,
    58      d3 DOUBLE,
    59      PRIMARY KEY (id)
    60  );
    61  -- @bvt:issue
    62  
    63  DROP TABLE IF EXISTS t;
    64  CREATE TABLE t(
    65      d INT,
    66      d1 BIGINT,
    67      d2 FLOAT,
    68      d3 DOUBLE,
    69      PRIMARY KEY (d)
    70  );
    71  INSERT INTO t VALUES (1,101210131014,50565056.5566,80898089.8899);
    72  INSERT INTO t VALUES (2,46863515648464,9876453.3156153,6486454631564.156153489);
    73  -- @bvt:issue#5513
    74  SELECT LEFT(d1,3), LEFT(d2,4), LEFT(d3,5) FROM t;
    75  SELECT LEFT(d1,LENGTH(d1)), LEFT(d2,LENGTH(d2)) FROM t;
    76  -- @bvt:issue
    77  
    78  -- JOIN
    79  DROP TABLE IF EXISTS t;
    80  DROP TABLE IF EXISTS t1;
    81  CREATE TABLE t(
    82      d INT,
    83      d1 VARCHAR(20),
    84      d2 BIGINT,
    85      PRIMARY KEY (d)
    86  );
    87  CREATE TABLE t1(
    88      d INT,
    89      d1 CHAR(20),
    90      d2 DATE,
    91      PRIMARY KEY (d)
    92  );
    93  INSERT INTO t VALUES (1,'lijklnfdsalj',19290988), (2,'xlziblkfdi',1949100132);
    94  INSERT INTO t VALUES (3,'ixioklakmaria',69456486), (4,'brzilaiusd',6448781575);
    95  INSERT INTO t1 VALUES (1,'usaisagoodnat','1970-01-02'),(2,'chanialfakbjap','1971-11-12');
    96  INSERT INTO t1 VALUES (3,'indiaisashit','1972-09-09'),(4,'xingoporelka','1973-12-07');
    97  SELECT t.d, LEFT(t.d1,FIND_IN_SET('d','a,b,c,d')) FROM t;
    98  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;
    99  SELECT t.d, LEFT(t1.d2, NULL) FROM t JOIN t1 ON t.d = t1.d;
   100  --SELECT t.d, LEFT(t1.d2, 'NULL') FROM t RIGHT JOIN t1 ON t.d = t1.d;
   101  --SELECT t.d, LEFT(t1.d2, 2>1) FROM t JOIN t1 ON t.d = t1.d;
   102  SELECT t.d, LEFT(t1.d2, BIN(1)) FROM t RIGHT JOIN t1 ON t.d = t1.d;
   103  SELECT t.d, LEFT(t1.d2, BIN(2)) FROM t RIGHT JOIN t1 ON t.d = t1.d;
   104  DROP TABLE IF EXISTS t;
   105  DROP TABLE IF EXISTS t1;