An error occurred while querying the sum result of JPA + MySQL and Union combinations.

Union all

There are two tables, A and B.

Table A data is similar

ID	A1	A2	A3	A4	A5
1	5	0.5	A	G1	...
2	10	1.0	A	G2	...
3	15	1.5	A	G1	...

Table B data is similar

ID	A1	A2	A3	A4	A5
1	10	2	B	G2	...
2	20	4	B	G1	...
3	30	6	B	G1	...

SELECT count(*), sum(x),sum(y),z,w FROM ( (SELECT a1 as x, a2 as y, a3 as z, a4 as w FROM A)UNION ALL (SELECT b1 as x, b3 as z, b2 as y, b4 as w FROM B))t  GROUP BY w

The result shows that sum (X) is correct, but sum (Y) is wrong. Why?

Because according to my thinking, the Select result of union should be:

X	Y	Z	W
5	0.5	A	G1
10	1.0	A	G2
15	1.5	A	G1
10	2	B	G2
20	4	B	G1
30	6	B	G1

But the actual result is:

X	Y	Z	W
5	0.5	A	G1
10	1.0	A	G2
15	1.5	A	G1
10	B	2	G2
20	B	4	G1
30	B	6	G1

Note: The three lines below y are not numbers but letters.

At this time, we can see the order of the two Select fields that have been union,

The first one is A1, A2, A3, A4,

The second is: B1, B3, B2, B4,

Although the corresponding xyzw has no corresponding error,:

Union queries do not correspond to one-to-one aliases for each field, but in the order of fields in your select statement.