Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.0.2
-
None
-
None
-
None
-
Ubuntu22.04
Description
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 (7491, 'sample_970', '9108479905@qq.com', 95, '2025-07-20', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8548, 'sample_286', '6264360088@qq.com', 56, '2024-11-26', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8771, 'sample_851', '1288168102@qq.com', 35, '2025-02-19', 'girl'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8011, 'sample_187', '6433943147@qq.com', 30, '2024-12-09', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2606, 'sample_642', '5705695944@qq.com', 78, '2025-01-24', 'girl'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4085, 'sample_93', '5441361909@qq.com', 4, '2025-08-18', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (5910, 'sample_194', '9932167149@qq.com', 11, '2025-06-27', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (2104, 'sample_815', '9904916978@qq.com', 70, '2025-07-19', 'girl'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (889, 'sample_45', '3945938760@qq.com', 63, '2024-11-16', 'boy'); |
INSERT INTO users (id, name, email, age, created_at, sex) VALUES (127, 'sample_814', '5954881147@qq.com', 27, '2025-06-21', 'girl'); |
|
|
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9985, 127, 739, 'finished', '2025-07-20'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (676, 7491, 872, 'finished', '2025-06-19'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (3868, 4085, 685, 'finishing', '2025-02-20'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7561, 7491, 965, 'finished', '2025-04-03'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (7210, 8548, 285, 'to_finish', '2025-03-20'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5549, 5910, 794, 'to_finish', '2025-06-15'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9628, 2104, 861, 'to_finish', '2025-06-30'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9114, 2104, 610, 'finished', '2024-12-07'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5308, 5910, 472, 'to_finish', '2025-07-24'); |
INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5086, 4085, 344, 'to_finish', '2024-10-19'); |
|
|
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY 1 = 1) AS dense_rank_col, subq.sex AS sex, COUNT(subq.email) AS count_col, SUM(subq.avg_age) AS sum_col, subq.sex AS sex_1, subq.avg_age AS avg_age FROM (SELECT s864.sex AS sex, AVG(s864.age) AS avg_age, s864.email AS email FROM users AS s864 GROUP BY s864.sex, s864.email) AS subq WHERE NOT subq.avg_age IS NULL GROUP BY subq.avg_age, subq.sex, subq.avg_age, subq.sex, subq.avg_age, subq.avg_age, subq.avg_age HAVING (SUM(subq.avg_age) <= 5.94) ORDER BY subq.avg_age ASC; |
|
|
SELECT DISTINCT DENSE_RANK() OVER (ORDER BY 1 = 1) AS dense_rank_col, subq.sex AS sex, COUNT(subq.email) AS count_col, (AVG(subq.avg_age) * COUNT(subq.avg_age)) AS sum_col, subq.sex AS sex_1, subq.avg_age AS avg_age FROM (SELECT s864.sex AS sex, AVG(s864.age) AS avg_age, s864.email AS email FROM users AS s864 GROUP BY s864.sex, s864.email) AS subq WHERE NOT subq.avg_age IS NULL GROUP BY subq.avg_age, subq.sex, subq.avg_age, subq.sex, subq.avg_age, subq.avg_age, subq.avg_age HAVING ((AVG(subq.avg_age) * COUNT(subq.avg_age)) <= 5.94) ORDER BY subq.avg_age ASC; |
the last two queries' result should be the same,but the second query returns an empty set
|
count and sum |
mysql>
|
SELECT DISTINCT DENSE_RANK() OVER (
|
ORDER BY 1 = 1) AS dense_rank_col,
|
subq.sex AS sex,
|
COUNT(subq.email) AS count_col,
|
SUM(subq.avg_age) AS sum_col,
|
subq.sex AS sex_1,
|
subq.avg_age AS avg_age
|
FROM
|
(SELECT s864.sex AS sex,
|
AVG(s864.age) AS avg_age,
|
s864.email AS email
|
FROM users AS s864
|
GROUP BY s864.sex,
|
s864.email) AS subq
|
WHERE NOT subq.avg_age IS NULL
|
GROUP BY subq.avg_age,
|
subq.sex,
|
subq.avg_age,
|
subq.sex,
|
subq.avg_age,
|
subq.avg_age,
|
subq.avg_age
|
HAVING (SUM(subq.avg_age) <= 5.94)
|
ORDER BY subq.avg_age ASC;
|
+----------------+-----+-----------+---------+-------+---------+
|
| dense_rank_col | sex | count_col | sum_col | sex_1 | avg_age |
|
+----------------+-----+-----------+---------+-------+---------+
|
| 1 | boy | 1 | 4.0000 | boy | 4.0000 |
|
+----------------+-----+-----------+---------+-------+---------+
|
1 row in set (0.01 sec)
|
|
avg * count |
mysql> SELECT DISTINCT DENSE_RANK() OVER (
|
ORDER BY 1 = 1) AS dense_rank_col,
|
subq.sex AS sex,
|
COUNT(subq.email) AS count_col,
|
(AVG(subq.avg_age) * COUNT(subq.avg_age)) AS sum_col,
|
subq.sex AS sex_1,
|
subq.avg_age AS avg_age
|
FROM (SELECT s864.sex AS sex,
|
AVG(s864.age) AS avg_age,
|
s864.email AS email
|
FROM USER s AS s864
|
GROUP BY s864.sex,
|
s864.email) AS subq
|
WHERE NOT subq.avg_age IS NULL
|
GROUP BY subq.avg_age,
|
subq.sex,
|
subq.avg_age,
|
subq.sex,
|
subq.avg_age,
|
sub q.avg_age,
|
subq.avg_age
|
HAVING ((AVG(subq.avg_age) * COUNT(subq.avg_age)) <= 5.94)
|
ORDER BY subq.avg_age ASC;
|
Empty set (0.00 sec)
|