List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 19 2005 9:40am
Subject:Re: ALTER TABLE - how to fix truncated data?
View as plain text  
> mysql> insert into dt set a=999.1;
> >> Query OK, 1 row affected (0.00 sec)
> >>
> mysql> alter table dt change a a decimal(2,1);
> >> Query OK, 1 row affected, 1 warning (0.02 sec)
> >> Records: 1  Duplicates: 0  Warnings: 1
> >>
> mysql> show warnings;
> >>
+---------+------+-----------------------------------------------------+
> >> | Level   | Code | Message
|
> >>
+---------+------+-----------------------------------------------------+
> >> | Warning | 1264 | Out of range value adjusted for column 'a' at row 1
|
> >>
+---------+------+-----------------------------------------------------+
>
> > Could be me ... but isn't this a little too late?
>
> > eg: AFTER you have lost your data?
>
> > IMO, it should raise an error UNLESS you force it to truncate the data.
>
> This would contradict the "MySQL design philosophy" (others call it
> simply "gotcha") that the user should know what he's doing and the
> DBMS tries its best to obey.  Consider this (version 4.1.14):


Yeah yeah ... so the MySQL design philosophy is that users
never make mistakes...

Guess they want to sell support contracts, ey?

Seriously, if you give a user enough rope to hang themselves
AND hand them a chair to stand on, better make sure you
have a way to deal with the corpses.

>   CREATE TEMPORARY TABLE t1 (
>     i TINYINT
>   );
>
>   INSERT INTO  t1 VALUES (42);
>
>   SELECT * FROM t1;
>   -- Ok, shows 42
>
>   UPDATE t1 SET i = 4242;
>   -- SHOW WARNINGS;
>
>   SELECT * FROM t1;
>   -- Oops, shows 127

Yeah, total rubbish.

Do this in your application - by accident - and explain your
boss that the totals are wrong or that he had an input value
of "4242" and got "127" in return. Rubbish.

If a value doesn't fit (in the "domain" of "tinyint"), an exception
should be raised. Plain and simple.

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Thread
ALTER TABLE - how to fix truncated data?Jim Seymour17 Oct
  • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko18 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies18 Oct
    • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko18 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Harald Fuchs19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct
    • Re: ALTER TABLE - how to fix truncated data?Jigal van Hemert19 Oct
    • Re: ALTER TABLE - how to fix truncated data?Gleb Paharenko19 Oct
  • Re: ALTER TABLE - how to fix truncated data?Martijn Tonies19 Oct