Bug #90992 DISTINCT ignores window functions
Submitted: 23 May 2018 14:01 Modified: 18 Feb 2021 9:38
Reporter: Andrej Thomsen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: distinct, window-function

[23 May 2018 14:01] Andrej Thomsen 
Description:
When using a normal select statement with "distinct", columns which result from window functions are ignored.
When executing the following commands, I would expect four rows to be returned but the statement returns just two.

How to repeat:
CREATE TABLE `TestTable`
(
  `GroupID` INT NOT NULL,
  `TestTime` TIME NOT NULL
);

INSERT INTO TestTable
(`GroupID`,`TestTime`)
VALUES
(1,'10:00:00'),
(1,'11:00:00'),
(2,'10:00:00'),
(2,'11:00:00'),
(2,'12:00:00');

SELECT DISTINCT
    GroupID,
    IF(
        FIRST_VALUE(TestTime) OVER (
            PARTITION BY GroupID
        ) >= TestTime,
        1,
        2
    ) AS TimeStart
FROM    TestTable;

Suggested fix:
If it's designed like this on purpose, at least mention it in the official docs
[23 May 2018 15:08] MySQL Verification Team 
Thank you for the bug report. Verified as described.
[12 Oct 2018 11:17] Dag Wanvik 
Posted by developer:
 
Fixed by patch for Bug#28672483, probably an alias.
[18 Feb 2021 9:38] Erlend Dahl 
Duplicate of

Bug#92503 Wrong result from distinct and arithmetic expression using window function