How to expand the dates in Clickhouse?

Question

I have the following table in ClickHouse:

valid_from	valid_until
2020-01-01	2020-01-03
2020-01-02	2020-01-03

I want to expand each row of the table into rows with the dates in between:

valid_from	valid_until	expanded_dates
2020-01-01	2020-01-03	2020-01-01
2020-01-01	2020-01-03	2020-01-02
2020-01-01	2020-01-03	2020-01-03
2020-01-02	2020-01-03	2020-01-02
2020-01-02	2020-01-03	2020-01-03

In MariaDB I used this code and it worked, but it does not work in ClickHouse:

  CREATE OR REPLACE VIEW exp_dates_view AS
  WITH RECURSIVE dates AS (
    SELECT
      valid_from,
      CASE
        WHEN valid_until IS NULL THEN CURDATE()
        ELSE valid_until
      END AS valid_until, valid_from AS expanded_dates
    FROM my_table
    UNION ALL
    SELECT valid_from, valid_until, expanded_dates + INTERVAL '1' DAY
    FROM dates
    WHERE expanded_dates < valid_until
  )
  SELECT valid_from, valid_until, expanded_dates
  FROM dates;

How can I achieve this in ClickHouse?

Answer 1

It's a bit easier in ClickHouse with arrayJoin:

SELECT
    valid_from,
    valid_until,
    arrayJoin(
        arrayMap(
            x -> toDate(x),
            range(toInt32(valid_from), toInt32(valid_until) + 1)
        )
    ) AS expanded
FROM my_table

┌─valid_from─┬─valid_until─┬───expanded─┐
│ 2020-01-01 │  2020-01-03 │ 2020-01-01 │
│ 2020-01-01 │  2020-01-03 │ 2020-01-02 │
│ 2020-01-01 │  2020-01-03 │ 2020-01-03 │
│ 2020-01-02 │  2020-01-03 │ 2020-01-02 │
│ 2020-01-02 │  2020-01-03 │ 2020-01-03 │
└────────────┴─────────────┴────────────┘