github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/load_data/load_data_set_null.sql (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  
    10  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set a=nullif(col1,'1');
    11  
    12  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col2=nullif(col3,'1');
    13  
    14  load data infile "$resources/load_data/not_exists.csv" into table t1 fields terminated by ',';
    15  
    16  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col1=nullif(col1,'1');
    17  select * from t1;
    18  
    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  
    22  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col2=nullif(col3,'"1111-11-11"');
    23  select * from t1;
    24  
    25  load data infile "$resources/load_data/set_null_1.csv" into table t1 fields terminated by ',' set col4=nullif(col4,'1');
    26  select * from t1;
    27  
    28  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);
    29  select * from t1;
    30  
    31  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);
    32  select * from t1;
    33  
    34  drop table t1;
    35  
    36  drop table if exists t2;
    37  create table t2(
    38  col1 int primary key auto_increment,
    39  col2 varchar(100)
    40  );
    41  
    42  load data infile "$resources/load_data/set_null_3.csv" into table t2 fields terminated by ',' set col1=nullif(col1,'null');
    43  select * from t2;
    44  delete from t2;
    45  insert into t2 values();
    46  select * from t2;
    47  
    48  -- test load character set
    49  load data infile "$resources/load_data/set_null_3.csv" into table t2 character set utf8 fields terminated by ',' set col1=nullif(col1,'null');
    50  
    51  drop table t2;