MySQL function returns wrong value on repeat, when it contains window functions

Question

MySQL 8.0.33

I have a stored function to calculate the sum of all time gaps in a series of events on a given day. This function uses a window function LEAD(...) OVER() to determine the gap between the end of one event and the start of the next event.

If I run the query itself multiple times, the result is always the same. If use the function, the first execution has the correct value, and the rest return NULL.

Why???

Edit:

I have corrected some errors in the code in the block below and made an Fiddle.

Now my question has changed to: "Why does this work correctly in the Fiddle, but not on my machine? What setting can affect this?"

Edit 2:

Adding FLUSH TABLES events; between the two function calls fixes the issue on my server. Again, why is this happening?

---

Schema:

CREATE TABLE `events` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `start` (`start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `events` (`id`, `start`, `end`) VALUES
(1, '2023-02-06 08:14:00', '2023-02-06 10:30:00'),
(2, '2023-02-06 11:57:00', '2023-02-06 12:25:00'),
(3, '2023-02-06 14:00:00', '2023-02-06 14:15:00'),
(4, '2023-02-06 15:00:00', '2023-02-06 16:49:00');


CREATE FUNCTION fx_timegap (dt DATE)
RETURNS DECIMAL(4,2) NOT DETERMINISTIC READS SQL DATA
RETURN (
    WITH
    C1 AS (
        SELECT
            (UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
        FROM events
        WHERE DATE(start) = dt
    )
    SELECT SUM(gap)
    FROM C1
);

This works:

WITH
C1 AS (
    SELECT
        (UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
    FROM events
    WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;

>>> 3.7833


WITH
C1 AS (
    SELECT
        (UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
    FROM events
    WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;

>>> 3.7833

This doesn't:

SELECT fx_timegap('2023-02-06');

>>> 3.78


SELECT fx_timegap('2023-02-06');

>>> NULL

Answer 1

This problems see in: https://bugs.mysql.com/bug.php?id=110983 https://bugs.mysql.com/bug.php?id=110847

If you have more information, please write.

Answer 2

Return a value, not a SELECT.

DELIMITER //
CREATE FUNCTION fx_timegap (_dt DATE)
RETURNS DECIMAL(4,2) NOT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _res DECIMAL(4,2);
    WITH C1 AS (
        SELECT
            (UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) -
             UNIX_TIMESTAMP(end)) / 3600 AS gap
        FROM se326488
        WHERE DATE(start) = _dt
    )
    SELECT SUM(gap)  INTO _res  FROM C1;
    RETURN _res;
END //
DELIMITER ;

SELECT fx_timegap('2023-02-06');
SELECT fx_timegap('2023-02-06');