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
start
field unique or are there any duplicates?