github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/load_data/load_data.result (about) 1 drop table if exists t1; 2 create table t1( 3 col1 tinyint, 4 col2 smallint, 5 col3 int, 6 col4 bigint, 7 col5 tinyint unsigned, 8 col6 smallint unsigned, 9 col7 int unsigned, 10 col8 bigint unsigned 11 ); 12 load data infile '$resources/load_data/integer_numbers_1.csv' into table t1; 13 select * from t1; 14 col1 col2 col3 col4 col5 col6 col7 col8 15 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 16 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 17 0 0 0 0 0 0 0 0 18 null null null null null null null null 19 1 null null 0 0 0 null null 20 null null null null null null null null 21 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 22 select * from t1 into outfile '$resources/into_outfile/outfile_integer_numbers_1.csv'; 23 col1 col2 col3 col4 col5 col6 col7 col8 24 delete from t1; 25 load data infile '$resources/into_outfile/outfile_integer_numbers_1.csv' into table t1 ignore 1 lines; 26 select * from t1; 27 col1 col2 col3 col4 col5 col6 col7 col8 28 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 29 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 30 0 0 0 0 0 0 0 0 31 null null null null null null null null 32 1 null null 0 0 0 null null 33 null null null null null null null null 34 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 35 delete from t1; 36 load data infile '$resources/load_data/integer_numbers_2.csv' into table t1 fields terminated by'*'; 37 select * from t1; 38 col1 col2 col3 col4 col5 col6 col7 col8 39 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 40 -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 41 0 0 0 0 0 0 0 0 42 null null null null null null null null 43 1 null null 0 0 0 null null 44 null null null null null null null null 45 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 46 delete from t1; 47 drop table t1; 48 drop table if exists t2; 49 create table t2( 50 col1 char(225), 51 col2 varchar(225), 52 col3 text, 53 col4 varchar(225) 54 ); 55 load data infile '$resources/load_data/char_varchar_1.csv' into table t2; 56 select * from t2; 57 col1 col2 col3 col4 58 a b c d 59 a b c d 60 'a' 'b' 'c' 'd' 61 'a' 'b' 'c' 'd' 62 aa,aa bb,bb cc,cc dd,dd 63 aa, bb, cc, dd, 64 aa,,,aa bb,,,bb cc,,,cc dd,,,dd 65 aa',',,aa bb',',,bb cc',',,cc dd',',,dd 66 aa"aa bb"bb cc"cc dd"dd 67 aa"aa bb"bb cc"cc dd"dd 68 aa"aa bb"bb cc"cc dd"dd 69 aa""aa bb""bb cc""cc dd""dd 70 aa""aa bb""bb cc""cc dd""dd 71 aa",aa bb",bb cc",cc dd",dd 72 aa"",aa bb"",bb cc"",cc dd"",dd 73 74 75 null null null null 76 77 " " " " 78 "" "" "" "" 79 select * from t2 into outfile '$resources/into_outfile/outfile_char_varchar_1.csv'; 80 col1 col2 col3 col4 81 delete from t2; 82 83 load data infile '$resources/into_outfile/outfile_char_varchar_1.csv' into table t2 ignore 1 lines; 84 85 select * from t2; 86 col1 col2 col3 col4 87 a b c d 88 a b c d 89 'a' 'b' 'c' 'd' 90 'a' 'b' 'c' 'd' 91 aa,aa bb,bb cc,cc dd,dd 92 aa, bb, cc, dd, 93 aa,,,aa bb,,,bb cc,,,cc dd,,,dd 94 aa',',,aa bb',',,bb cc',',,cc dd',',,dd 95 aa"aa bb"bb cc"cc dd"dd 96 aa"aa bb"bb cc"cc dd"dd 97 aa"aa bb"bb cc"cc dd"dd 98 aa""aa bb""bb cc""cc dd""dd 99 aa""aa bb""bb cc""cc dd""dd 100 aa",aa bb",bb cc",cc dd",dd 101 aa"",aa bb"",bb cc"",cc dd"",dd 102 103 104 null null null null 105 106 " " " " 107 "" "" "" "" 108 delete from t2; 109 110 load data infile '$resources/load_data/char_varchar_2.csv' into table t2; 111 112 select * from t2; 113 col1 col2 col3 col4 114 aa""aa bb""bb cc""cc dd""dd 115 aa"aa bb"bb cc"cc dd"dd 116 aa"""aa bb"""bb cc"""cc dd"""dd 117 aa""aa bb""bb cc""cc dd""dd 118 aa""""aa bb""""bb cc""""cc dd""""dd 119 aa"""aa bb""bb cc""cc dd""dd 120 delete from t2; 121 122 load data infile '$resources/load_data/char_varchar_3.csv' into table t2; 123 select * from t2; 124 col1 col2 col3 col4 125 aa",aa bb",bb cc",cc dd",dd 126 aa" aa" bb" bb" 127 aa"",aa bb"",bb cc"",cc dd"",dd 128 aa"" aa" bb"",bb cc"",cc 129 delete from t2; 130 load data infile '$resources/load_data/char_varchar_4.csv' into table t2 fields terminated by'|'; 131 select * from t2; 132 col1 col2 col3 col4 133 a b c d 134 a b c d 135 'a' 'b' 'c' 'd' 136 'a' 'b' 'c' 'd' 137 aa|aa bb|bb cc|cc dd|dd 138 aa| bb| cc| dd| 139 aa|||aa bb|||bb cc|||cc dd|||dd 140 aa'|'||aa bb'|'||bb cc'|'||cc dd'|'||dd 141 aa"aa bb"bb cc"cc dd"dd 142 aa"aa bb"bb cc"cc dd"dd 143 aa"aa bb"bb cc"cc dd"dd 144 aa""aa bb""bb cc""cc dd""dd 145 aa""aa bb""bb cc""cc dd""dd 146 aa"|aa bb"|bb cc"|cc dd"|dd 147 aa""|aa bb""|bb cc""|cc dd""|dd 148 149 150 151 " " " " 152 "" "" "" "" 153 delete from t2; 154 load data infile '$resources/load_data/char_varchar_5.csv' into table t2 fields terminated by'?'; 155 select * from t2; 156 col1 col2 col3 col4 157 a b c d 158 a b c d 159 'a' 'b' 'c' 'd' 160 'a' 'b' 'c' 'd' 161 aa?aa bb?bb cc?cc dd?dd 162 aa? bb? cc? dd? 163 aa???aa bb???bb cc???cc dd???dd 164 aa'?'??aa bb'?'??bb cc'?'??cc dd'?'??dd 165 aa"aa bb"bb cc"cc dd"dd 166 aa"aa bb"bb cc"cc dd"dd 167 aa"aa bb"bb cc"cc dd"dd 168 aa""aa bb""bb cc""cc dd""dd 169 aa""aa bb""bb cc""cc dd""dd 170 aa"?aa bb"?bb cc"?cc dd"?dd 171 aa""?aa bb""?bb cc""?cc dd""?dd 172 173 174 175 " " " " 176 "" "" "" "" 177 delete from t2; 178 drop table t2; 179 drop table if exists t3; 180 create table t3( 181 col1 float, 182 col2 double, 183 col3 decimal(5,2), 184 col4 decimal(20,5) 185 ); 186 insert into t3 values (1.3,1.3,1.3,1.3); 187 select * from t3; 188 col1 col2 col3 col4 189 1.3 1.3 1.30 1.30000 190 load data infile '$resources/load_data/float_1.csv' into table t3; 191 192 select * from t3; 193 col1 col2 col3 col4 194 1.3 1.3 1.30 1.30000 195 1.3 1.3 1.30 1.30000 196 delete from t3; 197 load data infile '$resources/load_data/float_2.csv' into table t3; 198 select * from t3; 199 col1 col2 col3 col4 200 1.0 1.0 1.00 1.00000 201 null null null null 202 1.23456 1.23456 1.23 1.23456 203 1.2345679 1.23456789 1.23 1.23457 204 1.0123457 1.0123456789 1.01 1.01235 205 1.0E-10 1.0E-10 0.00 0.00000 206 select * from t3 into outfile '$resources/into_outfile/outfile_float_2.csv'; 207 col1 col2 col3 col4 208 delete from t3; 209 load data infile '$resources/into_outfile/outfile_float_2.csv' into table t3 ignore 1 lines; 210 select * from t3; 211 col1 col2 col3 col4 212 1.0 1.0 1.00 1.00000 213 null null null null 214 1.23456 1.23456 1.23 1.23456 215 1.2345679 1.23456789 1.23 1.23457 216 1.0123457 1.0123456789 1.01 1.01235 217 1.0E-10 1.0E-10 0.00 0.00000 218 delete from t3; 219 load data infile '$resources/load_data/float_3.csv' into table t3; 220 internal error: the input value '1000.001' is invalid Decimal64 type for column 2 221 222 drop table t3; 223 drop table if exists t4; 224 create table t4( 225 col1 date, 226 col2 datetime, 227 col3 timestamp, 228 col4 bool 229 ); 230 set time_zone = 'SYSTEM'; 231 load data infile '$resources/load_data/time_date_1.csv' into table t4; 232 233 select * from t4; 234 col1 col2 col3 col4 235 1000-01-01 0001-01-01 00:00:00 1970-01-01 00:00:01 false 236 9999-12-31 9999-12-31 00:00:00 2038-01-19 00:00:00 true 237 delete from t4; 238 load data infile '$resources/load_data/time_date_2.csv' into table t4; 239 select * from t4; 240 col1 col2 col3 col4 241 1000-01-01 0001-01-01 00:00:00 null false 242 1000-01-01 0001-01-01 00:00:00 null false 243 1000-01-01 0001-01-01 00:00:00 null false 244 1000-01-01 0001-01-01 00:00:00 null false 245 9999-12-31 9999-12-31 00:00:00 null true 246 9999-12-31 9999-12-31 00:00:00 null true 247 9999-12-31 9999-12-31 23:59:59 null true 248 null null null true 249 null null null true 250 null null null false 251 null null null true 252 null null null null 253 null null null null 254 select * from t4 into outfile '$resources/into_outfile/outfile_time_date_2.csv'; 255 col1 col2 col3 col4 256 delete from t4; 257 load data infile '$resources/into_outfile/outfile_time_date_2.csv' into table t4 ignore 1 lines; 258 select * from t4; 259 col1 col2 col3 col4 260 1000-01-01 0001-01-01 00:00:00 null false 261 1000-01-01 0001-01-01 00:00:00 null false 262 1000-01-01 0001-01-01 00:00:00 null false 263 1000-01-01 0001-01-01 00:00:00 null false 264 9999-12-31 9999-12-31 00:00:00 null true 265 9999-12-31 9999-12-31 00:00:00 null true 266 9999-12-31 9999-12-31 23:59:59 null true 267 null null null true 268 null null null true 269 null null null false 270 null null null true 271 null null null null 272 null null null null 273 delete from t4; 274 load data infile '$resources/load_data/time_date_3.csv' into table t4; 275 276 delete from t4; 277 load data infile '$resources/load_data/time_date_4.csv' into table t4 fields terminated by';'; 278 select * from t4; 279 col1 col2 col3 col4 280 1000-01-01 0001-01-01 00:00:00 null false 281 1000-01-01 0001-01-01 00:00:00 null false 282 9999-12-31 9999-12-31 00:00:00 null true 283 9999-12-31 9999-12-31 00:00:00 null true 284 null null null true 285 null null null true 286 null null null false 287 null null null true 288 null null null null 289 null null null null 290 delete from t4; 291 load data infile '$resources/load_data/time_date_5.csv' into table t4; 292 internal error: the input value '9999-12-31 23:59:59.999999' is not Datetime type for column 1 293 294 select * from t4; 295 col1 col2 col3 col4 296 delete from t4; 297 drop table t4; 298 drop table if exists t5; 299 create table t5( 300 col1 int auto_increment primary key, 301 col2 int, 302 col3 int 303 ); 304 insert into t5 values (1,1,1); 305 load data infile '$resources/load_data/auto_increment_1.csv' into table t5; 306 Duplicate entry '1' for key 'col1' 307 select * from t5; 308 col1 col2 col3 309 1 1 1 310 drop table t5; 311 drop table if exists t6; 312 create table t6( 313 col1 int auto_increment primary key, 314 col2 int, 315 col3 int 316 ); 317 load data infile '$resources/load_data/auto_increment_2.csv' into table t6; 318 Duplicate entry '4' for key 'col1' 319 select * from t6; 320 col1 col2 col3 321 drop table t6; 322 323 create table t7( 324 col1 int, 325 col2 int, 326 col3 int 327 ); 328 load data infile '$resources/load_data/auto_increment_2.csv' into table t7 parallel 'true'; 329 select * from t7 order by col1; 330 col1 col2 col3 331 1 1 1 332 2 2 2 333 3 3 3 334 4 4 4 335 4 5 5 336 drop table t7; 337 338 create table t8(a int, b int); 339 load data infile '$resources/load_data/auto_increment_20.csv' into table t7 set col2=nullif(col2, '1'); 340 invalid input: the file does not exist in load flow