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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.