BigQuery - Duplicate rows x time using recursive method

Question

I tried duplicating all rows in a table using the recursive method.

I have this base table

base
+------+
| rn   |
+------+
| 1    |
| 2    |
| 3    |
+------+

Then, I want to replicate each of rows 3 times so the expected output would be like (9 rows)

+------+-------------+
| rn   | iteration   |
+------+-------------+
| 1    |     1       |
| 2    |     1       |
| 3    |     1       |
| 1    |     2       |
| 2    |     2       |
| 3    |     2       |
| 1    |     3       |
| 2    |     3       |
| 3    |     3       |
+------+-------------+

I want to use recursive method

WITH recursive test AS (
    SELECT
      *,
      1 AS iteration
    FROM `base`

    UNION ALL

    SELECT
      a.*,
      b.iteration + 1 AS iteration
    FROM `base` a
    JOIN test b ON b.iteration < 3
)
SELECT *
FROM test
ORDER BY 2,1

But the result is not what I expected, it would produce 39 rows instead of 9 rows, it seems it's because of the JOIN that using cross join. Is there any way to fix the query to produce the expected value?

Thanks in advance!

Answer 1

Instead of RECURSIVE CTE, you can consider below

WITH base AS (
  SELECT * FROM UNNEST([1, 2, 3]) rn
)
SELECT * 
  FROM base, UNNEST(GENERATE_ARRAY(1, 3)) iteration;

Query results

And for recursive approach, you need to add another join condition a.rn = b.rn.

WITH RECURSIVE base AS (
  SELECT * FROM UNNEST([1, 2, 3]) rn
),
test AS (
  SELECT *, 1 AS iteration FROM base
   UNION ALL
  SELECT b.*, a.iteration + 1 
    FROM test a 
    JOIN `base` b ON a.rn = b.rn AND a.iteration < 3
)
SELECT * FROM test ORDER BY 2,1;