join two columns with different sized arrays

Question

I have a table, as a result of query:

SELECT o.id AS order_id,
       arrayMap(i -> i, range(1, o.quantity)) AS virtual_sku,
       s.sku AS sku
  FROM orders o
  LEFT JOIN (SELECT order_id,
                    groupArray(sku) AS sku
               FROM storage
              GROUP BY 
                    order) s
    ON o.id = s.order_id

order_id	virtual_sku	sku
41273	[1,2,3]	[12478431567,12478431568]

In this example I have order 41273 with quantity 3 and two items from this order, received at the storage with unique sku.

I want to make this table

order	v_sku	_sku
41273	1	12478431567
41273	2	12478431568
41273	3

I try

SELECT o.id AS order_id,
       virtual_sku,
       sku
 ARRAY JOIN
       virtual_sku AS v_sku,
       sku as _sku

How can i avoid "Sizes of ARRAY-JOIN-ed arrays do not match" error?

Answer 1

It can be used enable_unaligned_array_join setting:

SELECT *
FROM
(
    SELECT
        41273 AS order_id,
        [1, 2, 3] AS virtual_sku,
        [12478431567, 12478431568] AS sku
)
ARRAY JOIN
    virtual_sku,
    sku
SETTINGS enable_unaligned_array_join = 1

/*
┌─order_id─┬─virtual_sku─┬─────────sku─┐
│    41273 │           1 │ 12478431567 │
│    41273 │           2 │ 12478431568 │
│    41273 │           3 │           0 │
└──────────┴─────────────┴─────────────┘
*/