List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 21 1999 11:20am
Subject:Re: IMPORT BUG!?
View as plain text  
At 5:09 AM -0500 7/21/99, Para-dox wrote:
>Import file qtest.txt contains:
>
>133575,70625,26400.00,"O"
>123456,70626,76400.12,"A"
>
>
>table setup is:
>CREATE TABLE QTEST (
>INVNUM INT,
>DATE INT,
>AMOUNT DECIMAL(7,2),
>TYPE CHAR(10));
>
>import command:
>
>LOAD DATA LOCAL INFILE 'qtest.txt' INTO TABLE QTEST FIELDS TERMINATED BY ','
>OPTIONALLY ENCLOSED BY '"';
>
>Results:
>+--------+-------+---------+------+
>| INVNUM | DATE  | AMOUNT  | TYPE |
>+--------+-------+---------+------+
>| 133575 | 70625 | 9999.99 | O    |
>| 123456 | 70626 | 9999.99 | A    |
>+--------+-------+---------+------+
>
>Why hath the amount forsaken me?

Your type is DECIMAL(7,2), and the range that can be represented with
that type is -999.99 (7 characters) to 9999.99 (7 characters).  When
MySQL encounters an out-of-range value, it clips it to the appropriate
endpoint of the range and stores the result.  That's what you're seeing.

Tables created in MySQL 3.23 and up will see a range of -99999.99 to
999999.99 with DECIMAL(7,2).

--
Paul DuBois, paul@stripped
Thread
IMPORT BUG!?Para-dox21 Jul
  • Re: IMPORT BUG!?Tobias Auchter21 Jul
  • Re: IMPORT BUG!?Paul DuBois21 Jul
    • Re: IMPORT BUG!?Para-dox21 Jul
    • Re: IMPORT BUG!?Para-dox21 Jul
  • Re: IMPORT BUG!?Para-dox21 Jul
  • Re: IMPORT BUG!?Joel Palenychka21 Jul
    • Re: IMPORT BUG!?Vivek Khera21 Jul
Re: IMPORT BUG!?Kevin Smith21 Jul
Re: IMPORT BUG!?Para-dox21 Jul