[MDEV-30890] HAVING clause causes ORDER BY statement to fail when window function in use Created: 2023-03-20  Updated: 2023-06-18  Resolved: 2023-06-18

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: James Kevin O'Halloran Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None


 Description   

@@version:
10.4.27-MariaDB-1:10.4.27+maria~ubu2004

The following query honors the ORDER BY statement when the HAVING clause is commented out, but ignores the ORDER BY statement when the HAVING clause is not commented out:

SELECT	cause_id, 
		COUNT(*) as cause_row_count, 
		COUNT(*) OVER() as cause_count
 
FROM	test_db.test_run_log 
WHERE   test_run_id = 1
 
GROUP BY cause_id
 
-- HAVING   COUNT(*) = 5
 
ORDER BY cause_id;

NOTE that when window OVER function column is commented out, and the HAVING clause is not commented out, the ordering of rows are as expected.

 Comments   
Comment by James Kevin O'Halloran [ 2023-03-20 ]

partial output when HAVING clause is NOT commented out:

cause_id|cause_row_count|cause_count|
 
--------+---------------+-----------+
 
 
 
...
 
    943|              5|        302|
 
    958|              5|        302|
 
    970|              5|        302|
 
    995|              5|        302|
 
   1008|              5|        302|
 
   1032|              5|        302|
 
    300|              5|        302|
 
    323|              5|        302|
 
    337|              5|        302|
 
    346|              5|        302|
 
    358|              5|        302|
 
...

When the HAVING clause is commented out, ordering is as expected.

Comment by Sergei Golubchik [ 2023-05-19 ]

could you please provide some sample data to help us to repeat this bug? It seems to be data dependent.

Generated at Thu Feb 08 10:19:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.