[MDEV-19017] incorrect LAST_VALUE() result Created: 2019-03-22  Updated: 2020-07-20  Resolved: 2020-07-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.3.11, 10.2.19, 10.2, 10.3, 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Varun Gupta (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

https://dba.stackexchange.com/questions/232814/last-value-in-two-levels-of-group-by-in-mysql

https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=803339c7ce6ae11967944c427b01ff8b

CREATE TABLE fruits_flow (
 
  `date` date,
 
  `fruit` VARCHAR(7),
 
  `init` INTEGER,
 
  `end` INTEGER
 
);
 
 
 
INSERT INTO fruits_flow
 
  (`date`, `fruit`, `init`, `end`)
 
VALUES
 
  ('2018-01-02', 'Oranges', '1479', '1480'),
 
  ('2018-01-03', 'Oranges', '1478', '1504'),
 
  ('2018-01-04', 'Oranges', '1507', '1494'),
 
  ('2018-01-05', 'Oranges', '1510', '1551'),
 
  ('2018-01-02', 'Apples', '2150', '2196'),
 
  ('2018-01-03', 'Apples', '2161', '2167'),
 
  ('2018-01-04', 'Apples', '2192', '2169'),
 
  ('2018-01-05', 'Apples', '2169', '2194');
 
 
 
SELECT
 
         CONCAT(YEAR(`date`),'/',WEEK(`date`)) as `week`,
 
         fruit,
 
         FIRST_VALUE(init) OVER (PARTITION BY fruit,`week` ORDER BY `date` ASC  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'first',
 
         LAST_VALUE(end)   OVER (PARTITION BY fruit,`week` ORDER BY `date` ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'last'
 
       FROM fruits_flow
 
       GROUP BY `week`,fruit

Results:

week fruit first last
:----- :------ ----: ---:
2018/0 Apples 2150 2196
2018/0 Oranges 1479 1480

Expected Results

week fruit first last
:----- :------ ----: ---:
2018/0 Apples 2150 2194
2018/0 Oranges 1479 1551


 Comments   
Comment by Alice Sherepa [ 2019-03-25 ]

Thanks! Reproduced as described on 10.2-10.4

MariaDB [test]> SELECT fruit,
 
    ->   LAST_VALUE(end) OVER (PARTITION BY fruit ORDER BY `date` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as l
 
    ->        FROM fruits_flow;
 
+---------+------+
 
| fruit   | l    |
 
+---------+------+
 
| Apples  | 2194 |
 
| Apples  | 2194 |
 
| Apples  | 2194 |
 
| Apples  | 2194 |
 
| Oranges | 1551 |
 
| Oranges | 1551 |
 
| Oranges | 1551 |
 
| Oranges | 1551 |
 
+---------+------+
 
8 rows in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT fruit,
 
    ->   LAST_VALUE(end) OVER (PARTITION BY fruit ORDER BY `date` ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as l
 
    ->        FROM fruits_flow
 
    -> GROUP BY fruit;
 
+---------+------+
 
| fruit   | l    |
 
+---------+------+
 
| Apples  | 2196 |
 
| Oranges | 1480 |
 
+---------+------+
 
2 rows in set (0.00 sec)

Comment by Varun Gupta (Inactive) [ 2020-07-16 ]

This is not a bug, GROUP BY clause is applied to the query before window function computation.

MariaDB [test]> SELECT
 
    -> CONCAT(YEAR(date),'/',WEEK(date)) as week,
 
    -> fruit, end, init
 
    -> FROM fruits_flow
 
    -> GROUP BY week, fruit;
 
+--------+---------+------+------+
 
| week   | fruit   | end  | init |
 
+--------+---------+------+------+
 
| 2018/0 | Apples  | 2196 | 2150 |
 
| 2018/0 | Oranges | 1480 | 1479 |
 
+--------+---------+------+------+
 
2 rows in set (0.00 sec)

and so window function last_value and first value will be applied to this result

MariaDB [test]> SELECT
 
    -> CONCAT(YEAR(date),'/',WEEK(date)) as week,
 
    -> fruit,
 
    -> FIRST_VALUE(init) OVER (PARTITION BY fruit, week ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first,
 
    -> LAST_VALUE(end)   OVER (PARTITION BY fruit, week ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last
 
    -> FROM fruits_flow
 
    -> GROUP BY week, fruit;
 
+--------+---------+-------+------+
 
| week   | fruit   | first | last |
 
+--------+---------+-------+------+
 
| 2018/0 | Apples  |  2150 | 2196 |
 
| 2018/0 | Oranges |  1479 | 1480 |
 
+--------+---------+-------+------+
 
2 rows in set (0.00 sec)

and this returns correct result.

Generated at Thu Feb 08 08:48:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.