MySQL 8 Recursive CTE Create row for each item

Question

I would like to create a data return from a non existing table:

+-------+--------+------------+
| type  | name   | expiration |
+-------+--------+------------+
| fruit | orange | 1999-12-31 |
| fruit | banana | 1999-12-31 |
| fruit | apple  | 1999-12-31 |
| fruit | orange | 2000-01-01 |
| fruit | banana | 2000-01-01 |
| fruit | apple  | 2000-01-01 |
+-------+--------+------------+

Where for each fruit there is a single row with the same date. Then the date is incremented by one day and for that date there is created a row for each fruit.

So far I'm having this query:

WITH RECURSIVE cte  
AS (
      SELECT 
          "fruit" as `type`
          ,"orange" as `name`
          ,"1999-12-31" as `expiration`
      UNION ALL
      SELECT 
          "fruit" as `type`
          ,"banana" as `name`
          ,date_add(`expiration`, INTERVAL 1 DAY) as `expiration`
      FROM cte 
      WHERE `expiration` < "2000-01-01"
    )
SELECT *
FROM cte
;

that generates:

+-------+--------+------------+
| type  | name   | expiration |
+-------+--------+------------+
| fruit | orange | 1999-12-31 |
| fruit | banana | 2000-01-01 |
+-------+--------+------------+

I think there could be solution to that problem by doing inside of the recursive CTE select from temporary fruit_list table that has fruits' names but I don't know how to implement that.

The example fruit_list table:

CREATE TEMPORARY TABLE IF NOT EXISTS `fruit_list` (
    `name` varchar(128) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `fruit_list` VALUES
("orange")
,("banana")
,("apple")
;

I would like to solve the problem with a regular query instead of procedure. Is it possible?

The aim of the solution it to have a query that can return some test data for each fruit and date range.

score 1 · Accepted Answer · answered Oct 26 '19 at 17:16

You could handle this via a series of cross joins:

SELECT
    f.type,
    n.name,
    e.expiration
FROM (SELECT 'fruit' AS type) f
CROSS JOIN
(
    SELECT 'orange' AS name UNION ALL
    SELECT 'banana' UNION ALL
    SELECT 'apple'
) n
CROSS JOIN
(
    SELECT '1999-12-31' AS expiration UNION ALL
    SELECT '2000-01-01'
) e
ORDER BY
    f.type,
    e.expiration,
    n.name;

Demo

score 0 · Answer 2 · answered Oct 26 '19 at 20:00

For those who would like to use temporary table here's the code:

CREATE TEMPORARY TABLE IF NOT EXISTS `fruit_list` (
    `name` varchar(128) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `fruit_list` VALUES
("orange")
,("banana")
,("apple")
;

WITH RECURSIVE cte  
AS (
      SELECT 
        "1999-12-30" as `expiration`
      UNION ALL
      SELECT 
          date_add(`expiration`, INTERVAL 1 DAY) as `expiration`
      FROM cte 
      WHERE `expiration` < "2000-01-02"
    )
,cte1 as (
    SELECT * FROM cte
    CROSS JOIN `fruit_list`
)
SELECT     
    "fruit" as `type`
    ,`name`
    ,`expiration`
FROM cte1
ORDER BY
    `expiration`
    ,`name`
;

Result:

+-------+--------+------------+
| type  | name   | expiration |
+-------+--------+------------+
| fruit | apple  | 1999-12-30 |
| fruit | banana | 1999-12-30 |
| fruit | orange | 1999-12-30 |
| fruit | apple  | 1999-12-31 |
| fruit | banana | 1999-12-31 |
| fruit | orange | 1999-12-31 |
| fruit | apple  | 2000-01-01 |
| fruit | banana | 2000-01-01 |
| fruit | orange | 2000-01-01 |
| fruit | apple  | 2000-01-02 |
| fruit | banana | 2000-01-02 |
| fruit | orange | 2000-01-02 |
+-------+--------+------------+
12 rows in set (0.00 sec)

MySQL 8 Recursive CTE Create row for each item

2 Answers2

Demo