window function and min(indexed_column) in derived produces wrong result

mysql> WITH cte_38 AS (SELECT MIN(faz24.id) AS col_1, faz24.amount AS col_2, CONCAT(faz24.status, faz24.status) AS col_3, DENSE_RANK() OVER (ORDER BY faz24.status ASC) AS col_4 FROM orders AS faz24) SELECT vdk42.col_1 AS main_col_1, vdk42.col_2 AS main_col_2, vdk42.col_3 AS main_col_3 FROM cte_38 AS vdk42 WHERE NOT vdk42.col_1 IS NULL;

Empty set (0.00 sec)

mysql> WITH cte_38 AS (SELECT -MAX(-faz24.id) AS col_1, faz24.amount AS col_2, CONCAT(faz24.status, faz24.status) AS col_3, DENSE_RANK() OVER (ORDER BY faz24.status ASC) AS col_4 FROM orders AS faz24) SELECT vdk42.col_1 AS main_col_1, vdk42.col_2 AS main_col_2, vdk42.col_3 AS main_col_3 FROM cte_38 AS vdk42 WHERE NOT vdk42.col_1 IS NULL;

+------------+------------+--------------------+

| main_col_1 | main_col_2 | main_col_3         |

+------------+------------+--------------------+

|         11 |     676.00 | to_finishto_finish |

+------------+------------+--------------------+

1 row in set (0.00 sec)

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 (4997, 'sample_870', '4767493460@qq.com', 11, '2025-05-31', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (650, 'sample_604', '8687088431@qq.com', 100, '2025-03-08', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4761, 'sample_374', '6307530696@qq.com', 52, '2025-01-24', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6824, 'sample_370', '2696250186@qq.com', 9, '2024-12-30', 'boy');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1961, 'sample_780', '2738040443@qq.com', 22, '2024-10-09', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9641, 'sample_872', '1849004062@qq.com', 64, '2025-03-31', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3003, 'sample_461', '6118259649@qq.com', 50, '2025-04-02', 'boy');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7489, 'sample_800', '5016709826@qq.com', 64, '2025-09-20', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8898, 'sample_397', '1662666130@qq.com', 87, '2025-03-22', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (3779, 'sample_588', '5642487697@qq.com', 74, '2025-06-06', 'boy');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (4549, 'sample_919', '4055363219@qq.com', 45, '2025-03-28', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (838, 'sample_628', '8759992911@qq.com', 34, '2025-05-11', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (9163, 'sample_885', '5675626920@qq.com', 74, '2025-02-07', 'boy');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7255, 'sample_743', '1020270162@qq.com', 24, '2025-06-05', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (8412, 'sample_30', '6939104762@qq.com', 23, '2024-11-14', 'boy');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (7019, 'sample_410', '3202893566@qq.com', 25, '2025-03-16', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6127, 'sample_205', '2429364999@qq.com', 15, '2025-01-20', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (6134, 'sample_580', '8939683583@qq.com', 24, '2024-10-05', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (1655, 'sample_319', '4200080951@qq.com', 41, '2025-01-19', 'girl');

INSERT INTO users (id, name, email, age, created_at, sex) VALUES (508, 'sample_944', '1150690704@qq.com', 90, '2025-03-08', 'girl');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6148, 9641, 934, 'finished', '2025-06-22');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (11, 6127, 676, 'to_finish', '2025-08-31');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2065, 4761, 20, 'finishing', '2025-09-14');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (8092, 8898, 219, 'to_finish', '2025-08-26');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9762, 3779, 126, 'finishing', '2025-04-16');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6818, 9641, 737, 'to_finish', '2025-05-06');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4390, 508, 990, 'finishing', '2025-02-01');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9512, 838, 749, 'finishing', '2025-09-14');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (4796, 6127, 85, 'to_finish', '2025-01-24');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6398, 6134, 930, 'finishing', '2025-05-05');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1983, 8898, 806, 'to_finish', '2025-03-04');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6210, 7255, 258, 'finishing', '2024-11-21');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (5570, 1961, 562, 'finished', '2024-10-15');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2143, 4997, 26, 'finishing', '2025-01-09');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (6241, 7489, 614, 'to_finish', '2025-06-27');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2785, 7489, 22, 'finished', '2025-01-03');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (2921, 6127, 278, 'to_finish', '2024-10-16');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (628, 6127, 276, 'finishing', '2025-07-15');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (1534, 4761, 667, 'finished', '2025-08-04');

INSERT INTO orders (id, user_id, amount, status, order_date) VALUES (9599, 9641, 632, 'to_finish', '2025-03-30');

WITH cte_38 AS (SELECT MIN(faz24.id) AS col_1, faz24.amount AS col_2, CONCAT(faz24.status, faz24.status) AS col_3, DENSE_RANK() OVER (ORDER BY faz24.status ASC) AS col_4 FROM orders AS faz24) SELECT vdk42.col_1 AS main_col_1, vdk42.col_2 AS main_col_2, vdk42.col_3 AS main_col_3 FROM cte_38 AS vdk42 WHERE NOT vdk42.col_1 IS NULL;

WITH cte_38 AS (SELECT -MAX(-faz24.id) AS col_1, faz24.amount AS col_2, CONCAT(faz24.status, faz24.status) AS col_3, DENSE_RANK() OVER (ORDER BY faz24.status ASC) AS col_4 FROM orders AS faz24) SELECT vdk42.col_1 AS main_col_1, vdk42.col_2 AS main_col_2, vdk42.col_3 AS main_col_3 FROM cte_38 AS vdk42 WHERE NOT vdk42.col_1 IS NULL;