Import CSV layout detects too small of a INT value (maybe did not scan all the lines?)

sacrophyte's profile image sacrophyte posted 3 years ago in General Permalink

Still using the IMDB public dataset: https://datasets.imdbws.com/title.akas.tsv.gz

The Import CSV layout detecting tool incorrectly used TINYINT for the 'ordering' column:

CREATE TABLE `test`.`title_akas` (
    `titleId` VARCHAR(10) NOT NULL,
    `ordering` TINYINT NOT NULL,
    `title` TEXT NOT NULL,
    `region` VARCHAR(10) NULL,
    `language` VARCHAR(10) NULL,
    `types` VARCHAR(20) NULL,
    `attributes` VARCHAR(70) NULL,
    `isOriginalTitle` VARCHAR(10) NULL
)
;

However, the largest value is 176; SMALLINT would be more appropriate. Just curious, perhaps did the CSV layout detector not scan the entire file? Here are the errors when using TINYINT:

/* Warning (1264): Out of range value for column 'ordering' at row 1121372 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121373 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121375 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121376 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121377 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121378 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121379 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121380 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121381 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121382 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121383 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121384 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121386 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121387 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121388 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121389 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121390 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121391 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121392 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121393 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121394 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121395 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121397 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121398 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121399 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121400 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121401 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121402 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121403 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121404 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121405 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121406 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121408 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121409 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121410 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121411 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121412 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121413 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121414 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121415 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121416 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121417 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121419 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121420 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121421 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121422 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121423 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121424 */
/* Warning (1264): Out of range value for column 'ordering' at row 1121425 */
sacrophyte's profile image sacrophyte posted 3 years ago Permalink

I also have similar issues with a different file (name.basics):

CREATE TABLE `test`.`name_basics` (
    `nconst` VARCHAR(10) NOT NULL,
    `primaryName` VARCHAR(50) NOT NULL,
    `birthYear` VARCHAR(10) NULL,
    `deathYear` VARCHAR(10) NULL,
    `primaryProfession` VARCHAR(70) NULL,
    `knownForTitles` VARCHAR(50) NULL
)
;

/* Warning (1265): Data truncated for column 'knownForTitles' at row 446853 */
/* Warning (1265): Data truncated for column 'primaryName' at row 905069 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1185982 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1528406 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1794468 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1799068 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1878343 */
/* Warning (1265): Data truncated for column 'primaryName' at row 1930640 */
/* Warning (1265): Data truncated for column 'primaryName' at row 2236662 */
/* Warning (1265): Data truncated for column 'primaryName' at row 2282937 */
/* Warning (1265): Data truncated for column 'knownForTitles' at row 2462275 */
/* Warning (1265): Data truncated for column 'primaryName' at row 2483967 */
/* Warning (1265): Data truncated for column 'primaryName' at row 3385082 */
/* Warning (1265): Data truncated for column 'primaryName' at row 3806933 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4061653 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4254818 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4255562 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4255563 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4255564 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4295874 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4364284 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4416916 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4422517 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4423540 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4423843 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4430134 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4486873 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4487955 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4521995 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4568423 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4600718 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4612395 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4645078 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4658586 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4700016 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4714548 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4715774 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4764850 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4780789 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4789373 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4806485 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4897528 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4959433 */
/* Warning (1265): Data truncated for column 'primaryName' at row 4966129 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5029074 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5050590 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5064832 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5066138 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5251243 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5262453 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5286191 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5301114 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5316532 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5322001 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5351759 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5445768 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5465021 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5477399 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5485488 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5520196 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5554598 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5586836 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5630355 */
/* Warning (1265): Data truncated for column 'primaryName' at row 5642365 */

Please login to leave a reply, or register at first.