Bug #118669 Issue in applying limit on aggregated data

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.

Hello Balakumar,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh