github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/load_data/load_data_set_null.result (about)

     1  drop table if exists t1;
     2  create table t1(
     3  col1 int,
     4  col2 varchar(100),
     5  col3 float,
     6  col4 date,
     7  col5 text
     8  );
     9  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set a=nullif(col1,'1');
    10  invalid input: the nullif func first param must equal to colName
    11  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col2=nullif(col3,'1');
    12  invalid input: the nullif func first param must equal to colName
    13  load data infile "$resources/load_data/not_exists.csv" into table t1 fields terminated by ',';
    14  invalid input: the file does not exist in load flow
    15  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col1=nullif(col1,'1');
    16  select * from t1;
    17  col1    col2    col3    col4    col5
    18  null    1    1.0    1111-11-11    1
    19  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col2=nullif(col2,'1');
    20  select * from t1;
    21  col1    col2    col3    col4    col5
    22  null    1    1.0    1111-11-11    1
    23  1    null    1.0    1111-11-11    1
    24  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col2=nullif(col3,'"1111-11-11"');
    25  invalid input: the nullif func first param must equal to colName
    26  select * from t1;
    27  col1    col2    col3    col4    col5
    28  null    1    1.0    1111-11-11    1
    29  1    null    1.0    1111-11-11    1
    30  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col4=nullif(col4,'1');
    31  select * from t1;
    32  col1    col2    col3    col4    col5
    33  null    1    1.0    1111-11-11    1
    34  1    null    1.0    1111-11-11    1
    35  1    1    1.0    1111-11-11    1
    36  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col1=nullif(col1,1), col2=nullif(col2,1),col3=nullif(col3,1) ,col4=nullif(col4,'1111-11-11'),col5=nullif(col5,1);
    37  select * from t1;
    38  col1    col2    col3    col4    col5
    39  null    1    1.0    1111-11-11    1
    40  1    null    1.0    1111-11-11    1
    41  1    1    1.0    1111-11-11    1
    42  null    null    null    null    null
    43  load data infile "$resources/load_data/set_null_2.csv" into table t1 fields terminated by ',' set col1=nullif(col1,1), col2=nullif(col2,2),col3=nullif(col3,2) ,col4=nullif(col4,'1111-04-11'),col5=nullif(col5,5);
    44  select * from t1;
    45  col1    col2    col3    col4    col5
    46  null    1    1.0    1111-11-11    1
    47  1    null    1.0    1111-11-11    1
    48  1    1    1.0    1111-11-11    1
    49  null    null    null    null    null
    50  null    1    1.0    1111-11-11    1
    51  2    null    null    1111-02-11    2
    52  3    3    3.0    1111-03-11    3
    53  4    4    4.0    null    4
    54  5    5    5.0    1111-05-11    null
    55  6    6    6.0    1111-06-11    6
    56  7    7    7.0    1111-07-11    7
    57  8    8    8.0    1111-08-11    8
    58  9    9    9.0    1111-09-11    9
    59  10    10    10.0    1111-10-11    10
    60  drop table t1;
    61  drop table if exists t2;
    62  create table t2(
    63  col1 int primary key auto_increment,
    64  col2 varchar(100)
    65  );
    66  load data infile "$resources/load_data/set_null_3.csv" into table t2 fields terminated by ',' set col1=nullif(col1,'null');
    67  select * from t2;
    68  col1    col2
    69  1    a
    70  2    b
    71  3    c
    72  4    d
    73  5    e
    74  6    f
    75  7    g
    76  8    null
    77  delete from t2;
    78  insert into t2 values();
    79  select * from t2;
    80  col1    col2
    81  9    null
    82  load data infile "$resources/load_data/set_null_3.csv" into table t2 character set utf8 fields terminated by ',' set col1=nullif(col1,'null');
    83  drop table t2;