Description:
the col_2's value changes after added union all clause
even they become the same value 2910.0000
mysql> (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM orders AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5);
+--------+-----------+
| col_1 | col_2 |
+--------+-----------+
| 636.00 | 663.0000 |
| 658.00 | 1684.0000 |
| 282.00 | 3361.0000 |
| 734.00 | 3993.0000 |
| 585.00 | 5946.0000 |
+--------+-----------+
5 rows in set (0.001 sec)
mysql> (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM order
s AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5) UNION ALL SELECT eiv4.user_id
AS col_1, eiv4.id AS col_2 FROM orders AS eiv4;
+---------+-----------+
| col_1 | col_2 |
+---------+-----------+
| 636.00 | 2910.0000 |
| 658.00 | 2910.0000 |
| 282.00 | 2910.0000 |
| 734.00 | 2910.0000 |
| 585.00 | 2910.0000 |
| 663.00 | 2014.0000 |
| 1603.00 | 5862.0000 |
| 1603.00 | 7274.0000 |
| 1684.00 | 3279.0000 |
| 2910.00 | 4609.0000 |
| 3361.00 | 957.0000 |
| 3361.00 | 7152.0000 |
| 3476.00 | 2359.0000 |
| 3799.00 | 5110.0000 |
| 3799.00 | 7861.0000 |
| 3993.00 | 2498.0000 |
| 3993.00 | 4340.0000 |
| 3993.00 | 8298.0000 |
| 5923.00 | 7319.0000 |
| 5923.00 | 9516.0000 |
| 5946.00 | 5907.0000 |
| 6009.00 | 5834.0000 |
| 8392.00 | 3540.0000 |
| 8392.00 | 3850.0000 |
| 9508.00 | 8473.0000 |
+---------+-----------+
25 rows in set (0.002 sec)
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NULL,
age INT NULL,
created_at DATE NOT NULL,
sex VARCHAR(10) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3476, 'sample_608', '6135518710@qq.com', 99, '2025-07-17', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1684, 'sample_845', '7007356294@qq.com', 29, '2024-10-15', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (663, 'sample_347', '3104382090@qq.com', 99, '2025-06-15', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3993, 'sample_958', '4788563212@qq.com', 11, '2025-04-21', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3361, 'sample_955', '6930703558@qq.com', 8, '2024-12-04', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5923, 'sample_747', '8029176222@qq.com', 92, '2025-01-12', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9508, 'sample_667', '4079072794@qq.com', 100, '2025-02-23', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5925, 'sample_671', '2486234342@qq.com', 38, '2024-12-23', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5946, 'sample_440', '2685182271@qq.com', 62, '2025-05-12', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2370, 'sample_638', '4798700520@qq.com', 50, '2025-02-19', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1603, 'sample_440', '9519948937@qq.com', 79, '2024-10-11', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8392, 'sample_247', '3755255048@qq.com', 5, '2024-10-03', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3799, 'sample_851', '6627617255@qq.com', 97, '2025-04-08', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5213, 'sample_849', '9653353770@qq.com', 37, '2025-06-26', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2910, 'sample_317', '4756488088@qq.com', 73, '2024-09-23', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2022, 'sample_188', '7207286235@qq.com', 45, '2025-03-21', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3823, 'sample_157', '9014554600@qq.com', 19, '2025-08-22', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2800, 'sample_925', '9671185040@qq.com', 32, '2024-11-28', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6009, 'sample_762', '6332784748@qq.com', 24, '2024-12-26', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4346, 'sample_323', '8418663358@qq.com', 31, '2025-02-14', 'girl');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4609, 2910, 952, 'to_finish', '2025-07-20');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3850, 8392, 337, 'finished', '2025-02-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5907, 5946, 585, 'finishing', '2024-11-15');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7319, 5923, 272, 'to_finish', '2025-09-15');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8473, 9508, 251, 'to_finish', '2025-08-23');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9516, 5923, 294, 'finishing', '2025-04-01');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7861, 3799, 865, 'finished', '2025-09-12');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2359, 3476, 938, 'finished', '2025-01-18');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (957, 3361, 486, 'finishing', '2025-01-20');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2498, 3993, 734, 'finished', '2024-11-05');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5834, 6009, 339, 'finished', '2025-05-28');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3279, 1684, 658, 'to_finish', '2024-10-01');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3540, 8392, 847, 'finished', '2025-06-18');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2014, 663, 636, 'finishing', '2024-09-24');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5862, 1603, 945, 'finished', '2025-08-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7274, 1603, 45, 'to_finish', '2025-03-27');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8298, 3993, 662, 'to_finish', '2025-04-16');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7152, 3361, 282, 'finishing', '2024-10-02');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4340, 3993, 530, 'to_finish', '2025-02-20');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5110, 3799, 355, 'finishing', '2025-01-16');
(SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM orders AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5);
(SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM order
s AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5) UNION ALL SELECT eiv4.user_id
AS col_1, eiv4.id AS col_2 FROM orders AS eiv4;
© 2025, Oracle Corporation and/or its affiliates
Description: the col_2's value changes after added union all clause even they become the same value 2910.0000 mysql> (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM orders AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5); +--------+-----------+ | col_1 | col_2 | +--------+-----------+ | 636.00 | 663.0000 | | 658.00 | 1684.0000 | | 282.00 | 3361.0000 | | 734.00 | 3993.0000 | | 585.00 | 5946.0000 | +--------+-----------+ 5 rows in set (0.001 sec) mysql> (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM order s AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5) UNION ALL SELECT eiv4.user_id AS col_1, eiv4.id AS col_2 FROM orders AS eiv4; +---------+-----------+ | col_1 | col_2 | +---------+-----------+ | 636.00 | 2910.0000 | | 658.00 | 2910.0000 | | 282.00 | 2910.0000 | | 734.00 | 2910.0000 | | 585.00 | 2910.0000 | | 663.00 | 2014.0000 | | 1603.00 | 5862.0000 | | 1603.00 | 7274.0000 | | 1684.00 | 3279.0000 | | 2910.00 | 4609.0000 | | 3361.00 | 957.0000 | | 3361.00 | 7152.0000 | | 3476.00 | 2359.0000 | | 3799.00 | 5110.0000 | | 3799.00 | 7861.0000 | | 3993.00 | 2498.0000 | | 3993.00 | 4340.0000 | | 3993.00 | 8298.0000 | | 5923.00 | 7319.0000 | | 5923.00 | 9516.0000 | | 5946.00 | 5907.0000 | | 6009.00 | 5834.0000 | | 8392.00 | 3540.0000 | | 8392.00 | 3850.0000 | | 9508.00 | 8473.0000 | +---------+-----------+ 25 rows in set (0.002 sec) How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE users ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255) NULL, age INT NULL, created_at DATE NOT NULL, sex VARCHAR(10) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(50) NOT NULL, order_date DATE NOT NULL, PRIMARY KEY (id), FOREIGN KEY (user_id) REFERENCES users(id) ); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3476, 'sample_608', '6135518710@qq.com', 99, '2025-07-17', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1684, 'sample_845', '7007356294@qq.com', 29, '2024-10-15', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (663, 'sample_347', '3104382090@qq.com', 99, '2025-06-15', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3993, 'sample_958', '4788563212@qq.com', 11, '2025-04-21', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3361, 'sample_955', '6930703558@qq.com', 8, '2024-12-04', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5923, 'sample_747', '8029176222@qq.com', 92, '2025-01-12', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9508, 'sample_667', '4079072794@qq.com', 100, '2025-02-23', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5925, 'sample_671', '2486234342@qq.com', 38, '2024-12-23', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5946, 'sample_440', '2685182271@qq.com', 62, '2025-05-12', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2370, 'sample_638', '4798700520@qq.com', 50, '2025-02-19', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1603, 'sample_440', '9519948937@qq.com', 79, '2024-10-11', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8392, 'sample_247', '3755255048@qq.com', 5, '2024-10-03', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3799, 'sample_851', '6627617255@qq.com', 97, '2025-04-08', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5213, 'sample_849', '9653353770@qq.com', 37, '2025-06-26', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2910, 'sample_317', '4756488088@qq.com', 73, '2024-09-23', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2022, 'sample_188', '7207286235@qq.com', 45, '2025-03-21', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3823, 'sample_157', '9014554600@qq.com', 19, '2025-08-22', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2800, 'sample_925', '9671185040@qq.com', 32, '2024-11-28', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6009, 'sample_762', '6332784748@qq.com', 24, '2024-12-26', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4346, 'sample_323', '8418663358@qq.com', 31, '2025-02-14', 'girl'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4609, 2910, 952, 'to_finish', '2025-07-20'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3850, 8392, 337, 'finished', '2025-02-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5907, 5946, 585, 'finishing', '2024-11-15'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7319, 5923, 272, 'to_finish', '2025-09-15'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8473, 9508, 251, 'to_finish', '2025-08-23'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9516, 5923, 294, 'finishing', '2025-04-01'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7861, 3799, 865, 'finished', '2025-09-12'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2359, 3476, 938, 'finished', '2025-01-18'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (957, 3361, 486, 'finishing', '2025-01-20'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2498, 3993, 734, 'finished', '2024-11-05'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5834, 6009, 339, 'finished', '2025-05-28'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3279, 1684, 658, 'to_finish', '2024-10-01'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3540, 8392, 847, 'finished', '2025-06-18'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2014, 663, 636, 'finishing', '2024-09-24'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5862, 1603, 945, 'finished', '2025-08-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7274, 1603, 45, 'to_finish', '2025-03-27'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8298, 3993, 662, 'to_finish', '2025-04-16'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7152, 3361, 282, 'finishing', '2024-10-02'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4340, 3993, 530, 'to_finish', '2025-02-20'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5110, 3799, 355, 'finishing', '2025-01-16'); (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM orders AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5); (SELECT huk80.amount AS col_1, (AVG(DISTINCT huk80.user_id) * COUNT(DISTINCT huk80.user_id)) AS col_2 FROM order s AS huk80 GROUP BY huk80.amount, huk80.order_date ORDER BY huk80.order_date ASC LIMIT 5) UNION ALL SELECT eiv4.user_id AS col_1, eiv4.id AS col_2 FROM orders AS eiv4;