WITH RECURSIVE limited to 1001 records?

Hi guys,

I need to generate a list of dates from A to B. In the example below I use the range 2001-01-01 --> '2020-01-01'.

Here below the query I wrote:

WITH RECURSIVE dates_array as (
    select '2001-01-01' as this_day
  UNION
    SELECT DATE_ADD(dates_array.this_day, INTERVAL 1 DAY) FROM dates_array WHERE DATE_ADD(dates_array.this_day, INTERVAL 1 DAY) <= '2020-01-01'
) 
select this_day FROM dates_array;

When I run it, it shows 1001 records and then it stops. I tried to change the dates range but it always stops at the 1001st record. Is it a software limit? Is there any way I can increase it to 100.000 records instead?

+------------+
| this_day   |
+------------+
| 2001-01-01 |
| 2001-01-02 |
| 2001-01-03 |

[....]

| 2003-09-26 |
| 2003-09-27 |
| 2003-09-28 |
+------------+
1001 rows in set (0.004 sec)

Thanks, Dario Vanin

Answer Answered by Ian Gilfillan in this comment.

You don't specify your version, but since MariaDB 10.6, max_recursive_iterations has defaulted to 1000. It can be increased up to 4294967295.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.