Справочное руководство по MySQL версии 4.1.1-alpha
A.5.7 Проблемы со сравнением чисел с плавающей точкой
Числа с плавающей точкой иногда служат источником неприятностей, поскольку эти числа архитектурно хранятся в компьютере не как точные числа. То, что обычно мы видим на экране, не является точным значением числа.
Поля типов FLOAT, DOUBLE и DECIMAL следующие.
CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2)); INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
Результат правильный. Хотя кажется, что первые пять записей должны быть отброшены, не выдержав проверки на сравнение, тем не менее, они могут удовлетворить условиям по той причине, что в зависимости от архитектуры компьютера между числами проявляются отличия примерно на уровне десятого знака.
С помощью ROUND() (или подобной функции) проблема не может быть решена, поскольку результат все равно будет числом с плавающей запятой, например:
mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+-------+ | i | a | b | +------+--------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | | 6 | -51.40 | 0.00 | +------+--------+-------+
Вот как выглядят числа в столбце 'a':
mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a, -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b; +------+----------------------+-------+ | i | a | b | +------+----------------------+-------+ | 1 | 21.3999999999999986 | 21.40 | | 2 | 76.7999999999999972 | 76.80 | | 3 | 7.4000000000000004 | 7.40 | | 4 | 15.4000000000000004 | 15.40 | | 5 | 7.2000000000000002 | 7.20 | | 6 | -51.3999999999999986 | 0.00 | +------+----------------------+-------+
В вашей системе результаты могут либо такими, либо нет - это зависит от архитектуры компьютера. Каждый процессор выполняет вычисления с плавающей точкой по-своему. Например, на некоторых машинах можно получить ``правильные'' результаты, если умножить оба аргумента на 1 (см. пример ниже).
ПРЕДУПРЕЖДЕНИЕ: НИКОГДА НЕ ПОЛАГАЙТЕСЬ НА ДАННЫЙ МЕТОД В СВОЕМ ПРИЛОЖЕНИИ, ЭТО ПРИМЕР ТОГО, КАКИЕ МЕТОДЫ НЕ СЛЕДУЕТ ИСПОЛЬЗОВАТЬ!!!
mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b -> FROM t1 GROUP BY i HAVING a <> b; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+
Приведенный выше пример вроде бы работает. Но это случайность, поскольку именно на той конкретной машине, где выполнялась проверка, процессор выполняет арифметические операции с плавающей точкой таким образом, что числа округляются до одинаковых значений. Однако полагаться на то, что так будут работать все без исключения процессоры, нельзя.
Правильный способ сравнения чисел с плавающей запятой заключается в том, чтобы сначала определиться с допустимым отклонением одного числа от другого, а затем при сравнении учитывать этот допуск. Например, если мы договоримся, что числа должны считаться одинаковыми, если они равны с точностью до одной десятитысячной (0,0001), то сравнение должно проводиться следующим образом:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) > 0.0001; +------+--------+------+ | i | a | b | +------+--------+------+ | 6 | -51.40 | 0.00 | +------+--------+------+ 1 row in set (0.00 sec)
И наоборот, если мы хотим оставить строки, в которых числа одинаковы, то проверка должна быть следующей:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1 -> GROUP BY i HAVING ABS(a - b) < 0.0001; +------+-------+-------+ | i | a | b | +------+-------+-------+ | 1 | 21.40 | 21.40 | | 2 | 76.80 | 76.80 | | 3 | 7.40 | 7.40 | | 4 | 15.40 | 15.40 | | 5 | 7.20 | 7.20 | +------+-------+-------+
