Description:
hello ,
In theory,the col_1's value should be the same.
However,the first column value was 8591.0000,but after adding union_all clause the value changes to 'NULL'.
--------------------------------------------------------------------------
mysql> (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1);
+-----------+
| col_1 |
+-----------+
| 8591.0000 |
+-----------+
1 row in set (0.001 sec)
mysql> (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1) UNION ALL SELECT ltc46.id AS col_1 FROM users AS ltc46 WHERE ltc46.id
BETWEEN 26 AND 62;
+-------+
| col_1 |
+-------+
| NULL |
+-------+
1 row in set (0.001 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 DATETIME 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 (5637, 'sample_397', '9172210635@qq.com', 59, '2025-04-07', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8591, 'sample_827', '8124385751@qq.com', 76, '2025-03-22', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4370, 'sample_908', '5157930292@qq.com', 79, '2025-07-10', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1303, 'sample_915', '8622486320@qq.com', 46, '2025-06-15', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7472, 'sample_99', '6283703771@qq.com', 75, '2024-12-16', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9522, 'sample_318', '8602745773@qq.com', 43, '2025-08-20', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2239, 'sample_980', '1802576937@qq.com', 52, '2025-03-13', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3007, 'sample_133', '3793373661@qq.com', 56, '2024-10-02', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5703, 'sample_960', '7063023722@qq.com', 73, '2024-12-30', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7509, 'sample_960', '1897771675@qq.com', 62, '2025-04-28', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8151, 'sample_988', '9575504289@qq.com', 41, '2025-06-27', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7385, 'sample_200', '7109100852@qq.com', 40, '2024-10-18', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3419, 'sample_357', '8198378445@qq.com', 6, '2024-11-12', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1759, 'sample_417', '4159256032@qq.com', 86, '2025-01-25', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3301, 'sample_720', '3399685539@qq.com', 8, '2025-02-11', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8425, 'sample_277', '7988058628@qq.com', 6, '2025-09-04', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7658, 'sample_896', '8776371625@qq.com', 71, '2025-06-10', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5355, 'sample_470', '3093540213@qq.com', 63, '2025-02-06', 'girl');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (108, 'sample_449', '6994694481@qq.com', 52, '2025-03-31', 'boy');
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (765, 'sample_166', '6396916450@qq.com', 16, '2024-09-27', 'boy');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4753, 1759, 702, 'to_finish', '2024-10-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2965, 108, 173, 'finished', '2024-12-19');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5020, 2239, 394, 'finished', '2025-07-14');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6695, 3419, 202, 'finished', '2025-04-26');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1448, 3419, 358, 'finishing', '2024-12-06');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7852, 5355, 642, 'finishing', '2025-02-04');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2875, 2239, 219, 'finished', '2024-10-03');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (708, 8591, 437, 'to_finish', '2025-01-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8010, 1759, 530, 'finished', '2025-06-17');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (335, 3007, 27, 'to_finish', '2025-07-08');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5460, 108, 397, 'finishing', '2024-10-06');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7893, 8591, 128, 'finishing', '2025-03-22');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2009, 7472, 675, 'finishing', '2025-04-27');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8026, 3419, 249, 'to_finish', '2025-09-12');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1381, 7472, 389, 'to_finish', '2024-10-13');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6631, 8425, 1000, 'finishing', '2025-03-12');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1134, 9522, 530, 'finished', '2025-04-06');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8691, 7472, 974, 'finishing', '2025-05-08');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8182, 765, 112, 'finished', '2025-08-28');
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7929, 1759, 471, 'finishing', '2025-02-07');
(SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1);
(SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1) UNION ALL SELECT ltc46.id AS col_1 FROM users AS ltc46 WHERE ltc46.id
BETWEEN 26 AND 62;
Description: hello , In theory,the col_1's value should be the same. However,the first column value was 8591.0000,but after adding union_all clause the value changes to 'NULL'. -------------------------------------------------------------------------- mysql> (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1); +-----------+ | col_1 | +-----------+ | 8591.0000 | +-----------+ 1 row in set (0.001 sec) mysql> (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1) UNION ALL SELECT ltc46.id AS col_1 FROM users AS ltc46 WHERE ltc46.id BETWEEN 26 AND 62; +-------+ | col_1 | +-------+ | NULL | +-------+ 1 row in set (0.001 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 DATETIME 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 (5637, 'sample_397', '9172210635@qq.com', 59, '2025-04-07', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8591, 'sample_827', '8124385751@qq.com', 76, '2025-03-22', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4370, 'sample_908', '5157930292@qq.com', 79, '2025-07-10', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1303, 'sample_915', '8622486320@qq.com', 46, '2025-06-15', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7472, 'sample_99', '6283703771@qq.com', 75, '2024-12-16', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9522, 'sample_318', '8602745773@qq.com', 43, '2025-08-20', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2239, 'sample_980', '1802576937@qq.com', 52, '2025-03-13', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3007, 'sample_133', '3793373661@qq.com', 56, '2024-10-02', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5703, 'sample_960', '7063023722@qq.com', 73, '2024-12-30', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7509, 'sample_960', '1897771675@qq.com', 62, '2025-04-28', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8151, 'sample_988', '9575504289@qq.com', 41, '2025-06-27', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7385, 'sample_200', '7109100852@qq.com', 40, '2024-10-18', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3419, 'sample_357', '8198378445@qq.com', 6, '2024-11-12', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1759, 'sample_417', '4159256032@qq.com', 86, '2025-01-25', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3301, 'sample_720', '3399685539@qq.com', 8, '2025-02-11', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8425, 'sample_277', '7988058628@qq.com', 6, '2025-09-04', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7658, 'sample_896', '8776371625@qq.com', 71, '2025-06-10', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5355, 'sample_470', '3093540213@qq.com', 63, '2025-02-06', 'girl'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (108, 'sample_449', '6994694481@qq.com', 52, '2025-03-31', 'boy'); INSERT INTO users (id, name, email, age, created_at, sex) VALUES (765, 'sample_166', '6396916450@qq.com', 16, '2024-09-27', 'boy'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4753, 1759, 702, 'to_finish', '2024-10-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2965, 108, 173, 'finished', '2024-12-19'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5020, 2239, 394, 'finished', '2025-07-14'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6695, 3419, 202, 'finished', '2025-04-26'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1448, 3419, 358, 'finishing', '2024-12-06'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7852, 5355, 642, 'finishing', '2025-02-04'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2875, 2239, 219, 'finished', '2024-10-03'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (708, 8591, 437, 'to_finish', '2025-01-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8010, 1759, 530, 'finished', '2025-06-17'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (335, 3007, 27, 'to_finish', '2025-07-08'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5460, 108, 397, 'finishing', '2024-10-06'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7893, 8591, 128, 'finishing', '2025-03-22'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2009, 7472, 675, 'finishing', '2025-04-27'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8026, 3419, 249, 'to_finish', '2025-09-12'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1381, 7472, 389, 'to_finish', '2024-10-13'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6631, 8425, 1000, 'finishing', '2025-03-12'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1134, 9522, 530, 'finished', '2025-04-06'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8691, 7472, 974, 'finishing', '2025-05-08'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8182, 765, 112, 'finished', '2025-08-28'); INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7929, 1759, 471, 'finishing', '2025-02-07'); (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1); (SELECT (SUM(xdz94.id) / COUNT(xdz94.id)) AS col_1 FROM users AS xdz94 INNER JOIN orders AS twe35 ON ((xdz94.created_at = twe35.order_date) AND (xdz94.sex > twe35.status)) WHERE (NOT xdz94.sex LIKE 'sample_70') GROUP BY xdz94.age ORDER BY xdz94.age DESC LIMIT 1) UNION ALL SELECT ltc46.id AS col_1 FROM users AS ltc46 WHERE ltc46.id BETWEEN 26 AND 62;