unexpected behaviour with dates

Question

I am trying to list all the months between two dates in Mysql and ended up with the following:

WITH RECURSIVE dates AS ( 
    SELECT start_date as period, end_date 
    FROM progetti WHERE id = 1 
    UNION ALL 
    SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date FROM dates WHERE period < end_date ) 
SELECT * from dates; 

That returns:

period	end date
2022-01-01	2022-12-31
2022-02-01	2022-12-31
2022-03-01	2022-12-31
2022-04-01	2022-12-31
2022-05-01	2022-12-31
2022-06-01	2022-12-31
2022-07-01	2022-12-31
2022-08-01	2022-12-31
2022-09-01	2022-12-31
2022-10-01	2022-12-31
2022-11-01	2022-12-31
2022-12-01	2022-12-31
2023-01-01	2022-12-31

Why do I also get the last row? isn't 2023-01-01 greater than 2022-12-31? The same happens with any set of dates. I always get one record more.

Answer 1

The last row is a selection from pre-last row where 2022-12-01 is less than 2022-12-31.

If day component in start_date may differ from 01 then you must use complex query:

WITH RECURSIVE dates AS ( 
    SELECT start_date, start_date as period, end_date, 1 as rn
    FROM progetti 
    WHERE id = 1 
  UNION ALL 
    SELECT start_date, start_date + INTERVAL rn MONTH, end_date, rn + 1
    FROM dates 
    WHERE period <= end_date - INTERVAL 1 MONTH) 
SELECT period, end_date 
FROM dates;

This avoids day deviation (for example, if start_date = '2022-01-31 then all next rows will contain 28 in day component of the date).

https://dbfiddle.uk/eViMoI9c