MySQL Recursive CTE table does not exist

Question

I'm learning about recursive functions, Since I need to extract a row for each day in a range of days. This is my current data

+----+------------+------------+
| id |    from    |     to     |
+----+------------+------------+
|  1 | 09-20-2019 | 09-25-2019 |
+----+------------+------------+

The goal is to receive my data as follows

+----+------------+
| id |    date    |
+----+------------+
|  1 | 09-20-2019 |
|  1 | 09-21-2019 |
|  1 | 09-22-2019 |
|  1 | 09-23-2019 |
|  1 | 09-24-2019 |
|  1 | 09-25-2019 |
+----+------------+

I'm following an example seen here: https://stackoverflow.com/a/54538866/1731057 But for some reason my recursive function is looking for the 'cte' table.

Query 1 ERROR: Table 'project.cte' doesn't exist

WITH cte AS (
    SELECT date_from
    FROM event_dates
  UNION ALL
    SELECT DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) 
    FROM cte
    WHERE DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) <= event_dates.date_until
)
select * FROM cte;

Before learniing about recursive functions, learn about data types. — Strawberry, Jul 29 '19 at 14:09

Tim Biegeleisen · Answer 1 · 2019-07-29T13:57:15.717

3

The structure of your recursive CTE is off, and the upper half of the union should be a seed base case. Then, the recursive part should add one day to the previous incoming value:

WITH RECURSIVE cte (n, dt) AS (
    SELECT 1, '2019-09-20'
    UNION ALL
    SELECT n + 1, TIMESTAMPADD(DAY, n, '2019-09-20') FROM cte WHERE n <= 5
)

SELECT * FROM cte;

Demo

Of note, we use TIMESTAMPADD() here to get around the problem of the INTERVAL expression, which can't really take a variable.

If you want to use this approach to generate a series of dates which matches the from and to values in your table, then you can try a join:

SELECT
    t1.dt
FROM cte t1
INNER JOIN yourTable t2
    ON t1.dt BETWEEN t2.from_date AND t2.to_date;

When used this way, the recursive CTE is acting as a calendar table.

edited Jul 29 '19 at 13:57

answered Jul 29 '19 at 13:39

Tim Biegeleisen

451,927
24
239
318

Thanks for your time, when I replace your hardcoded '2019-09-20' with 'event_dates.date_from' I get "Unknown table 'event_dates' in field list". Any idea how I can make this variable? – Miguel Stevens Jul 29 '19 at 13:47
You can't really do exactly what you have in mind. The above recursive CTE is intended to be used with a constant date starting point (and an ending point in mind). _But_, we could generate a calendar table using my answer, and then left join to your table. – Tim Biegeleisen Jul 29 '19 at 13:48
Could you explain how to do that? – Miguel Stevens Jul 29 '19 at 13:54
Thanks for the addition, this still leaves 2 dates hardcoded in the cte() function? – Miguel Stevens Jul 29 '19 at 14:11
Yes, it does. The idea here is that you generate a date range with the CTE which is large enough to cover your entire table. – Tim Biegeleisen Jul 29 '19 at 14:13
So with this approach I would have to change the hardcoded dates every few years, too bad mysql doesn't have a more elegant solution for this, thanks for your insights! – Miguel Stevens Jul 29 '19 at 14:18

MySQL Recursive CTE table does not exist

1 Answers1

Demo

Linked