Esta sección proporciona algunos ejemplos que muestran cómo la matemática precisa mejora los resultados de consultas en MySQL 5 comparados con versiones anteriores.
Ejemplo 1. Los números se usan con su valor exacto tal y como se da cuando es posible.
Antes de MySQL 5.0.3, los números tratados como valores en coma flotante producen valores inexactos:
mysql> SELECT .1 + .2 = .3; +--------------+ | .1 + .2 = .3 | +--------------+ | 0 | +--------------+
Desde MySQL 5.0.3, los números se usan tal y como se dan cuando es posible:
mysql> SELECT .1 + .2 = .3; +--------------+ | .1 + .2 = .3 | +--------------+ | 1 | +--------------+
Sin embargo, para valores en coma flotante, todavía ocurre la inexactitud:
mysql> SELECT .1E0 + .2E0 = .3E0; +--------------------+ | .1E0 + .2E0 = .3E0 | +--------------------+ | 0 | +--------------------+
Otra forma de ver la diferencia en el tratamiento de valores aproximados y exactos es añadir un pequeño número en una suma muchas veces. Considere el siguiente procedimiento aproximado, que añade .0001 a una variable 1000 veces.
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE d DECIMAL(10,4) DEFAULT 0;
DECLARE f FLOAT DEFAULT 0;
WHILE i < 10000 DO
SET d = d + .0001;
SET f = f + .0001E0;
SET i = i + 1;
END WHILE;
SELECT d, f;
END;
La suma de ambos d y f
lógicamente debe ser 1, pero eso es cierto sólo para cálculos
decimales. El cálculo de coma flotante introduce pequeños
errores:
+--------+------------------+ | d | f | +--------+------------------+ | 1.0000 | 0.99999999999991 | +--------+------------------+
Ejemplo 2. La multiplicación se
hace con la escala requerida por el estándar SQL. Esto es, para
dos números X1 y
X2 con escala
S1 y S2, la
escala del resultado es S1 +
S2.
Antes de MySQL 5.0.3, esto es lo que ocurre:
mysql> SELECT .01 * .01; +-----------+ | .01 * .01 | +-----------+ | 0.00 | +-----------+
El valor mostrado es incorrecto. El valor se calcula correctamente en este caso, pero no se muestra en la escala requerida. Para comprobar que el valor calculado realmente es .0001, pruebe:
mysql> SELECT .01 * .01 + .0000; +-------------------+ | .01 * .01 + .0000 | +-------------------+ | 0.0001 | +-------------------+
Desde MySQL 5.0.3, la escala mostrada es correcta:
mysql> SELECT .01 * .01; +-----------+ | .01 * .01 | +-----------+ | 0.0001 | +-----------+
Ejemplo 3. El comportamiento del redondeo está bien definido.
Antes de MySQL 5.0.3, el comportamiento para el redondeo (por
ejemplo con la función ROUND() ) depende de la
implementación de la biblioteca C subyacente. Esto provoca
inconsistencias entre plataformas. Por ejemplo, puede obtener un
valor distinto en Windows y en Linux, o un valor distinto en
máquinas x86 y PowerPc.
Desde MySQL 5.0.3, el redondeo se realiza así:
El redondeo para columnas DECIMAL y de valor
exacto usa la regla de "redondeo hacia arriba". Los valoeres con
una parte fraccional de .5 o mayor se redondean al entero más
cercano y más lejano al cero, como se muestra aquí:
mysql> SELECT ROUND(2.5), ROUND(-2.5); +------------+-------------+ | ROUND(2.5) | ROUND(-2.5) | +------------+-------------+ | 3 | -3 | +------------+-------------+
El redondeo de valores en coma flotante todavía usa la biblioteca C, que en muchos sistemas usa la regla "redondeo al número par más cercano". Los valores con cualquier parte fraccional se redondean al entero par más cercano:
mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0); +--------------+---------------+ | ROUND(2.5E0) | ROUND(-2.5E0) | +--------------+---------------+ | 2 | -2 | +--------------+---------------+
Ejemplo 4. Para inserciones en tablas, un valor demasiado grande provoca un desbordamiento y un error, no se trunca a un valor legal. (Esto requiere modo estricto.)
Antes de MySQL 5.0.2, se truncaba a un valor legal:
mysql> CREATE TABLE t (i TINYINT); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SET i = 128; Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT i FROM t; +------+ | i | +------+ | 127 | +------+ 1 row in set (0.00 sec)
Desde MySQL 5.0.2, ocurre un desbordamiento si el modo estricto está activado:
mysql> CREATE TABLE t (i TINYINT); Query OK, 0 rows affected (0.01 sec) mysql> SET sql_mode='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.10 sec) mysql> INSERT INTO t SET i = 128; ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1 mysql> SELECT i FROM t; Empty set (0.00 sec)
Ejemplo 5. Para inserciones en
tablas, la división por cero causa un error, no un resultado
NULL. (Esto requiere modo estricto y
ERROR_FOR_DIVISION_BY_ZERO.)
Antes de MySQL 5.0.2, la división por cero tiene un resultado de
NULL:
mysql> CREATE TABLE t (i TINYINT); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t SET i = 1 / 0; Query OK, 1 row affected (0.06 sec) mysql> SELECT i FROM t; +------+ | i | +------+ | NULL | +------+ 1 row in set (0.01 sec)
Desde MySQL 5.0.2, la división por cero es un error si el modo SQL apropiado está activado:
mysql> CREATE TABLE t (i TINYINT); Query OK, 0 rows affected (0.00 sec) mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t SET i = 1 / 0; ERROR 1365 (22012): Division by 0 mysql> SELECT i FROM t; Empty set (0.01 sec)
Ejemplo 6. En MySQL 4, literales de valores aproximados y exactos se convierten en valores de coma flotante y doble precisión:
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b; mysql> DESCRIBE t; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | double(3,1) | | | 0.0 | | | b | double | | | 0 | | +-------+-------------+------+-----+---------+-------+
En MySQL 5, el literal de valor aproximado todavía se convierte
en un valor de coma flotante, pero el literal de valor exacto se
trata como DECIMAL:
mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b; mysql> DESCRIBE t; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | a | decimal(3,1) | NO | | 0.0 | | | b | double | NO | | 0 | | +-------+--------------+------+-----+---------+-------+
Ejemplo 7. Si el argumento de una función agregada es un tipo numérico exacto, el resultado debe serlo también, con una escala al menos igual a la del argumento. El resultado no debe siempre ser un valor double.
Considere estos comandos:
mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT); mysql> INSERT INTO t VALUES(1,1,1); mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;
Resultado antes de MySQL 5.0.3:
mysql> DESCRIBE y; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | AVG(i) | double(17,4) | YES | | NULL | | | AVG(d) | double(17,4) | YES | | NULL | | | AVG(f) | double | YES | | NULL | | +--------+--------------+------+-----+---------+-------+
El resultado es un double independientemente del tipo del argumento.
Resultado desde MySQL 5.0.3:
mysql> DESCRIBE y; +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | AVG(i) | decimal(64,0) | YES | | NULL | | | AVG(d) | decimal(64,0) | YES | | NULL | | | AVG(f) | double | YES | | NULL | | +--------+---------------+------+-----+---------+-------+
El resultado es un double sólo para el argumento de coma flotante. El resultado es un tipo exacto para argumentos con tipos exactos.