Jeff,

I believe the old behavior was erroneous.

http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html " FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(p) (where p is in the range from 0 to 24) stands for a single-precision floating-point number. "

Define the table like mathis2 below, with less decimal places specified in D. I guess D means the number of decimal places after the period '.' in the representation.

Best regards,

Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php

Order MySQL support from http://www.mysql.com/support/index.html

........................

heikki@hundin:~/mysql-4.1/client> ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.4-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from mathis; +----------------+----------------+----------------+ | a | b | c | +----------------+----------------+----------------+ | -999.250000000 | -345.269989014 | 456.779998779 | | -9.250000000 | -5.269999981 | 6.780000210 | | 999.250000000 | 345.269989014 | -456.779998779 | +----------------+----------------+----------------+ 3 rows in set (0.11 sec)

mysql> insert into mathis values (-9999.25, -3451.27, 4560.78); Query OK, 1 row affected, 3 warnings (0.01 sec)

mysql> show warnings; +---------+------+------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------+ | Warning | 1264 | Data truncated; out of range for column 'a' at row 1 | | Warning | 1264 | Data truncated; out of range for column 'b' at row 1 | | Warning | 1264 | Data truncated; out of range for column 'c' at row 1 | +---------+------+------------------------------------------------------+ 3 rows in set (0.00 sec)

mysql> select * from mathis; +----------------+----------------+----------------+ | a | b | c | +----------------+----------------+----------------+ | -999.250000000 | -345.269989014 | 456.779998779 | | -100.000000000 | -100.000000000 | 100.000000000 | | -9.250000000 | -5.269999981 | 6.780000210 | | 999.250000000 | 345.269989014 | -456.779998779 | +----------------+----------------+----------------+ 4 rows in set (0.00 sec)

mysql> alter table mathis type = myisam; Query OK, 4 rows affected, 1 warning (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0

mysql> show warnings; +---------+------+----------------------------------------------------------

---- ------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------

---- ------------+ | Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engi ne' instead | +---------+------+----------------------------------------------------------

---- ------------+ 1 row in set (0.00 sec)

mysql> insert into mathis values (-9999.25, -3451.27, 4560.78); ERROR 1062 (23000): Duplicate entry '-100.000000000' for key 1 mysql> insert into mathis values (-9999.25, -3451.27, 4560.78); ERROR 1062 (23000): Duplicate entry '-100.000000000' for key 1 mysql> mysql> mysql> create table mathis2(a float(11,2) not null primary key, b float(11,2), c float(11,2), index(b)) type = innodb; Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> mysql> mysql> insert into mathis2 values (-9999.25, -3451.27, 4560.78); Query OK, 1 row affected (0.00 sec)

mysql> select * from mathis2; +----------+----------+---------+ | a | b | c | +----------+----------+---------+ | -9999.25 | -3451.27 | 4560.78 | +----------+----------+---------+ 1 row in set (0.00 sec)

mysql>