Hi Doug,
> I spent the last couple of hours replacing the mainboard in another
box I have here and doing some testing. It now has the
> same board/cpu as this Win98SE machine. It is running Linux-Mandrake
8.0 with MySQL 3.23.38 which came with the LM distro.
>
> I then created a table and populated it with fifteen, or so, rows of
data.
>
> The SELECT statement that started this thread :
>
> SELECT number,round(number,digits) FROM testme
> was then tested as well as a few variations. I also retested on this
Win98 machine.
>
> +--------------------------------+--------------------------------+
> | Win98SE / AMD cpu | Mandrake 8.0 / AMD cpu |
> +--------------------------------+--------------------------------+
> | MySQL 3.23.43 | MySQL 3.23.38 |
> +--------------------------------+--------------------------------+
> | ROUND(cola,colb) works | ROUND(cola,colb) works |
> | Rounding is DOWN from 0.500 | Rounding is UP from 0.500 |
> | F.P. format is inherited by | F.P. format is inherited by |
> | calculated column | calculated column |
> +--------------------------------+--------------------------------+
>
> I don't believe I made it clear that ROUND(cola,colb) always worked
for me on the Windows machine, too.
I know it's getting late, so perhaps I'm not following you sufficiently
closely. What does "inherited" mean? Also how do you define "worked".
You said you didn't have the original post. Herewith Richard's first
description:-
-----
create table testme
-> (number double (5,4),digits tinyint(3));
Query OK, 0 rows affected (0.08 sec)
mysql> explain testme;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | double(6,4) | YES | | NULL | |
| digits | tinyint(3) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
mysql> insert into testme values (100.4235,3),(85.4,1);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from testme;
+----------+--------+
| number | digits |
+----------+--------+
| 100.4235 | 3 |
| 85.4000 | 1 |
+----------+--------+
2 rows in set (0.08 sec)
mysql> select round(number,digits) from testme;
+----------------------+
| round(number,digits) |
+----------------------+
| 100.4240 |
| 85.4000 |
+----------------------+
2 rows in set (0.08 sec)
-----
What he/we were expecting to see was 100.4235 rounded to 3
decimal-digits and displayed as 100.424; plus 85.4000 rounded to 1
decimal-digit and displayed as 85.4.
For a moment, let's ignore the correctness (or otherwise) of the Clib
rounding algorithm, and just accept that 100.4235 should become 100.424.
Then the rounding appears to have happened. What hasn't happened is that
we expect that the result will be presented with no trailing zeros!
One theory suggests that the number of decimal-digits has been 'set' by
the original row-value in the number column. Another that the use of row
data (digits) in round() has been disregarded for presentation purposes,
although it has been used for the actual rounding process. Finally that
the digits value of the first row was evaluated (3) and then applied to
the every row of the query - instead of being evaluated row-by-row.
Some of this would be shown if we added a third row with (eg)
number=85.1234 and digits=1.
Would you please try the experiment with Richard's data (and that last
extension, if you have time), and see what you can get back from the two
boxes - and show it to be Win and/or *nix and/or version dependent?
Will look forward to hearing from you,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
