> 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
