Description:
When executing a UNION ALL query where one or more branches contain aggregate functions (e.g., SUM, COUNT) or function calls (e.g., IFNULL), the MySQL server sometimes pushes the LIMIT clause down to the individual branches before aggregation. This is incorrect, as it can cause the aggregate to operate on a subset of rows, leading to wrong results (e.g., returning 0.00 instead of the correct sum).
How to repeat:
try executing the following code snippet to reproduce.
CREATE TABLE table1 (
id INT PRIMARY KEY,
name VARCHAR(100),
number INT,
amount DECIMAL(10,2)
);
INSERT INTO table1 (id, name, number, amount) VALUES
(1, 'Alice', 10, 250.75),
(2, 'Bob', 20, 500.00),
(3, 'Charlie', 30, 1250.25);
CREATE TABLE table2 (
id INT PRIMARY KEY,
name VARCHAR(100),
number INT,
amount DECIMAL(10,2)
);
INSERT INTO table2 (id, name, number, amount) VALUES
(1, 'David', 5, 100.50),
(2, 'Eva', 15, 750.00),
(3, 'Frank', 25, 300.00);
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table1
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
)
UNION ALL
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table2
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
LIMIT 0, 2
);
the above query produces following result.
+----+---------+--------+--------------+
| id | name | number | total_amount |
+----+---------+--------+--------------+
| 1 | Alice | 10 | 250.75 |
| 2 | Bob | 20 | 500.00 |
| 3 | Charlie | 30 | 1250.25 |
| 1 | David | 5 | 0.00 |
| 2 | Eva | 15 | 0.00 |
+----+---------+--------+--------------+
5 rows in set (0.00 sec)
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table1
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
)
UNION
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table2
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
LIMIT 0, 2
);
output:
+----+---------+--------+--------------+
| id | name | number | total_amount |
+----+---------+--------+--------------+
| 1 | Alice | 10 | 250.75 |
| 2 | Bob | 20 | 500.00 |
| 3 | Charlie | 30 | 1250.25 |
| 1 | David | 5 | 100.50 |
| 2 | Eva | 15 | 750.00 |
+----+---------+--------+--------------+
5 rows in set (0.00 sec)
(
SELECT
id,
name,
number,
SUM(amount) AS total_amount
FROM table1
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
)
UNION ALL
(
SELECT
id,
name,
number,
SUM(amount) AS total_amount
FROM table2
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
LIMIT 0, 2
);
+----+---------+--------+--------------+
| id | name | number | total_amount |
+----+---------+--------+--------------+
| 1 | Alice | 10 | 250.75 |
| 2 | Bob | 20 | 500.00 |
| 3 | Charlie | 30 | 1250.25 |
| 1 | David | 5 | 100.50 |
| 2 | Eva | 15 | 750.00 |
+----+---------+--------+--------------+
5 rows in set (0.00 sec)
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table1
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
)
UNION ALL
(
SELECT
id,
name,
number,
IFNULL(SUM(amount), 0) AS total_amount
FROM table2
WHERE id > 0
GROUP BY id, name, number
ORDER BY id
);
output :
+----+---------+--------+--------------+
| id | name | number | total_amount |
+----+---------+--------+--------------+
| 1 | Alice | 10 | 250.75 |
| 2 | Bob | 20 | 500.00 |
| 3 | Charlie | 30 | 1250.25 |
| 1 | David | 5 | 100.50 |
| 2 | Eva | 15 | 750.00 |
| 3 | Frank | 25 | 300.00 |
+----+---------+--------+--------------+
6 rows in set (0.05 sec)
Suggested fix:
revisit the logic of applying function over aggregated resultset.
Description: When executing a UNION ALL query where one or more branches contain aggregate functions (e.g., SUM, COUNT) or function calls (e.g., IFNULL), the MySQL server sometimes pushes the LIMIT clause down to the individual branches before aggregation. This is incorrect, as it can cause the aggregate to operate on a subset of rows, leading to wrong results (e.g., returning 0.00 instead of the correct sum). How to repeat: try executing the following code snippet to reproduce. CREATE TABLE table1 ( id INT PRIMARY KEY, name VARCHAR(100), number INT, amount DECIMAL(10,2) ); INSERT INTO table1 (id, name, number, amount) VALUES (1, 'Alice', 10, 250.75), (2, 'Bob', 20, 500.00), (3, 'Charlie', 30, 1250.25); CREATE TABLE table2 ( id INT PRIMARY KEY, name VARCHAR(100), number INT, amount DECIMAL(10,2) ); INSERT INTO table2 (id, name, number, amount) VALUES (1, 'David', 5, 100.50), (2, 'Eva', 15, 750.00), (3, 'Frank', 25, 300.00); ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table1 WHERE id > 0 GROUP BY id, name, number ORDER BY id ) UNION ALL ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table2 WHERE id > 0 GROUP BY id, name, number ORDER BY id LIMIT 0, 2 ); the above query produces following result. +----+---------+--------+--------------+ | id | name | number | total_amount | +----+---------+--------+--------------+ | 1 | Alice | 10 | 250.75 | | 2 | Bob | 20 | 500.00 | | 3 | Charlie | 30 | 1250.25 | | 1 | David | 5 | 0.00 | | 2 | Eva | 15 | 0.00 | +----+---------+--------+--------------+ 5 rows in set (0.00 sec) ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table1 WHERE id > 0 GROUP BY id, name, number ORDER BY id ) UNION ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table2 WHERE id > 0 GROUP BY id, name, number ORDER BY id LIMIT 0, 2 ); output: +----+---------+--------+--------------+ | id | name | number | total_amount | +----+---------+--------+--------------+ | 1 | Alice | 10 | 250.75 | | 2 | Bob | 20 | 500.00 | | 3 | Charlie | 30 | 1250.25 | | 1 | David | 5 | 100.50 | | 2 | Eva | 15 | 750.00 | +----+---------+--------+--------------+ 5 rows in set (0.00 sec) ( SELECT id, name, number, SUM(amount) AS total_amount FROM table1 WHERE id > 0 GROUP BY id, name, number ORDER BY id ) UNION ALL ( SELECT id, name, number, SUM(amount) AS total_amount FROM table2 WHERE id > 0 GROUP BY id, name, number ORDER BY id LIMIT 0, 2 ); +----+---------+--------+--------------+ | id | name | number | total_amount | +----+---------+--------+--------------+ | 1 | Alice | 10 | 250.75 | | 2 | Bob | 20 | 500.00 | | 3 | Charlie | 30 | 1250.25 | | 1 | David | 5 | 100.50 | | 2 | Eva | 15 | 750.00 | +----+---------+--------+--------------+ 5 rows in set (0.00 sec) ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table1 WHERE id > 0 GROUP BY id, name, number ORDER BY id ) UNION ALL ( SELECT id, name, number, IFNULL(SUM(amount), 0) AS total_amount FROM table2 WHERE id > 0 GROUP BY id, name, number ORDER BY id ); output : +----+---------+--------+--------------+ | id | name | number | total_amount | +----+---------+--------+--------------+ | 1 | Alice | 10 | 250.75 | | 2 | Bob | 20 | 500.00 | | 3 | Charlie | 30 | 1250.25 | | 1 | David | 5 | 100.50 | | 2 | Eva | 15 | 750.00 | | 3 | Frank | 25 | 300.00 | +----+---------+--------+--------------+ 6 rows in set (0.05 sec) Suggested fix: revisit the logic of applying function over aggregated resultset.