github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/insert/insert_with_function.sql (about)

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for insert data with function
     5  -- @label:bvt
     6  CREATE TABLE char_test(
     7  	str1 CHAR(50),
     8  	str2 VARCHAR(50)
     9  );
    10  INSERT INTO char_test(str1, str2) VALUES('ABCED', 'MESSI');
    11  
    12  -- CONCAT_WS()
    13  INSERT INTO char_test(str1) VALUES(CONCAT_WS(' ', ' I have ', '  a dream'));
    14  INSERT INTO char_test(str1) VALUES(CONCAT_WS(',', ',I have ', ',a dream'));
    15  INSERT INTO char_test(str1) VALUES(CONCAT_WS('x', ' I have x', 'x a dream'));
    16  INSERT INTO char_test(str1) SELECT CONCAT_WS('.', 'MESSI is a ', '. aloha bumda.');
    17  DELETE FROM char_test;
    18  
    19  --FIND_IN_SET()
    20  INSERT INTO char_test(str2) VALUES(FIND_IN_SET('a', 'b,d,c,a'));
    21  INSERT INTO char_test(str2) VALUES(FIND_IN_SET('a', 'if i were a boy'));
    22  INSERT INTO char_test(str2) VALUES(FIND_IN_SET('A', CONCAT_WS(',', 'The english Union',', have a king.')));
    23  SELECT * FROM char_test;
    24  DELETE FROM char_test;
    25  
    26  -- OCT()
    27  INSERT INTO char_test(str2) VALUES(OCT(FIND_IN_SET('a', 'b,c,d,e,f,g,h,a')));
    28  INSERT INTO char_test(str2) VALUES(OCT(NULL));
    29  INSERT INTO char_test(str2) VALUES(OCT(LENGTH('JFKLD;AJKFLD;AJFKDL;ASJFKDLSA;FJDKSAL;FJDKSAL;FJDKA;')));
    30  SELECT * FROM char_test;
    31  DELETE FROM char_test;
    32  
    33  -- EMPTY()
    34  INSERT INTO char_test(str1) VALUES(SPACE(100));
    35  INSERT INTO char_test(str1) VALUES('RONALDOSHOOTGOAL');
    36  INSERT INTO char_test(str2) VALUES(EMPTY(""));
    37  INSERT INTO char_test(str2) VALUES(EMPTY(null));
    38  INSERT INTO char_test(str2) VALUES(EMPTY(CONCAT_WS(' ', 'ABCDE','JKFL;JDK','FDAFD')));
    39  INSERT INTO char_test(str2) VALUES(EMPTY(SPACE(100)));
    40  INSERT INTO char_test(str2) VALUES(EMPTY(OCT(4564123156)));
    41  select * from char_test;
    42  
    43  -- 日期时间类型
    44  DROP TABLE IF EXISTS date_test;
    45  CREATE TABLE date_test(
    46  	d2 DATE,
    47  	d3 DATETIME,
    48  	d4 TIMESTAMP,
    49  	d5 BIGINT
    50  );
    51  INSERT INTO date_test(d2,d3) VALUES('2022-08-07', '2018-09-13 13:45:13');
    52  INSERT INTO date_test(d2,d3) VALUES('2015-11-07', '2013-09-14 13:45:13');
    53  INSERT INTO date_test(d2,d3) VALUES('2013-08-07', '2006-05-23 13:23:13');
    54  INSERT INTO date_test(d2,d3) VALUES('2011-08-07', '2018-07-08 23:59:59');
    55  INSERT INTO date_test(d5) SELECT UNIX_TIMESTAMP("2021-02-29");
    56  INSERT INTO date_test(d3) VALUES(DATE_ADD('2008-13-26 23:59:59', NULL));
    57  SELECT * FROM date_test;
    58  DELETE FROM date_test;
    59  
    60  
    61  -- 数字类型
    62  CREATE TABLE math_test(
    63  	tiny TINYINT,
    64  	small SMALLINT,
    65  	int_test INT,
    66  	big BIGINT,
    67  	tiny_un TINYINT UNSIGNED,
    68  	small_un SMALLINT UNSIGNED,
    69  	int_un INT UNSIGNED,
    70  	big_un BIGINT UNSIGNED,
    71  	float_32 FLOAT,
    72  	float_64 DOUBLE
    73  );
    74  
    75  INSERT INTO math_test(tiny,small,int_test,big) VALUES(32, 2432, 54354, 543324324);
    76  INSERT INTO math_test(tiny_un, small_un, int_un) VALUES(127, 32768, 2147483648);
    77  SELECT * FROM math_test;
    78  
    79  -- @bvt:issue#4952
    80  --INSERT INTO math_test(tiny_un, small_un, int_un) VALUES(-128, -32768, -2147483648);
    81  -- @bvt:issue
    82  
    83  -- 小数位数
    84  DROP TABLE IF EXISTS test1;
    85  CREATE TABLE test1(
    86  	num1 FLOAT(6,2),
    87  	num2 DOUBLE(6,2),
    88  	num3 DECIMAL(6,2)
    89  );
    90  INSERT INTO test1(num1, num2, num3) VALUES(12.21, 43.43, 999.899);
    91  INSERT INTO test1 VALUES(3.1415, 3.1415, 3.1415);
    92  SELECT * FROM test1;
    93  
    94  DROP TABLE char_test;
    95  DROP TABLE date_test;
    96  DROP TABLE math_test;
    97  DROP TABLE test1;