Description:
Output:
=======
mysql> DROP TABLE if exists t1, t2, t3;
CREATE TABLE t1 (Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t1 (t1_id INT UNSIGNED PRIMARY KEY) ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t2 (t1_id INT UNSIGNED PRIMARY KEY);
VALUES (1), (2), (3), (4),Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t2 (t1_id) VALUES (1), (2), (3), (4), (5);
TE TABLE t3Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE t3 (t3_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, t1_id INT UNSIGNED, amount DECIMAL(16,2)) ;
, t3_id, amount) VALUES (Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t3 (t1_id, t3_id, amount) VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT
-> t1.t1_id,
-> IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0.0000000) AS total_amount
-> FROM
-> t1
-> LEFT JOIN t2 ON t2.t1_id=t1.t1_id
-> GROUP BY
-> t1.t1_id
-> ;
+-------+--------------+
| t1_id | total_amount |
+-------+--------------+
| 1 | 100.00 |
| 2 | 200.00 |
| 3 | 0.0000000 |
| 4 | 400.00 |
| 5 | 0.0000000 |
+-------+--------------+
5 rows in set (0.00 sec)
Problem:
========
The scale for IFNULL() in the above scenario should be with 7 decimals.
How to repeat:
DROP TABLE if exists t1, t2, t3;
CREATE TABLE t1 (t1_id INT UNSIGNED PRIMARY KEY) ;
INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5);
CREATE TABLE t2 (t1_id INT UNSIGNED PRIMARY KEY);
INSERT INTO t2 (t1_id) VALUES (1), (2), (3), (4), (5);
CREATE TABLE t3 (t3_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, t1_id INT UNSIGNED, amount DECIMAL(16,2)) ;
INSERT INTO t3 (t1_id, t3_id, amount) VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00);
SELECT
t1.t1_id,
IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0.0000000) AS total_amount
FROM
t1
LEFT JOIN t2 ON t2.t1_id=t1.t1_id
GROUP BY
t1.t1_id
;
Suggested fix:
The scale for IFNULL() in the above scenario should be with 7 decimals.
Description: Output: ======= mysql> DROP TABLE if exists t1, t2, t3; CREATE TABLE t1 (Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 (t1_id INT UNSIGNED PRIMARY KEY) ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE t2 (t1_id INT UNSIGNED PRIMARY KEY); VALUES (1), (2), (3), (4),Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t2 (t1_id) VALUES (1), (2), (3), (4), (5); TE TABLE t3Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE t3 (t3_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, t1_id INT UNSIGNED, amount DECIMAL(16,2)) ; , t3_id, amount) VALUES (Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t3 (t1_id, t3_id, amount) VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT -> t1.t1_id, -> IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0.0000000) AS total_amount -> FROM -> t1 -> LEFT JOIN t2 ON t2.t1_id=t1.t1_id -> GROUP BY -> t1.t1_id -> ; +-------+--------------+ | t1_id | total_amount | +-------+--------------+ | 1 | 100.00 | | 2 | 200.00 | | 3 | 0.0000000 | | 4 | 400.00 | | 5 | 0.0000000 | +-------+--------------+ 5 rows in set (0.00 sec) Problem: ======== The scale for IFNULL() in the above scenario should be with 7 decimals. How to repeat: DROP TABLE if exists t1, t2, t3; CREATE TABLE t1 (t1_id INT UNSIGNED PRIMARY KEY) ; INSERT INTO t1 (t1_id) VALUES (1), (2), (3), (4), (5); CREATE TABLE t2 (t1_id INT UNSIGNED PRIMARY KEY); INSERT INTO t2 (t1_id) VALUES (1), (2), (3), (4), (5); CREATE TABLE t3 (t3_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, t1_id INT UNSIGNED, amount DECIMAL(16,2)) ; INSERT INTO t3 (t1_id, t3_id, amount) VALUES (1, 1, 100.00), (2, 2, 200.00), (4, 4, 400.00); SELECT t1.t1_id, IFNULL((SELECT SUM(amount) FROM t3 WHERE t3.t1_id=t1.t1_id), 0.0000000) AS total_amount FROM t1 LEFT JOIN t2 ON t2.t1_id=t1.t1_id GROUP BY t1.t1_id ; Suggested fix: The scale for IFNULL() in the above scenario should be with 7 decimals.