Presto: sequence() -> array<date> does not behave consistently with last day of the month

[cps333]

sequenceonly generates a series of mont end dates if the starting month is a 31-day month.

For example,

SELECT * FROM UNNEST(
    SEQUENCE(
        DATE '2018-05-31',
        DATE '2018-01-01', 
        INTERVAL '-1' MONTH
    )
)

returns

2018-04-30
2018-03-31
2018-02-28
2018-01-31

However, when

SELECT * FROM UNNEST(
    SEQUENCE(
        DATE '2018-06-30',
        DATE '2018-01-01', 
        INTERVAL '-1' MONTH
    )
)

returns

2018-06-30
2018-05-30
2018-04-30
2018-03-30
2018-02-28
2018-01-30

[electrum]

We get the same behavior going forwards:

SELECT * FROM UNNEST(
    SEQUENCE(
        DATE '2018-01-31', 
        DATE '2018-07-31',
        INTERVAL '1' MONTH
    )
)

which returns:

------------
 2018-01-31 
 2018-02-28 
 2018-03-31 
 2018-04-30 
 2018-05-31 
 2018-06-30 
 2018-07-31 

[electrum]

This behavior is consistent with year-to-month interval math:

select date '2018-01-31' + interval '1' month;

which returns:

------------
 2018-02-28 

This should be covered by the SQL standard and hopefully our implementation is correct. I tried the above in PostgreSQL and received the same answer.

[findepi]

@cps333 if you need a sequence of month end-dates but the starting month is not 31-day (e.g June), you can generate sequence of month start-days (starting with a July in our example) and then subtract 1 day.