Hmm - and further to this..... Strange differences of behaiour between the
last two production versions of MySQL....
sh-2.05b# ln -s mysql-standard-4.0.24-apple-darwin7.7.0-powerpc mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.24-standard-log
mysql> create table wibble3 (
-> test1 double(3,6),
-> test2 double(7,6),
-> test3 double(9,6));
Query OK, 0 rows affected (0.11 sec)
mysql> desc wibble3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| test1 | double(7,6) | YES | | NULL | |
| test2 | double(7,6) | YES | | NULL | |
| test3 | double(9,6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.17 sec)
mysql> insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected (0.08 sec)
mysql> select * from wibble3;
+-----------+-----------+-----------+
| test1 | test2 | test3 |
+-----------+-----------+-----------+
| 50.123456 | 50.123456 | 50.123456 |
+-----------+-----------+-----------+
1 row in set (0.08 sec)
sh-2.05b# ln -s mysql-standard-4.1.10a-apple-darwin7.7.0-powerpc mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.10a-standard-log
mysql> insert into wibble3 values(50.123456,50.123456,50.123456);
Query OK, 1 row affected, 2 warnings (0.11 sec)
mysql> select * from wibble3;
+-----------+-----------+-----------+
| test1 | test2 | test3 |
+-----------+-----------+-----------+
| 50.123456 | 50.123456 | 50.123456 |
| 9.999999 | 9.999999 | 50.123456 |
+-----------+-----------+-----------+
2 rows in set (0.06 sec)
Looks like while MySQL 4.1 was not changing what was stored in the data but
changing what is inserted into new records to match the proper data tye
definitions.
On 19/9/05 17:49, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:
> Thanks; you're absolutely right - doh! It's just amazing that this ever
> worked in MySQL 4.0 and below...
>
> Andrew
>
> -----Original Message-----
> From: Roger Baklund [mailto:[EMAIL PROTECTED]
> Sent: Mon, 19 Sep 2005 12:27
> To: mysql@lists.mysql.com
> Cc: Andrew Braithwaite
> Subject: Re: possible MySQL bug - insert into 'double' column problem
> with mysql 4.1
>
> Andrew Braithwaite wrote:
>> Hi All,
>>
>> I have a strange error when trying to insert into a table with 2
>> 'double' fields. It inserts into the 1st field OK but fills the 2nd
> one
>> with nines. See below for a complete recreate.
> [...]
>> | lon | double(7,6) | YES | MUL | NULL |
> |
>> | lat | double(7,6) | YES | | NULL |
> |
>
> From the manual:
>
> DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
>
> M is the total number of decimal digits and D is the number of digits
> following the decimal point.
>
> <URL: http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html >
>
>> mysql> INSERT INTO table1VALUES
>> (8002,'2a',-0.361080,39.468270,'2005-08-31','ES052',NULL,NULL);
>> Query OK, 1 row affected, 1 warning (0.01 sec)
>>
>> mysql> select * from table1 where id=8002\G
>> *************************** 1. row ***************************
>> id: 8002
>> GeoQuality: 2a
>> lon: -0.361080
>> lat: 9.999999
>
> You have created your columns with a max width of 7, out of which 6 are
> decimals, but you are trying to insert a number with 8 digits.
>
> --
> Roger
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
