« MySQL Pop Quiz #8 | Main | MySQL DATE_FORMAT() and PHP DATE() Tool »
By Carsten | February 29, 2008
This poser comes from Diego Madina.
Using a fairly recent MySQL 5.0 distribution, we see that…
mysql> SELECT 1.1E0, 2.2E0, 3.3E0;
+-------+-------+-------+
| 1.1E0 | 2.2E0 | 3.3E0 |
+-------+-------+-------+
| 1.1 | 2.2 | 3.3 |
+-------+-------+-------+
1 row in set (0.00 sec)
and…
mysql> select 1.1+2.2=3.3;
+-------------+
| 1.1+2.2=3.3 |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
But…
mysql> select 1.1E0+2.2E0=3.3E0;
+-------------------+
| 1.1E0+2.2E0=3.3E0 |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
Why?
Click here for the answer
Answer: In MySQL versions prior to 5.0.3, any number that looked like a non-integer would be converted to a floating-point value before any computations were made. Starting with MySQL 5.0.3, exact-value decimal numbers (such as “1.1″ and “2.2″) are treated as DECIMAL numbers and a new precision-math library is used to compute mathematical expressions. Numbers such as “1.1E0″ are not considered to be exact-value decimals, and as such computations on these numbers are made using floating-point routines. Floating-point calculations have an inherent degree of rounding errors, and indeed 1.1+2.2 does not equal 3.3 (but rather 3.3000000715256) when calculated using standard floating-point routines. On all computer equipment made since the 1980s, handling of floating-point numbers is done according to the IEEE 754 standard. You can read about that on Wikipedia here, but if you have access to it, you really should read up on the excellent Appendix B of Andrew Tannenbaum’s Structured Computer Organization (us
; uk
).
If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting. More
Pop Quiz entries.
Topics: MySQL, Pop Quiz |
Comments
March 26th, 2008 at 1:26 pm
[...] Traducido con permiso expreso de Mysql Test Quiz 9 Original [...]
March 31st, 2008 at 9:09 am
[...] ???????????? ? ?????????? Carsten Pedersen. ???????? [...]
April 9th, 2008 at 7:52 am
[...] Traducido con permiso expreso de Mysql Test Quiz 9 Original [...]
April 9th, 2008 at 7:52 am
[...] Traducido con permiso expreso de Mysql Test Quiz 9 Original [...]