HAVING clause incorrectly includes rows when comparing aggregates with derived table columns containing window functions (regression in 10.11.14)

Environment:

• 10.11.14-MariaDB-0ubuntu0.24.04.1 (Ubuntu 24.04) - BUG PRESENT
• 10.11.13-MariaDB-ubu2204 (Ubuntu 22.04) - WORKS CORRECTLY

—

DESCRIPTION

A query using a HAVING clause that compares an aggregate function with a column from a derived table containing window functions incorrectly includes rows that should be filtered out. The HAVING condition evaluates to FALSE but rows are still returned.

This is a regression introduced between 10.11.13 and 10.11.14.

—

STEPS TO REPRODUCE

– Setup
CREATE DATABASE bug_repro;
USE bug_repro;

CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(17,2),
group_key VARCHAR(100),
created_at DATETIME,
modified_at DATETIME
);

CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
value DECIMAL(17,2)
);

– Insert 1000 orders with random amounts
INSERT INTO orders (amount, group_key, created_at)
SELECT
ROUND(20 + RAND() * 80, 2),
CONCAT('GK_', seq),
DATE_SUB(NOW(), INTERVAL FLOOR(RAND()*365) DAY)
FROM (
SELECT @r := @r + 1 as seq FROM
information_schema.columns a,
information_schema.columns b,
(SELECT @r := 0) r
LIMIT 1000
) nums;

– Insert matching items where item.value = order.amount (so SUM = amount, not greater)
INSERT INTO order_items (order_id, value)
SELECT id, amount FROM orders;

– Query: Should return 0 rows since no order has SUM(value) > amount
SELECT subq.id, subq.amount, SUM(oi.value) as total,
SUM(oi.value) > subq.amount as having_condition
FROM (
SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY COALESCE(o.group_key, CONCAT('BT_', o.id))
ORDER BY CASE WHEN o.modified_at IS NULL THEN 0 ELSE 1 END,
o.created_at DESC, o.id DESC
) as row_num
FROM orders o
) ranked WHERE row_num = 1
) subq
INNER JOIN order_items oi ON subq.id = oi.order_id
GROUP BY subq.id, subq.amount
HAVING SUM(oi.value) > subq.amount
LIMIT 5;

—

EXPECTED RESULT

Empty result set (0 rows) - No order has SUM(order_items.value) > orders.amount since they are equal.

—

ACTUAL RESULT (10.11.14 - BUG)

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

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

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

Note: having_condition = 0 proves the condition is FALSE, yet rows are returned.

—

ACTUAL RESULT (10.11.13 - CORRECT)

Empty set (0 rows)

—

ADDITIONAL INFORMATION

Key observations:
1. The bug only manifests when the derived table contains window functions (ROW_NUMBER)
2. The bug requires a moderately sized dataset (100+ rows) - small datasets may not trigger it
3. Direct queries without derived tables work correctly
4. The calculated HAVING condition is correct (returns 0/FALSE), but filtering doesn't occur

Workaround:
Wrap the query in an outer SELECT and use WHERE instead of HAVING:

SELECT * FROM (
SELECT subq.id, subq.amount, SUM(oi.value) as total,
SUM(oi.value) - subq.amount as difference
FROM ( /* derived table with window function */ ) subq
INNER JOIN order_items oi ON subq.id = oi.order_id
GROUP BY subq.id, subq.amount
) results
WHERE difference > 0;