1

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
8

2 Answers 2

0

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.

2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 11 at 11:35
  • So, it's a bug in the server, confirmed by the developers. I have accepted your answer. Thank you.
    – Dmitriy
    May 21 at 16:05
0

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');
2
  • No difference. SELECT returns the rowset which can be converted to scalar value.
    – Akina
    Apr 27 at 16:14
  • @Akina - Well, it worked for me, so I don't know what their problem is.
    – Rick James
    Apr 27 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.